This is an automated email from the ASF dual-hosted git repository.
xiong pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new d336724daa [CALCITE-6785] RelToSqlConverter generate wrong sql when
UNNEST has a correlate variable
d336724daa is described below
commit d336724daacbc68b7823f4c02b14f890b052f4f0
Author: Xiong Duan <[email protected]>
AuthorDate: Sun Jan 19 09:32:47 2025 +0800
[CALCITE-6785] RelToSqlConverter generate wrong sql when UNNEST has a
correlate variable
---
.../calcite/rel/rel2sql/RelToSqlConverter.java | 30 +++++++++++++++++-----
.../org/apache/calcite/sql/SqlLateralOperator.java | 2 +-
.../rel/rel2sql/RelToSqlConverterStructsTest.java | 2 +-
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 22 ++++++++++++++++
.../java/org/apache/calcite/test/PigRelOpTest.java | 6 ++---
5 files changed, 50 insertions(+), 12 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
b/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
index 353e2fa3a4..b171d88d03 100644
--- a/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
+++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
@@ -405,13 +405,29 @@ public Result visit(Correlate e) {
.resetAlias(e.getCorrelVariable(), e.getInput(0).getRowType());
parseCorrelTable(e, leftResult);
final Result rightResult = visitInput(e, 1);
- final SqlNode rightLateral =
- SqlStdOperatorTable.LATERAL.createCall(POS, rightResult.node);
- final SqlNode rightLateralAs =
- SqlStdOperatorTable.AS.createCall(POS, rightLateral,
- new SqlIdentifier(
- requireNonNull(rightResult.neededAlias,
- () -> "rightResult.neededAlias is null, node is " +
rightResult.node), POS));
+ final SqlNode rightResultNode = rightResult.node;
+ final SqlIdentifier id =
+ new SqlIdentifier(
+ requireNonNull(rightResult.neededAlias,
+ () -> "rightResult.neededAlias is null, node is " +
rightResultNode), POS);
+ SqlNode rightLateral =
+ SqlStdOperatorTable.LATERAL.createCall(POS, rightResultNode);
+ SqlNode rightLateralAs;
+ if (rightResultNode.getKind() == SqlKind.AS) {
+ // If node already is an AS node, we need to replace the alias
+ // For example:
+ // Before: AS "t1" ("xs") AS "t10"
+ // Now：AS "t10" ("xs")
+ SqlCall sqlRightCall = (SqlCall) rightResultNode;
+ List<SqlNode> operands = new ArrayList<>(sqlRightCall.getOperandList());
+ rightLateral =
+ SqlStdOperatorTable.LATERAL.createCall(POS, operands.get(0));
+ operands.set(0, rightLateral);
+ operands.set(1, id);
+ rightLateralAs = SqlStdOperatorTable.AS.createCall(POS, operands);
+ } else {
+ rightLateralAs = SqlStdOperatorTable.AS.createCall(POS, rightLateral,
id);
+ }
final SqlNode join =
new SqlJoin(POS,
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlLateralOperator.java
b/core/src/main/java/org/apache/calcite/sql/SqlLateralOperator.java
index f515e5d335..437343a7fc 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlLateralOperator.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlLateralOperator.java
@@ -39,7 +39,7 @@ public SqlLateralOperator(SqlKind kind) {
@Override public void unparse(SqlWriter writer, SqlCall call, int leftPrec,
int rightPrec) {
final Set<SqlKind> specialOperandKinds =
- ImmutableSet.of(SqlKind.COLLECTION_TABLE, SqlKind.SELECT, SqlKind.AS);
+ ImmutableSet.of(SqlKind.COLLECTION_TABLE, SqlKind.SELECT, SqlKind.AS,
SqlKind.UNNEST);
if (call.operandCount() == 1
&& specialOperandKinds.contains(call.operand(0).getKind())) {
// Do not create ( ) around the following TABLE clause.
diff --git
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterStructsTest.java
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterStructsTest.java
index ccc422da83..ee7f6cae47 100644
---
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterStructsTest.java
+++
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterStructsTest.java
@@ -81,7 +81,7 @@ private RelToSqlConverterTest.Sql sql(String sql) {
final String expected = "SELECT \"$cor0\".\"a\", \"t10\".\"xs\" AS \"x\"\n"
+ "FROM (SELECT \"a\", \"n1\".\"n11\".\"b\", \"n1\".\"n12\".\"c\",
\"n2\".\"d\", \"xs\", \"e\"\n"
+ "FROM \"myDb\".\"myTable\") AS \"$cor0\",\nLATERAL UNNEST (SELECT
\"$cor0\".\"xs\"\n"
- + "FROM (VALUES (0)) AS \"t\" (\"ZERO\")) AS \"t1\" (\"xs\") AS
\"t10\"";
+ + "FROM (VALUES (0)) AS \"t\" (\"ZERO\")) AS \"t10\" (\"xs\")";
sql(query).schema(CalciteAssert.SchemaSpec.MY_DB).ok(expected);
}
}
diff --git
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index de3e11382a..6b82317b7d 100644
---
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -1739,6 +1739,28 @@ private static String toSql(RelNode root, SqlDialect
dialect,
assertThat(toSql(root), isLinux(expectedSql));
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6785";>[CALCITE-6785]
+ * RelToSqlConverter generate wrong sql when UNNEST has a correlate
variable</a>. */
+ @Test void testUnnestWithCorrelate() {
+ final String sql = "SELECT\n"
+ + " \"department_id\",\n"
+ + " SPLIT (\"department_description\", ','),\n"
+ + " UNNESTVALUES AS UNNESTALIAS\n"
+ + "FROM\n"
+ + " \"foodmart\".\"department\",\n"
+ + " UNNEST(SPLIT (\"department_description\", ',')) AS
UNNESTVALUES";
+
+ final String expected = "SELECT \"$cor0\".\"department_id\", "
+ + "SPLIT(\"$cor0\".\"department_description\", ','), \"t10\".\"col_0\"
AS \"UNNESTALIAS\"\n"
+ + "FROM (SELECT \"department_id\", \"department_description\", "
+ + "SPLIT(\"department_description\", ',') AS \"$f2\"\n"
+ + "FROM \"foodmart\".\"department\") AS \"$cor0\",\n"
+ + "LATERAL UNNEST (SELECT \"$cor0\".\"$f2\"\n"
+ + "FROM (VALUES (0)) AS \"t\" (\"ZERO\")) AS \"t10\" (\"col_0\")";
+ sql(sql).withLibrary(SqlLibrary.BIG_QUERY).ok(expected);
+ }
+
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-5395";>[CALCITE-5395]
* RelToSql converter fails when SELECT * is under a semi-join node</a>. */
diff --git a/piglet/src/test/java/org/apache/calcite/test/PigRelOpTest.java
b/piglet/src/test/java/org/apache/calcite/test/PigRelOpTest.java
index 26905b3fe8..9c982a82ed 100644
--- a/piglet/src/test/java/org/apache/calcite/test/PigRelOpTest.java
+++ b/piglet/src/test/java/org/apache/calcite/test/PigRelOpTest.java
@@ -392,8 +392,8 @@ private Fluent pig(String script) {
+ " WHERE JOB <> 'CLERK'\n"
+ " GROUP BY DEPTNO, JOB) AS $cor1,\n"
+ " LATERAL UNNEST (SELECT $cor1.$f2 AS $f0\n"
- + " FROM (VALUES (0)) AS t (ZERO)) AS t3 (EMPNO, ENAME, JOB,"
- + " MGR, HIREDATE, SAL, COMM, DEPTNO) AS t30\n"
+ + " FROM (VALUES (0)) AS t (ZERO)) AS t30 (EMPNO, ENAME, JOB,"
+ + " MGR, HIREDATE, SAL, COMM, DEPTNO)\n"
+ "ORDER BY $cor1.DEPTNO, $cor1.JOB";
pig(script).assertRel(hasTree(plan))
.assertSql(is(sql));
@@ -488,7 +488,7 @@ private Fluent pig(String script) {
+ " ORDER BY SAL) AS t6\n"
+ " GROUP BY $f0) AS t8) AS $cor5,\n"
+ " LATERAL UNNEST (SELECT $cor5.X AS $f0\n"
- + " FROM (VALUES (0)) AS t (ZERO)) AS t11 (ENAME, JOB, DEPTNO, SAL)
AS t110\n"
+ + " FROM (VALUES (0)) AS t (ZERO)) AS t110 (ENAME, JOB, DEPTNO,
SAL)\n"
+ "ORDER BY $cor5.group";
pig(script).assertRel(hasTree(plan))
.assertResult(is(result))
