[calcite] 01/01: [CALCITE-3381] When using BigQuery dialect, Rel2SQL converter converts SQL types to BigQuery types (Rui Wang)

[hy...@apache.org]

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

hyuan pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git

commit 30a0dd0a2edebdc08292b7d59988c60ccf754bed
Author: amaliujia <am...@163.com>
AuthorDate: Wed Oct 2 11:01:43 2019 -0700

    [CALCITE-3381] When using BigQuery dialect, Rel2SQL converter converts SQL types to BigQuery types (Rui Wang)
    
    VARCHAR -> STRING
    VARBINARY -> BYTES
    BIGINT -> INT64
    DECIMAL -> NUMERIC
    DOUBLE -> FLOAT64
    BOOLEAN -> BOOL
    DATE -> DATE
    TIME -> TIME
    TIMESTAMP -> TIMESTAMP
    
    Close #1482
---
 .../calcite/sql/dialect/BigQuerySqlDialect.java    | 42 ++++++++++++++++++++++
 .../calcite/rel/rel2sql/RelToSqlConverterTest.java | 22 ++++++++++++
 2 files changed, 64 insertions(+)

diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java b/core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java
index 8581c68..7917e69 100644
--- a/core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/dialect/BigQuerySqlDialect.java
@@ -18,14 +18,20 @@ package org.apache.calcite.sql.dialect;
 
 import org.apache.calcite.avatica.util.Casing;
 import org.apache.calcite.config.NullCollation;
+import org.apache.calcite.rel.type.RelDataType;
 import org.apache.calcite.sql.SqlCall;
+import org.apache.calcite.sql.SqlDataTypeSpec;
 import org.apache.calcite.sql.SqlDialect;
+import org.apache.calcite.sql.SqlIdentifier;
 import org.apache.calcite.sql.SqlKind;
 import org.apache.calcite.sql.SqlNode;
 import org.apache.calcite.sql.SqlOperator;
 import org.apache.calcite.sql.SqlSetOperator;
 import org.apache.calcite.sql.SqlSyntax;
+import org.apache.calcite.sql.SqlUserDefinedTypeNameSpec;
 import org.apache.calcite.sql.SqlWriter;
+import org.apache.calcite.sql.parser.SqlParserPos;
+import org.apache.calcite.sql.type.BasicSqlType;
 
 import com.google.common.collect.ImmutableList;
 
@@ -132,6 +138,42 @@ public class BigQuerySqlDialect extends SqlDialect {
     }
   }
 
+  /** BigQuery data type reference:
+   * <a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types">
+   * Bigquery Standard SQL Data Types</a>
+   */
+  @Override public SqlNode getCastSpec(final RelDataType type) {
+    if (type instanceof BasicSqlType) {
+      switch (type.getSqlTypeName()) {
+      case BIGINT:
+        return createSqlDataTypeSpecByName("INT64");
+      case DOUBLE:
+        return createSqlDataTypeSpecByName("FLOAT64");
+      case DECIMAL:
+        return createSqlDataTypeSpecByName("NUMERIC");
+      case BOOLEAN:
+        return createSqlDataTypeSpecByName("BOOL");
+      case VARCHAR:
+        return createSqlDataTypeSpecByName("STRING");
+      case VARBINARY:
+        return createSqlDataTypeSpecByName("BYTES");
+      case DATE:
+        return createSqlDataTypeSpecByName("DATE");
+      case TIME:
+        return createSqlDataTypeSpecByName("TIME");
+      case TIMESTAMP:
+        return createSqlDataTypeSpecByName("TIMESTAMP");
+      }
+    }
+    return super.getCastSpec(type);
+  }
+
+  private SqlDataTypeSpec createSqlDataTypeSpecByName(String identifierName) {
+    SqlUserDefinedTypeNameSpec typeNameSpec = new SqlUserDefinedTypeNameSpec(
+            new SqlIdentifier(identifierName, SqlParserPos.ZERO), SqlParserPos.ZERO);
+    return new SqlDataTypeSpec(typeNameSpec, SqlParserPos.ZERO);
+  }
+
   /**
    * List of BigQuery Specific Operators needed to form Syntactically Correct SQL.
    */
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 a4185b2..6a137ac 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
@@ -816,6 +816,28 @@ public class RelToSqlConverterTest {
     sql(query).withHive().ok(expected);
   }
 
+  @Test public void testBigQueryCast() {
+    String query = "select cast(cast(\"employee_id\" as varchar) as bigint), "
+            + "cast(cast(\"employee_id\" as varchar) as varbinary), "
+            + "cast(cast(\"employee_id\" as varchar) as timestamp), "
+            + "cast(cast(\"employee_id\" as varchar) as double), "
+            + "cast(cast(\"employee_id\" as varchar) as decimal), "
+            + "cast(cast(\"employee_id\" as varchar) as date), "
+            + "cast(cast(\"employee_id\" as varchar) as time), "
+            + "cast(cast(\"employee_id\" as varchar) as boolean) "
+            + "from \"foodmart\".\"reserve_employee\" ";
+    final String expected = "SELECT CAST(CAST(employee_id AS STRING) AS INT64), "
+            + "CAST(CAST(employee_id AS STRING) AS BYTES), "
+            + "CAST(CAST(employee_id AS STRING) AS TIMESTAMP), "
+            + "CAST(CAST(employee_id AS STRING) AS FLOAT64), "
+            + "CAST(CAST(employee_id AS STRING) AS NUMERIC), "
+            + "CAST(CAST(employee_id AS STRING) AS DATE), "
+            + "CAST(CAST(employee_id AS STRING) AS TIME), "
+            + "CAST(CAST(employee_id AS STRING) AS BOOL)\n"
+            + "FROM foodmart.reserve_employee";
+    sql(query).withBigQuery().ok(expected);
+  }
+
   /** Test case for
    * <a href="https://issues.apache.org/jira/browse/CALCITE-3220">[CALCITE-3220]
    * HiveSqlDialect should transform the SQL-standard TRIM function to TRIM,