(calcite) branch main updated: [CALCITE-6756] Preserving CAST of STRING operand in binary comparison for PostgreSQL

[xiong]

This is an automated email from the ASF dual-hosted git repository.

xiong 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 ab8843d2f7 [CALCITE-6756] Preserving CAST of STRING operand in binary 
comparison for PostgreSQL
ab8843d2f7 is described below

commit ab8843d2f74bb0786a403012d66e9d3f8b66321d
Author: Xiong Duan <nobigo...@gmail.com>
AuthorDate: Sat Jan 18 10:02:41 2025 +0800

    [CALCITE-6756] Preserving CAST of STRING operand in binary comparison for 
PostgreSQL
---
 .../calcite/sql/dialect/PostgresqlSqlDialect.java  | 15 +++++++++++++
 .../calcite/rel/rel2sql/RelToSqlConverterTest.java | 26 ++++++++++++++++++++++
 2 files changed, 41 insertions(+)

diff --git 
a/core/src/main/java/org/apache/calcite/sql/dialect/PostgresqlSqlDialect.java 
b/core/src/main/java/org/apache/calcite/sql/dialect/PostgresqlSqlDialect.java
index 77db1f3636..12f55919f5 100644
--- 
a/core/src/main/java/org/apache/calcite/sql/dialect/PostgresqlSqlDialect.java
+++ 
b/core/src/main/java/org/apache/calcite/sql/dialect/PostgresqlSqlDialect.java
@@ -21,6 +21,9 @@
 import org.apache.calcite.rel.type.RelDataType;
 import org.apache.calcite.rel.type.RelDataTypeSystem;
 import org.apache.calcite.rel.type.RelDataTypeSystemImpl;
+import org.apache.calcite.rex.RexCall;
+import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.rex.RexUtil;
 import org.apache.calcite.sql.SqlAlienSystemTypeNameSpec;
 import org.apache.calcite.sql.SqlBasicCall;
 import org.apache.calcite.sql.SqlCall;
@@ -40,6 +43,7 @@
 import org.apache.calcite.sql.fun.SqlStdOperatorTable;
 import org.apache.calcite.sql.parser.SqlParserPos;
 import org.apache.calcite.sql.type.SqlTypeName;
+import org.apache.calcite.sql.type.SqlTypeUtil;
 
 import com.google.common.collect.ImmutableList;
 
@@ -155,6 +159,17 @@ public PostgresqlSqlDialect(Context context) {
     }
   }
 
+  @Override public boolean supportsImplicitTypeCoercion(RexCall call) {
+    final RexNode operand0 = call.getOperands().get(0);
+    RelDataType callType = call.getType();
+    boolean supportImplicit = super.supportsImplicitTypeCoercion(call);
+    boolean isNumericType = supportImplicit && SqlTypeUtil.isNumeric(callType);
+    if (isNumericType) {
+      return false;
+    }
+    return supportImplicit && RexUtil.isLiteral(operand0, false);
+  }
+
   @Override public boolean requiresAliasForFromItems() {
     return true;
   }
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 6b82317b7d..59997df046 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
@@ -7982,6 +7982,32 @@ private void checkLiteral2(String expression, String 
expected) {
         .withBigQuery().ok(expectedBiqquery);
   }
 
+  /** Test case for
+   * <a 
href="https://issues.apache.org/jira/browse/CALCITE-6756";>[CALCITE-6756]
+   * Preserving CAST of STRING operand in binary comparison for 
PostgreSQL</a>. */
+  @Test void testImplicitTypeCoercionPostgreSQL() {
+    final String query = "select \"employee_id\" "
+        + "from \"foodmart\".\"employee\" "
+        + "where 10 = cast(\"full_name\" as int) and "
+        + "  \"first_name\" > cast(10 as varchar) and "
+        + "\"birth_date\" = cast('1914-02-02' as date) or "
+        + "\"hire_date\" = cast('1996-01-01 '||'00:00:00' as timestamp) or "
+        + "\"hire_date\" = '1996-01-01 00:00:00' or "
+        + "cast(\"full_name\" as timestamp) = \"hire_date\" or "
+        + "cast('10' as varchar) = 1";
+    final String expectedPostgresql = "SELECT \"employee_id\"\n"
+        + "FROM \"foodmart\".\"employee\"\n"
+        + "WHERE 10 = CAST(\"full_name\" AS INTEGER) AND "
+        + "\"first_name\" > CAST(10 AS VARCHAR) AND "
+        + "\"birth_date\" = '1914-02-02' OR "
+        + "\"hire_date\" = CAST('1996-01-01 ' || '00:00:00' AS TIMESTAMP(0)) 
OR "
+        + "\"hire_date\" = '1996-01-01 00:00:00' OR "
+        + "CAST(\"full_name\" AS TIMESTAMP(0)) = \"hire_date\" OR "
+        + "CAST('10' AS INTEGER) = 1";
+    sql(query)
+        .withPostgresql().ok(expectedPostgresql);
+  }
+
   /** Test case for
    * <a 
href="https://issues.apache.org/jira/browse/CALCITE-6149";>[CALCITE-6149]
    * Unparse for CAST Nullable with ClickHouseSqlDialect</a>. */