This is an automated email from the ASF dual-hosted git repository.
mbudiu 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 607126846b [CALCITE-7343] RelToSqlConverter generate wrong sql when
scalar correlated sub-query in Project
607126846b is described below
commit 607126846b12348beae54bbcbb7d1f8ea4a44a0d
Author: krooswu <[email protected]>
AuthorDate: Wed Feb 25 23:49:08 2026 +0800
[CALCITE-7343] RelToSqlConverter generate wrong sql when scalar correlated
sub-query in Project
---
.../calcite/rel/rel2sql/RelToSqlConverter.java | 74 ++++++++++++++++++-
.../apache/calcite/rel/rel2sql/SqlImplementor.java | 61 +++++++++++++---
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 84 ++++++++++++++++++++--
3 files changed, 203 insertions(+), 16 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 592df51a0b..0404fa7bc5 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
@@ -604,14 +604,82 @@ private static boolean selectListRequired(Context
context) {
return false;
}
- /** Visits a Project; called by {@link #dispatch} via reflection. */
+ /**
+ * Extracts the table name from a SqlNode if it represents a simple table
reference.
+ * Returns null for complex nodes like subqueries or joins.
+ *
+ * <p>Examples:
+ * <ul>
+ * <li>"product" → "product"</li>
+ * <li>"foodmart.product" → "product"</li>
+ * <li>"SCOTT"."EMP" → "EMP"</li>
+ * <li>Subquery/Join/Other → null</li>
+ * </ul>
+ *
+ * @param node The SQL node to examine
+ * @return The table name if it's a simple identifier, null otherwise
+ */
+ private @Nullable String unqualifiedName(SqlNode node) {
+ if (node instanceof SqlIdentifier) {
+ SqlIdentifier id = (SqlIdentifier) node;
+ // Return the last component (table name)
+ return id.names.get(id.names.size() - 1);
+ }
+
+ // All other cases: return null
+ return null;
+ }
+ /**
+ * Visits a {@link Project} and converts it to a {@link SqlSelect}.
+ *
+ * <p>If the project defines correlation variables (e.g., via {@code $cor0}),
+ * this method ensures that the input relation is assigned a stable alias
+ * (either the natural table name or a synthetic alias like 't').
+ * This enables nested correlated subqueries to correctly qualify their
+ * column references back to this project's scope.
+ *
+ * <p>For simple table scans, it avoids forcing an explicit 'AS' clause
+ * to maintain compatibility with DML statements (like UPDATE/DELETE)
+ * in certain SQL dialects.
+ */
public Result visit(Project e) {
// If the input is a Sort, wrap SELECT is not required.
final Result x;
+ final Set<CorrelationId> definedHere = e.getVariablesSet();
+ boolean pushed = !definedHere.isEmpty();
+
+ // Visit input node
+ Result inputResult;
if (e.getInput() instanceof Sort) {
- x = visitInput(e, 0);
+ inputResult = visitInput(e, 0);
+ } else {
+ inputResult = visitInput(e, 0, Clause.SELECT);
+ }
+
+ // If this Project defines correlations, fill in alias and force explicit
generation
+ // Resolve the correlation alias using a three-level priority:
+ // 1. Use the existing alias from inputResult if it's already defined.
+ // 2. If not, extract the natural table name to maintain DML compatibility.
+ // 3. Fallback to 't' for anonymous relations (e.g., Joins or Sub-queries).
+ //
+ // 't' is safe and standard in Calcite's SqlImplementor because:
+ // - Anonymous relations in the FROM clause require an alias in most
dialects.
+ // - SQL name shadowing rules ensure that nested sub-queries correctly bind
+ // to the nearest qualifying 't' in their scope.
+ if (pushed) {
+ String alias = inputResult.neededAlias;
+ if (alias != null) {
+ x = inputResult.resetAliasForCorrelation(alias,
e.getInput().getRowType());
+ } else {
+ alias = unqualifiedName(inputResult.node);
+ if (alias == null) {
+ alias = "t";
+ }
+ x = inputResult.resetAliasForCorrelation
+ (alias, e.getInput().getRowType());
+ }
} else {
- x = visitInput(e, 0, Clause.SELECT);
+ x = inputResult;
}
parseCorrelTable(e, x);
final Builder builder = x.builder(e);
diff --git
a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
index 6d60d52aa4..dec24ca1f2 100644
--- a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
+++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
@@ -1910,16 +1910,24 @@ public class Result {
private final @Nullable RelNode expectedRel;
private final boolean needNew;
+ /**
+ * Whether to force explicit alias generation in FROM clause.
+ * Set to true when this Result is used in a correlation context
+ * where the table alias must be explicit even if the dialect
+ * normally supports implicit aliases.
+ */
+ private final boolean forceExplicitAlias;
+
public Result(SqlNode node, Collection<Clause> clauses, @Nullable String
neededAlias,
@Nullable RelDataType neededType, Map<String, RelDataType> aliases) {
this(node, clauses, neededAlias, neededType, aliases, false, false,
- ImmutableSet.of(), null);
+ ImmutableSet.of(), null, false);
}
private Result(SqlNode node, Collection<Clause> clauses, @Nullable String
neededAlias,
@Nullable RelDataType neededType, Map<String, RelDataType> aliases,
boolean anon,
boolean ignoreClauses, Set<Clause> expectedClauses,
- @Nullable RelNode expectedRel) {
+ @Nullable RelNode expectedRel, boolean forceExplicitAlias) {
this.node = node;
this.neededAlias = neededAlias;
this.neededType = neededType;
@@ -1929,6 +1937,7 @@ private Result(SqlNode node, Collection<Clause> clauses,
@Nullable String needed
this.ignoreClauses = ignoreClauses;
this.expectedClauses = ImmutableSet.copyOf(expectedClauses);
this.expectedRel = expectedRel;
+ this.forceExplicitAlias = forceExplicitAlias;
final Set<Clause> clauses2 =
ignoreClauses ? ImmutableSet.of() : expectedClauses;
this.needNew = expectedRel != null
@@ -2241,9 +2250,23 @@ public SqlSelect subSelect() {
* INTERSECT, EXCEPT) remain as is. */
public SqlSelect asSelect() {
if (node instanceof SqlSelect) {
- return (SqlSelect) node;
+ SqlSelect select = (SqlSelect) node;
+ // Check if we need to add explicit alias to FROM clause
+ if (forceExplicitAlias && neededAlias != null) {
+ SqlNode from = select.getFrom();
+
+ // Only add alias if FROM doesn't already have one
+ if (from != null && from.getKind() != SqlKind.AS) {
+ SqlNode newFrom =
+ SqlStdOperatorTable.AS.createCall(POS, from,
+ new SqlIdentifier(neededAlias, POS));
+ select.setFrom(newFrom);
+ }
+ }
+ return select;
}
- if (!dialect.hasImplicitTableAlias() || hasConflictTableAlias(node)) {
+ // For non-SELECT nodes, wrap in SELECT *
+ if (forceExplicitAlias || !dialect.hasImplicitTableAlias() ||
hasConflictTableAlias(node)) {
return wrapSelect(asFrom());
}
return wrapSelect(node);
@@ -2369,7 +2392,7 @@ public Result resetAlias() {
} else {
return new Result(node, clauses, neededAlias, neededType,
ImmutableMap.of(neededAlias, castNonNull(neededType)), anon,
ignoreClauses,
- expectedClauses, expectedRel);
+ expectedClauses, expectedRel, false);
}
}
@@ -2382,14 +2405,36 @@ public Result resetAlias() {
public Result resetAlias(String alias, RelDataType type) {
return new Result(node, clauses, alias, neededType,
ImmutableMap.of(alias, type), anon, ignoreClauses,
- expectedClauses, expectedRel);
+ expectedClauses, expectedRel, false);
+ }
+
+ /**
+ * Sets the alias and forces explicit alias generation in FROM clause.
+ * Used when correlation requires an explicit table alias.
+ *
+ * @param alias New alias to use
+ * @param type Type of the node associated with the alias
+ * @return New Result with forced explicit alias
+ */
+ public Result resetAliasForCorrelation(String alias, RelDataType type) {
+ return new Result(
+ node,
+ clauses,
+ alias,
+ neededType,
+ ImmutableMap.of(alias, type),
+ anon,
+ ignoreClauses,
+ expectedClauses,
+ expectedRel,
+ true); // Force explicit alias
}
/** Returns a copy of this Result, overriding the value of {@code anon}. */
Result withAnon(boolean anon) {
return anon == this.anon ? this
: new Result(node, clauses, neededAlias, neededType, aliases, anon,
- ignoreClauses, expectedClauses, expectedRel);
+ ignoreClauses, expectedClauses, expectedRel, false);
}
/** Returns a copy of this Result, overriding the value of
@@ -2401,7 +2446,7 @@ Result withExpectedClauses(boolean ignoreClauses,
&& expectedRel == this.expectedRel
? this
: new Result(node, clauses, neededAlias, neededType, aliases, anon,
- ignoreClauses, ImmutableSet.copyOf(expectedClauses),
expectedRel);
+ ignoreClauses, ImmutableSet.copyOf(expectedClauses),
expectedRel, false);
}
}
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 a98f7fd1f8..4250181e66 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
@@ -11107,7 +11107,7 @@ private void checkLiteral2(String expression, String
expected) {
+ "FROM \"scott\".\"EMP\"\n"
+ "GROUP BY \"DEPTNO\"\n"
+ "HAVING \"DEPTNO\" = \"DEPT\".\"DEPTNO\") AS \"$f2\"\n"
- + "FROM \"scott\".\"DEPT\"";
+ + "FROM \"scott\".\"DEPT\" AS \"DEPT\"";
relFn(relFn).ok(expected);
}
@@ -11256,7 +11256,7 @@ private void checkLiteral2(String expression, String
expected) {
final String expected = "SELECT (SELECT COUNT(*)\n"
+ "FROM \"foodmart\".\"employee\"\n"
+ "WHERE \"product\".\"product_id\" >= 2), 3\n"
- + "FROM \"foodmart\".\"product\"";
+ + "FROM \"foodmart\".\"product\" AS \"product\"";
sql(sql).ok(expected);
}
@@ -11651,9 +11651,10 @@ public Sql schema(CalciteAssert.SchemaSpec schemaSpec)
{
}
}
- /** Test case for
+ /** Test cases for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-7279";>[CALCITE-7279]
- * ClickHouse dialect should wrap nested JOINs with explicit aliasing</a>. */
+ * ClickHouse dialect should wrap nested JOINs with explicit aliasing</a>.
+ */
@Test void testClickHouseNestedJoin() {
final String query = "SELECT e.empno, j.dname, j.loc\n"
+ "FROM emp e\n"
@@ -11757,8 +11758,81 @@ public Sql schema(CalciteAssert.SchemaSpec schemaSpec)
{
sql(query)
.schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
- .withMysql()
+ .withMysql();
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7343";>[CALCITE-7343]
+ RelToSqlConverter generate wrong sql when scalar correlated sub-query in
Project </a>. */
+ @Test void testProjectScalarSubquery() {
+ final String sql = "SELECT \"EMPNO\",\n"
+ + " (SELECT COUNT(*) AS \"c\" FROM \"EMP\" WHERE \"MGR\" <
\"m\".\"MGR\") AS \"$f1\"\n"
+ + "FROM \"EMP\" AS \"m\"\n"
+ + "WHERE \"SAL\" > 10";
+
+ final String expected = "SELECT \"EMPNO\", "
+ + "(SELECT COUNT(*) AS \"c\"\n"
+ + "FROM \"SCOTT\".\"EMP\"\n"
+ + "WHERE \"MGR\" < \"t\".\"MGR\") AS \"$f1\"\n"
+ + "FROM \"SCOTT\".\"EMP\" AS \"t\"\n"
+ + "WHERE CAST(\"SAL\" AS DECIMAL(12, 2)) > 10.00";
+
+ sql(sql)
+ .schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
+ .withCalcite().ok(expected);
+ }
+ @Test void testProjectDeeplyNestedScalarSubquery() {
+ final String sql = "SELECT \"EMPNO\",\n"
+ + " (SELECT MAX((SELECT COUNT(*) FROM \"DEPT\" "
+ + "WHERE \"DEPTNO\" = \"m\".\"DEPTNO\"))\n"
+ + " FROM \"DEPT\" WHERE \"LOC\" = 'NEW YORK') AS \"$f1\"\n"
+ + "FROM \"EMP\" AS \"m\"";
+
+ final String expected = "SELECT \"EMPNO\", "
+ + "(SELECT MAX((SELECT COUNT(*)\n"
+ + "FROM \"SCOTT\".\"DEPT\"\n"
+ + "WHERE \"DEPTNO\" = \"EMP\".\"DEPTNO\"))\n"
+ + "FROM \"SCOTT\".\"DEPT\"\n"
+ + "WHERE \"LOC\" = 'NEW YORK') AS \"$f1\"\n"
+ + "FROM \"SCOTT\".\"EMP\" AS \"EMP\"";
+
+ sql(sql)
+ .schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
+ .withCalcite()
.ok(expected);
}
+ @Test void testMultiLayerProjectCorrelation() {
+ final String sql = "SELECT \"EMPNO\" + 1, \n"
+ + " (SELECT \"DNAME\" FROM \"DEPT\" WHERE \"DEPTNO\" =
\"sub\".\"DEPTNO\")\n"
+ + "FROM (SELECT * FROM \"EMP\") AS \"sub\"";
+
+ final String expected = "SELECT \"EMPNO\" + 1, "
+ + "(SELECT \"DNAME\"\nFROM \"SCOTT\".\"DEPT\"\n"
+ + "WHERE \"DEPTNO\" = \"t\".\"DEPTNO\")\n"
+ + "FROM \"SCOTT\".\"EMP\" AS \"t\"";
+
+ sql(sql).schema(CalciteAssert.SchemaSpec.JDBC_SCOTT).ok(expected);
+ }
+
+ @Test void testMultiLevelCrossReference() {
+ final String sql = "SELECT \"e\".\"ENAME\",\n"
+ + " (SELECT COUNT(*)\n"
+ + " FROM (SELECT \"d\".\"DEPTNO\", \"d\".\"DNAME\" FROM \"DEPT\"
\"d\" "
+ + " JOIN \"BONUS\" \"b\" ON \"d\".\"DEPTNO\" =
\"e\".\"DEPTNO\") AS \"mid\"\n"
+ + " WHERE \"mid\".\"DNAME\" = (SELECT \"DNAME\" FROM \"DEPT\" "
+ + " WHERE \"DEPTNO\" = \"e\".\"DEPTNO\" "
+ + " AND \"LOC\" = \"mid\".\"DNAME\"))\n"
+ + "FROM \"EMP\" AS \"e\"";
+ final String expected = "SELECT \"ENAME\", (SELECT COUNT(*)\n"
+ + "FROM (SELECT \"DEPT\".\"DEPTNO\", \"DEPT\".\"DNAME\"\n"
+ + "FROM \"SCOTT\".\"DEPT\"\n"
+ + "INNER JOIN \"SCOTT\".\"BONUS\" ON \"DEPT\".\"DEPTNO\" =
\"EMP\".\"DEPTNO\") AS \"t\"\n"
+ + "WHERE \"DNAME\" = (SELECT \"DNAME\"\n"
+ + "FROM \"SCOTT\".\"DEPT\"\n"
+ + "WHERE \"DEPTNO\" = \"EMP\".\"DEPTNO\" AND \"LOC\" =
\"t\".\"DNAME\"))\n"
+ + "FROM \"SCOTT\".\"EMP\" AS \"EMP\"";
+
+ sql(sql).schema(CalciteAssert.SchemaSpec.JDBC_SCOTT).ok(expected);
+ }
}
