This is an automated email from the ASF dual-hosted git repository.
mbudiu 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 1cdf864e9f [CALCITE-6454] Implement array comparison operators
1cdf864e9f is described below
commit 1cdf864e9f853284f05aa3bf8515951a018ae22e
Author: Norman Jordan <[email protected]>
AuthorDate: Wed Jul 10 11:06:48 2024 -0700
[CALCITE-6454] Implement array comparison operators
* <, <=, >, >= now work for arrays and rows
* Can also sort arrays and rows
* Comparison is performed along corresponding indexes
* Longer arrays are considered greater
* null is considered greater than anything
* Cannot change whether nulls are first or last
---
.../org/apache/calcite/runtime/SqlFunctions.java | 37 +++
core/src/test/resources/sql/operator.iq | 326 +++++++++++++++++++++
core/src/test/resources/sql/sort.iq | 39 +++
.../apache/calcite/linq4j/function/Functions.java | 80 ++++-
4 files changed, 474 insertions(+), 8 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 6625e14a02..7a5dc3e3e6 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -31,6 +31,7 @@ import org.apache.calcite.linq4j.Ord;
import org.apache.calcite.linq4j.function.Deterministic;
import org.apache.calcite.linq4j.function.Experimental;
import org.apache.calcite.linq4j.function.Function1;
+import org.apache.calcite.linq4j.function.Functions;
import org.apache.calcite.linq4j.function.NonDeterministic;
import org.apache.calcite.linq4j.function.Predicate1;
import org.apache.calcite.linq4j.tree.Primitive;
@@ -2040,6 +2041,14 @@ public class SqlFunctions {
return b0 < b1;
}
+ public static boolean lt(List<?> b0, List<?> b1) {
+ return Functions.compareLists(b0, b1) < 0;
+ }
+
+ public static boolean lt(Object[] b0, Object[] b1) {
+ return Functions.compareObjectArrays(b0, b1) < 0;
+ }
+
/** SQL <code><</code> operator applied to Object values. */
public static boolean ltAny(Object b0, Object b1) {
if (b0.getClass().equals(b1.getClass())
@@ -2080,6 +2089,16 @@ public class SqlFunctions {
return b0.compareTo(b1) <= 0;
}
+ /** SQL <code>≤</code> operator applied to List values. */
+ public static boolean le(List<?> b0, List<?> b1) {
+ return Functions.compareLists(b0, b1) <= 0;
+ }
+
+ /** SQL <code>≤</code> operator applied to Object[] values. */
+ public static boolean le(Object[] b0, Object[] b1) {
+ return Functions.compareObjectArrays(b0, b1) <= 0;
+ }
+
/** SQL <code>≤</code> operator applied to Object values (at least one
* operand has ANY type; neither may be null). */
public static boolean leAny(Object b0, Object b1) {
@@ -2155,6 +2174,14 @@ public class SqlFunctions {
return b0 > b1;
}
+ public static boolean gt(List<?> b0, List<?> b1) {
+ return Functions.compareLists(b0, b1) > 0;
+ }
+
+ public static boolean gt(Object[] b0, Object[] b1) {
+ return Functions.compareObjectArrays(b0, b1) > 0;
+ }
+
/** SQL <code>></code> operator applied to Object values (at least one
* operand has ANY type; neither may be null). */
public static boolean gtAny(Object b0, Object b1) {
@@ -2196,6 +2223,16 @@ public class SqlFunctions {
return b0.compareTo(b1) >= 0;
}
+ /** SQL <code>≥</code> operator applied to List values. */
+ public static boolean ge(List<?> b0, List<?> b1) {
+ return Functions.compareLists(b0, b1) >= 0;
+ }
+
+ /** SQL <code>≥</code> operator applied to Object[] values. */
+ public static boolean ge(Object[] b0, Object[] b1) {
+ return Functions.compareObjectArrays(b0, b1) >= 0;
+ }
+
/** SQL <code>≥</code> operator applied to Object values (at least one
* operand has ANY type; neither may be null). */
public static boolean geAny(Object b0, Object b1) {
diff --git a/core/src/test/resources/sql/operator.iq
b/core/src/test/resources/sql/operator.iq
index 278010727c..cc1fea0ec4 100644
--- a/core/src/test/resources/sql/operator.iq
+++ b/core/src/test/resources/sql/operator.iq
@@ -366,4 +366,330 @@ CITY VARCHAR
!ok
+# [CALCITE-6454] Implement array comparison operators
+select array[1, 2] < array[1, 2] as foo;
+
+FOO BOOLEAN(1) NOT NULL
+!type
++-------+
+| FOO |
++-------+
+| false |
++-------+
+(1 row)
+
+!ok
+
+select array[1, 2] < array[2, 3] as foo;
+
+FOO BOOLEAN(1) NOT NULL
+!type
++------+
+| FOO |
++------+
+| true |
++------+
+(1 row)
+
+!ok
+
+select array[1, 2] < array[1] as foo;
+
+FOO BOOLEAN(1) NOT NULL
+!type
++-------+
+| FOO |
++-------+
+| false |
++-------+
+(1 row)
+
+!ok
+
+select array[1] < array[1, 2] as foo;
+
+FOO BOOLEAN(1) NOT NULL
+!type
++------+
+| FOO |
++------+
+| true |
++------+
+(1 row)
+
+!ok
+
+select array[1, 2] < array[1, null] as foo;
+
+FOO BOOLEAN(1) NOT NULL
+!type
++------+
+| FOO |
++------+
+| true |
++------+
+(1 row)
+
+!ok
+
+select array[1, 2] <= array[1, 2] as foo;
+
+FOO BOOLEAN(1) NOT NULL
+!type
++------+
+| FOO |
++------+
+| true |
++------+
+(1 row)
+
+!ok
+
+select array[1, 2] <= array[2, 3] as foo;
+
+FOO BOOLEAN(1) NOT NULL
+!type
++------+
+| FOO |
++------+
+| true |
++------+
+(1 row)
+
+!ok
+
+select array[1, 2] <= array[1] as foo;
+
+FOO BOOLEAN(1) NOT NULL
+!type
++-------+
+| FOO |
++-------+
+| false |
++-------+
+(1 row)
+
+!ok
+
+select array[1] <= array[1, 2] as foo;
+
+FOO BOOLEAN(1) NOT NULL
+!type
++------+
+| FOO |
++------+
+| true |
++------+
+(1 row)
+
+!ok
+
+select array[1, 2] <= array[1, null] as foo;
+
+FOO BOOLEAN(1) NOT NULL
+!type
++------+
+| FOO |
++------+
+| true |
++------+
+(1 row)
+
+!ok
+
+select array[1, 2] > array[1, 2] as foo;
+
+FOO BOOLEAN(1) NOT NULL
+!type
++-------+
+| FOO |
++-------+
+| false |
++-------+
+(1 row)
+
+!ok
+
+select array[1, 2] > array[2, 3] as foo;
+
+FOO BOOLEAN(1) NOT NULL
+!type
++-------+
+| FOO |
++-------+
+| false |
++-------+
+(1 row)
+
+!ok
+
+select array[1, 2] > array[1] as foo;
+
+FOO BOOLEAN(1) NOT NULL
+!type
++------+
+| FOO |
++------+
+| true |
++------+
+(1 row)
+
+!ok
+
+select array[1] > array[1, 2] as foo;
+
+FOO BOOLEAN(1) NOT NULL
+!type
++-------+
+| FOO |
++-------+
+| false |
++-------+
+(1 row)
+
+!ok
+
+select array[1, 2] > array[1, null] as foo;
+
+FOO BOOLEAN(1) NOT NULL
+!type
++-------+
+| FOO |
++-------+
+| false |
++-------+
+(1 row)
+
+!ok
+
+select array[1, 2] >= array[1, 2] as foo;
+
+FOO BOOLEAN(1) NOT NULL
+!type
++------+
+| FOO |
++------+
+| true |
++------+
+(1 row)
+
+!ok
+
+select array[1, 2] >= array[2, 3] as foo;
+
+FOO BOOLEAN(1) NOT NULL
+!type
++-------+
+| FOO |
++-------+
+| false |
++-------+
+(1 row)
+
+!ok
+
+select array[1, 2] >= array[1] as foo;
+
+FOO BOOLEAN(1) NOT NULL
+!type
++------+
+| FOO |
++------+
+| true |
++------+
+(1 row)
+
+!ok
+
+select array[1] >= array[1, 2] as foo;
+
+FOO BOOLEAN(1) NOT NULL
+!type
++-------+
+| FOO |
++-------+
+| false |
++-------+
+(1 row)
+
+!ok
+
+select array[1, 2] >= array[1, null] as foo;
+
+FOO BOOLEAN(1) NOT NULL
+!type
++-------+
+| FOO |
++-------+
+| false |
++-------+
+(1 row)
+
+!ok
+
+select array[array[1, 2]] < array[array[2, 3]] as foo;
+
+FOO BOOLEAN(1) NOT NULL
+!type
++------+
+| FOO |
++------+
+| true |
++------+
+(1 row)
+
+!ok
+
+select array[row(1, 2)] < array[row(2, 3)] as foo;
+
+FOO BOOLEAN(1) NOT NULL
+!type
++------+
+| FOO |
++------+
+| true |
++------+
+(1 row)
+
+!ok
+
+select array[array[1, 2], array[3, 4]] < array[array[1, 2], array[5, 6]] as
foo;
+
+FOO BOOLEAN(1) NOT NULL
+!type
++------+
+| FOO |
++------+
+| true |
++------+
+(1 row)
+
+!ok
+
+select array[row(1, 2), row(3, 4)] < array[row(1, 2), row(5, 6)] as foo;
+
+FOO BOOLEAN(1) NOT NULL
+!type
++------+
+| FOO |
++------+
+| true |
++------+
+(1 row)
+
+!ok
+
+select row(array[1, 2], array[3, 4]) < row(array[1, 2], array[5, 6]) as foo;
+
+FOO BOOLEAN(1) NOT NULL
+!type
++------+
+| FOO |
++------+
+| true |
++------+
+(1 row)
+
+!ok
+
# End operator.iq
diff --git a/core/src/test/resources/sql/sort.iq
b/core/src/test/resources/sql/sort.iq
index 4e11362b24..32f75d2e2b 100644
--- a/core/src/test/resources/sql/sort.iq
+++ b/core/src/test/resources/sql/sort.iq
@@ -378,4 +378,43 @@ order by 1;
!ok
+# [CALCITE-6454] Implement array comparison operators
+select * from
+(values
+ (2, array[2, 3]),
+ (3, array[3, 4]),
+ (1, array[1, 2]),
+ (4, array[4, 5])) as t(id, arr)
+order by arr asc;
++----+--------+
+| ID | ARR |
++----+--------+
+| 1 | [1, 2] |
+| 2 | [2, 3] |
+| 3 | [3, 4] |
+| 4 | [4, 5] |
++----+--------+
+(4 rows)
+
+!ok
+
+select * from
+(values
+ (2, array[2, 3]),
+ (3, array[3, 4]),
+ (1, array[1, 2]),
+ (4, array[4, 5])) as t(id, arr)
+order by arr desc;
++----+--------+
+| ID | ARR |
++----+--------+
+| 4 | [4, 5] |
+| 3 | [3, 4] |
+| 2 | [2, 3] |
+| 1 | [1, 2] |
++----+--------+
+(4 rows)
+
+!ok
+
# End sort.iq
diff --git
a/linq4j/src/main/java/org/apache/calcite/linq4j/function/Functions.java
b/linq4j/src/main/java/org/apache/calcite/linq4j/function/Functions.java
index 6a4a0da2a3..329e758b94 100644
--- a/linq4j/src/main/java/org/apache/calcite/linq4j/function/Functions.java
+++ b/linq4j/src/main/java/org/apache/calcite/linq4j/function/Functions.java
@@ -16,6 +16,8 @@
*/
package org.apache.calcite.linq4j.function;
+import com.google.common.collect.Lists;
+
import org.checkerframework.checker.nullness.qual.Nullable;
import org.checkerframework.framework.qual.DefaultQualifier;
import org.checkerframework.framework.qual.TypeUseLocation;
@@ -554,8 +556,8 @@ public abstract class Functions {
/** Nulls last comparator. */
private static class NullsLastComparator
- implements Comparator<Comparable>, Serializable {
- @Override public int compare(Comparable o1, Comparable o2) {
+ implements Comparator<Object>, Serializable {
+ @Override public int compare(@Nullable Object o1, @Nullable Object o2) {
if (o1 == o2) {
return 0;
}
@@ -565,15 +567,25 @@ public abstract class Functions {
if (o2 == null) {
return -1;
}
- //noinspection unchecked
- return o1.compareTo(o2);
+ if (o1 instanceof Comparable && o2 instanceof Comparable) {
+ //noinspection unchecked
+ return ((Comparable) o1).compareTo(o2);
+ } else if (o1 instanceof List && o2 instanceof List) {
+ return compareLists((List<?>) o1, (List<?>) o2);
+ } else if (o1 instanceof Object[] && o2 instanceof Object[]) {
+ final List<Object> list1 = Lists.newArrayList((Object[]) o1);
+ final List<Object> list2 = Lists.newArrayList((Object[]) o2);
+ return compareLists(list1, list2);
+ } else {
+ throw new IllegalArgumentException();
+ }
}
}
/** Nulls first reverse comparator. */
private static class NullsFirstReverseComparator
- implements Comparator<Comparable>, Serializable {
- @Override public int compare(Comparable o1, Comparable o2) {
+ implements Comparator<Object>, Serializable {
+ @Override public int compare(Object o1, Object o2) {
if (o1 == o2) {
return 0;
}
@@ -583,11 +595,63 @@ public abstract class Functions {
if (o2 == null) {
return 1;
}
- //noinspection unchecked
- return -o1.compareTo(o2);
+ if (o1 instanceof Comparable && o2 instanceof Comparable) {
+ //noinspection unchecked
+ return -((Comparable) o1).compareTo(o2);
+ } else if (o1 instanceof List && o2 instanceof List) {
+ return -compareLists((List<?>) o1, (List<?>) o2);
+ } else if (o1 instanceof Object[] && o2 instanceof Object[]) {
+ final List<Object> list1 = Lists.newArrayList((Object[]) o1);
+ final List<Object> list2 = Lists.newArrayList((Object[]) o2);
+ return -compareLists(list1, list2);
+ } else {
+ throw new IllegalArgumentException();
+ }
}
}
+ public static int compareLists(List<?> b0, List<?> b1) {
+ if (b0.isEmpty() && b1.isEmpty()) {
+ return 0;
+ }
+ for (int i = 0; i < b0.size() && i < b1.size(); i++) {
+ final int comparison = compareListItems(b0.get(i), b1.get(i));
+ if (comparison != 0) {
+ return comparison;
+ }
+ }
+ return Integer.compare(b0.size(), b1.size());
+ }
+
+ private static int compareListItems(@Nullable Object item0, @Nullable Object
item1) {
+ if (item0 == null && item1 == null) {
+ return 0;
+ } else if (item0 == null) {
+ return 1;
+ } else if (item1 == null) {
+ return -1;
+ }
+ if (item0 instanceof List && item1 instanceof List) {
+ final List<?> b0ItemList = (List<?>) item0;
+ final List<?> b1ItemList = (List<?>) item1;
+ return compareLists(b0ItemList, b1ItemList);
+ } else if (item0 instanceof Object[] && item1 instanceof Object[]) {
+ return compareObjectArrays((Object[]) item0, (Object[]) item1);
+ } else if (item0.getClass().equals(item1.getClass()) && item0 instanceof
Comparable<?>) {
+ final Comparable b0Comparable = (Comparable) item0;
+ final Comparable b1Comparable = (Comparable) item1;
+ return b0Comparable.compareTo(b1Comparable);
+ } else {
+ throw new IllegalArgumentException("Item types do not match");
+ }
+ }
+
+ public static int compareObjectArrays(Object[] b0, Object[] b1) {
+ final List<Object> b0List = Lists.newArrayList(b0);
+ final List<Object> b1List = Lists.newArrayList(b1);
+ return compareLists(b0List, b1List);
+ }
+
/** Nulls last reverse comparator. */
private static class NullsLastReverseComparator
implements Comparator<Comparable>, Serializable {
