[CALCITE-6218] RelToSqlConverter fails to convert correlated lateral joins

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.36.0
• Fix Version/s: 1.38.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Unassigned
• Reporter: Guillaume Massé
• Created: 22/Jan/24 20:26
• Updated: 17/Oct/24 22:41
• Resolved: 17/Oct/24 22:41

Description

 

input query:

SELECT 
  "a", "x"
FROM 
  "myDb"."myTable",
  unnest("xs") as "x"; 

schema:

https://github.com/MasseGuillaume/calcite/blob/0126e6cfa47061886b2012ad2d2c32408455ae88/testkit/src/main/java/org/apache/calcite/test/CalciteAssert.java#L2180-L2211

myTable(
    a: BIGINT,
    // ...
    xs: ARRAY<BIGINT>,
    // ...
) 

logical plan:

LogicalProject(a=[$0], x=[$6])
  LogicalCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{4}])
    LogicalProject(a=[$0], b=[$1.n11.b], c=[$1.n12.c], d=[$2.d], xs=[$3], e=[$4])
      LogicalTableScan(table=[[myDb, myTable]])
    Uncollect
      LogicalProject(xs=[$cor0.xs])
        LogicalValues(tuples=[[{ 0 }]])

obtained sql:

 

SELECT
  "$cor0"."a",
  "$cor0"."xs0" AS "x" -- <-- xs0 ?
FROM 
  (
    SELECT "a", "n1"."n11"."b", "n1"."n12"."c", "n2"."d", "xs", "e" FROM "myDb"."myTable"
  ) AS "$cor0",
  LATERAL UNNEST (
   SELECT "$cor0"."xs" FROM (VALUES (0)) AS "t" ("ZERO")
  ) AS "t1" ("xs") AS "t10"

I would expect the query to be converted to something close to the original query. Here "xs0" does not exists.

 

https://github.com/MasseGuillaume/calcite/commit/0126e6cfa47061886b2012ad2d2c32408455ae88

Issue Links

• links to: GitHub Pull Request #3656
• links to: GitHub Pull Request #3856