You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by xu...@apache.org on 2026/09/10 11:52:09 UTC
(calcite) branch main updated: [CALCITE-4922] Unqualified common column in ORDER BY fails for JOINs with USING and for NATURAL JOINs
This is an automated email from the ASF dual-hosted git repository.
xuzifu666 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 a55a520fb4 [CALCITE-4922] Unqualified common column in ORDER BY fails for JOINs with USING and for NATURAL JOINs
a55a520fb4 is described below
commit a55a520fb48ca5fee817febbf5c0704cfa61aa0d
Author: Yu Xu <12...@qq.com>
AuthorDate: Thu Sep 3 22:54:49 2026 +0800
[CALCITE-4922] Unqualified common column in ORDER BY fails for JOINs with USING and for NATURAL JOINs
---
.../apache/calcite/sql/validate/AggChecker.java | 17 +-
.../calcite/sql/validate/SqlValidatorImpl.java | 252 +++++++++++++--------
.../apache/calcite/test/SqlToRelConverterTest.java | 40 ++++
.../org/apache/calcite/test/SqlValidatorTest.java | 47 ++++
.../apache/calcite/test/SqlToRelConverterTest.xml | 85 +++++++
core/src/test/resources/sql/join.iq | 85 +++++++
6 files changed, 431 insertions(+), 95 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/sql/validate/AggChecker.java b/core/src/main/java/org/apache/calcite/sql/validate/AggChecker.java
index 23928b83ac..d039a41418 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/AggChecker.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/AggChecker.java
@@ -32,6 +32,7 @@
import java.util.Deque;
import java.util.List;
+import static org.apache.calcite.sql.SqlUtil.stripAs;
import static org.apache.calcite.util.Static.RESOURCE;
import static java.util.Objects.requireNonNull;
@@ -83,11 +84,25 @@ class AggChecker extends SqlBasicVisitor<Void> {
//~ Methods ----------------------------------------------------------------
boolean isGroupExpr(SqlNode e) {
- for (SqlNode expr : Iterables.concat(extraExprs, measureExprs, groupExprs)) {
+ // Measures are compared exactly (without stripping an AS wrapper from
+ // 'e'): a select item such as "count_plus_100 AS x" is not itself a
+ // measure expression, and stripping its alias would bypass the
+ // naked-measure check in visit(SqlIdentifier).
+ for (SqlNode expr : measureExprs) {
if (expr.equalsDeep(e, Litmus.IGNORE)) {
return true;
}
}
+ // A GROUP BY item may be a common column of a NATURAL/USING join that
+ // was expanded with an AS wrapper (e.g. "EMP.DEPTNO AS DEPTNO"); an
+ // ORDER BY reference to the underlying expression should match, so
+ // strip the AS wrapper from both sides before comparing.
+ final SqlNode e2 = stripAs(e);
+ for (SqlNode expr : Iterables.concat(extraExprs, groupExprs)) {
+ if (stripAs(expr).equalsDeep(e2, Litmus.IGNORE)) {
+ return true;
+ }
+ }
return false;
}
diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
index f84c3c790b..85a0f535a2 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
@@ -638,6 +638,110 @@ private List<String> deriveNaturalJoinColumnList(SqlJoin join) {
getNamespaceOrThrow(join.getRight()).getRowType());
}
+ /** Expands an identifier that refers to a common column of a NATURAL or
+ * USING join into a qualified reference or, for FULL JOIN, a COALESCE
+ * expression. */
+ SqlNode expandCommonColumn(SqlSelect sqlSelect, SqlNode selectItem,
+ SelectScope scope) {
+ return expandCommonColumn(sqlSelect, selectItem, scope, true);
+ }
+
+ /** As {@link #expandCommonColumn(SqlSelect, SqlNode, SelectScope)}, but
+ * allows control over whether the result is wrapped with an {@code AS}
+ * operator to preserve the column alias. */
+ SqlNode expandCommonColumn(SqlSelect sqlSelect, SqlNode selectItem,
+ SelectScope scope, boolean wrapAlias) {
+ if (!(selectItem instanceof SqlIdentifier)) {
+ return selectItem;
+ }
+
+ final SqlNode from = sqlSelect.getFrom();
+ if (!(from instanceof SqlJoin)) {
+ return selectItem;
+ }
+
+ final SqlIdentifier identifier = (SqlIdentifier) selectItem;
+ if (!identifier.isSimple()) {
+ // Qualified identifiers (e.g. t1.col) are returned unchanged.
+ // Validation of qualified common columns is performed before expansion,
+ // in validateSelectCommonColumns, where the original user-written
+ // identifier (with its source position) is still available.
+ return selectItem;
+ }
+
+ return expandExprFromJoin((SqlJoin) from, identifier, scope, wrapAlias);
+ }
+
+ private SqlNode expandExprFromJoin(SqlJoin join, SqlIdentifier identifier,
+ SelectScope scope, boolean wrapAlias) {
+ final List<String> commonColumnNames;
+ // NATURAL and USING (mutually exclusive, they cannot be combined) are
+ // the only joins with common columns; for other joins (e.g. ON), return
+ // the identifier unchanged so that regular validation reports an
+ // ambiguity error where applicable.
+ if (join.isNatural()) {
+ commonColumnNames = deriveNaturalJoinColumnList(join);
+ } else if (join.getConditionType() == JoinConditionType.USING) {
+ commonColumnNames = SqlIdentifier.simpleNames((SqlNodeList) getCondition(join));
+ } else {
+ return identifier;
+ }
+
+ final SqlNameMatcher matcher = catalogReader.nameMatcher();
+ final Set<String> fieldAliases = getFieldsAliased(scope);
+
+ for (String name : commonColumnNames) {
+ if (matcher.matches(identifier.getSimple(), name)) {
+ final List<SqlNode> qualifiedNode = new ArrayList<>();
+ for (ScopeChild child : requireNonNull(scope, "scope").children) {
+ if (matcher.indexOf(child.namespace.getRowType().getFieldNames(), name) >= 0) {
+ final SqlIdentifier exp =
+ new SqlIdentifier(
+ ImmutableList.of(child.name, name),
+ identifier.getParserPosition());
+ qualifiedNode.add(exp);
+ }
+ }
+
+ assert qualifiedNode.size() == 2;
+
+ // COALESCE is only needed for FULL JOIN; for RIGHT JOIN use the right
+ // column (always non-null), and for INNER/LEFT JOIN use the left column.
+ final JoinType joinType = join.getJoinType();
+ final SqlNode colRef;
+ if (joinType.generatesNullsOnLeft() && joinType.generatesNullsOnRight()) {
+ colRef =
+ SqlStdOperatorTable.COALESCE.createCall(
+ qualifiedNode.get(0).getParserPosition()
+ .plus(qualifiedNode.get(1).getParserPosition()),
+ qualifiedNode.get(0), qualifiedNode.get(1));
+ } else if (joinType.generatesNullsOnLeft()) {
+ colRef = qualifiedNode.get(1);
+ } else {
+ colRef = qualifiedNode.get(0);
+ }
+
+ // If there is an alias for the column, no need to wrap with an AS operator
+ boolean haveAlias = fieldAliases.contains(name);
+ if (haveAlias || !wrapAlias) {
+ return colRef;
+ } else {
+ return SqlStdOperatorTable.AS.createCall(SqlParserPos.ZERO, colRef,
+ new SqlIdentifier(identifier.getSimple(), identifier.getParserPosition()));
+ }
+ }
+ }
+
+ // Only need to try to expand the expr from the left input of join
+ // since it is always left-deep join.
+ final SqlNode node = join.getLeft();
+ if (node instanceof SqlJoin) {
+ return expandExprFromJoin((SqlJoin) node, identifier, scope, wrapAlias);
+ } else {
+ return identifier;
+ }
+ }
+
private static void validateQualifiedCommonColumn(SqlJoin join,
SqlIdentifier identifier, SelectScope scope, SqlValidatorImpl validator) {
List<String> names = validator.usingNames(join);
@@ -4644,12 +4748,13 @@ private static boolean qualifiedMatchesIdentifier(SqlQualified q1, SqlQualified
private void checkRollUpInSelectList(SqlSelect select) {
SqlValidatorScope scope = getSelectScope(select);
final SelectScope selectScope = getRawSelectScopeNonNull(select);
- final Expander expander = new Expander(this, selectScope);
for (SqlNode item : SqlNonNullableAccessors.getSelectList(select)) {
if (SqlValidatorUtil.isMeasure(item)) {
continue;
}
- SqlNode expandedItem = expander.expandCommonColumn(select, item, selectScope);
+ // Expand a common column of a NATURAL/USING join into qualified
+ // references so that roll-up checks see the underlying columns.
+ SqlNode expandedItem = expandCommonColumn(select, item, selectScope);
if (expandedItem instanceof SqlCall
&& ((SqlCall) expandedItem).getOperator() instanceof SqlAsOperator) {
expandedItem = ((SqlCall) expandedItem).operand(0);
@@ -5338,10 +5443,16 @@ protected void validateOrderList(SqlSelect select) {
final SqlValidatorScope orderScope = getOrderScope(select);
requireNonNull(orderScope, "orderScope");
+ // Replace unqualified common columns of NATURAL or USING joins with
+ // qualified references (or a COALESCE call for FULL JOIN) before regular
+ // expansion, which performs name resolution and would fail with an
+ // ambiguity error for the original item.
+ final SelectScope selectScope = getRawSelectScopeNonNull(select);
List<SqlNode> expandList = new ArrayList<>();
for (SqlNode orderItem : orderList) {
- SqlNode expandedOrderItem = expand(orderItem, orderScope);
- expandList.add(expandedOrderItem);
+ SqlNode expandedCommon =
+ expandCommonColumnInOrderBy(orderItem, select, selectScope);
+ expandList.add(expand(expandedCommon, orderScope));
}
SqlNodeList expandedOrderList =
@@ -5353,6 +5464,34 @@ protected void validateOrderList(SqlSelect select) {
}
}
+ /** Expands an unqualified reference to a common column of a NATURAL or
+ * USING join that appears in an ORDER BY item, including inside a
+ * DESCENDING, NULLS FIRST, or NULLS LAST wrapper. */
+ private SqlNode expandCommonColumnInOrderBy(SqlNode orderItem,
+ SqlSelect select, SelectScope selectScope) {
+ if (orderItem instanceof SqlIdentifier) {
+ return expandCommonColumn(select, orderItem, selectScope, false);
+ }
+ if (orderItem instanceof SqlCall) {
+ final SqlCall call = (SqlCall) orderItem;
+ switch (call.getKind()) {
+ case DESCENDING:
+ case NULLS_FIRST:
+ case NULLS_LAST:
+ final List<SqlNode> operands = call.getOperandList();
+ final SqlNode expanded =
+ expandCommonColumnInOrderBy(operands.get(0), select, selectScope);
+ if (expanded != operands.get(0)) {
+ return call.getOperator().createCall(call.getParserPosition(), expanded);
+ }
+ return orderItem;
+ default:
+ return orderItem;
+ }
+ }
+ return orderItem;
+ }
+
/** Expands a single "*" or "t.*" select item into its underlying columns,
* for a GROUP BY ALL / ORDER BY ALL rewrite.
*
@@ -8406,94 +8545,6 @@ protected SqlNode expandDynamicStar(SqlIdentifier id, SqlIdentifier fqId) {
return fqId;
}
- protected SqlNode expandCommonColumn(SqlSelect sqlSelect, SqlNode selectItem,
- SelectScope scope) {
- if (!(selectItem instanceof SqlIdentifier)) {
- return selectItem;
- }
-
- final SqlNode from = sqlSelect.getFrom();
- if (!(from instanceof SqlJoin)) {
- return selectItem;
- }
-
- final SqlIdentifier identifier = (SqlIdentifier) selectItem;
- if (!identifier.isSimple()) {
- // Qualified identifiers (e.g. t1.col) are returned unchanged.
- // Validation of qualified common columns is performed before expansion,
- // in validateSelectCommonColumns, where the original user-written
- // identifier (with its source position) is still available.
- return selectItem;
- }
-
- return expandExprFromJoin((SqlJoin) from, identifier, scope);
- }
-
- private SqlNode expandExprFromJoin(SqlJoin join, SqlIdentifier identifier, SelectScope scope) {
- List<String> commonColumnNames;
- // must be NATURAL or USING here, and cannot specify NATURAL keyword with USING clause
- if (join.isNatural()) {
- commonColumnNames = validator.deriveNaturalJoinColumnList(join);
- } else if (join.getConditionType() == JoinConditionType.USING) {
- commonColumnNames = SqlIdentifier.simpleNames((SqlNodeList) getCondition(join));
- } else {
- return identifier;
- }
-
- final SqlNameMatcher matcher = validator.getCatalogReader().nameMatcher();
- final Set<String> fieldAliases = getFieldsAliased(scope);
-
- for (String name : commonColumnNames) {
- if (matcher.matches(identifier.getSimple(), name)) {
- final List<SqlNode> qualifiedNode = new ArrayList<>();
- for (ScopeChild child : requireNonNull(scope, "scope").children) {
- if (matcher.indexOf(child.namespace.getRowType().getFieldNames(), name) >= 0) {
- final SqlIdentifier exp =
- new SqlIdentifier(
- ImmutableList.of(child.name, name),
- identifier.getParserPosition());
- qualifiedNode.add(exp);
- }
- }
-
- assert qualifiedNode.size() == 2;
-
- // COALESCE is only needed for FULL JOIN; for RIGHT JOIN use the right
- // column (always non-null), and for INNER/LEFT JOIN use the left column.
- final JoinType joinType = join.getJoinType();
- final SqlNode colRef;
- if (joinType.generatesNullsOnLeft() && joinType.generatesNullsOnRight()) {
- colRef =
- SqlStdOperatorTable.COALESCE.createCall(
- qualifiedNode.get(0).getParserPosition()
- .plus(qualifiedNode.get(1).getParserPosition()),
- qualifiedNode.get(0), qualifiedNode.get(1));
- } else if (joinType.generatesNullsOnLeft()) {
- colRef = qualifiedNode.get(1);
- } else {
- colRef = qualifiedNode.get(0);
- }
-
- // If there is an alias for the column, no need to wrap with an AS operator
- boolean haveAlias = fieldAliases.contains(name);
- if (haveAlias) {
- return colRef;
- } else {
- return SqlStdOperatorTable.AS.createCall(SqlParserPos.ZERO, colRef,
- new SqlIdentifier(identifier.getSimple(), identifier.getParserPosition()));
- }
- }
- }
-
- // Only need to try to expand the expr from the left input of join
- // since it is always left-deep join.
- final SqlNode node = join.getLeft();
- if (node instanceof SqlJoin) {
- return expandExprFromJoin((SqlJoin) node, identifier, scope);
- } else {
- return identifier;
- }
- }
}
/**
@@ -8586,6 +8637,18 @@ private SqlNode nthSelectItem(int ordinal, final SqlParserPos pos) {
}
}
+ // Expand unqualified common columns of NATURAL or USING joins,
+ // e.g. 'select name from emp join dept using (deptno) order by deptno'.
+ // Otherwise, name resolution would fail with an ambiguity error.
+ if (id.isSimple()) {
+ final SelectScope selectScope = getRawSelectScopeNonNull(select);
+ final SqlNode common =
+ expandCommonColumn(select, id, selectScope, false);
+ if (common != id) {
+ return common;
+ }
+ }
+
// No match. Return identifier unchanged.
return getScope().fullyQualify(id).identifier;
}
@@ -8654,7 +8717,8 @@ private SelectExpander(SelectExpander expander) {
}
@Override public @Nullable SqlNode visit(SqlIdentifier id) {
- final SqlNode node = expandCommonColumn(select, id, (SelectScope) getScope());
+ final SqlNode node =
+ validator.expandCommonColumn(select, id, (SelectScope) getScope());
if (node != id) {
return node;
} else {
@@ -8772,7 +8836,7 @@ static class ExtendedExpander extends Expander {
try {
// First try a standard expansion
if (clause == Clause.GROUP_BY) {
- SqlNode node = expandCommonColumn(select, id, selectScope);
+ SqlNode node = validator.expandCommonColumn(select, id, selectScope);
if (node != id) {
return node;
}
diff --git a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
index 4b2a764e66..88fa2a27c8 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -818,6 +818,46 @@ private void checkCallTypesConsistent(String sql) {
sql(sql).ok();
}
+ /** Similar to {@link #testJoinUsingWithUnqualifiedCommonColumn()},
+ * but with an unqualified common column in ORDER BY. */
+ @Test void testJoinUsingWithUnqualifiedCommonColumnInOrderBy() {
+ sql("SELECT name\n"
+ + "FROM emp JOIN dept using (deptno)\n"
+ + "ORDER BY deptno").ok();
+ }
+
+ /** Similar to {@link #testJoinUsingWithUnqualifiedCommonColumn()},
+ * but NATURAL JOIN and an unqualified common column in ORDER BY. */
+ @Test void testJoinNaturalWithUnqualifiedCommonColumnInOrderBy() {
+ sql("SELECT name\n"
+ + "FROM emp NATURAL JOIN dept\n"
+ + "ORDER BY deptno").ok();
+ }
+
+ /** Similar to {@link #testJoinUsingWithUnqualifiedCommonColumn()},
+ * but FULL JOIN and an unqualified common column in ORDER BY. */
+ @Test void testFullJoinUsingWithUnqualifiedCommonColumnInOrderBy() {
+ sql("SELECT name\n"
+ + "FROM emp FULL JOIN dept using (deptno)\n"
+ + "ORDER BY deptno DESC").ok();
+ }
+
+ /** Similar to {@link #testJoinUsingWithAggregate()},
+ * but with GROUP BY and an unqualified common column in ORDER BY. */
+ @Test void testJoinUsingWithUnqualifiedCommonColumnInGroupByAndOrderBy() {
+ sql("SELECT deptno, count(*)\n"
+ + "FROM emp JOIN dept using (deptno)\n"
+ + "GROUP BY deptno ORDER BY deptno").ok();
+ }
+
+ /** Similar to {@link #testJoinNaturalWithAggregate()},
+ * but with GROUP BY and an unqualified common column in ORDER BY. */
+ @Test void testJoinNaturalWithUnqualifiedCommonColumnInGroupByAndOrderBy() {
+ sql("SELECT deptno, count(*)\n"
+ + "FROM emp NATURAL JOIN dept\n"
+ + "GROUP BY deptno ORDER BY deptno DESC").ok();
+ }
+
/** Similar to {@link #testJoinUsingWithUnqualifiedCommonColumn()},
* but with nested common column. */
@Test void testJoinUsingWithUnqualifiedNestedCommonColumn() {
diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
index 19005f352c..f5d9f76dfe 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -6253,6 +6253,53 @@ private ImmutableList<ImmutableBitSet> cube(ImmutableBitSet... sets) {
.ok();
}
+ /** Tests that an unqualified common column in ORDER BY is valid for
+ * JOINs with USING and for NATURAL JOINs. */
+ @Test void testOrderByUnqualifiedCommonColumn() {
+ sql("select name from emp join dept using (deptno) order by deptno").ok();
+ sql("select name from emp natural join dept order by deptno").ok();
+ sql("select name from emp full join dept using (deptno) order by deptno").ok();
+ sql("select name from emp left join dept using (deptno) order by deptno").ok();
+ sql("select name from emp right join dept using (deptno) order by deptno").ok();
+ sql("select name from emp join dept using (deptno) order by deptno desc").ok();
+ sql("select name from emp join dept using (deptno)"
+ + " order by deptno nulls first").ok();
+ sql("select count(*) from emp join dept using (deptno)"
+ + " group by deptno").ok();
+ sql("select count(*) from emp join dept using (deptno)"
+ + " group by deptno order by deptno").ok();
+ sql("select deptno from emp join dept using (deptno)")
+ .withValidatorConfig(c -> c.withIdentifierExpansion(true))
+ .ok();
+ sql("select deptno from emp join dept using (deptno)"
+ + " order by deptno")
+ .withValidatorConfig(c -> c.withIdentifierExpansion(true))
+ .ok();
+ sql("select deptno as d from emp join dept using (deptno)"
+ + " order by deptno")
+ .withValidatorConfig(c -> c.withIdentifierExpansion(true))
+ .ok();
+
+ final String sql = "select name from emp join dept using (deptno) order by deptno";
+ final String expected = "SELECT `DEPT`.`NAME`\n"
+ + "FROM `CATALOG`.`SALES`.`EMP` AS `EMP`\n"
+ + "INNER JOIN `CATALOG`.`SALES`.`DEPT` AS `DEPT` USING (`DEPTNO`)\n"
+ + "ORDER BY `EMP`.`DEPTNO`";
+ sql(sql)
+ .withValidatorConfig(c -> c.withIdentifierExpansion(true))
+ .rewritesTo(expected);
+
+ final String fullSql =
+ "select name from emp full join dept using (deptno) order by deptno";
+ final String fullExpected = "SELECT `DEPT`.`NAME`\n"
+ + "FROM `CATALOG`.`SALES`.`EMP` AS `EMP`\n"
+ + "FULL JOIN `CATALOG`.`SALES`.`DEPT` AS `DEPT` USING (`DEPTNO`)\n"
+ + "ORDER BY COALESCE(`EMP`.`DEPTNO`, `DEPT`.`DEPTNO`)";
+ sql(fullSql)
+ .withValidatorConfig(c -> c.withIdentifierExpansion(true))
+ .rewritesTo(fullExpected);
+ }
+
/** Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-7051">[CALCITE-7051]
* JOIN with USING does not match the appropriate columns when caseSensitive is false</a>. */
diff --git a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
index f3c0134656..2c32494f60 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -2679,6 +2679,23 @@ from emp]]>
<![CDATA[
LogicalProject(F=[*(/INT(CASE(>=(11045000:INTERVAL HOUR TO SECOND, 0), 11045000:INTERVAL HOUR TO SECOND, -(11045000:INTERVAL HOUR TO SECOND, 3599999:INTERVAL HOUR TO SECOND)), 3600000), 3600000)], C=[*(/INT(CASE(>=(11045000:INTERVAL HOUR TO SECOND, 0), +(11045000:INTERVAL HOUR TO SECOND, 3599999:INTERVAL HOUR TO SECOND), 11045000:INTERVAL HOUR TO SECOND), 3600000), 3600000)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testFullJoinUsingWithUnqualifiedCommonColumnInOrderBy">
+ <Resource name="sql">
+ <![CDATA[SELECT name
+FROM emp FULL JOIN dept using (deptno)
+ORDER BY deptno DESC]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(NAME=[$0])
+ LogicalSort(sort0=[$1], dir0=[DESC])
+ LogicalProject(NAME=[$10], EXPR$1=[COALESCE($7, $9)])
+ LogicalJoin(condition=[=($7, $9)], joinType=[full])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
</Resource>
</TestCase>
@@ -4325,6 +4342,40 @@ LogicalProject(DEPTNO=[$7], NAME=[$10])
LogicalJoin(condition=[=($7, $9)], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testJoinNaturalWithUnqualifiedCommonColumnInGroupByAndOrderBy">
+ <Resource name="sql">
+ <![CDATA[SELECT deptno, count(*)
+FROM emp NATURAL JOIN dept
+GROUP BY deptno ORDER BY deptno DESC]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalSort(sort0=[$0], dir0=[DESC])
+ LogicalAggregate(group=[{0}], EXPR$1=[COUNT()])
+ LogicalProject(DEPTNO=[$7])
+ LogicalJoin(condition=[=($7, $9)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testJoinNaturalWithUnqualifiedCommonColumnInOrderBy">
+ <Resource name="sql">
+ <![CDATA[SELECT name
+FROM emp NATURAL JOIN dept
+ORDER BY deptno]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(NAME=[$0])
+ LogicalSort(sort0=[$1], dir0=[ASC])
+ LogicalProject(NAME=[$10], DEPTNO=[$7])
+ LogicalJoin(condition=[=($7, $9)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
</Resource>
</TestCase>
@@ -4762,6 +4813,40 @@ LogicalProject(DEPTNO=[$7], NAME=[$10])
LogicalJoin(condition=[=($7, $9)], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testJoinUsingWithUnqualifiedCommonColumnInGroupByAndOrderBy">
+ <Resource name="sql">
+ <![CDATA[SELECT deptno, count(*)
+FROM emp JOIN dept using (deptno)
+GROUP BY deptno ORDER BY deptno]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalSort(sort0=[$0], dir0=[ASC])
+ LogicalAggregate(group=[{0}], EXPR$1=[COUNT()])
+ LogicalProject(DEPTNO=[$7])
+ LogicalJoin(condition=[=($7, $9)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testJoinUsingWithUnqualifiedCommonColumnInOrderBy">
+ <Resource name="sql">
+ <![CDATA[SELECT name
+FROM emp JOIN dept using (deptno)
+ORDER BY deptno]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(NAME=[$0])
+ LogicalSort(sort0=[$1], dir0=[ASC])
+ LogicalProject(NAME=[$10], DEPTNO=[$7])
+ LogicalJoin(condition=[=($7, $9)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
</Resource>
</TestCase>
diff --git a/core/src/test/resources/sql/join.iq b/core/src/test/resources/sql/join.iq
index ca1ba1451c..55add71f05 100644
--- a/core/src/test/resources/sql/join.iq
+++ b/core/src/test/resources/sql/join.iq
@@ -405,6 +405,91 @@ right join dept using (deptno);
!ok
+# [CALCITE-4922] Unqualified common column in ORDER BY fails for JOINs
+# with USING and for NATURAL JOINs. A second sort key (ename) makes the
+# result deterministic, so it matches PostgreSQL and other engines.
+select ename
+from emp
+join dept using (deptno)
+order by deptno, ename;
++--------+
+| ENAME |
++--------+
+| CLARK |
+| KING |
+| MILLER |
+| ADAMS |
+| FORD |
+| JONES |
+| SCOTT |
+| SMITH |
+| ALLEN |
+| BLAKE |
+| JAMES |
+| MARTIN |
+| TURNER |
+| WARD |
++--------+
+(14 rows)
+
+!ok
+
+# Same, but NATURAL JOIN and descending
+select ename
+from emp
+natural join dept
+order by deptno desc, ename;
++--------+
+| ENAME |
++--------+
+| ALLEN |
+| BLAKE |
+| JAMES |
+| MARTIN |
+| TURNER |
+| WARD |
+| ADAMS |
+| FORD |
+| JONES |
+| SCOTT |
+| SMITH |
+| CLARK |
+| KING |
+| MILLER |
++--------+
+(14 rows)
+
+!ok
+
+# Same, but FULL JOIN: the unqualified common column in ORDER BY sorts
+# on the coalesced column, so dept 40 (with no employees) comes last
+select deptno, dname
+from emp
+full join dept using (deptno)
+order by deptno, ename;
++--------+------------+
+| DEPTNO | DNAME |
++--------+------------+
+| 10 | ACCOUNTING |
+| 10 | ACCOUNTING |
+| 10 | ACCOUNTING |
+| 20 | RESEARCH |
+| 20 | RESEARCH |
+| 20 | RESEARCH |
+| 20 | RESEARCH |
+| 20 | RESEARCH |
+| 30 | SALES |
+| 30 | SALES |
+| 30 | SALES |
+| 30 | SALES |
+| 30 | SALES |
+| 30 | SALES |
+| 40 | OPERATIONS |
++--------+------------+
+(15 rows)
+
+!ok
+
# Push aggregate through join
select distinct dept.deptno, emp.deptno
from "scott".emp join "scott".dept using (deptno);