[calcite] branch master updated: [CALCITE-3072] Generate right SQL for FLOOR&SUBSTRING functions in SparkSqlDialect (DonnyZone)

[danny0405]

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

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


The following commit(s) were added to refs/heads/master by this push:
     new 7fa13bf  [CALCITE-3072] Generate right SQL for FLOOR&SUBSTRING 
functions in SparkSqlDialect (DonnyZone)
7fa13bf is described below

commit 7fa13bf164f9354aed492b03f753c2b9b30b0164
Author: wellfengzhu <wellfeng...@tencent.com>
AuthorDate: Thu May 16 13:01:31 2019 +0800

    [CALCITE-3072] Generate right SQL for FLOOR&SUBSTRING functions in 
SparkSqlDialect (DonnyZone)
    
    TimeUnit first in SparkSQL date_trunc;
    Add test for floor with one operand.
    
    Close apache/calcite#1216
---
 .../calcite/sql/dialect/SparkSqlDialect.java       | 41 ++++++++++++++++++++++
 .../calcite/rel/rel2sql/RelToSqlConverterTest.java | 32 +++++++++++++++++
 2 files changed, 73 insertions(+)

diff --git 
a/core/src/main/java/org/apache/calcite/sql/dialect/SparkSqlDialect.java 
b/core/src/main/java/org/apache/calcite/sql/dialect/SparkSqlDialect.java
index f37eb5a..e7edb08 100644
--- a/core/src/main/java/org/apache/calcite/sql/dialect/SparkSqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/dialect/SparkSqlDialect.java
@@ -16,11 +16,21 @@
  */
 package org.apache.calcite.sql.dialect;
 
+import org.apache.calcite.avatica.util.TimeUnitRange;
 import org.apache.calcite.config.NullCollation;
 import org.apache.calcite.sql.JoinType;
+import org.apache.calcite.sql.SqlCall;
 import org.apache.calcite.sql.SqlDialect;
+import org.apache.calcite.sql.SqlFunction;
+import org.apache.calcite.sql.SqlFunctionCategory;
+import org.apache.calcite.sql.SqlKind;
+import org.apache.calcite.sql.SqlLiteral;
 import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.SqlUtil;
 import org.apache.calcite.sql.SqlWriter;
+import org.apache.calcite.sql.fun.SqlFloorFunction;
+import org.apache.calcite.sql.fun.SqlStdOperatorTable;
+import org.apache.calcite.sql.type.ReturnTypes;
 
 /**
  * A <code>SqlDialect</code> implementation for the APACHE SPARK database.
@@ -31,6 +41,11 @@ public class SparkSqlDialect extends SqlDialect {
           .withDatabaseProduct(DatabaseProduct.SPARK)
           .withNullCollation(NullCollation.LOW));
 
+  private static final SqlFunction SPARKSQL_SUBSTRING =
+      new SqlFunction("SUBSTRING", SqlKind.OTHER_FUNCTION,
+          ReturnTypes.ARG0_NULLABLE_VARYING, null, null,
+          SqlFunctionCategory.STRING);
+
   /**
    * Creates a SparkSqlDialect.
    */
@@ -55,6 +70,32 @@ public class SparkSqlDialect extends SqlDialect {
       SqlNode fetch) {
     unparseFetchUsingLimit(writer, offset, fetch);
   }
+
+  @Override public void unparseCall(SqlWriter writer, SqlCall call,
+      int leftPrec, int rightPrec) {
+    if (call.getOperator() == SqlStdOperatorTable.SUBSTRING) {
+      SqlUtil.unparseFunctionSyntax(SPARKSQL_SUBSTRING, writer, call);
+    } else {
+      switch (call.getKind()) {
+      case FLOOR:
+        if (call.operandCount() != 2) {
+          super.unparseCall(writer, call, leftPrec, rightPrec);
+          return;
+        }
+
+        final SqlLiteral timeUnitNode = call.operand(1);
+        final TimeUnitRange timeUnit = 
timeUnitNode.getValueAs(TimeUnitRange.class);
+
+        SqlCall call2 = SqlFloorFunction.replaceTimeUnitOperand(call, 
timeUnit.name(),
+            timeUnitNode.getParserPosition());
+        SqlFloorFunction.unparseDatetimeFunction(writer, call2, "DATE_TRUNC", 
false);
+        break;
+
+      default:
+        super.unparseCall(writer, call, leftPrec, rightPrec);
+      }
+    }
+  }
 }
 
 // End SparkSqlDialect.java
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 2ebfadc..693c3cc 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
@@ -3265,6 +3265,38 @@ public class RelToSqlConverterTest {
     sql(query).withSpark().ok(expected);
   }
 
+  @Test public void testSubstringInSpark() {
+    final String query = "select substring(\"brand_name\" from 2) "
+        + "from \"product\"\n";
+    final String expected = "SELECT SUBSTRING(brand_name, 2)\n"
+        + "FROM foodmart.product";
+    sql(query).withSpark().ok(expected);
+  }
+
+  @Test public void testSubstringWithForInSpark() {
+    final String query = "select substring(\"brand_name\" from 2 for 3) "
+        + "from \"product\"\n";
+    final String expected = "SELECT SUBSTRING(brand_name, 2, 3)\n"
+        + "FROM foodmart.product";
+    sql(query).withSpark().ok(expected);
+  }
+
+  @Test public void testFloorInSpark() {
+    final String query = "select floor(\"hire_date\" TO MINUTE) "
+        + "from \"employee\"";
+    final String expected = "SELECT DATE_TRUNC('MINUTE', hire_date)\n"
+        + "FROM foodmart.employee";
+    sql(query).withSpark().ok(expected);
+  }
+
+  @Test public void testNumericFloorInSpark() {
+    final String query = "select floor(\"salary\") "
+        + "from \"employee\"";
+    final String expected = "SELECT FLOOR(salary)\n"
+        + "FROM foodmart.employee";
+    sql(query).withSpark().ok(expected);
+  }
+
   @Test public void testJsonType() {
     String query = "select json_type(\"product_name\") from \"product\"";
     final String expected = "SELECT "