This is an automated email from the ASF dual-hosted git repository.
zhenchen 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 b12a9cefb6 [CALCITE-7335] RelToSqlConverter generate sql containing
Scala subqueries includes redundant parentheses
b12a9cefb6 is described below
commit b12a9cefb67eefdd3b24ca2ac5893afeb021b80f
Author: Zhen Chen <[email protected]>
AuthorDate: Wed Dec 17 10:00:26 2025 +0800
[CALCITE-7335] RelToSqlConverter generate sql containing Scala subqueries
includes redundant parentheses
---
.../calcite/sql/fun/SqlStdOperatorTable.java | 14 +++++---
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 41 +++++++++++++++-------
2 files changed, 39 insertions(+), 16 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
index 074c3103f2..2a26a78929 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
@@ -2391,7 +2391,7 @@ public class SqlStdOperatorTable extends
ReflectiveSqlOperatorTable {
new SqlInternalOperator(
"$SCALAR_QUERY",
SqlKind.SCALAR_QUERY,
- 0,
+ 100, // High precedence to prevent SqlCall from adding extra
parentheses
false,
ReturnTypes.RECORD_TO_SCALAR,
null,
@@ -2401,9 +2401,15 @@ public class SqlStdOperatorTable extends
ReflectiveSqlOperatorTable {
SqlCall call,
int leftPrec,
int rightPrec) {
- final SqlWriter.Frame frame = writer.startList("(", ")");
- call.operand(0).unparse(writer, 0, 0);
- writer.endList(frame);
+ final SqlNode operand = call.operand(0);
+ if (operand.getKind() == SqlKind.SELECT) {
+ operand.unparse(writer, leftPrec, rightPrec);
+ } else {
+ final SqlWriter.Frame frame =
+ writer.startList(SqlWriter.FrameTypeEnum.SUB_QUERY, "(", ")");
+ operand.unparse(writer, 0, 0);
+ writer.endList(frame);
+ }
}
@Override public boolean argumentMustBeScalar(int ordinal) {
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 d2125c0ec8..06e1b93358 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
@@ -8627,9 +8627,9 @@ private void checkLiteral2(String expression, String
expected) {
+ "HAVING \"t1\".\"department_id\" = MIN(\"t1\".\"department_id\"))
\"t4\" ON \"employee\".\"department_id\" = \"t4\".\"department_id0\"";
final String expectedNoExpand = "SELECT \"department_id\"\n"
+ "FROM \"foodmart\".\"employee\"\n"
- + "WHERE \"department_id\" = (((SELECT
MIN(\"employee\".\"department_id\")\n"
+ + "WHERE \"department_id\" = (SELECT
MIN(\"employee\".\"department_id\")\n"
+ "FROM \"foodmart\".\"department\"\n"
- + "WHERE 1 = 2)))";
+ + "WHERE 1 = 2)";
final String expected = "SELECT \"employee\".\"department_id\"\n"
+ "FROM \"foodmart\".\"employee\"\n"
+ "INNER JOIN (SELECT \"t1\".\"department_id\" AS \"department_id0\",
MIN(\"t1\".\"department_id\") AS \"EXPR$0\"\n"
@@ -9802,17 +9802,17 @@ private void checkLiteral2(String expression, String
expected) {
final String sql0 = "update \"foodmart\".\"product\" a set \"product_id\"
= "
+ "(select \"product_class_id\" from \"foodmart\".\"product_class\" b "
+ "where a.\"product_class_id\" = b.\"product_class_id\")";
- final String expected0 = "UPDATE \"foodmart\".\"product\" SET
\"product_id\" = (((SELECT "
+ final String expected0 = "UPDATE \"foodmart\".\"product\" SET
\"product_id\" = (SELECT "
+ "\"product_class_id\"\nFROM \"foodmart\".\"product_class\"\nWHERE
\"product\""
- + ".\"product_class_id\" = \"product_class_id\")))";
+ + ".\"product_class_id\" = \"product_class_id\")";
sql(sql0).ok(expected0);
final String sql1 = "update \"foodmart\".\"product\" a set \"brand_name\"
= "
+ "(select cast(\"product_category\" as varchar(60)) from
\"foodmart\".\"product_class\" b "
+ "where a.\"product_class_id\" = b.\"product_class_id\")";
- final String expected1 = "UPDATE \"foodmart\".\"product\" SET
\"brand_name\" = (((SELECT CAST"
+ final String expected1 = "UPDATE \"foodmart\".\"product\" SET
\"brand_name\" = (SELECT CAST"
+ "(\"product_category\" AS VARCHAR(60) CHARACTER SET
\"ISO-8859-1\")\nFROM \"foodmart\""
- + ".\"product_class\"\nWHERE \"product\".\"product_class_id\" =
\"product_class_id\")))";
+ + ".\"product_class\"\nWHERE \"product\".\"product_class_id\" =
\"product_class_id\")";
sql(sql1).ok(expected1);
final String sql2 = "update \"foodmart\".\"product\"\n"
@@ -10972,10 +10972,10 @@ private void checkLiteral2(String expression, String
expected) {
final String expected = "SELECT "
+ "\"DEPTNO\", "
+ "\"DNAME\", "
- + "(((SELECT COUNT(*) AS \"COUNT\"\n"
+ + "(SELECT COUNT(*) AS \"COUNT\"\n"
+ "FROM \"scott\".\"EMP\"\n"
+ "GROUP BY \"DEPTNO\"\n"
- + "HAVING \"DEPTNO\" = \"DEPT\".\"DEPTNO\"))) AS \"$f2\"\n"
+ + "HAVING \"DEPTNO\" = \"DEPT\".\"DEPTNO\") AS \"$f2\"\n"
+ "FROM \"scott\".\"DEPT\"";
relFn(relFn).ok(expected);
@@ -11053,9 +11053,9 @@ private void checkLiteral2(String expression, String
expected) {
final String expected = "SELECT \"EMP\".\"EMPNO\"\n"
+ "FROM \"SCOTT\".\"EMP\"\n"
+ "INNER JOIN \"SCOTT\".\"DEPT\" ON \"EMP\".\"DEPTNO\" =
\"DEPT\".\"DEPTNO\"\n"
- + "WHERE \"DEPT\".\"DEPTNO\" = (((SELECT MIN(\"DEPTNO\")\n"
+ + "WHERE \"DEPT\".\"DEPTNO\" = (SELECT MIN(\"DEPTNO\")\n"
+ "FROM \"SCOTT\".\"DEPT\"\n"
- + "WHERE \"DEPTNO\" = \"EMP\".\"DEPTNO\")))";
+ + "WHERE \"DEPTNO\" = \"EMP\".\"DEPTNO\")";
HepProgramBuilder builder = new HepProgramBuilder();
builder.addRuleClass(FilterJoinRule.FilterIntoJoinRule.class);
@@ -11083,8 +11083,8 @@ private void checkLiteral2(String expression, String
expected) {
+ "FROM \"SCOTT\".\"EMP\" AS \"$cor1\",\n"
+ "LATERAL (SELECT *\nFROM \"SCOTT\".\"DEPT\"\n"
+ "WHERE \"$cor1\".\"DEPTNO\" = \"DEPTNO\") AS \"t\"\n"
- + "WHERE \"t\".\"DEPTNO\" = (((SELECT MIN(\"DEPTNO\")\n"
- + "FROM \"SCOTT\".\"DEPT\"\nWHERE \"DEPTNO\" =
\"$cor1\".\"DEPTNO\")))";
+ + "WHERE \"t\".\"DEPTNO\" = (SELECT MIN(\"DEPTNO\")\n"
+ + "FROM \"SCOTT\".\"DEPT\"\nWHERE \"DEPTNO\" = \"$cor1\".\"DEPTNO\")";
HepProgramBuilder builder = new HepProgramBuilder();
builder.addRuleClass(JoinToCorrelateRule.class);
builder.addRuleClass(FilterCorrelateRule.class);
@@ -11099,6 +11099,23 @@ private void checkLiteral2(String expression, String
expected) {
.ok(expected);
}
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7335";>[CALCITE-7335]
+ * RelToSqlConverter generate sql containing Scala subqueries
+ * includes redundant parentheses</a>. */
+ @Test void testScalarSubqueryInSelectList() {
+ final String sql = "SELECT\n"
+ + " (SELECT COUNT(*)\n"
+ + " FROM \"employee\"\n"
+ + " WHERE v.\"product_id\" >= 2), 3\n"
+ + "FROM \"product\" AS v";
+ final String expected = "SELECT (SELECT COUNT(*)\n"
+ + "FROM \"foodmart\".\"employee\"\n"
+ + "WHERE \"product\".\"product_id\" >= 2), 3\n"
+ + "FROM \"foodmart\".\"product\"";
+ sql(sql).ok(expected);
+ }
+
/** Fluid interface to run tests. */
static class Sql {
private final CalciteAssert.SchemaSpec schemaSpec;
