(calcite) branch main updated: [CALCITE-7322] The POSITION function in MySQL is missing the FROM clause

[cancai]

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

cancai 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 20fae9a1b1 [CALCITE-7322] The POSITION function in MySQL is missing 
the FROM clause
20fae9a1b1 is described below

commit 20fae9a1b12cb993b06a1722826a4a64f7965373
Author: xuzifu666 <xuzifu...@gmail.com>
AuthorDate: Wed Dec 10 11:59:07 2025 +0800

    [CALCITE-7322] The POSITION function in MySQL is missing the FROM clause
---
 .../java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java | 12 ++++++++----
 .../apache/calcite/rel/rel2sql/RelToSqlConverterTest.java    | 10 +++++++++-
 2 files changed, 17 insertions(+), 5 deletions(-)

diff --git 
a/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java 
b/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java
index f828e89fd7..7b4475283e 100644
--- a/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java
@@ -257,12 +257,16 @@ public MysqlSqlDialect(Context context) {
       int leftPrec, int rightPrec) {
     switch (call.getKind()) {
     case POSITION:
-      final SqlWriter.Frame frame = writer.startFunCall("INSTR");
-      writer.sep(",");
-      call.operand(1).unparse(writer, leftPrec, rightPrec);
+      final SqlWriter.Frame f = writer.startFunCall("LOCATE");
       writer.sep(",");
       call.operand(0).unparse(writer, leftPrec, rightPrec);
-      writer.endFunCall(frame);
+      writer.sep(",");
+      call.operand(1).unparse(writer, leftPrec, rightPrec);
+      if (call.operandCount() == 3) {
+        writer.sep(",");
+        call.operand(2).unparse(writer, leftPrec, rightPrec);
+      }
+      writer.endFunCall(f);
       break;
     case FLOOR:
       if (call.operandCount() != 2) {
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 915db7da6e..1b34d42c28 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
@@ -3600,11 +3600,19 @@ private SqlDialect nonOrdinalDialect() {
     sql(query).withHive().ok(expected);
   }
 
+  /** Test case for
+   * <a 
href="https://issues.apache.org/jira/browse/CALCITE-7322";>[CALCITE-7322]
+   * The POSITION function in MySQL is missing the FROM clause</a>. */
   @Test void testPositionFunctionForMySql() {
     final String query = "select position('A' IN 'ABC') from \"product\"";
-    final String expected = "SELECT INSTR('ABC', 'A')\n"
+    final String expected = "SELECT LOCATE('A', 'ABC')\n"
         + "FROM `foodmart`.`product`";
     sql(query).withMysql().ok(expected);
+
+    final String query1 = "select position('A' IN 'ABC' FROM '2') from 
\"product\"";
+    final String expected1 = "SELECT LOCATE('A', 'ABC', 2)\n"
+        + "FROM `foodmart`.`product`";
+    sql(query1).withMysql().ok(expected1);
   }
 
   @Test void testPositionFunctionForBigQuery() {