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 c1d9f50bf6 [CALCITE-7085] JOIN USING with unqualified common column
fails in a conformance where allowQualifyingCommonColumn is false (e.g. Oracle,
Presto)
c1d9f50bf6 is described below
commit c1d9f50bf6931ac2b499692e121112c4de4b5b40
Author: Terran <[email protected]>
AuthorDate: Wed Apr 29 16:23:32 2026 +0800
[CALCITE-7085] JOIN USING with unqualified common column fails in a
conformance where allowQualifyingCommonColumn is false (e.g. Oracle, Presto)
---
.../calcite/sql/validate/SqlValidatorImpl.java | 62 ++++++++++++++++++++--
.../apache/calcite/test/SqlToRelConverterTest.java | 41 ++++++++++++++
.../apache/calcite/test/SqlToRelConverterTest.xml | 56 +++++++++++++++++++
3 files changed, 156 insertions(+), 3 deletions(-)
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 e21d62aecb..1fcbc24c36 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
@@ -643,6 +643,31 @@ private static void validateQualifiedCommonColumn(SqlJoin
join,
}
}
+ /** Validates that a SQL node tree does not contain qualified references
+ * to common columns in a JOIN USING or NATURAL JOIN context.
+ * This is called before identifier expansion to catch user-written
+ * qualified common columns in conformances where they are disallowed
+ * (e.g. Oracle, Presto).
+ *
+ * @param nodeList The list of SQL nodes to check
+ * @param join The JOIN node containing USING/NATURAL condition
+ * @param scope The select scope for resolving identifiers
+ */
+ private void validateNoQualifiedCommonColumns(SqlNodeList nodeList,
+ SqlJoin join, SelectScope scope) {
+ for (SqlNode item : nodeList) {
+ item.accept(new SqlShuttle() {
+ @Override public SqlNode visit(SqlIdentifier id) {
+ if (!id.isSimple()) {
+ validateQualifiedCommonColumn(join, id, scope,
+ SqlValidatorImpl.this);
+ }
+ return id;
+ }
+ });
+ }
+ }
+
private boolean expandStar(List<SqlNode> selectItems, Set<String> aliases,
PairList<String, RelDataType> fields, boolean includeSystemVars,
SelectScope scope, SqlNode node) {
@@ -5253,6 +5278,17 @@ protected void validateGroupClause(SqlSelect select) {
// expand the expression in group list.
List<SqlNode> expandedList = new ArrayList<>();
+ // Validate that GROUP BY items do not qualify common columns
+ // in conformances where it is disallowed (e.g. Oracle, Presto).
+ // This must run before expansion, because expansion generates
+ // qualified identifiers that should not trigger this validation.
+ if (!config.conformance().allowQualifyingCommonColumn()) {
+ final SqlNode from = select.getFrom();
+ if (from instanceof SqlJoin) {
+ validateNoQualifiedCommonColumns(groupList,
+ (SqlJoin) from, getRawSelectScopeNonNull(select));
+ }
+ }
for (SqlNode groupItem : groupList) {
SqlNode expandedItem =
extendedExpand(groupItem, groupScope, select, Clause.GROUP_BY);
@@ -5404,6 +5440,19 @@ protected void validateHavingClause(SqlSelect select) {
}
}
+ /** Validates that SELECT items do not qualify common columns
+ * in conformances where it is disallowed (e.g. Oracle, Presto). */
+ private void validateSelectCommonColumns(SqlNodeList selectItems,
+ SqlSelect select) {
+ if (!config().conformance().allowQualifyingCommonColumn()) {
+ final SqlNode from = select.getFrom();
+ if (from instanceof SqlJoin) {
+ validateNoQualifiedCommonColumns(selectItems,
+ (SqlJoin) from, getRawSelectScopeNonNull(select));
+ }
+ }
+ }
+
protected RelDataType validateSelectList(final SqlNodeList selectItems,
SqlSelect select, RelDataType targetRowType) {
// First pass, ensure that aliases are unique. "*" and "TABLE.*" items
@@ -5417,6 +5466,12 @@ protected RelDataType validateSelectList(final
SqlNodeList selectItems,
// Populated during select expansion when SqlConformance.isSelectAlias !=
UNSUPPORTED
final Map<String, SqlNode> expansions = new HashMap<>();
+ // Validate that SELECT items do not qualify common columns
+ // in conformances where it is disallowed (e.g. Oracle, Presto).
+ // This must run before expansion, because expansion generates
+ // qualified identifiers that should not trigger this validation.
+ validateSelectCommonColumns(selectItems, select);
+
for (SqlNode selectItem : selectItems) {
if (selectItem instanceof SqlSelect) {
handleScalarSubQuery(select, (SqlSelect) selectItem,
@@ -7672,9 +7727,10 @@ protected SqlNode expandCommonColumn(SqlSelect
sqlSelect, SqlNode selectItem,
final SqlIdentifier identifier = (SqlIdentifier) selectItem;
if (!identifier.isSimple()) {
- if (!validator.config().conformance().allowQualifyingCommonColumn()) {
- validateQualifiedCommonColumn((SqlJoin) from, identifier, scope,
validator);
- }
+ // 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;
}
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 4bc62e7c3d..ebad0e9450 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -6092,4 +6092,45 @@ void checkUserDefinedOrderByOver(NullCollation
nullCollation) {
final String sql = "select distinct deptno, deptno, empno, 1, 'a' from emp
order by rand(), 1";
sql(sql).ok();
}
+
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7085";>[CALCITE-7085]
+ * JOIN USING with unqualified common column fails in a conformance where
+ * allowQualifyingCommonColumn is false (e.g. Oracle, Presto)</a>. */
+ @Test void testJoinUsingWithConformanceOracle() {
+ final String sql = "SELECT deptno, name\n"
+ + "FROM emp JOIN dept using (deptno)";
+ sql(sql).withConformance(SqlConformanceEnum.ORACLE_10).ok();
+ }
+
+
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7085";>[CALCITE-7085]
+ * JOIN USING with unqualified common column fails in a conformance where
+ * allowQualifyingCommonColumn is false (e.g. Oracle, Presto)</a>. */
+ @Test void testLeftJoinUsingWithConformanceOracle() {
+ final String sql = "SELECT deptno, name\n"
+ + "FROM emp LEFT OUTER JOIN dept using (deptno)";
+ sql(sql).withConformance(SqlConformanceEnum.ORACLE_10).ok();
+ }
+
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7085";>[CALCITE-7085]
+ * JOIN USING with unqualified common column fails in a conformance where
+ * allowQualifyingCommonColumn is false (e.g. Oracle, Presto)</a>. */
+ @Test void testRightJoinUsingWithConformanceOracle() {
+ final String sql = "SELECT deptno, name\n"
+ + "FROM emp RIGHT OUTER JOIN dept using (deptno)";
+ sql(sql).withConformance(SqlConformanceEnum.ORACLE_10).ok();
+ }
+
+ /** Test case of
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7085";>[CALCITE-7085]
+ * JOIN USING with unqualified common column fails in a conformance where
+ * allowQualifyingCommonColumn is false (e.g. Oracle, Presto)</a>. */
+ @Test void testJoinUsingWithConformancePresto() {
+ final String sql = "SELECT deptno, name\n"
+ + "FROM emp JOIN dept using (deptno)";
+ sql(sql).withConformance(SqlConformanceEnum.PRESTO).ok();
+ }
}
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 53662875e0..56f169629a 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -4368,6 +4368,34 @@ LogicalProject(DEPTNO=[$0], EXPR$1=[$1])
LogicalJoin(condition=[=($7, $9)], joinType=[full])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testJoinUsingWithConformanceOracle">
+ <Resource name="sql">
+ <![CDATA[SELECT deptno, name
+FROM emp JOIN dept using (deptno)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+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="testJoinUsingWithConformancePresto">
+ <Resource name="sql">
+ <![CDATA[SELECT deptno, name
+FROM emp JOIN dept using (deptno)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(DEPTNO=[$7], NAME=[$10])
+ LogicalJoin(condition=[=($7, $9)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
</Resource>
</TestCase>
@@ -5000,6 +5028,20 @@ LogicalProject(C=[$0], N=[$3])
LogicalAggregate(group=[{0}])
LogicalProject($f2=[+($0, 1)])
LogicalValues(tuples=[[{ 4 }]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testLeftJoinUsingWithConformanceOracle">
+ <Resource name="sql">
+ <![CDATA[SELECT deptno, name
+FROM emp LEFT OUTER JOIN dept using (deptno)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(DEPTNO=[$7], NAME=[$10])
+ LogicalJoin(condition=[=($7, $9)], joinType=[left])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
</Resource>
</TestCase>
@@ -7396,6 +7438,20 @@ GROUP BY ROLLUP(deptno)]]>
LogicalAggregate(group=[{0}], groups=[[{0}, {}]], EXPR$1=[COUNT(DISTINCT $1)])
LogicalProject(DEPTNO=[$7], EMPNO=[$0])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testRightJoinUsingWithConformanceOracle">
+ <Resource name="sql">
+ <![CDATA[SELECT deptno, name
+FROM emp RIGHT OUTER JOIN dept using (deptno)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(DEPTNO=[COALESCE($7, $9)], NAME=[$10])
+ LogicalJoin(condition=[=($7, $9)], joinType=[right])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
</Resource>
</TestCase>
