[jira] [Commented] (CALCITE-6218) RelToSqlConverter fails to convert correlated lateral joins
[ https://issues.apache.org/jira/browse/CALCITE-6218?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17811644#comment-17811644 ] EveyWu commented on CALCITE-6218: -- [~MasseGuillaume] I submitted a PR to solve the problem of "xs0" not exists。 I also think there can be more improvements here,correlated lateral join can be handled directly instead of a lot of rewrites. > RelToSqlConverter fails to convert correlated lateral joins > --- > > Key: CALCITE-6218 > URL: https://issues.apache.org/jira/browse/CALCITE-6218 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.36.0 >Reporter: Guillaume Massé >Priority: Major > Labels: pull-request-available > > > input query: > {code:sql} > SELECT > "a", "x" > FROM > "myDb"."myTable", > unnest("xs") as "x"; {code} > schema: > [https://github.com/MasseGuillaume/calcite/blob/0126e6cfa47061886b2012ad2d2c32408455ae88/testkit/src/main/java/org/apache/calcite/test/CalciteAssert.java#L2180-L2211] > {code:java} > myTable( > a: BIGINT, > // ... > xs: ARRAY, > // ... > ) > {code} > logical plan: > {code:java} > 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 }]]) > {code} > obtained sql: > > {code: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" > {code} > 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] -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6218) RelToSqlConverter fails to convert correlated lateral joins
[ https://issues.apache.org/jira/browse/CALCITE-6218?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17810565#comment-17810565 ] Guillaume Massé commented on CALCITE-6218: -- I'm currently comparing coral execution vs calcite execution of RelToSqlConverter. There is a small difference in the RelNode I get that does not show when you use `RelOptUtil.dumpPlan`: when validating the node, the frontend did not set the rowType property for Correlate, Uncollect and it's child input LogicalProject.: {code:java} LogicalProject(a=[$0], x=[$6]) LogicalCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{4}]) // < rowType missing LogicalProject(a=[$0], b=[$1.n11.b], c=[$1.n12.c], d=[$2.d], xs=[$3], e=[$4]) LogicalTableScan(table=[[myDb, myTable]]) Uncollect // < rowType missing LogicalProject(xs=[$cor0.xs]) // < rowType missing LogicalValues(tuples=[[{ 0 }]]) {code} I will dig deeper to see if there is an impact on the output SQL. > RelToSqlConverter fails to convert correlated lateral joins > --- > > Key: CALCITE-6218 > URL: https://issues.apache.org/jira/browse/CALCITE-6218 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.36.0 >Reporter: Guillaume Massé >Priority: Major > > > input query: > {code:sql} > SELECT > "a", "x" > FROM > "myDb"."myTable", > unnest("xs") as "x"; {code} > schema: > [https://github.com/MasseGuillaume/calcite/blob/0126e6cfa47061886b2012ad2d2c32408455ae88/testkit/src/main/java/org/apache/calcite/test/CalciteAssert.java#L2180-L2211] > {code:java} > myTable( > a: BIGINT, > // ... > xs: ARRAY, > // ... > ) > {code} > logical plan: > {code:java} > 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 }]]) > {code} > obtained sql: > > {code: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" > {code} > 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] -- This message was sent by Atlassian Jira (v8.20.10#820010)
[jira] [Commented] (CALCITE-6218) RelToSqlConverter fails to convert correlated lateral joins
[ https://issues.apache.org/jira/browse/CALCITE-6218?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17809741#comment-17809741 ] Guillaume Massé commented on CALCITE-6218: -- It looks like Linkedin's Coral has a solution for this problem: [https://github.com/linkedin/coral/blob/26cb90c0c484156110d052001ebd0170714a9bdf/coral-hive/src/main/java/com/linkedin/coral/transformers/CoralRelToSqlNodeConverter.java#L241-L243] [https://github.com/linkedin/coral/blob/master/coral-spark/src/main/java/com/linkedin/coral/spark/CoralSqlNodeToSparkSqlNodeConverter.java] [https://github.com/linkedin/coral/blob/master/coral-spark/src/main/java/com/linkedin/coral/spark/functions/SqlLateralViewAsOperator.java] There is also test for it: [https://github.com/linkedin/coral/blob/26cb90c0c484156110d052001ebd0170714a9bdf/coral-spark/src/test/java/com/linkedin/coral/spark/CoralSparkTest.java#L208-L215] We can see that the RelNode it generates is similar to what we have has an input: {code:java} LogicalProject(a=[$0], ccol=[$6]) LogicalCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{2}]) LogicalTableScan(table=[[hive, default, complex]]) HiveUncollect LogicalProject(col=[$cor0.c]) LogicalValues(type=[RecordType(INTEGER ZERO)], tuples=[[{ 0 }]]) {code} > RelToSqlConverter fails to convert correlated lateral joins > --- > > Key: CALCITE-6218 > URL: https://issues.apache.org/jira/browse/CALCITE-6218 > Project: Calcite > Issue Type: Bug > Components: core >Affects Versions: 1.36.0 >Reporter: Guillaume Massé >Priority: Major > > > input query: > {code:java} > SELECT > "a", "x" > FROM > "myDb"."myTable", > unnest("xs") as "x"; {code} > schema: > [https://github.com/MasseGuillaume/calcite/blob/0126e6cfa47061886b2012ad2d2c32408455ae88/testkit/src/main/java/org/apache/calcite/test/CalciteAssert.java#L2180-L2211] > {code:java} > myTable( > a: BIGINT, > // ... > xs: ARRAY, > // ... > ) {code} > logical plan: > {code:java} > 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 }]]) {code} > obtained sql: > > {code:java} > 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" > {code} > 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] -- This message was sent by Atlassian Jira (v8.20.10#820010)