(calcite) branch main updated: [CALCITE-7926] SUBSTRING_INDEX returns incorrect results for negative counts

[mbudiu.apache.org via commits]

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

mihaibudiu 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 ad6485e1ed [CALCITE-7926] SUBSTRING_INDEX returns incorrect results 
for negative counts
ad6485e1ed is described below

commit ad6485e1eda621c3fc5d1ee58a03555f4fbd0e4c
Author: Atharv Rastogi <ath...@e6x.io>
AuthorDate: Mon Nov 9 17:46:42 2026 +0530

    [CALCITE-7926] SUBSTRING_INDEX returns incorrect results for negative counts
---
 .../main/java/org/apache/calcite/runtime/SqlFunctions.java   |  8 +-------
 .../test/java/org/apache/calcite/rex/RexExecutorTest.java    |  7 +++++++
 .../main/java/org/apache/calcite/test/SqlOperatorTest.java   | 12 ++++++++++++
 3 files changed, 20 insertions(+), 7 deletions(-)

diff --git a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java 
b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
index 48d83c7f05..49c561e448 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -7729,13 +7729,7 @@ public static String substringIndex(String string, 
String delimiter, int count)
 
   /** Find the string from right to left. */
   private static int rfind(String string, String delim, int start) {
-    while (start >= 0) {
-      if (string.indexOf(delim, start) >= 0) {
-        return start;
-      }
-      start -= 1;
-    }
-    return -1;
+    return string.lastIndexOf(delim, start);
   }
 
   /** Support the SLICE function. */
diff --git a/core/src/test/java/org/apache/calcite/rex/RexExecutorTest.java 
b/core/src/test/java/org/apache/calcite/rex/RexExecutorTest.java
index 16d2978355..5c36a133d0 100644
--- a/core/src/test/java/org/apache/calcite/rex/RexExecutorTest.java
+++ b/core/src/test/java/org/apache/calcite/rex/RexExecutorTest.java
@@ -136,6 +136,13 @@ protected void check(final Action action) {
     });
   }
 
+  @Test void testSubstringIndexNegativeCount() {
+    checkConstant("b.c", rexBuilder ->
+        rexBuilder.makeCall(SqlLibraryOperators.SUBSTRING_INDEX,
+            rexBuilder.makeLiteral("a.b.c"), rexBuilder.makeLiteral("."),
+            rexBuilder.makeExactLiteral(BigDecimal.valueOf(-2))));
+  }
+
   /** Reduces several expressions to constants. */
   @Test void testConstant2() {
     // Same as testConstant; 10 -> 10
diff --git a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java 
b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
index 644a0f92a0..135b7eac47 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -9998,6 +9998,8 @@ void checkArrayReverseFunc(SqlOperatorFixture f0, 
SqlFunction function,
             + "<CHARACTER>, <CHARACTER>\\)", false);
 
     final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.SPARK);
+    // Expected string and NULL results below were verified against
+    // MySQL Community Server 8.4.13 (official mysql:8.4 Docker image).
     f.checkString("substring_index('www.apache.org', '.', 2)",
         "www.apache", "VARCHAR(14) NOT NULL");
     f.checkString("substring_index('www.apache.org', '.', 1)",
@@ -10006,6 +10008,16 @@ void checkArrayReverseFunc(SqlOperatorFixture f0, 
SqlFunction function,
         "www.apache.org", "VARCHAR(14) NOT NULL");
     f.checkString("substring_index('www.apache.org', '.', -1)",
         "org", "VARCHAR(14) NOT NULL");
+    f.checkString("substring_index('www.apache.org', '.', -2)",
+        "apache.org", "VARCHAR(14) NOT NULL");
+    f.checkString("substring_index('www.apache.org', '.', -3)",
+        "www.apache.org", "VARCHAR(14) NOT NULL");
+    f.checkString("substring_index('www.apache.org', '.', -10)",
+        "www.apache.org", "VARCHAR(14) NOT NULL");
+    f.checkString("substring_index('a.b.c', '.', -2)",
+        "b.c", "VARCHAR(5) NOT NULL");
+    f.checkString("substring_index('a--b--c', '--', -2)",
+        "b--c", "VARCHAR(7) NOT NULL");
 
     f.checkString("substring_index('aBc', 'B', -1)",
         "c", "VARCHAR(3) NOT NULL");