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 f4ba90ba2c [CALCITE-6838] RelToSqlConverter should generate double
parentheses when the input to Unnest is a query statement
f4ba90ba2c is described below
commit f4ba90ba2c3ea199652ba627589bfd4a310d5094
Author: Xiong Duan <[email protected]>
AuthorDate: Sat Feb 15 16:06:14 2025 +0800
[CALCITE-6838] RelToSqlConverter should generate double parentheses when
the input to Unnest is a query statement
---
.../org/apache/calcite/sql/SqlUnnestOperator.java | 9 +----
.../rel/rel2sql/RelToSqlConverterStructsTest.java | 4 +--
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 40 ++++++++++++----------
.../java/org/apache/calcite/test/PigRelOpTest.java | 12 +++----
4 files changed, 31 insertions(+), 34 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlUnnestOperator.java
b/core/src/main/java/org/apache/calcite/sql/SqlUnnestOperator.java
index 182ad78374..bd6023936c 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlUnnestOperator.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlUnnestOperator.java
@@ -121,14 +121,7 @@ private static boolean
allowAliasUnnestItems(SqlOperatorBinding operatorBinding)
@Override public void unparse(SqlWriter writer, SqlCall call, int leftPrec,
int rightPrec) {
- if (call.operandCount() == 1
- && call.getOperandList().get(0).getKind() == SqlKind.SELECT) {
- // avoid double ( ) on unnesting a sub-query
- writer.keyword(getName());
- call.operand(0).unparse(writer, 0, 0);
- } else {
- super.unparse(writer, call, leftPrec, rightPrec);
- }
+ super.unparse(writer, call, leftPrec, rightPrec);
if (withOrdinality) {
writer.keyword("WITH ORDINALITY");
}
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 ee7f6cae47..dca08b4999 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
@@ -80,8 +80,8 @@ private RelToSqlConverterTest.Sql sql(String sql) {
+ "unnest(\"xs\") as \"x\"";
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 \"t10\" (\"xs\")";
+ + "FROM \"myDb\".\"myTable\") AS \"$cor0\",\nLATERAL UNNEST((SELECT
\"$cor0\".\"xs\"\n"
+ + "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 f9900a6fbf..44330c7988 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
@@ -1756,8 +1756,8 @@ private static String toSql(RelNode root, SqlDialect
dialect,
+ "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\")";
+ + "LATERAL UNNEST((SELECT \"$cor0\".\"$f2\"\n"
+ + "FROM (VALUES (0)) AS \"t\" (\"ZERO\"))) AS \"t10\" (\"col_0\")";
sql(sql).withLibrary(SqlLibrary.BIG_QUERY).ok(expected);
}
@@ -7112,8 +7112,8 @@ private void checkLiteral2(String expression, String
expected) {
+ " from \"department\") as t(did)";
final String expected = "SELECT \"DEPTID\" + 1\n"
- + "FROM UNNEST (SELECT COLLECT(\"department_id\") AS \"DEPTID\"\n"
- + "FROM \"foodmart\".\"department\") AS \"t0\" (\"DEPTID\")";
+ + "FROM UNNEST((SELECT COLLECT(\"department_id\") AS \"DEPTID\"\n"
+ + "FROM \"foodmart\".\"department\")) AS \"t0\" (\"DEPTID\")";
sql(sql).ok(expected);
}
@@ -7123,8 +7123,8 @@ private void checkLiteral2(String expression, String
expected) {
+ " from \"department\") as t(did)";
final String expected = "SELECT \"col_0\" + 1\n"
- + "FROM UNNEST (SELECT COLLECT(\"department_id\")\n"
- + "FROM \"foodmart\".\"department\") AS \"t0\" (\"col_0\")";
+ + "FROM UNNEST((SELECT COLLECT(\"department_id\")\n"
+ + "FROM \"foodmart\".\"department\")) AS \"t0\" (\"col_0\")";
sql(sql).ok(expected);
}
@@ -7136,22 +7136,26 @@ private void checkLiteral2(String expression, String
expected) {
final String sql = "select did + 1\n"
+ "from unnest(select collect(\"department_id\") as deptid \n"
+ "from \"department\") with ordinality as t(did, pos)";
-
final String expected = "SELECT \"DEPTID\" + 1\n"
- + "FROM UNNEST (SELECT COLLECT(\"department_id\") AS \"DEPTID\"\n"
- + "FROM \"foodmart\".\"department\") WITH ORDINALITY AS \"t0\"
(\"DEPTID\", \"ORDINALITY\")";
+ + "FROM UNNEST((SELECT COLLECT(\"department_id\") AS \"DEPTID\"\n"
+ + "FROM \"foodmart\".\"department\")) WITH ORDINALITY AS \"t0\"
(\"DEPTID\", \"ORDINALITY\")";
sql(sql).ok(expected);
}
- @Test void testUncollectImplicitAliasWithOrd() {
- final String sql = "select did + 1\n"
- + "from unnest(select collect(\"department_id\") \n"
- + "from \"department\") with ordinality as t(did, pos)";
-
- final String expected = "SELECT \"col_0\" + 1\n"
- + "FROM UNNEST (SELECT COLLECT(\"department_id\")\n"
- + "FROM \"foodmart\".\"department\") WITH ORDINALITY AS \"t0\"
(\"col_0\", \"ORDINALITY\")";
- sql(sql).ok(expected);
+ @Test void testUnnestArray() {
+ final String sql = "select * from UNNEST(array [1, 2, 3])";
+ final String expected = "SELECT *\n"
+ + "FROM UNNEST((SELECT ARRAY[1, 2, 3]\n"
+ + "FROM (VALUES (0)) AS \"t\" (\"ZERO\"))) AS \"t0\" (\"col_0\")";
+ final String expectedPostgresql = "SELECT *\n"
+ + "FROM UNNEST((SELECT ARRAY[1, 2, 3]\n"
+ + "FROM (VALUES (0)) AS \"t\" (\"ZERO\"))) AS \"t0\" (\"col_0\")";
+ final String expectedHsqldb = "SELECT *\n"
+ + "FROM UNNEST((SELECT ARRAY[1, 2, 3]\n"
+ + "FROM (VALUES (0)) AS t (ZERO))) AS t0 (col_0)";
+ sql(sql).ok(expected).
+ withPostgresql().ok(expectedPostgresql).
+ withHsqldb().ok(expectedHsqldb);
}
@Test void testWithinGroup1() {
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 9c982a82ed..85d64e5a4d 100644
--- a/piglet/src/test/java/org/apache/calcite/test/PigRelOpTest.java
+++ b/piglet/src/test/java/org/apache/calcite/test/PigRelOpTest.java
@@ -391,8 +391,8 @@ private Fluent pig(String script) {
+ " FROM scott.EMP\n"
+ " 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 t30 (EMPNO, ENAME, JOB,"
+ + " LATERAL UNNEST((SELECT $cor1.$f2 AS $f0\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))
@@ -481,14 +481,14 @@ private Fluent pig(String script) {
+ " GROUP BY DEPTNO) AS $cor4,\n"
+ " LATERAL (SELECT COLLECT($f1) AS X\n"
+ " FROM (SELECT 'all' AS $f0, ROW(ENAME, JOB, DEPTNO, SAL) AS
$f1\n"
- + " FROM UNNEST (SELECT $cor4.A AS $f0\n"
- + " FROM (VALUES (0)) AS t (ZERO)) "
+ + " FROM UNNEST((SELECT $cor4.A AS $f0\n"
+ + " FROM (VALUES (0)) AS t (ZERO))) "
+ "AS t2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)\n"
+ " WHERE JOB <> 'CLERK'\n"
+ " 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 t110 (ENAME, JOB, DEPTNO,
SAL)\n"
+ + " LATERAL UNNEST((SELECT $cor5.X AS $f0\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))
