[
https://issues.apache.org/jira/browse/CALCITE-6218?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17809741#comment-17809741
]
Guillaume Massé edited comment on CALCITE-6218 at 1/23/24 5:34 AM:
-------------------------------------------------------------------
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]
Input
{code:java}
SELECT a, t.ccol
FROM complex
LATERAL VIEW explode(complex.c) t as ccol{code}
We can see that the input RelNode it generates is similar to what we have as
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}
Output
{code:java}
SELECT complex.a, t0.ccol
FROM default.complex complex
LATERAL VIEW EXPLODE(complex.c) t0 AS ccol {code}
was (Author: masseguillaume):
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 input RelNode it generates is similar to what we have as
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<BIGINT>,
> // ...
> ) {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)
