This is an automated email from the ASF dual-hosted git repository.
mihaibudiu 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 8447eda2f2 [CALCITE-7505] RelToSqlConverter fails to alias outer
relation for correlated sub-queries in Filter
8447eda2f2 is described below
commit 8447eda2f2fe34af9571f84ccb5de3d74c9e5ecd
Author: krooswu <[email protected]>
AuthorDate: Sat Jun 20 21:06:26 2026 +0800
[CALCITE-7505] RelToSqlConverter fails to alias outer relation for
correlated sub-queries in Filter
---
.../calcite/rel/rel2sql/RelToSqlConverter.java | 45 ++++++++++++--
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 68 ++++++++++++++++++++--
2 files changed, 103 insertions(+), 10 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 44eec4feb9..718e6b1cb9 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
@@ -547,17 +547,38 @@ public Result visit(Correlate e) {
null);
return result(join, leftResult, rightResult);
}
-
/** Visits a Filter; called by {@link #dispatch} via reflection. */
public Result visit(Filter e) {
final RelNode input = e.getInput();
+ final Set<CorrelationId> definedHere = e.getVariablesSet();
+
if (input instanceof Aggregate) {
final Aggregate aggregate = (Aggregate) input;
final boolean ignoreClauses = aggregate.getInput() instanceof Project;
- final Result x =
+ Result x =
visitInput(e, 0, isAnon(), ignoreClauses,
ImmutableSet.of(Clause.HAVING));
+ // Only rebind the correlation alias when e.getInput() renders as a
single
+ // addressable relation (<=1 input): TableScan, Project, Filter, etc.
+ // Join/Correlate (2+ inputs) already expose a multi-alias context via
+ // joinContext(); collapsing it here would break field resolution.
+ final boolean pushed = !definedHere.isEmpty()
+ && e.getInput().getInputs().size() <= 1;
+ if (pushed) {
+ String alias = x.neededAlias;
+ if (alias != null) {
+ x = x.resetAliasForCorrelation(alias, e.getInput().getRowType());
+ } else {
+ alias = unqualifiedName(x.node);
+ if (alias == null) {
+ alias = "t";
+ }
+ x = x.resetAliasForCorrelation
+ (alias, e.getInput().getRowType());
+ }
+ }
parseCorrelTable(e, x);
+
final Builder builder = x.builder(e);
x.asSelect().setHaving(
SqlUtil.andExpressions(x.asSelect().getHaving(),
@@ -565,8 +586,24 @@ public Result visit(Filter e) {
return builder.result();
} else {
Result x = visitInput(e, 0, Clause.WHERE);
- if (!e.getVariablesSet().isEmpty()) {
- x = x.resetAlias();
+ // Only rebind the correlation alias when e.getInput() renders as a
single
+ // addressable relation (<=1 input): TableScan, Project, Filter, etc.
+ // Join/Correlate (2+ inputs) already expose a multi-alias context via
+ // joinContext(); collapsing it here would break field resolution.
+ final boolean pushed = !definedHere.isEmpty()
+ && e.getInput().getInputs().size() <= 1;
+ if (pushed) {
+ String alias = x.neededAlias;
+ if (alias != null) {
+ x = x.resetAliasForCorrelation(alias, e.getInput().getRowType());
+ } else {
+ alias = unqualifiedName(x.node);
+ if (alias == null) {
+ alias = "t";
+ }
+ x = x.resetAliasForCorrelation
+ (alias, e.getInput().getRowType());
+ }
}
parseCorrelTable(e, x);
final Builder builder = x.builder(e);
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 affb7093f0..279454f6b3 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
@@ -6714,7 +6714,7 @@ private void checkLiteral2(String expression, String
expected) {
+ "from \"sales_fact_1997\"b "
+ "where b.\"product_id\" = a.\"product_id\")";
String expected = "SELECT \"product_name\"\n"
- + "FROM \"foodmart\".\"product\"\n"
+ + "FROM \"foodmart\".\"product\" AS \"product\"\n"
+ "WHERE EXISTS (SELECT COUNT(*)\n"
+ "FROM \"foodmart\".\"sales_fact_1997\"\n"
+ "WHERE \"product_id\" = \"product\".\"product_id\")";
@@ -6727,7 +6727,7 @@ private void checkLiteral2(String expression, String
expected) {
+ "from \"sales_fact_1997\"b "
+ "where b.\"product_id\" = a.\"product_id\")";
String expected = "SELECT \"product_name\"\n"
- + "FROM \"foodmart\".\"product\"\n"
+ + "FROM \"foodmart\".\"product\" AS \"product\"\n"
+ "WHERE NOT EXISTS (SELECT COUNT(*)\n"
+ "FROM \"foodmart\".\"sales_fact_1997\"\n"
+ "WHERE \"product_id\" = \"product\".\"product_id\")";
@@ -6740,7 +6740,7 @@ private void checkLiteral2(String expression, String
expected) {
+ "from \"sales_fact_1997\"b "
+ "where b.\"product_id\" = a.\"product_id\")";
String expected = "SELECT \"product_name\"\n"
- + "FROM \"foodmart\".\"product\"\n"
+ + "FROM \"foodmart\".\"product\" AS \"product\"\n"
+ "WHERE \"product_id\" IN (SELECT \"product_id\"\n"
+ "FROM \"foodmart\".\"sales_fact_1997\"\n"
+ "WHERE \"product_id\" = \"product\".\"product_id\")";
@@ -6762,7 +6762,7 @@ private void checkLiteral2(String expression, String
expected) {
+ "from \"sales_fact_1997\"b "
+ "where b.\"product_id\" = a.\"product_id\")";
String expected = "SELECT \"product_name\"\n"
- + "FROM \"foodmart\".\"product\"\n"
+ + "FROM \"foodmart\".\"product\" AS \"product\"\n"
+ "WHERE \"product_id\" NOT IN (SELECT \"product_id\"\n"
+ "FROM \"foodmart\".\"sales_fact_1997\"\n"
+ "WHERE \"product_id\" = \"product\".\"product_id\")";
@@ -6780,7 +6780,7 @@ private void checkLiteral2(String expression, String
expected) {
+ "where t2.\"product_id\" = t1.\"product_id\" "
+ "and t1.\"product_id\" = 2 and t2.\"product_id\" = 1)";
String expected = "SELECT \"product_name\"\n"
- + "FROM \"foodmart\".\"product\"\n"
+ + "FROM \"foodmart\".\"product\" AS \"product\"\n"
+ "WHERE \"product_id\" NOT IN (SELECT \"product_id\"\n"
+ "FROM \"foodmart\".\"product\" AS \"product0\"\n"
+ "WHERE \"product_id\" = \"product\".\"product_id\" "
@@ -9165,7 +9165,7 @@ private void checkLiteral2(String expression, String
expected) {
+ "GROUP BY \"t1\".\"department_id\"\n"
+ "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"
+ + "FROM \"foodmart\".\"employee\" AS \"employee\"\n"
+ "WHERE \"department_id\" = (SELECT
MIN(\"employee\".\"department_id\")\n"
+ "FROM \"foodmart\".\"department\"\n"
+ "WHERE 1 = 2)";
@@ -12733,4 +12733,60 @@ public Sql schema(CalciteAssert.SchemaSpec schemaSpec)
{
sql(query).withLibrary(SqlLibrary.HIVE).withHive().ok(expectedHive);
sql(query).withLibrary(SqlLibrary.SPARK).withSpark().ok(expectedSpark);
}
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7505";>[CALCITE-7505]
+ * RelToSqlConverter fails to alias outer relation for correlated
sub-queries in Filter</a>. */
+ @Test void testExistsSubQueryAliasConflict() {
+ final String sql =
+ "select deptno, sum(sal) as total\n"
+ + "from emp t\n"
+ + "where exists (\n"
+ + " select * from dept t0\n"
+ + " where deptno = t.deptno\n"
+ + ")\n"
+ + "group by deptno";
+
+
+ sql(sql)
+ .schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
+ .ok(
+ "SELECT \"DEPTNO\", SUM(\"SAL\") AS \"TOTAL\"\n"
+ + "FROM \"SCOTT\".\"EMP\" AS \"EMP\"\n"
+ + "WHERE EXISTS (SELECT *\n"
+ + "FROM \"SCOTT\".\"DEPT\"\n"
+ + "WHERE \"DEPTNO\" = \"EMP\".\"DEPTNO\")\n"
+ + "GROUP BY \"DEPTNO\"");
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7505";>[CALCITE-7505]
+ * RelToSqlConverter fails to alias outer relation for correlated
sub-queries in Filter</a>. */
+ @Test void testExistsSubQueryOverUnion() {
+ final String sql =
+ "SELECT *\n"
+ + "FROM (\n"
+ + " SELECT deptno FROM emp\n"
+ + " UNION ALL\n"
+ + " SELECT deptno FROM dept\n"
+ + ") u\n"
+ + "WHERE EXISTS (\n"
+ + " SELECT 1\n"
+ + " FROM emp e\n"
+ + " WHERE e.deptno = u.deptno\n"
+ + ")";
+
+ sql(sql)
+ .schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
+ .ok(
+ "SELECT *\n"
+ + "FROM (SELECT \"DEPTNO\"\n"
+ + "FROM \"SCOTT\".\"EMP\"\n"
+ + "UNION ALL\n"
+ + "SELECT \"DEPTNO\"\n"
+ + "FROM \"SCOTT\".\"DEPT\") AS \"t1\"\n"
+ + "WHERE EXISTS (SELECT *\n"
+ + "FROM \"SCOTT\".\"EMP\"\n"
+ + "WHERE \"DEPTNO\" = \"t1\".\"DEPTNO\")");
+ }
}
