commit 9bd371316da3bfbf1096142d69f0464100773590
author Mihai Budiu <mbudiu@feldera.com> Mon Apr 01 13:38:14 2024 -0700
committer Mihai Budiu <mbudiu@feldera.com> Tue Apr 09 08:46:29 2024 -0700
tree 1ef36f3f6c9e4fec905feded5a17d352b2cab83e
parent b91c0c00d2cd05db56c775ae335e4933a224b051

[CALCITE-6285] Function ARRAY_INSERT produces an incorrect result for negative indices

Signed-off-by: Mihai Budiu <mbudiu@feldera.com>

core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java

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 523c2d0..41d948d 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -5337,6 +5337,10 @@
           + "and not exceeds the allowed limit.");
     }
 
+    if (posInt == -1) {
+      // This means "append to the array"
+      posInt = baseArray.length + 1;
+    }
     boolean usePositivePos = posInt > 0;
 
     if (usePositivePos) {
@@ -5362,7 +5366,10 @@
 
       return Arrays.asList(newArray);
     } else {
-      int posIndex = posInt;
+      // 1-based index.
+      // The behavior of this function was changed in Spark 3.4.0.
+      // https://issues.apache.org/jira/browse/SPARK-44840
+      int posIndex = posInt + 1;
 
       boolean newPosExtendsArrayLeft = baseArray.length + posIndex < 0;
 

testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java

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 9f3c10e..d5cf46d 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -7035,18 +7035,20 @@
         "[1, 2, null, 3]", "INTEGER ARRAY NOT NULL");
     f1.checkScalar("array_insert(array[2, 3, 4], 1, 1)",
         "[1, 2, 3, 4]", "INTEGER NOT NULL ARRAY NOT NULL");
-    f1.checkScalar("array_insert(array[1, 3, 4], -2, 2)",
+    f1.checkScalar("array_insert(array[1, 3, 4], -1, 2)",
+        "[1, 3, 4, 2]", "INTEGER NOT NULL ARRAY NOT NULL");
+    f1.checkScalar("array_insert(array[1, 3, 4], -3, 2)",
         "[1, 2, 3, 4]", "INTEGER NOT NULL ARRAY NOT NULL");
-    f1.checkScalar("array_insert(array[2, 3, null, 4], -5, 1)",
+    f1.checkScalar("array_insert(array[2, 3, null, 4], -6, 1)",
         "[1, null, 2, 3, null, 4]", "INTEGER ARRAY NOT NULL");
     // check complex type
     f1.checkScalar("array_insert(array[array[1,2]], 1, array[1])",
         "[[1], [1, 2]]", "INTEGER NOT NULL ARRAY NOT NULL ARRAY NOT NULL");
     f1.checkScalar("array_insert(array[array[1,2]], -1, array[1])",
-        "[[1], [1, 2]]", "INTEGER NOT NULL ARRAY NOT NULL ARRAY NOT NULL");
+        "[[1, 2], [1]]", "INTEGER NOT NULL ARRAY NOT NULL ARRAY NOT NULL");
     f1.checkScalar("array_insert(array[map[1, 'a']], 1, map[2, 'b'])", "[{2=b}, {1=a}]",
         "(INTEGER NOT NULL, CHAR(1) NOT NULL) MAP NOT NULL ARRAY NOT NULL");
-    f1.checkScalar("array_insert(array[map[1, 'a']], -1, map[2, 'b'])", "[{2=b}, {1=a}]",
+    f1.checkScalar("array_insert(array[map[1, 'a']], -1, map[2, 'b'])", "[{1=a}, {2=b}]",
         "(INTEGER NOT NULL, CHAR(1) NOT NULL) MAP NOT NULL ARRAY NOT NULL");
 
     // element cast to the biggest type