You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by mb...@apache.org on 2026/10/15 18:47:58 UTC
(calcite) branch main updated: [CALCITE-7842] Multiset operators return incorrect results for ROW elements
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 7178b54f6d [CALCITE-7842] Multiset operators return incorrect results for ROW elements
7178b54f6d is described below
commit 7178b54f6d321eb59801ff3a88aa50af74678e06
Author: Kirill Tkalenko <tk...@yandex.ru>
AuthorDate: Fri Oct 9 12:43:38 2026 +0300
[CALCITE-7842] Multiset operators return incorrect results for ROW elements
---
.../org/apache/calcite/runtime/SqlFunctions.java | 159 ++--
.../org/apache/calcite/util/BuiltInMethod.java | 24 +-
.../org/apache/calcite/test/SqlFunctionsTest.java | 113 +++
core/src/test/resources/sql/row-equality.iq | 842 +++++++++++++++++++++
4 files changed, 1057 insertions(+), 81 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 4dfc4f542f..2b3a45c479 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -131,7 +131,6 @@
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.LinkedHashSet;
-import java.util.LinkedList;
import java.util.List;
import java.util.Locale;
import java.util.Map;
@@ -144,6 +143,7 @@
import java.util.function.BinaryOperator;
import java.util.function.Consumer;
import java.util.function.Function;
+import java.util.function.Supplier;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.regex.PatternSyntaxException;
@@ -7096,7 +7096,7 @@ private static AtomicLong getAtomicLong(String key) {
final List bigger = list2;
boolean hasNull = false;
if (!smaller.isEmpty() && !bigger.isEmpty()) {
- final Set<@Nullable Object> smallestSet = toDeepEqualitySet(smaller);
+ final Set<@Nullable Object> smallestSet = toDeepEqualityHashSet(smaller);
hasNull = smallestSet.remove(null);
for (Object element : bigger) {
if (element == null) {
@@ -7170,7 +7170,7 @@ public static List arrayAppend(List list, Object element) {
/** Support the ARRAY_DISTINCT function. */
public static List distinct(List list) {
- return unwrapDeepEqualitySet(toDeepEqualitySet(list));
+ return unwrapDeepEqualitySet(toDeepEqualityLinkedHashSet(list));
}
/** Support the ARRAY_MAX function. */
@@ -7252,8 +7252,10 @@ public static List arrayRemove(List list, Object element) {
/** Support the ARRAY_EXCEPT function. */
public static List arrayExcept(List list1, List list2) {
- final Set<@Nullable Object> result = toDeepEqualitySet(list1);
- result.removeAll(toDeepEqualitySet(list2));
+ final Set<@Nullable Object> result = toDeepEqualityLinkedHashSet(list1);
+ for (Object element : list2) {
+ result.remove(wrapForDeepEquality(element));
+ }
return unwrapDeepEqualitySet(result);
}
@@ -7354,30 +7356,54 @@ public static List arrayExcept(List list1, List list2) {
/** Support the ARRAY_INTERSECT function. */
public static List arrayIntersect(List list1, List list2) {
- final Set<@Nullable Object> result = toDeepEqualitySet(list1);
- result.retainAll(toDeepEqualitySet(list2));
- return unwrapDeepEqualitySet(result);
+ final Set<@Nullable Object> remaining = toDeepEqualityHashSet(list2);
+ final List<@Nullable Object> result = new ArrayList<>();
+ for (Object element : list1) {
+ if (remaining.remove(wrapForDeepEquality(element))) {
+ result.add(element);
+ }
+ }
+ return result;
}
/** Support the ARRAY_UNION function. */
public static List arrayUnion(List list1, List list2) {
- final Set<@Nullable Object> result = toDeepEqualitySet(list1);
+ final Set<@Nullable Object> result = toDeepEqualityLinkedHashSet(list1);
for (Object element : list2) {
result.add(wrapForDeepEquality(element));
}
return unwrapDeepEqualitySet(result);
}
- /** Creates a set using deep equality, preserving element encounter order.
- * This preserves the ordering required by ARRAY_DISTINCT. */
- private static Set<@Nullable Object> toDeepEqualitySet(List<?> list) {
- final Set<@Nullable Object> set = new LinkedHashSet<>();
+ /** Creates a set using deep equality, preserving element encounter order. */
+ private static Set<@Nullable Object> toDeepEqualityLinkedHashSet(List<?> list) {
+ return toDeepEqualitySet(list, LinkedHashSet::new);
+ }
+
+ /** Creates a hash set using deep equality. */
+ private static Set<@Nullable Object> toDeepEqualityHashSet(List<?> list) {
+ return toDeepEqualitySet(list, HashSet::new);
+ }
+
+ /** Creates a set using deep equality and the supplied factory. */
+ private static Set<@Nullable Object> toDeepEqualitySet(List<?> list,
+ Supplier<? extends Set<@Nullable Object>> setSupplier) {
+ final Set<@Nullable Object> set = setSupplier.get();
for (Object value : list) {
set.add(wrapForDeepEquality(value));
}
return set;
}
+ /** Counts element occurrences using deep equality. */
+ private static Map<@Nullable Object, Integer> toDeepEqualityCounts(List<?> list) {
+ final Map<@Nullable Object, Integer> counts = new HashMap<>();
+ for (Object value : list) {
+ counts.merge(wrapForDeepEquality(value), 1, Integer::sum);
+ }
+ return counts;
+ }
+
/** Wraps composite values, which may contain ROW values, but leaves scalar
* values and null unchanged. */
private static @Nullable Object wrapForDeepEquality(@Nullable Object value) {
@@ -7695,61 +7721,66 @@ public static List slice(List list) {
}
/** Support the MEMBER OF function. */
- public static boolean memberOf(@Nullable Object object, Collection collection) {
- return collection.contains(object);
+ public static boolean memberOf(@Nullable Object object, List<?> list) {
+ for (Object value : list) {
+ if (Functions.deepEquals(value, object)) {
+ return true;
+ }
+ }
+ return false;
}
/** Support the MULTISET INTERSECT DISTINCT function. */
- public static <E> Collection<E> multisetIntersectDistinct(Collection<E> c1,
- Collection<E> c2) {
- final Set<E> result = new HashSet<>(c1);
- result.retainAll(c2);
- return new ArrayList<>(result);
+ public static List multisetIntersectDistinct(List c1, List c2) {
+ return arrayIntersect(c1, c2);
}
/** Support the MULTISET INTERSECT ALL function. */
- public static <E> Collection<E> multisetIntersectAll(Collection<E> c1,
- Collection<E> c2) {
- final List<E> result = new ArrayList<>(c1.size());
- final List<E> c2Copy = new ArrayList<>(c2);
- for (E e : c1) {
- if (c2Copy.remove(e)) {
+ public static List multisetIntersectAll(List c1, List c2) {
+ final Map<@Nullable Object, Integer> counts = toDeepEqualityCounts(c2);
+ final List result = new ArrayList();
+ for (Object e : c1) {
+ counts.computeIfPresent(wrapForDeepEquality(e), (key, count) -> {
result.add(e);
- }
+ return count == 1 ? null : count - 1;
+ });
}
+
return result;
}
/** Support the MULTISET EXCEPT ALL function. */
- @SuppressWarnings("JdkObsolete")
- public static <E> Collection<E> multisetExceptAll(Collection<E> c1,
- Collection<E> c2) {
- // TOOD: use Multisets?
- final List<E> result = new LinkedList<>(c1);
- for (E e : c2) {
- result.remove(e);
+ public static List multisetExceptAll(List c1, List c2) {
+ final Map<@Nullable Object, Integer> counts = toDeepEqualityCounts(c2);
+ final List result = new ArrayList();
+ for (Object e : c1) {
+ counts.compute(wrapForDeepEquality(e), (key, count) -> {
+ if (count == null) {
+ result.add(e);
+ return null;
+ }
+ return count == 1 ? null : count - 1;
+ });
}
return result;
}
/** Support the MULTISET EXCEPT DISTINCT function. */
- public static <E> Collection<E> multisetExceptDistinct(Collection<E> c1,
- Collection<E> c2) {
- final Set<E> result = new HashSet<>(c1);
- result.removeAll(c2);
- return new ArrayList<>(result);
+ public static List multisetExceptDistinct(List c1, List c2) {
+ final Set<@Nullable Object> result = toDeepEqualityHashSet(c1);
+ for (Object e : c2) {
+ result.remove(wrapForDeepEquality(e));
+ }
+ return unwrapDeepEqualitySet(result);
}
/** Support the IS A SET function. */
- public static boolean isASet(Collection collection) {
- if (collection instanceof Set) {
- return true;
- }
+ public static boolean isASet(List<?> list) {
// capacity calculation is in the same way like for new HashSet(Collection)
// however return immediately in case of duplicates
- Set set = new HashSet(Math.max((int) (collection.size() / .75f) + 1, 16));
- for (Object e : collection) {
- if (!set.add(e)) {
+ final Set<@Nullable Object> set = new HashSet<>(Math.max((int) (list.size() / .75f) + 1, 16));
+ for (Object e : list) {
+ if (!set.add(wrapForDeepEquality(e))) {
return false;
}
}
@@ -7757,16 +7788,15 @@ public static boolean isASet(Collection collection) {
}
/** Support the SUBMULTISET OF function. */
- @SuppressWarnings("JdkObsolete")
- public static boolean submultisetOf(Collection possibleSubMultiset,
- Collection multiset) {
+ public static boolean submultisetOf(List<?> possibleSubMultiset, List<?> multiset) {
if (possibleSubMultiset.size() > multiset.size()) {
return false;
}
- // TODO: use Multisets?
- Collection multisetLocal = new LinkedList(multiset);
+ final Map<@Nullable Object, Integer> counts = toDeepEqualityCounts(multiset);
for (Object e : possibleSubMultiset) {
- if (!multisetLocal.remove(e)) {
+ final Integer remaining =
+ counts.computeIfPresent(wrapForDeepEquality(e), (key, count) -> count - 1);
+ if (remaining == null || remaining < 0) {
return false;
}
}
@@ -7774,23 +7804,20 @@ public static boolean submultisetOf(Collection possibleSubMultiset,
}
/** Support the MULTISET UNION function. */
- public static Collection multisetUnionDistinct(Collection collection1,
- Collection collection2) {
- // capacity calculation is in the same way like for new HashSet(Collection)
- Set resultCollection =
- new HashSet(Math.max((int) ((collection1.size() + collection2.size()) / .75f) + 1, 16));
- resultCollection.addAll(collection1);
- resultCollection.addAll(collection2);
- return new ArrayList(resultCollection);
+ public static List multisetUnionDistinct(List list1, List list2) {
+ final Set<@Nullable Object> result = toDeepEqualityHashSet(list1);
+ for (Object element : list2) {
+ result.add(wrapForDeepEquality(element));
+ }
+ return unwrapDeepEqualitySet(result);
}
/** Support the MULTISET UNION ALL function. */
- public static Collection multisetUnionAll(Collection collection1,
- Collection collection2) {
- List resultCollection = new ArrayList(collection1.size() + collection2.size());
- resultCollection.addAll(collection1);
- resultCollection.addAll(collection2);
- return resultCollection;
+ public static List multisetUnionAll(List list1, List list2) {
+ final List result = new ArrayList(list1.size() + list2.size());
+ result.addAll(list1);
+ result.addAll(list2);
+ return result;
}
/** Support the ARRAY_REVERSE function. */
diff --git a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
index 4f4a021c6d..6aba599523 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -905,23 +905,17 @@ public enum BuiltInMethod {
SEQUENCE_NEXT_VALUE(SqlFunctions.class, "sequenceNextValue", String.class),
SLICE(SqlFunctions.class, "slice", List.class),
ELEMENT(SqlFunctions.class, "element", List.class),
- MEMBER_OF(SqlFunctions.class, "memberOf", Object.class, Collection.class),
+ MEMBER_OF(SqlFunctions.class, "memberOf", Object.class, List.class),
MULTISET_INTERSECT_DISTINCT(SqlFunctions.class, "multisetIntersectDistinct",
- Collection.class, Collection.class),
- MULTISET_INTERSECT_ALL(SqlFunctions.class, "multisetIntersectAll",
- Collection.class, Collection.class),
- MULTISET_EXCEPT_DISTINCT(SqlFunctions.class, "multisetExceptDistinct",
- Collection.class, Collection.class),
- MULTISET_EXCEPT_ALL(SqlFunctions.class, "multisetExceptAll",
- Collection.class, Collection.class),
- MULTISET_UNION_DISTINCT(SqlFunctions.class, "multisetUnionDistinct",
- Collection.class, Collection.class),
- MULTISET_UNION_ALL(SqlFunctions.class, "multisetUnionAll", Collection.class,
- Collection.class),
- IS_A_SET(SqlFunctions.class, "isASet", Collection.class),
+ List.class, List.class),
+ MULTISET_INTERSECT_ALL(SqlFunctions.class, "multisetIntersectAll", List.class, List.class),
+ MULTISET_EXCEPT_DISTINCT(SqlFunctions.class, "multisetExceptDistinct", List.class, List.class),
+ MULTISET_EXCEPT_ALL(SqlFunctions.class, "multisetExceptAll", List.class, List.class),
+ MULTISET_UNION_DISTINCT(SqlFunctions.class, "multisetUnionDistinct", List.class, List.class),
+ MULTISET_UNION_ALL(SqlFunctions.class, "multisetUnionAll", List.class, List.class),
+ IS_A_SET(SqlFunctions.class, "isASet", List.class),
IS_EMPTY(Collection.class, "isEmpty"),
- SUBMULTISET_OF(SqlFunctions.class, "submultisetOf", Collection.class,
- Collection.class),
+ SUBMULTISET_OF(SqlFunctions.class, "submultisetOf", List.class, List.class),
ARRAY_APPEND(SqlFunctions.class, "arrayAppend", List.class, Object.class),
CYCLE_PATH_CONTAINS(SqlFunctions.class, "cyclePathContains", List.class, Object.class),
ARRAY_COMPACT(SqlFunctions.class, "compact", List.class),
diff --git a/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java b/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
index fa29ceebbf..da266bc7ad 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
@@ -1649,6 +1649,117 @@ private void thereAndBack(byte[] bytes) {
isListOf("a", "c", "d"));
}
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-7842">[CALCITE-7842]
+ * Multiset operators return incorrect results for ROW elements</a>. */
+ @Test void testMultisetWithRows() {
+ final Object row = new Object[] {1, "Foo"};
+ final Object equalRow = new Object[] {1, "Foo"};
+ final Object otherRow = new Object[] {2, "Bar"};
+ final List<Object> left = list(row, equalRow, otherRow);
+ final List<Object> right = list(equalRow);
+ assertThat(SqlFunctions.memberOf(row, right), is(true));
+ assertThat(SqlFunctions.memberOf(otherRow, right), is(false));
+ assertThat(SqlFunctions.isASet(left), is(false));
+ assertThat(SqlFunctions.isASet(list(row, otherRow)), is(true));
+ assertThat(SqlFunctions.submultisetOf(list(row, otherRow), left), is(true));
+ assertThat(SqlFunctions.submultisetOf(list(row, equalRow), right), is(false));
+ assertThat(SqlFunctions.multisetIntersectAll(left, right), is(list(row)));
+ assertThat(SqlFunctions.multisetIntersectDistinct(left, right), is(list(row)));
+ assertThat(SqlFunctions.multisetExceptAll(left, right), is(list(equalRow, otherRow)));
+ assertThat(SqlFunctions.multisetExceptDistinct(left, right), is(list(otherRow)));
+ final List<?> union = SqlFunctions.multisetUnionDistinct(list(row, equalRow), right);
+ assertThat(union, hasSize(1));
+ assertSame(row, union.get(0));
+ assertThat(SqlFunctions.multisetUnionAll(left, right),
+ is(list(row, equalRow, otherRow, equalRow)));
+ assertThat(left, is(list(row, equalRow, otherRow)));
+ assertThat(right, is(list(equalRow)));
+
+ final List<?> distinct =
+ SqlFunctions.multisetUnionDistinct(Collections.singletonList(new Object[] {-31}),
+ Collections.singletonList(new Object[] {-30}));
+ assertThat(distinct, hasSize(2));
+ }
+
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-7842">[CALCITE-7842]
+ * Multiset operators return incorrect results for ROW elements</a>. */
+ @Test void testMultisetIntersectAllCounts() {
+ final Object a1 = new Object[] {1, null};
+ final Object a2 = new Object[] {1, null};
+ final Object b = new Object[] {2, "Bar"};
+ final Object equalB = new Object[] {2, "Bar"};
+ final List<Object> left = list(a1, b, a2, null, new Object[] {1, null}, b, null);
+ final List<Object> right = list(equalB, a2, null, a1, null, null, "other");
+ assertThat(SqlFunctions.multisetIntersectAll(left, right), is(list(a1, b, a2, null, null)));
+ assertThat(SqlFunctions.multisetIntersectAll(right, left),
+ is(list(right.get(0), right.get(1), null, right.get(3), null)));
+
+ // Deep hash zero and its replacement collide, but represent different rows.
+ final Object zeroHash = new Object[] {-31};
+ final Object collision = new Object[] {-30};
+ assertThat(
+ SqlFunctions.multisetIntersectAll(list(zeroHash, collision, zeroHash),
+ list(new Object[] {-31}, new Object[] {-30}, new Object[] {-30})),
+ is(list(zeroHash, collision)));
+ }
+
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-7842">[CALCITE-7842]
+ * Multiset operators return incorrect results for ROW elements</a>. */
+ @Test void testMultisetExceptAllCounts() {
+ final Object a1 = new Object[] {1, null};
+ final Object a2 = new Object[] {1, null};
+ final Object a3 = new Object[] {1, null};
+ final Object b = new Object[] {2, "Bar"};
+ final Object equalB = new Object[] {2, "Bar"};
+ final List<Object> left = list(a1, b, a2, null, a3, b, null);
+ final List<Object> right = list(equalB, a2, null, a1, null, null, "other");
+ assertThat(SqlFunctions.multisetExceptAll(left, right), is(list(a3, b)));
+ assertThat(SqlFunctions.multisetExceptAll(right, left), is(list(null, "other")));
+ assertThat(SqlFunctions.multisetExceptAll(left, list()), is(left));
+ assertThat(SqlFunctions.multisetExceptAll(left, left), is(list()));
+ assertThat(left, is(list(a1, b, a2, null, a3, b, null)));
+ assertThat(right, is(list(equalB, a2, null, a1, null, null, "other")));
+
+ // Deep hash zero and its replacement collide, but represent different rows.
+ final Object zeroHash = new Object[] {-31};
+ final Object collision = new Object[] {-30};
+ assertThat(
+ SqlFunctions.multisetExceptAll(list(zeroHash, collision, zeroHash),
+ list(new Object[] {-31}, new Object[] {-30}, new Object[] {-30})),
+ is(Collections.singletonList(zeroHash)));
+ }
+
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-7842">[CALCITE-7842]
+ * Multiset operators return incorrect results for ROW elements</a>. */
+ @Test void testSubmultisetOfCounts() {
+ final Object row = new Object[] {1, null};
+ final Object equalRow = new Object[] {1, null};
+ final Object other = new Object[] {2, null};
+ final List<Object> sub = list(row, equalRow, null);
+ final List<Object> multiset = list(equalRow, other, null);
+ assertThat(SqlFunctions.submultisetOf(sub, multiset), is(false));
+ assertThat(SqlFunctions.submultisetOf(sub, list(equalRow, row, null, other)), is(true));
+ assertThat(SqlFunctions.submultisetOf(list(null, null), list(null, other)), is(false));
+ assertThat(SqlFunctions.submultisetOf(list(null, null), list(null, null)), is(true));
+ assertThat(SqlFunctions.submultisetOf(list(), list()), is(true));
+ assertThat(sub, is(list(row, equalRow, null)));
+ assertThat(multiset, is(list(equalRow, other, null)));
+
+ // Deep hash zero and its replacement collide, but represent different rows.
+ final Object zeroHash = new Object[] {-31};
+ final Object collision = new Object[] {-30};
+ assertThat(
+ SqlFunctions.submultisetOf(list(zeroHash, zeroHash),
+ list(new Object[] {-31}, collision)), is(false));
+ assertThat(
+ SqlFunctions.submultisetOf(list(zeroHash, collision),
+ list(new Object[] {-30}, new Object[] {-31})), is(true));
+ }
+
@Test void testMd5() {
assertThat("d41d8cd98f00b204e9800998ecf8427e", is(md5("")));
assertThat("d41d8cd98f00b204e9800998ecf8427e", is(md5(ByteString.of("", 16))));
@@ -2582,6 +2693,8 @@ private static void checkArrayFunctionsWithRows(Object row, Object equalRow) {
is(list(row, null, other)));
assertThat(SqlFunctions.arrayIntersect(input, list(equalRow, null)),
is(list(row, null)));
+ assertThat(SqlFunctions.arrayIntersect(input, list(null, equalRow, null, equalRow)),
+ is(list(row, null)));
assertThat(SqlFunctions.arrayExcept(input, list(equalRow, null)), is(list(other)));
assertThat(arraysOverlap(input, list(equalRow)), is(true));
assertThat(arraysOverlap(list(row), list(other)), is(false));
diff --git a/core/src/test/resources/sql/row-equality.iq b/core/src/test/resources/sql/row-equality.iq
index b83302a006..aa9de0d6fc 100644
--- a/core/src/test/resources/sql/row-equality.iq
+++ b/core/src/test/resources/sql/row-equality.iq
@@ -1011,4 +1011,846 @@ FROM (VALUES (1, 'Foo', 1, 'Foo'), (2, 'Bar', 2, 'Car')) AS t(x, y, z, w);
!ok
+# Test cases for https://issues.apache.org/jira/browse/CALCITE-7842
+# [CALCITE-7842] Multiset operators return incorrect results for ROW elements
+# All 31 queries below were checked against Oracle using OneCompiler.
+# The Oracle adaptation used object types in place of ROW values and nested
+# table types in place of MULTISET literals. Explicit MAP methods compared
+# object fields, treating corresponding NULL fields as equal.
+# All 217 result rows matched after ordering by case id and element fields.
+!use scott
+
+# Original MEMBER OF reproduction.
+SELECT ROW(1, 'Foo') MEMBER OF MULTISET[ROW(x, y)] AS r
+FROM (VALUES (1, 'Foo'), (2, 'Bar')) AS t(x, y);
++-------+
+| R |
++-------+
+| true |
+| false |
++-------+
+(2 rows)
+
+!ok
+
+# Flat ROW values.
+
+# MEMBER OF
+SELECT id, ROW(x, y) MEMBER OF MULTISET[ROW(z, w)] AS r
+FROM (VALUES
+ (1, 1, 'Foo', 1, 'Foo'),
+ (2, 2, 'Bar', 2, 'Car'),
+ (3, 3, 'Baz', 4, 'Baz'),
+ (4, 5, NULL, 5, NULL)) AS t(id, x, y, z, w)
+ORDER BY id;
++----+-------+
+| ID | R |
++----+-------+
+| 1 | true |
+| 2 | false |
+| 3 | false |
+| 4 | true |
++----+-------+
+(4 rows)
+
+!ok
+
+# IS A SET and IS NOT A SET
+SELECT id,
+ MULTISET[ROW(x, y), ROW(z, w)] IS A SET AS r,
+ MULTISET[ROW(x, y), ROW(z, w)] IS NOT A SET AS not_r
+FROM (VALUES
+ (1, 1, 'Foo', 1, 'Foo'),
+ (2, 2, 'Bar', 2, 'Car'),
+ (3, 3, 'Baz', 4, 'Baz'),
+ (4, 5, NULL, 5, NULL)) AS t(id, x, y, z, w)
+ORDER BY id;
++----+-------+-------+
+| ID | R | NOT_R |
++----+-------+-------+
+| 1 | false | true |
+| 2 | true | false |
+| 3 | true | false |
+| 4 | false | true |
++----+-------+-------+
+(4 rows)
+
+!ok
+
+# SUBMULTISET OF and NOT SUBMULTISET OF
+SELECT id,
+ MULTISET[ROW(x, y), ROW(x, y)]
+ SUBMULTISET OF MULTISET[ROW(z, w), ROW(z, w), ROW(z, w), ROW(9, 'Zzz')] AS r,
+ MULTISET[ROW(x, y), ROW(x, y)]
+ NOT SUBMULTISET OF MULTISET[ROW(z, w), ROW(z, w), ROW(z, w), ROW(9, 'Zzz')] AS not_r
+FROM (VALUES
+ (1, 1, 'Foo', 1, 'Foo'),
+ (2, 2, 'Bar', 2, 'Car'),
+ (3, 3, 'Baz', 4, 'Baz'),
+ (4, 5, NULL, 5, NULL)) AS t(id, x, y, z, w)
+ORDER BY id;
++----+-------+-------+
+| ID | R | NOT_R |
++----+-------+-------+
+| 1 | true | false |
+| 2 | false | true |
+| 3 | false | true |
+| 4 | true | false |
++----+-------+-------+
+(4 rows)
+
+!ok
+
+# Equal input cardinalities, but too few occurrences of the requested row.
+SELECT id, MULTISET[ROW(x, y), ROW(x, y)]
+ SUBMULTISET OF MULTISET[ROW(z, w), ROW(9, 'Zzz')] AS r
+FROM (VALUES
+ (1, 1, 'Foo', 1, 'Foo'),
+ (2, 2, 'Bar', 2, 'Car'),
+ (3, 3, 'Baz', 4, 'Baz'),
+ (4, 5, NULL, 5, NULL)) AS t(id, x, y, z, w)
+ORDER BY id;
++----+-------+
+| ID | R |
++----+-------+
+| 1 | false |
+| 2 | false |
+| 3 | false |
+| 4 | false |
++----+-------+
+(4 rows)
+
+!ok
+
+# MULTISET INTERSECT ALL
+SELECT id, ROW(a, b) AS r
+FROM (
+ SELECT id,
+ MULTISET[ROW(x, y), ROW(x, y), ROW(9, 'Zzz')]
+ MULTISET INTERSECT ALL
+ MULTISET[ROW(z, w), ROW(z, w), ROW(z, w), ROW(8, 'Yyy')] AS m
+ FROM (VALUES
+ (1, 1, 'Foo', 1, 'Foo'),
+ (2, 2, 'Bar', 2, 'Car'),
+ (3, 3, 'Baz', 4, 'Baz'),
+ (4, 5, NULL, 5, NULL)) AS t(id, x, y, z, w)) AS t
+CROSS JOIN UNNEST(m) AS u(a, b)
+ORDER BY id, a, b;
++----+-----------+
+| ID | R |
++----+-----------+
+| 1 | {1, Foo} |
+| 1 | {1, Foo} |
+| 4 | {5, null} |
+| 4 | {5, null} |
++----+-----------+
+(4 rows)
+
+!ok
+
+# MULTISET INTERSECT DISTINCT
+SELECT id, ROW(a, b) AS r
+FROM (
+ SELECT id,
+ MULTISET[ROW(x, y), ROW(x, y), ROW(9, 'Zzz')]
+ MULTISET INTERSECT DISTINCT
+ MULTISET[ROW(z, w), ROW(z, w), ROW(z, w), ROW(8, 'Yyy')] AS m
+ FROM (VALUES
+ (1, 1, 'Foo', 1, 'Foo'),
+ (2, 2, 'Bar', 2, 'Car'),
+ (3, 3, 'Baz', 4, 'Baz'),
+ (4, 5, NULL, 5, NULL)) AS t(id, x, y, z, w)) AS t
+CROSS JOIN UNNEST(m) AS u(a, b)
+ORDER BY id, a, b;
++----+-----------+
+| ID | R |
++----+-----------+
+| 1 | {1, Foo} |
+| 4 | {5, null} |
++----+-----------+
+(2 rows)
+
+!ok
+
+# MULTISET EXCEPT ALL
+SELECT id, ROW(a, b) AS r
+FROM (
+ SELECT id,
+ MULTISET[ROW(x, y), ROW(x, y), ROW(9, 'Zzz')]
+ MULTISET EXCEPT ALL
+ MULTISET[ROW(z, w), ROW(z, w), ROW(z, w), ROW(8, 'Yyy')] AS m
+ FROM (VALUES
+ (1, 1, 'Foo', 1, 'Foo'),
+ (2, 2, 'Bar', 2, 'Car'),
+ (3, 3, 'Baz', 4, 'Baz'),
+ (4, 5, NULL, 5, NULL)) AS t(id, x, y, z, w)) AS t
+CROSS JOIN UNNEST(m) AS u(a, b)
+ORDER BY id, a, b;
++----+----------+
+| ID | R |
++----+----------+
+| 1 | {9, Zzz} |
+| 2 | {2, Bar} |
+| 2 | {2, Bar} |
+| 2 | {9, Zzz} |
+| 3 | {3, Baz} |
+| 3 | {3, Baz} |
+| 3 | {9, Zzz} |
+| 4 | {9, Zzz} |
++----+----------+
+(8 rows)
+
+!ok
+
+# MULTISET EXCEPT DISTINCT
+SELECT id, ROW(a, b) AS r
+FROM (
+ SELECT id,
+ MULTISET[ROW(x, y), ROW(x, y), ROW(9, 'Zzz')]
+ MULTISET EXCEPT DISTINCT
+ MULTISET[ROW(z, w), ROW(z, w), ROW(z, w), ROW(8, 'Yyy')] AS m
+ FROM (VALUES
+ (1, 1, 'Foo', 1, 'Foo'),
+ (2, 2, 'Bar', 2, 'Car'),
+ (3, 3, 'Baz', 4, 'Baz'),
+ (4, 5, NULL, 5, NULL)) AS t(id, x, y, z, w)) AS t
+CROSS JOIN UNNEST(m) AS u(a, b)
+ORDER BY id, a, b;
++----+----------+
+| ID | R |
++----+----------+
+| 1 | {9, Zzz} |
+| 2 | {2, Bar} |
+| 2 | {9, Zzz} |
+| 3 | {3, Baz} |
+| 3 | {9, Zzz} |
+| 4 | {9, Zzz} |
++----+----------+
+(6 rows)
+
+!ok
+
+# MULTISET UNION DISTINCT
+SELECT id, ROW(a, b) AS r
+FROM (
+ SELECT id,
+ MULTISET[ROW(x, y), ROW(x, y), ROW(9, 'Zzz')]
+ MULTISET UNION DISTINCT
+ MULTISET[ROW(z, w), ROW(z, w), ROW(z, w), ROW(8, 'Yyy')] AS m
+ FROM (VALUES
+ (1, 1, 'Foo', 1, 'Foo'),
+ (2, 2, 'Bar', 2, 'Car'),
+ (3, 3, 'Baz', 4, 'Baz'),
+ (4, 5, NULL, 5, NULL)) AS t(id, x, y, z, w)) AS t
+CROSS JOIN UNNEST(m) AS u(a, b)
+ORDER BY id, a, b;
++----+-----------+
+| ID | R |
++----+-----------+
+| 1 | {1, Foo} |
+| 1 | {8, Yyy} |
+| 1 | {9, Zzz} |
+| 2 | {2, Bar} |
+| 2 | {2, Car} |
+| 2 | {8, Yyy} |
+| 2 | {9, Zzz} |
+| 3 | {3, Baz} |
+| 3 | {4, Baz} |
+| 3 | {8, Yyy} |
+| 3 | {9, Zzz} |
+| 4 | {5, null} |
+| 4 | {8, Yyy} |
+| 4 | {9, Zzz} |
++----+-----------+
+(14 rows)
+
+!ok
+
+# MULTISET UNION ALL
+SELECT id, ROW(a, b) AS r
+FROM (
+ SELECT id,
+ MULTISET[ROW(x, y), ROW(x, y), ROW(9, 'Zzz')]
+ MULTISET UNION ALL
+ MULTISET[ROW(z, w), ROW(z, w), ROW(z, w), ROW(8, 'Yyy')] AS m
+ FROM (VALUES
+ (1, 1, 'Foo', 1, 'Foo'),
+ (2, 2, 'Bar', 2, 'Car'),
+ (3, 3, 'Baz', 4, 'Baz'),
+ (4, 5, NULL, 5, NULL)) AS t(id, x, y, z, w)) AS t
+CROSS JOIN UNNEST(m) AS u(a, b)
+ORDER BY id, a, b;
++----+-----------+
+| ID | R |
++----+-----------+
+| 1 | {1, Foo} |
+| 1 | {1, Foo} |
+| 1 | {1, Foo} |
+| 1 | {1, Foo} |
+| 1 | {1, Foo} |
+| 1 | {8, Yyy} |
+| 1 | {9, Zzz} |
+| 2 | {2, Bar} |
+| 2 | {2, Bar} |
+| 2 | {2, Car} |
+| 2 | {2, Car} |
+| 2 | {2, Car} |
+| 2 | {8, Yyy} |
+| 2 | {9, Zzz} |
+| 3 | {3, Baz} |
+| 3 | {3, Baz} |
+| 3 | {4, Baz} |
+| 3 | {4, Baz} |
+| 3 | {4, Baz} |
+| 3 | {8, Yyy} |
+| 3 | {9, Zzz} |
+| 4 | {5, null} |
+| 4 | {5, null} |
+| 4 | {5, null} |
+| 4 | {5, null} |
+| 4 | {5, null} |
+| 4 | {8, Yyy} |
+| 4 | {9, Zzz} |
++----+-----------+
+(28 rows)
+
+!ok
+
+# Nested ROW values.
+
+# MEMBER OF
+SELECT id, ROW(ROW(x, y), p) MEMBER OF MULTISET[ROW(ROW(z, w), q)] AS r
+FROM (VALUES
+ (1, 1, 'Foo', 10, 1, 'Foo', 10),
+ (2, 2, 'Bar', 10, 2, 'Car', 10),
+ (3, 3, 'Baz', 10, 4, 'Baz', 10),
+ (4, 5, NULL, 10, 5, NULL, 10),
+ (5, 6, 'Qux', 10, 6, 'Qux', 11)) AS t(id, x, y, p, z, w, q)
+ORDER BY id;
++----+-------+
+| ID | R |
++----+-------+
+| 1 | true |
+| 2 | false |
+| 3 | false |
+| 4 | true |
+| 5 | false |
++----+-------+
+(5 rows)
+
+!ok
+
+# IS A SET and IS NOT A SET
+SELECT id,
+ MULTISET[ROW(ROW(x, y), p), ROW(ROW(z, w), q)] IS A SET AS r,
+ MULTISET[ROW(ROW(x, y), p), ROW(ROW(z, w), q)] IS NOT A SET AS not_r
+FROM (VALUES
+ (1, 1, 'Foo', 10, 1, 'Foo', 10),
+ (2, 2, 'Bar', 10, 2, 'Car', 10),
+ (3, 3, 'Baz', 10, 4, 'Baz', 10),
+ (4, 5, NULL, 10, 5, NULL, 10),
+ (5, 6, 'Qux', 10, 6, 'Qux', 11)) AS t(id, x, y, p, z, w, q)
+ORDER BY id;
++----+-------+-------+
+| ID | R | NOT_R |
++----+-------+-------+
+| 1 | false | true |
+| 2 | true | false |
+| 3 | true | false |
+| 4 | false | true |
+| 5 | true | false |
++----+-------+-------+
+(5 rows)
+
+!ok
+
+# SUBMULTISET OF and NOT SUBMULTISET OF
+SELECT id,
+ MULTISET[ROW(ROW(x, y), p), ROW(ROW(x, y), p)]
+ SUBMULTISET OF MULTISET[ROW(ROW(z, w), q), ROW(ROW(z, w), q), ROW(ROW(z, w), q), ROW(ROW(9, 'Zzz'), 10)] AS r,
+ MULTISET[ROW(ROW(x, y), p), ROW(ROW(x, y), p)]
+ NOT SUBMULTISET OF MULTISET[ROW(ROW(z, w), q), ROW(ROW(z, w), q), ROW(ROW(z, w), q), ROW(ROW(9, 'Zzz'), 10)] AS not_r
+FROM (VALUES
+ (1, 1, 'Foo', 10, 1, 'Foo', 10),
+ (2, 2, 'Bar', 10, 2, 'Car', 10),
+ (3, 3, 'Baz', 10, 4, 'Baz', 10),
+ (4, 5, NULL, 10, 5, NULL, 10),
+ (5, 6, 'Qux', 10, 6, 'Qux', 11)) AS t(id, x, y, p, z, w, q)
+ORDER BY id;
++----+-------+-------+
+| ID | R | NOT_R |
++----+-------+-------+
+| 1 | true | false |
+| 2 | false | true |
+| 3 | false | true |
+| 4 | true | false |
+| 5 | false | true |
++----+-------+-------+
+(5 rows)
+
+!ok
+
+# Equal input cardinalities, but too few occurrences of the requested row.
+SELECT id, MULTISET[ROW(ROW(x, y), p), ROW(ROW(x, y), p)]
+ SUBMULTISET OF MULTISET[ROW(ROW(z, w), q), ROW(ROW(9, 'Zzz'), 10)] AS r
+FROM (VALUES
+ (1, 1, 'Foo', 10, 1, 'Foo', 10),
+ (2, 2, 'Bar', 10, 2, 'Car', 10),
+ (3, 3, 'Baz', 10, 4, 'Baz', 10),
+ (4, 5, NULL, 10, 5, NULL, 10),
+ (5, 6, 'Qux', 10, 6, 'Qux', 11)) AS t(id, x, y, p, z, w, q)
+ORDER BY id;
++----+-------+
+| ID | R |
++----+-------+
+| 1 | false |
+| 2 | false |
+| 3 | false |
+| 4 | false |
+| 5 | false |
++----+-------+
+(5 rows)
+
+!ok
+
+# MULTISET INTERSECT ALL
+SELECT id, ROW(a, b) AS r
+FROM (
+ SELECT id,
+ MULTISET[ROW(ROW(x, y), p), ROW(ROW(x, y), p), ROW(ROW(9, 'Zzz'), 10)]
+ MULTISET INTERSECT ALL
+ MULTISET[ROW(ROW(z, w), q), ROW(ROW(z, w), q), ROW(ROW(z, w), q), ROW(ROW(8, 'Yyy'), 10)] AS m
+ FROM (VALUES
+ (1, 1, 'Foo', 10, 1, 'Foo', 10),
+ (2, 2, 'Bar', 10, 2, 'Car', 10),
+ (3, 3, 'Baz', 10, 4, 'Baz', 10),
+ (4, 5, NULL, 10, 5, NULL, 10),
+ (5, 6, 'Qux', 10, 6, 'Qux', 11)) AS t(id, x, y, p, z, w, q)) AS t
+CROSS JOIN UNNEST(m) AS u(a, b)
+ORDER BY id, u.a."EXPR$0", u.a."EXPR$1", b;
++----+-----------------+
+| ID | R |
++----+-----------------+
+| 1 | {{1, Foo}, 10} |
+| 1 | {{1, Foo}, 10} |
+| 4 | {{5, null}, 10} |
+| 4 | {{5, null}, 10} |
++----+-----------------+
+(4 rows)
+
+!ok
+
+# MULTISET INTERSECT DISTINCT
+SELECT id, ROW(a, b) AS r
+FROM (
+ SELECT id,
+ MULTISET[ROW(ROW(x, y), p), ROW(ROW(x, y), p), ROW(ROW(9, 'Zzz'), 10)]
+ MULTISET INTERSECT DISTINCT
+ MULTISET[ROW(ROW(z, w), q), ROW(ROW(z, w), q), ROW(ROW(z, w), q), ROW(ROW(8, 'Yyy'), 10)] AS m
+ FROM (VALUES
+ (1, 1, 'Foo', 10, 1, 'Foo', 10),
+ (2, 2, 'Bar', 10, 2, 'Car', 10),
+ (3, 3, 'Baz', 10, 4, 'Baz', 10),
+ (4, 5, NULL, 10, 5, NULL, 10),
+ (5, 6, 'Qux', 10, 6, 'Qux', 11)) AS t(id, x, y, p, z, w, q)) AS t
+CROSS JOIN UNNEST(m) AS u(a, b)
+ORDER BY id, u.a."EXPR$0", u.a."EXPR$1", b;
++----+-----------------+
+| ID | R |
++----+-----------------+
+| 1 | {{1, Foo}, 10} |
+| 4 | {{5, null}, 10} |
++----+-----------------+
+(2 rows)
+
+!ok
+
+# MULTISET EXCEPT ALL
+SELECT id, ROW(a, b) AS r
+FROM (
+ SELECT id,
+ MULTISET[ROW(ROW(x, y), p), ROW(ROW(x, y), p), ROW(ROW(9, 'Zzz'), 10)]
+ MULTISET EXCEPT ALL
+ MULTISET[ROW(ROW(z, w), q), ROW(ROW(z, w), q), ROW(ROW(z, w), q), ROW(ROW(8, 'Yyy'), 10)] AS m
+ FROM (VALUES
+ (1, 1, 'Foo', 10, 1, 'Foo', 10),
+ (2, 2, 'Bar', 10, 2, 'Car', 10),
+ (3, 3, 'Baz', 10, 4, 'Baz', 10),
+ (4, 5, NULL, 10, 5, NULL, 10),
+ (5, 6, 'Qux', 10, 6, 'Qux', 11)) AS t(id, x, y, p, z, w, q)) AS t
+CROSS JOIN UNNEST(m) AS u(a, b)
+ORDER BY id, u.a."EXPR$0", u.a."EXPR$1", b;
++----+----------------+
+| ID | R |
++----+----------------+
+| 1 | {{9, Zzz}, 10} |
+| 2 | {{2, Bar}, 10} |
+| 2 | {{2, Bar}, 10} |
+| 2 | {{9, Zzz}, 10} |
+| 3 | {{3, Baz}, 10} |
+| 3 | {{3, Baz}, 10} |
+| 3 | {{9, Zzz}, 10} |
+| 4 | {{9, Zzz}, 10} |
+| 5 | {{6, Qux}, 10} |
+| 5 | {{6, Qux}, 10} |
+| 5 | {{9, Zzz}, 10} |
++----+----------------+
+(11 rows)
+
+!ok
+
+# MULTISET EXCEPT DISTINCT
+SELECT id, ROW(a, b) AS r
+FROM (
+ SELECT id,
+ MULTISET[ROW(ROW(x, y), p), ROW(ROW(x, y), p), ROW(ROW(9, 'Zzz'), 10)]
+ MULTISET EXCEPT DISTINCT
+ MULTISET[ROW(ROW(z, w), q), ROW(ROW(z, w), q), ROW(ROW(z, w), q), ROW(ROW(8, 'Yyy'), 10)] AS m
+ FROM (VALUES
+ (1, 1, 'Foo', 10, 1, 'Foo', 10),
+ (2, 2, 'Bar', 10, 2, 'Car', 10),
+ (3, 3, 'Baz', 10, 4, 'Baz', 10),
+ (4, 5, NULL, 10, 5, NULL, 10),
+ (5, 6, 'Qux', 10, 6, 'Qux', 11)) AS t(id, x, y, p, z, w, q)) AS t
+CROSS JOIN UNNEST(m) AS u(a, b)
+ORDER BY id, u.a."EXPR$0", u.a."EXPR$1", b;
++----+----------------+
+| ID | R |
++----+----------------+
+| 1 | {{9, Zzz}, 10} |
+| 2 | {{2, Bar}, 10} |
+| 2 | {{9, Zzz}, 10} |
+| 3 | {{3, Baz}, 10} |
+| 3 | {{9, Zzz}, 10} |
+| 4 | {{9, Zzz}, 10} |
+| 5 | {{6, Qux}, 10} |
+| 5 | {{9, Zzz}, 10} |
++----+----------------+
+(8 rows)
+
+!ok
+
+# MULTISET UNION DISTINCT
+SELECT id, ROW(a, b) AS r
+FROM (
+ SELECT id,
+ MULTISET[ROW(ROW(x, y), p), ROW(ROW(x, y), p), ROW(ROW(9, 'Zzz'), 10)]
+ MULTISET UNION DISTINCT
+ MULTISET[ROW(ROW(z, w), q), ROW(ROW(z, w), q), ROW(ROW(z, w), q), ROW(ROW(8, 'Yyy'), 10)] AS m
+ FROM (VALUES
+ (1, 1, 'Foo', 10, 1, 'Foo', 10),
+ (2, 2, 'Bar', 10, 2, 'Car', 10),
+ (3, 3, 'Baz', 10, 4, 'Baz', 10),
+ (4, 5, NULL, 10, 5, NULL, 10),
+ (5, 6, 'Qux', 10, 6, 'Qux', 11)) AS t(id, x, y, p, z, w, q)) AS t
+CROSS JOIN UNNEST(m) AS u(a, b)
+ORDER BY id, u.a."EXPR$0", u.a."EXPR$1", b;
++----+-----------------+
+| ID | R |
++----+-----------------+
+| 1 | {{1, Foo}, 10} |
+| 1 | {{8, Yyy}, 10} |
+| 1 | {{9, Zzz}, 10} |
+| 2 | {{2, Bar}, 10} |
+| 2 | {{2, Car}, 10} |
+| 2 | {{8, Yyy}, 10} |
+| 2 | {{9, Zzz}, 10} |
+| 3 | {{3, Baz}, 10} |
+| 3 | {{4, Baz}, 10} |
+| 3 | {{8, Yyy}, 10} |
+| 3 | {{9, Zzz}, 10} |
+| 4 | {{5, null}, 10} |
+| 4 | {{8, Yyy}, 10} |
+| 4 | {{9, Zzz}, 10} |
+| 5 | {{6, Qux}, 10} |
+| 5 | {{6, Qux}, 11} |
+| 5 | {{8, Yyy}, 10} |
+| 5 | {{9, Zzz}, 10} |
++----+-----------------+
+(18 rows)
+
+!ok
+
+# MULTISET UNION ALL
+SELECT id, ROW(a, b) AS r
+FROM (
+ SELECT id,
+ MULTISET[ROW(ROW(x, y), p), ROW(ROW(x, y), p), ROW(ROW(9, 'Zzz'), 10)]
+ MULTISET UNION ALL
+ MULTISET[ROW(ROW(z, w), q), ROW(ROW(z, w), q), ROW(ROW(z, w), q), ROW(ROW(8, 'Yyy'), 10)] AS m
+ FROM (VALUES
+ (1, 1, 'Foo', 10, 1, 'Foo', 10),
+ (2, 2, 'Bar', 10, 2, 'Car', 10),
+ (3, 3, 'Baz', 10, 4, 'Baz', 10),
+ (4, 5, NULL, 10, 5, NULL, 10),
+ (5, 6, 'Qux', 10, 6, 'Qux', 11)) AS t(id, x, y, p, z, w, q)) AS t
+CROSS JOIN UNNEST(m) AS u(a, b)
+ORDER BY id, u.a."EXPR$0", u.a."EXPR$1", b;
++----+-----------------+
+| ID | R |
++----+-----------------+
+| 1 | {{1, Foo}, 10} |
+| 1 | {{1, Foo}, 10} |
+| 1 | {{1, Foo}, 10} |
+| 1 | {{1, Foo}, 10} |
+| 1 | {{1, Foo}, 10} |
+| 1 | {{8, Yyy}, 10} |
+| 1 | {{9, Zzz}, 10} |
+| 2 | {{2, Bar}, 10} |
+| 2 | {{2, Bar}, 10} |
+| 2 | {{2, Car}, 10} |
+| 2 | {{2, Car}, 10} |
+| 2 | {{2, Car}, 10} |
+| 2 | {{8, Yyy}, 10} |
+| 2 | {{9, Zzz}, 10} |
+| 3 | {{3, Baz}, 10} |
+| 3 | {{3, Baz}, 10} |
+| 3 | {{4, Baz}, 10} |
+| 3 | {{4, Baz}, 10} |
+| 3 | {{4, Baz}, 10} |
+| 3 | {{8, Yyy}, 10} |
+| 3 | {{9, Zzz}, 10} |
+| 4 | {{5, null}, 10} |
+| 4 | {{5, null}, 10} |
+| 4 | {{5, null}, 10} |
+| 4 | {{5, null}, 10} |
+| 4 | {{5, null}, 10} |
+| 4 | {{8, Yyy}, 10} |
+| 4 | {{9, Zzz}, 10} |
+| 5 | {{6, Qux}, 10} |
+| 5 | {{6, Qux}, 10} |
+| 5 | {{6, Qux}, 11} |
+| 5 | {{6, Qux}, 11} |
+| 5 | {{6, Qux}, 11} |
+| 5 | {{8, Yyy}, 10} |
+| 5 | {{9, Zzz}, 10} |
++----+-----------------+
+(35 rows)
+
+!ok
+
+# ROW values with NULL fields.
+
+# MEMBER OF
+SELECT id, ROW(x, CAST(NULL AS INTEGER)) MEMBER OF MULTISET[ROW(z, CAST(NULL AS INTEGER))] AS r
+FROM (VALUES
+ (1, 1, 1),
+ (2, 2, 3)) AS t(id, x, z)
+ORDER BY id;
++----+-------+
+| ID | R |
++----+-------+
+| 1 | true |
+| 2 | false |
++----+-------+
+(2 rows)
+
+!ok
+
+# IS A SET and IS NOT A SET
+SELECT id,
+ MULTISET[ROW(x, CAST(NULL AS INTEGER)), ROW(z, CAST(NULL AS INTEGER))] IS A SET AS r,
+ MULTISET[ROW(x, CAST(NULL AS INTEGER)), ROW(z, CAST(NULL AS INTEGER))] IS NOT A SET AS not_r
+FROM (VALUES
+ (1, 1, 1),
+ (2, 2, 3)) AS t(id, x, z)
+ORDER BY id;
++----+-------+-------+
+| ID | R | NOT_R |
++----+-------+-------+
+| 1 | false | true |
+| 2 | true | false |
++----+-------+-------+
+(2 rows)
+
+!ok
+
+# SUBMULTISET OF and NOT SUBMULTISET OF
+SELECT id,
+ MULTISET[ROW(x, CAST(NULL AS INTEGER)), ROW(x, CAST(NULL AS INTEGER))]
+ SUBMULTISET OF MULTISET[ROW(z, CAST(NULL AS INTEGER)), ROW(z, CAST(NULL AS INTEGER)), ROW(z, CAST(NULL AS INTEGER)), ROW(9, CAST(NULL AS INTEGER))] AS r,
+ MULTISET[ROW(x, CAST(NULL AS INTEGER)), ROW(x, CAST(NULL AS INTEGER))]
+ NOT SUBMULTISET OF MULTISET[ROW(z, CAST(NULL AS INTEGER)), ROW(z, CAST(NULL AS INTEGER)), ROW(z, CAST(NULL AS INTEGER)), ROW(9, CAST(NULL AS INTEGER))] AS not_r
+FROM (VALUES
+ (1, 1, 1),
+ (2, 2, 3)) AS t(id, x, z)
+ORDER BY id;
++----+-------+-------+
+| ID | R | NOT_R |
++----+-------+-------+
+| 1 | true | false |
+| 2 | false | true |
++----+-------+-------+
+(2 rows)
+
+!ok
+
+# Equal input cardinalities, but too few occurrences of the requested row.
+SELECT id, MULTISET[ROW(x, CAST(NULL AS INTEGER)), ROW(x, CAST(NULL AS INTEGER))]
+ SUBMULTISET OF MULTISET[ROW(z, CAST(NULL AS INTEGER)), ROW(9, CAST(NULL AS INTEGER))] AS r
+FROM (VALUES
+ (1, 1, 1),
+ (2, 2, 3)) AS t(id, x, z)
+ORDER BY id;
++----+-------+
+| ID | R |
++----+-------+
+| 1 | false |
+| 2 | false |
++----+-------+
+(2 rows)
+
+!ok
+
+# MULTISET INTERSECT ALL
+SELECT id, ROW(a, b) AS r
+FROM (
+ SELECT id,
+ MULTISET[ROW(x, CAST(NULL AS INTEGER)), ROW(x, CAST(NULL AS INTEGER)), ROW(9, CAST(NULL AS INTEGER))]
+ MULTISET INTERSECT ALL
+ MULTISET[ROW(z, CAST(NULL AS INTEGER)), ROW(z, CAST(NULL AS INTEGER)), ROW(z, CAST(NULL AS INTEGER)), ROW(8, CAST(NULL AS INTEGER))] AS m
+ FROM (VALUES
+ (1, 1, 1),
+ (2, 2, 3)) AS t(id, x, z)) AS t
+CROSS JOIN UNNEST(m) AS u(a, b)
+ORDER BY id, a, b;
++----+-----------+
+| ID | R |
++----+-----------+
+| 1 | {1, null} |
+| 1 | {1, null} |
++----+-----------+
+(2 rows)
+
+!ok
+
+# MULTISET INTERSECT DISTINCT
+SELECT id, ROW(a, b) AS r
+FROM (
+ SELECT id,
+ MULTISET[ROW(x, CAST(NULL AS INTEGER)), ROW(x, CAST(NULL AS INTEGER)), ROW(9, CAST(NULL AS INTEGER))]
+ MULTISET INTERSECT DISTINCT
+ MULTISET[ROW(z, CAST(NULL AS INTEGER)), ROW(z, CAST(NULL AS INTEGER)), ROW(z, CAST(NULL AS INTEGER)), ROW(8, CAST(NULL AS INTEGER))] AS m
+ FROM (VALUES
+ (1, 1, 1),
+ (2, 2, 3)) AS t(id, x, z)) AS t
+CROSS JOIN UNNEST(m) AS u(a, b)
+ORDER BY id, a, b;
++----+-----------+
+| ID | R |
++----+-----------+
+| 1 | {1, null} |
++----+-----------+
+(1 row)
+
+!ok
+
+# MULTISET EXCEPT ALL
+SELECT id, ROW(a, b) AS r
+FROM (
+ SELECT id,
+ MULTISET[ROW(x, CAST(NULL AS INTEGER)), ROW(x, CAST(NULL AS INTEGER)), ROW(9, CAST(NULL AS INTEGER))]
+ MULTISET EXCEPT ALL
+ MULTISET[ROW(z, CAST(NULL AS INTEGER)), ROW(z, CAST(NULL AS INTEGER)), ROW(z, CAST(NULL AS INTEGER)), ROW(8, CAST(NULL AS INTEGER))] AS m
+ FROM (VALUES
+ (1, 1, 1),
+ (2, 2, 3)) AS t(id, x, z)) AS t
+CROSS JOIN UNNEST(m) AS u(a, b)
+ORDER BY id, a, b;
++----+-----------+
+| ID | R |
++----+-----------+
+| 1 | {9, null} |
+| 2 | {2, null} |
+| 2 | {2, null} |
+| 2 | {9, null} |
++----+-----------+
+(4 rows)
+
+!ok
+
+# MULTISET EXCEPT DISTINCT
+SELECT id, ROW(a, b) AS r
+FROM (
+ SELECT id,
+ MULTISET[ROW(x, CAST(NULL AS INTEGER)), ROW(x, CAST(NULL AS INTEGER)), ROW(9, CAST(NULL AS INTEGER))]
+ MULTISET EXCEPT DISTINCT
+ MULTISET[ROW(z, CAST(NULL AS INTEGER)), ROW(z, CAST(NULL AS INTEGER)), ROW(z, CAST(NULL AS INTEGER)), ROW(8, CAST(NULL AS INTEGER))] AS m
+ FROM (VALUES
+ (1, 1, 1),
+ (2, 2, 3)) AS t(id, x, z)) AS t
+CROSS JOIN UNNEST(m) AS u(a, b)
+ORDER BY id, a, b;
++----+-----------+
+| ID | R |
++----+-----------+
+| 1 | {9, null} |
+| 2 | {2, null} |
+| 2 | {9, null} |
++----+-----------+
+(3 rows)
+
+!ok
+
+# MULTISET UNION DISTINCT
+SELECT id, ROW(a, b) AS r
+FROM (
+ SELECT id,
+ MULTISET[ROW(x, CAST(NULL AS INTEGER)), ROW(x, CAST(NULL AS INTEGER)), ROW(9, CAST(NULL AS INTEGER))]
+ MULTISET UNION DISTINCT
+ MULTISET[ROW(z, CAST(NULL AS INTEGER)), ROW(z, CAST(NULL AS INTEGER)), ROW(z, CAST(NULL AS INTEGER)), ROW(8, CAST(NULL AS INTEGER))] AS m
+ FROM (VALUES
+ (1, 1, 1),
+ (2, 2, 3)) AS t(id, x, z)) AS t
+CROSS JOIN UNNEST(m) AS u(a, b)
+ORDER BY id, a, b;
++----+-----------+
+| ID | R |
++----+-----------+
+| 1 | {1, null} |
+| 1 | {8, null} |
+| 1 | {9, null} |
+| 2 | {2, null} |
+| 2 | {3, null} |
+| 2 | {8, null} |
+| 2 | {9, null} |
++----+-----------+
+(7 rows)
+
+!ok
+
+# MULTISET UNION ALL
+SELECT id, ROW(a, b) AS r
+FROM (
+ SELECT id,
+ MULTISET[ROW(x, CAST(NULL AS INTEGER)), ROW(x, CAST(NULL AS INTEGER)), ROW(9, CAST(NULL AS INTEGER))]
+ MULTISET UNION ALL
+ MULTISET[ROW(z, CAST(NULL AS INTEGER)), ROW(z, CAST(NULL AS INTEGER)), ROW(z, CAST(NULL AS INTEGER)), ROW(8, CAST(NULL AS INTEGER))] AS m
+ FROM (VALUES
+ (1, 1, 1),
+ (2, 2, 3)) AS t(id, x, z)) AS t
+CROSS JOIN UNNEST(m) AS u(a, b)
+ORDER BY id, a, b;
++----+-----------+
+| ID | R |
++----+-----------+
+| 1 | {1, null} |
+| 1 | {1, null} |
+| 1 | {1, null} |
+| 1 | {1, null} |
+| 1 | {1, null} |
+| 1 | {8, null} |
+| 1 | {9, null} |
+| 2 | {2, null} |
+| 2 | {2, null} |
+| 2 | {3, null} |
+| 2 | {3, null} |
+| 2 | {3, null} |
+| 2 | {8, null} |
+| 2 | {9, null} |
++----+-----------+
+(14 rows)
+
+!ok
+
# End row-equality.iq