(calcite) branch main updated: [CALCITE-6370] AS operator problems with USING clause

[mbudiu]

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 a7c1e8ac77 [CALCITE-6370] AS operator problems with USING clause
a7c1e8ac77 is described below

commit a7c1e8ac774f0ca602ed598f9dd0aef99ca7ad23
Author: Norman Jordan <norman.jor...@improving.com>
AuthorDate: Thu May 23 11:00:48 2024 -0700

    [CALCITE-6370] AS operator problems with USING clause
    
    * Detect when a nested AS call is inserted and prevent it
---
 .../calcite/sql/validate/SqlValidatorImpl.java     | 43 +++++++++++++++++++---
 .../calcite/rel/rel2sql/RelToSqlConverterTest.java | 15 ++++++++
 2 files changed, 53 insertions(+), 5 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 a0e970fadb..f9d9e951df 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
@@ -47,6 +47,7 @@ import org.apache.calcite.sql.JoinType;
 import org.apache.calcite.sql.SqlAccessEnum;
 import org.apache.calcite.sql.SqlAccessType;
 import org.apache.calcite.sql.SqlAggFunction;
+import org.apache.calcite.sql.SqlAsOperator;
 import org.apache.calcite.sql.SqlBasicCall;
 import org.apache.calcite.sql.SqlCall;
 import org.apache.calcite.sql.SqlCallBinding;
@@ -118,6 +119,7 @@ import org.apache.calcite.util.Util;
 import org.apache.calcite.util.trace.CalciteTrace;
 
 import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableMap;
 import com.google.common.collect.ImmutableSet;
 import com.google.common.collect.Sets;
 
@@ -514,6 +516,8 @@ public class SqlValidatorImpl implements 
SqlValidatorWithHints {
       return identifier;
     }
 
+    final Map<String, String> fieldAliases = getFieldAliases(scope);
+
     for (String name
         : SqlIdentifier.simpleNames((SqlNodeList) getCondition(join))) {
       if (identifier.getSimple().equals(name)) {
@@ -529,11 +533,20 @@ public class SqlValidatorImpl implements 
SqlValidatorWithHints {
         }
 
         assert qualifiedNode.size() == 2;
-        return SqlStdOperatorTable.AS.createCall(SqlParserPos.ZERO,
-            SqlStdOperatorTable.COALESCE.createCall(SqlParserPos.ZERO,
-                qualifiedNode.get(0),
-                qualifiedNode.get(1)),
-            new SqlIdentifier(name, SqlParserPos.ZERO));
+
+        // If there is an alias for the column, no need to wrap the coalesce 
with an AS operator
+        boolean haveAlias = fieldAliases.containsKey(name);
+
+        final SqlCall coalesceCall =
+            SqlStdOperatorTable.COALESCE.createCall(SqlParserPos.ZERO, 
qualifiedNode.get(0),
+            qualifiedNode.get(1));
+
+        if (haveAlias) {
+          return coalesceCall;
+        } else {
+          return SqlStdOperatorTable.AS.createCall(SqlParserPos.ZERO, 
coalesceCall,
+              new SqlIdentifier(name, SqlParserPos.ZERO));
+        }
       }
     }
 
@@ -547,6 +560,26 @@ public class SqlValidatorImpl implements 
SqlValidatorWithHints {
     }
   }
 
+  private static Map<String, String> getFieldAliases(final SelectScope scope) {
+    final ImmutableMap.Builder<String, String> fieldAliases = new 
ImmutableMap.Builder<>();
+
+    for (SqlNode selectItem : scope.getNode().getSelectList()) {
+      if (selectItem instanceof SqlCall) {
+        final SqlCall call = (SqlCall) selectItem;
+        if (!(call.getOperator() instanceof SqlAsOperator)
+            || !(call.operand(0) instanceof SqlIdentifier)) {
+          continue;
+        }
+
+        final SqlIdentifier fieldIdentifier = call.operand(0);
+        fieldAliases.put(fieldIdentifier.getSimple(),
+            ((SqlIdentifier) call.operand(1)).getSimple());
+      }
+    }
+
+    return fieldAliases.build();
+  }
+
   /** Returns the set of field names in the join condition specified by USING
    * or implicitly by NATURAL, de-duplicated and in order. */
   public @Nullable List<String> usingNames(SqlJoin join) {
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 16c1011e0d..60285257b9 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
@@ -8260,6 +8260,21 @@ class RelToSqlConverterTest {
     sql(query).withStarRocks().ok(expectedStarRocks);
   }
 
+  /** Test case for
+   * <a 
href="https://issues.apache.org/jira/browse/CALCITE-6370";>[CALCITE-6370]
+   * AS operator problems with USING clause</a>.
+   */
+  @Test void testUsingClauseWithAsInProjection() {
+    String query = "select \"product_id\" AS \"x\" from 
\"foodmart\".\"product\" p0 join "
+        + " \"foodmart\".\"product\" p1 using (\"product_id\")";
+    String expectedQuery = "SELECT \"product\".\"product_id\" AS \"x\"\n"
+        + "FROM \"foodmart\".\"product\"\n"
+        + "INNER JOIN \"foodmart\".\"product\" AS \"product0\" ON "
+        + "\"product\".\"product_id\" = \"product0\".\"product_id\"";
+    sql(query)
+        .withPostgresql().ok(expectedQuery);
+  }
+
   /** Fluid interface to run tests. */
   static class Sql {
     private final CalciteAssert.SchemaSpec schemaSpec;