LATERAL join bugs

[max-hoffman]

Several bugs trying to run a nested lateral join. One of the issues is that ExecBuilder.buildSubqueryAlias should pass through the input row:

--- a/sql/rowexec/rel.go
+++ b/sql/rowexec/rel.go
@@ -729,7 +729,7 @@ func (b *BaseBuilder) buildSetOp(ctx *sql.Context, s *plan.SetOp, row sql.Row) (
 func (b *BaseBuilder) buildSubqueryAlias(ctx *sql.Context, n *plan.SubqueryAlias, row sql.Row) (sql.RowIter, error) {
        span, ctx := ctx.Span("plan.SubqueryAlias")

-       if !n.OuterScopeVisibility {
+       if !n.OuterScopeVisibility && !n.IsLateral {
                row = nil
        }

Another problem is maybe that MySQL considers the first projection special if it references across the LATERAL join, rather than the default of rolling up all of the lateral projections as output. I am not sure we do this.

Setup

Create table students (
  Id int primary key,
  Name varchar(50),
  Major int
);
Create table classes (
  Id int primary key,
  Name varchar(50),
  Department int
);
Create table grades (
  Grade float,
  Student int,
  Class int,
  Primary key (class, student)
);
Create table majors (
  Id int,
  Name varchar(50),
  Department int,
  Primary key (name, department)
);
Create table departments (
  Id int primary key,
  Name varchar(50)
);
Insert into students values (1, 'Elle', 4), (2, 'Latham', 2);
Insert into classes values
  (1, 'Corporate Finance', 1),
  (2, 'ESG Studies', 1),
  (3, 'Late Bronze Age Collapse', 2),
  (4, 'Greek Mythology', 2);
Insert into majors values
  (1, 'Roman Studies', 2),
  (2, 'Bronze Age Studies', 2),
  (3, 'Accounting', 1),
  (4, 'Finance', 1);
Insert into departments values
  (1, 'Business'),
  (2, 'History');
Insert into grades values 
  (94, 1, 1),
  (97, 1, 2),
  (85, 2, 3),
  (92, 2, 4);

Expected result:

+--------+-----------------+-----------+
| Name   | class_name      | max_grade |
+--------+-----------------+-----------+
| Latham | ESG Studies     | 97        |
| Elle   | Greek Mythology | 92        |
+--------+-----------------+-----------+

Query that fails to resolve the column name name (students.name):

  Select name, class.class_name, grade.max_grade
  -- (1) get student's name, id, and major
  from students,
  -- (2) get the student's major department
  LATERAL (
    Select departments.id as did
    from majors join departments
    On majors.department = departments.id
    where majors.id = students.major
  ) dept,
  -- (3) get student's best performing class within their major's department
  LATERAL (
    Select
      grade as max_grade,
      classes.id as cid
    From grades
    Join classes
      On grades.class = classes.id
    Where grades.student = students.id and
          classes.department = dept.did
    Order by grade desc limit 1
  ) grade,
  -- (4) get name for class_id
  LATERAL (
    Select name as class_name from classes where grade.cid = classes.id
  ) class
column "name" could not be found in any table in scope

Substituting qualified students.name has an indexing error:

  Select students.name, class.class_name, grade.max_grade
  -- (1) get student's name, id, and major
  from students,
  -- (2) get the student's major department
  LATERAL (
    Select departments.id as did
    from majors join departments
    On majors.department = departments.id
    where majors.id = students.major
  ) dept,
  -- (3) get student's best performing class within their major's department
  LATERAL (
    Select
      grade as max_grade,
      classes.id as cid
    From grades
    Join classes
      On grades.class = classes.id
    Where grades.student = students.id and
          classes.department = dept.did
    Order by grade desc limit 1
  ) grade,
  -- (4) get name for class_id
  LATERAL (
    Select name as class_name from classes where grade.cid = classes.id
  ) class
unable to find field with index -1 in row of 4 columns