[calcite] branch main updated: [CALCITE-5922] The SQL generated for the POSITION function(with 3 input arguments) by the SparkSqlDialect is not recognized by Spark SQL

[jiajunxie]

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

jiajunxie 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 730361b664 [CALCITE-5922] The SQL generated for the POSITION 
function(with 3 input arguments) by the SparkSqlDialect is not recognized by 
Spark SQL
730361b664 is described below

commit 730361b66442e5d12448e120e8ce67fc070b271a
Author: macroguo <macro...@tencent.com>
AuthorDate: Sun Aug 13 16:29:45 2023 +0800

    [CALCITE-5922] The SQL generated for the POSITION function(with 3 input 
arguments) by the SparkSqlDialect is not recognized by Spark SQL
---
 .../org/apache/calcite/sql/dialect/SparkSqlDialect.java  |  3 +++
 .../calcite/rel/rel2sql/RelToSqlConverterTest.java       | 16 ++++++++++++++++
 2 files changed, 19 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 4c3aa6abd5..e97cfcac01 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
@@ -147,6 +147,9 @@ public class SparkSqlDialect extends SqlDialect {
       case TRIM:
         unparseHiveTrim(writer, call, leftPrec, rightPrec);
         break;
+      case POSITION:
+        SqlUtil.unparseFunctionSyntax(SqlStdOperatorTable.POSITION, writer, 
call, false);
+        break;
       default:
         super.unparseCall(writer, call, leftPrec, rightPrec);
       }
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 dded73c882..50e9808a84 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
@@ -2494,6 +2494,22 @@ class RelToSqlConverterTest {
     sql(query).withBigQuery().ok(expected);
   }
 
+  /** Test case for
+   * <a 
href="https://issues.apache.org/jira/browse/CALCITE-5922";>[CALCITE-5922]
+   * The SQL generated for the POSITION function(with 3 input arguments) by the
+   * SparkSqlDialect is not recognized by Spark SQL</a>. */
+  @Test void testPositionForSpark() {
+    final String query = "SELECT POSITION('a' IN 'abc')";
+    final String expected = "SELECT POSITION('a', 'abc')\n"
+        + "FROM (VALUES (0)) t (ZERO)";
+    sql(query).withSpark().ok(expected);
+
+    final String query2 = "SELECT POSITION('a' IN 'abc' FROM 1)";
+    final String expected2 = "SELECT POSITION('a', 'abc', 1)\n"
+        + "FROM (VALUES (0)) t (ZERO)";
+    sql(query2).withSpark().ok(expected2);
+  }
+
   @Test void testInstrFunction4Operands() {
     final String query = "SELECT INSTR('ABC', 'A', 1, 1) from \"product\"";
     final String expectedBQ = "SELECT INSTR('ABC', 'A', 1, 1)\n"