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 c9de14bc59 [CALCITE-7439] RelToSqlConverter emits ambiguous GROUP BY
after LEFT JOIN USING with semi-join rewrite
c9de14bc59 is described below
commit c9de14bc5966bd5b2c578983ab09b4ab2b74e1c3
Author: bvolpato <[email protected]>
AuthorDate: Wed Aug 5 02:45:03 2026 -0400
[CALCITE-7439] RelToSqlConverter emits ambiguous GROUP BY after LEFT JOIN
USING with semi-join rewrite
---
.../calcite/rel/rel2sql/RelToSqlConverter.java | 108 +++++++++++-
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 182 +++++++++++++++++++++
2 files changed, 288 insertions(+), 2 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 718e6b1cb9..2f96e8f73b 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
@@ -895,12 +895,17 @@ private List<SqlNode> generateGroupList(Builder builder,
+ aggregate.getGroupSet() + ", just possibly a different order";
final List<SqlNode> groupKeys = new ArrayList<>();
+ final SqlJoin fromJoin =
+ builder.select.getFrom() instanceof SqlJoin ? (SqlJoin)
builder.select.getFrom() : null;
for (int key : groupList) {
- final SqlNode field = builder.context.field(key);
+ SqlNode field = builder.context.field(key);
+ field = maybeQualifyJoinKey(field, key, fromJoin, aggregate.getInput());
groupKeys.add(field);
}
for (int key : sortedGroupList) {
- final SqlNode field = builder.context.field(key);
+ SqlNode field =
+ maybeQualifyJoinKey(builder.context.field(key), key, fromJoin,
+ aggregate.getInput());
addSelect(selectList, field, aggregate.getRowType());
}
switch (aggregate.getGroupType()) {
@@ -942,6 +947,105 @@ private List<SqlNode> generateGroupList(Builder builder,
}
}
+ /** Qualifies a group key when its aggregate input renders as a SQL join
+ * whose columns would otherwise be ambiguous. */
+ private SqlNode maybeQualifyJoinKey(SqlNode field, int key,
+ @Nullable SqlJoin fromJoin, RelNode input) {
+ if (fromJoin == null) {
+ return field;
+ }
+ final @Nullable SqlNode qualified = joinField(input, key, fromJoin);
+ return qualified != null ? qualified : field;
+ }
+
+ /** Resolves a field through row-preserving nodes and nested joins to the SQL
+ * relation that supplies it. Set operations are handled as aliased derived
+ * tables. */
+ private static @Nullable SqlNode joinField(RelNode input, int field,
+ SqlNode from) {
+ final @Nullable String alias = SqlValidatorUtil.alias(from);
+ if (alias != null) {
+ return new SqlIdentifier(
+ ImmutableList.of(alias,
+ input.getRowType().getFieldList().get(field).getName()), POS);
+ }
+ if (input instanceof Project) {
+ final Project project = (Project) input;
+ return joinExpression(project.getInput(),
project.getProjects().get(field),
+ from);
+ }
+ if (input instanceof Filter) {
+ return joinField(((Filter) input).getInput(), field, from);
+ }
+ final RelNode left;
+ final RelNode right;
+ final JoinRelType joinType;
+ final boolean correlateInput;
+ if (input instanceof Join) {
+ final Join join = (Join) input;
+ left = join.getLeft();
+ right = join.getRight();
+ joinType = join.getJoinType();
+ correlateInput = false;
+ } else if (input instanceof Correlate) {
+ final Correlate correlateRel = (Correlate) input;
+ left = correlateRel.getLeft();
+ right = correlateRel.getRight();
+ joinType = correlateRel.getJoinType();
+ correlateInput = true;
+ } else {
+ return null;
+ }
+ if (!joinType.projectsRight()) {
+ if (correlateInput) {
+ return from instanceof SqlJoin
+ ? joinField(left, field, ((SqlJoin) from).getLeft())
+ : null;
+ }
+ return joinField(left, field, from);
+ }
+ if (!(from instanceof SqlJoin)) {
+ return null;
+ }
+ final SqlJoin fromJoin = (SqlJoin) from;
+ final int leftFieldCount = left.getRowType().getFieldCount();
+ final RelNode side;
+ final SqlNode sqlSide;
+ final int sideField;
+ if (field < leftFieldCount) {
+ side = left;
+ sqlSide = fromJoin.getLeft();
+ sideField = field;
+ } else {
+ side = right;
+ sqlSide = fromJoin.getRight();
+ sideField = field - leftFieldCount;
+ }
+ return joinField(side, sideField, sqlSide);
+ }
+
+ /** Converts field references and merged join keys in a project expression
+ * to qualified SQL expressions. */
+ private static @Nullable SqlNode joinExpression(RelNode input,
+ RexNode expression, SqlNode from) {
+ if (expression instanceof RexInputRef) {
+ return joinField(input, ((RexInputRef) expression).getIndex(), from);
+ }
+ if (expression.getKind() == SqlKind.COALESCE) {
+ final List<SqlNode> operands = new ArrayList<>();
+ for (RexNode operand : ((RexCall) expression).getOperands()) {
+ final @Nullable SqlNode sqlOperand =
+ joinExpression(input, operand, from);
+ if (sqlOperand == null) {
+ return null;
+ }
+ operands.add(sqlOperand);
+ }
+ return SqlStdOperatorTable.COALESCE.createCall(POS, operands);
+ }
+ return null;
+ }
+
private static SqlNode groupItem(List<SqlNode> groupKeys,
ImmutableBitSet groupSet, ImmutableBitSet wholeGroupSet) {
final List<SqlNode> nodes = groupSet.asList().stream()
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 ce06127a9f..83a4f87b28 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
@@ -50,6 +50,7 @@
import org.apache.calcite.rel.type.RelDataTypeSystem;
import org.apache.calcite.rel.type.RelDataTypeSystemImpl;
import org.apache.calcite.rex.RexCorrelVariable;
+import org.apache.calcite.rex.RexNode;
import org.apache.calcite.runtime.FlatLists;
import org.apache.calcite.runtime.Hook;
import org.apache.calcite.schema.SchemaPlus;
@@ -147,6 +148,52 @@ private Sql sql(String sql) {
return fixture().withSql(sql);
}
+ private void assertPostgresqlSqlValid(String sql) {
+ try {
+ final SchemaPlus rootSchema = Frameworks.createRootSchema(true);
+ final SchemaPlus defaultSchema =
+ CalciteAssert.addSchema(rootSchema,
CalciteAssert.SchemaSpec.JDBC_FOODMART);
+ final Planner planner =
+ getPlanner(null,
+ PostgresqlSqlDialect.DEFAULT.configureParser(SqlParser.config()),
+ defaultSchema,
+ SqlToRelConverter.config().withTrimUnusedFields(false),
+ ImmutableSet.of(),
+ DatabaseProduct.POSTGRESQL.getDialect().getTypeSystem(),
+ StandardConvertletTable.INSTANCE);
+ final SqlNode parsed = planner.parse(sql);
+ planner.validate(parsed);
+ } catch (Exception e) {
+ throw TestUtil.rethrow(e);
+ }
+ }
+
+ private static RuleSet semiJoinRules() {
+ return RuleSets.ofList(CoreRules.PROJECT_SUB_QUERY_TO_MARK_CORRELATE,
+ CoreRules.FILTER_SUB_QUERY_TO_MARK_CORRELATE,
+ CoreRules.MARK_TO_SEMI_OR_ANTI_JOIN_RULE,
+ CoreRules.PROJECT_TO_SEMI_JOIN);
+ }
+
+ private String postgresqlDistinctJoinSql(String select, String joinType,
+ String condition) {
+ final String query = "WITH product_keys AS (\n"
+ + " SELECT p.\"product_id\",\n"
+ + " (SELECT MAX(p3.\"product_id\")\n"
+ + " FROM \"foodmart\".\"product\" p3\n"
+ + " WHERE p3.\"product_id\" = p.\"product_id\") AS \"mx\"\n"
+ + " FROM \"foodmart\".\"product\" p\n"
+ + ")\n"
+ + "SELECT DISTINCT " + select + "\n"
+ + "FROM product_keys pk\n"
+ + joinType + " JOIN \"foodmart\".\"product\" p2 " + condition + "\n"
+ + "WHERE pk.\"product_id\" IN (\n"
+ + " SELECT p4.\"product_id\"\n"
+ + " FROM \"foodmart\".\"product\" p4\n"
+ + ")";
+ return sql(query).withPostgresql().optimize(semiJoinRules(), null).exec();
+ }
+
/** Initiates a test case with a given {@link RelNode} supplier. */
private Sql relFn(Function<RelBuilder, RelNode> relFn) {
return fixture()
@@ -12741,6 +12788,141 @@ public Sql schema(CalciteAssert.SchemaSpec
schemaSpec) {
sql(generated).withPostgresql().exec();
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7439";>[CALCITE-7439]
+ * RelToSqlConverter emits ambiguous GROUP BY after LEFT JOIN USING with
+ * semi-join rewrite.</a>. */
+ @Test void
testPostgresqlRoundTripDistinctLeftJoinInSubqueryWithSemiJoinRules() {
+ final String generated =
+ postgresqlDistinctJoinSql("\"product_id\"", "LEFT", "USING
(\"product_id\")");
+ assertPostgresqlSqlValid(generated);
+ }
+
+ @Test void testDistinctRightJoinUsing() {
+ final String generated =
+ postgresqlDistinctJoinSql("\"product_id\"", "RIGHT", "USING
(\"product_id\")");
+ assertThat(
+ generated, isLinux("SELECT \"product1\".\"product_id\"\n"
+ + "FROM (SELECT \"$cor0\".\"product_id\", \"t1\".\"EXPR$0\" AS
\"mx\"\n"
+ + "FROM \"foodmart\".\"product\" AS \"$cor0\",\n"
+ + "LATERAL (SELECT MAX(\"product_id\") AS \"EXPR$0\"\n"
+ + "FROM \"foodmart\".\"product\"\n"
+ + "WHERE \"product_id\" = \"$cor0\".\"product_id\") AS \"t1\") AS
\"t2\"\n"
+ + "RIGHT JOIN \"foodmart\".\"product\" AS \"product1\""
+ + " ON \"t2\".\"product_id\" = \"product1\".\"product_id\"\n"
+ + "WHERE EXISTS (SELECT 1\n"
+ + "FROM (SELECT \"product_id\"\n"
+ + "FROM \"foodmart\".\"product\") AS \"t3\"\n"
+ + "WHERE \"t2\".\"product_id\" = \"t3\".\"product_id\")\n"
+ + "GROUP BY \"product1\".\"product_id\""));
+ assertPostgresqlSqlValid(generated);
+ }
+
+ @Test void testDistinctFullJoinUsing() {
+ final String generated =
+ postgresqlDistinctJoinSql("\"product_id\"", "FULL", "USING
(\"product_id\")");
+ assertThat(generated,
+ isLinux("SELECT COALESCE(\"t2\".\"product_id\","
+ + " \"product1\".\"product_id\") AS \"product_id\"\n"
+ + "FROM (SELECT \"$cor0\".\"product_id\", \"t1\".\"EXPR$0\" AS
\"mx\"\n"
+ + "FROM \"foodmart\".\"product\" AS \"$cor0\",\n"
+ + "LATERAL (SELECT MAX(\"product_id\") AS \"EXPR$0\"\n"
+ + "FROM \"foodmart\".\"product\"\n"
+ + "WHERE \"product_id\" = \"$cor0\".\"product_id\") AS \"t1\") AS
\"t2\"\n"
+ + "FULL JOIN \"foodmart\".\"product\" AS \"product1\""
+ + " ON \"t2\".\"product_id\" = \"product1\".\"product_id\"\n"
+ + "WHERE EXISTS (SELECT 1\n"
+ + "FROM (SELECT \"product_id\"\n"
+ + "FROM \"foodmart\".\"product\") AS \"t3\"\n"
+ + "WHERE \"t2\".\"product_id\" = \"t3\".\"product_id\")\n"
+ + "GROUP BY COALESCE(\"t2\".\"product_id\","
+ + " \"product1\".\"product_id\")"));
+ assertPostgresqlSqlValid(generated);
+ }
+
+ @Test void testDistinctFullJoinOnKeepsSelectedSide() {
+ final String condition = "ON pk.\"product_id\" = p2.\"product_id\"";
+ final String generated =
+ postgresqlDistinctJoinSql("pk.\"product_id\"", "FULL", condition);
+ assertThat(
+ generated, isLinux("SELECT \"t2\".\"product_id\"\n"
+ + "FROM (SELECT \"$cor0\".\"product_id\", \"t1\".\"EXPR$0\" AS
\"mx\"\n"
+ + "FROM \"foodmart\".\"product\" AS \"$cor0\",\n"
+ + "LATERAL (SELECT MAX(\"product_id\") AS \"EXPR$0\"\n"
+ + "FROM \"foodmart\".\"product\"\n"
+ + "WHERE \"product_id\" = \"$cor0\".\"product_id\") AS \"t1\") AS
\"t2\"\n"
+ + "FULL JOIN \"foodmart\".\"product\" AS \"product1\""
+ + " ON \"t2\".\"product_id\" = \"product1\".\"product_id\"\n"
+ + "WHERE EXISTS (SELECT 1\n"
+ + "FROM (SELECT \"product_id\"\n"
+ + "FROM \"foodmart\".\"product\") AS \"t3\"\n"
+ + "WHERE \"t2\".\"product_id\" = \"t3\".\"product_id\")\n"
+ + "GROUP BY \"t2\".\"product_id\""));
+ assertPostgresqlSqlValid(generated);
+ }
+
+ @Test void testDistinctOverSemiJoinAndCorrelate() {
+ final Function<RelBuilder, RelNode> relFn = b -> {
+ final Holder<RexCorrelVariable> v = Holder.empty();
+ b.values(new String[]{"id"}, 1)
+ .variable(v::set);
+ b.values(new String[]{"id"}, 1)
+ .filter(
+ b.equals(b.field("id"),
+ b.getRexBuilder().makeFieldAccess(v.get(), 0)));
+ final RexNode correlateId = b.field(2, 0, 0);
+ b.correlate(JoinRelType.INNER, v.get().id, correlateId);
+ b.values(new String[]{"id"}, 1);
+ final RexNode leftId = b.field(2, 0, 0);
+ return b.join(JoinRelType.SEMI,
+ b.equals(leftId, b.field(2, 1, 0)))
+ .project(leftId)
+ .distinct()
+ .build();
+ };
+ final String generated = relFn(relFn).exec();
+ assertThat(
+ generated, isLinux("SELECT \"$cor0\".\"id\"\n"
+ + "FROM (VALUES (1)) AS \"$cor0\" (\"id\"),\n"
+ + "LATERAL (SELECT *\n"
+ + "FROM (VALUES (1)) AS \"t0\" (\"id\")\n"
+ + "WHERE \"id\" = \"$cor0\".\"id\") AS \"t1\"\n"
+ + "WHERE EXISTS (SELECT 1\n"
+ + "FROM (VALUES (1)) AS \"t2\" (\"id\")\n"
+ + "WHERE \"$cor0\".\"id\" = \"t2\".\"id\")\n"
+ + "GROUP BY \"$cor0\".\"id\""));
+ assertPostgresqlSqlValid(generated);
+ }
+
+ @Test void testDistinctOverNestedJoin() {
+ final Function<RelBuilder, RelNode> relFn = b -> {
+ b.scan("EMP");
+ b.scan("EMP");
+ b.join(JoinRelType.INNER,
+ b.equals(b.field(2, 0, "EMPNO"), b.field(2, 1, "EMPNO")));
+ b.scan("EMP");
+ b.join(JoinRelType.INNER,
+ b.equals(b.field(2, 0, 0), b.field(2, 1, "EMPNO")));
+ b.scan("DEPT");
+ final RexNode firstDeptNo = b.field(2, 0, 7);
+ return b.join(JoinRelType.SEMI,
+ b.equals(b.field(2, 0, 7), b.field(2, 1, "DEPTNO")))
+ .project(firstDeptNo)
+ .distinct()
+ .build();
+ };
+ relFn(relFn).ok("SELECT \"EMP\".\"DEPTNO\"\n"
+ + "FROM \"scott\".\"EMP\"\n"
+ + "INNER JOIN \"scott\".\"EMP\" AS \"EMP0\""
+ + " ON \"EMP\".\"EMPNO\" = \"EMP0\".\"EMPNO\"\n"
+ + "INNER JOIN \"scott\".\"EMP\" AS \"EMP1\""
+ + " ON \"EMP\".\"EMPNO\" = \"EMP1\".\"EMPNO\"\n"
+ + "WHERE EXISTS (SELECT 1\n"
+ + "FROM \"scott\".\"DEPT\"\n"
+ + "WHERE \"EMP\".\"DEPTNO\" = \"DEPT\".\"DEPTNO\")\n"
+ + "GROUP BY \"EMP\".\"DEPTNO\"");
+ }
+
@Test void testNotBetween() {
Sql f = fixture().withConvertletTable(new SqlRexConvertletTable() {
@Override public @Nullable SqlRexConvertlet get(SqlCall call) {
