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 22fac3fbac [CALCITE-7583] UNNEST with multiple array arguments returns
wrong result
22fac3fbac is described below
commit 22fac3fbac92e996047b8f10fdcb460c3a7fa291
Author: Mihai Budiu <[email protected]>
AuthorDate: Wed Jun 17 11:30:28 2026 -0700
[CALCITE-7583] UNNEST with multiple array arguments returns wrong result
Signed-off-by: Mihai Budiu <[email protected]>
---
.../adapter/enumerable/EnumerableUncollect.java | 4 +-
.../org/apache/calcite/rel/core/Uncollect.java | 26 +-
.../org/apache/calcite/runtime/SqlFunctions.java | 159 +++++++--
.../org/apache/calcite/sql/SqlUnnestOperator.java | 21 +-
.../org/apache/calcite/util/BuiltInMethod.java | 2 +-
.../java/org/apache/calcite/test/JdbcTest.java | 186 -----------
.../org/apache/calcite/test/SqlFunctionsTest.java | 107 ++++++
.../org/apache/calcite/test/SqlValidatorTest.java | 32 +-
core/src/test/resources/sql/unnest.iq | 364 +++++++++++++++++++++
9 files changed, 673 insertions(+), 228 deletions(-)
diff --git
a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableUncollect.java
b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableUncollect.java
index 187a1cca75..de167cd08e 100644
---
a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableUncollect.java
+++
b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableUncollect.java
@@ -89,7 +89,7 @@ public static EnumerableUncollect create(RelTraitSet
traitSet, RelNode input,
JavaRowFormat.LIST);
// final Enumerable<List<Employee>> child = <<child adapter>>;
- // return child.selectMany(FLAT_PRODUCT);
+ // return child.selectMany(FLAT_ZIP);
final Expression child_ =
builder.append(
"child", result.block);
@@ -125,7 +125,7 @@ public static EnumerableUncollect create(RelTraitSet
traitSet, RelNode input,
final Expression lambda = lambdaForStructWithSingleItem != null
? lambdaForStructWithSingleItem
- : Expressions.call(BuiltInMethod.FLAT_PRODUCT.method,
+ : Expressions.call(BuiltInMethod.FLAT_ZIP.method,
Expressions.constant(Ints.toArray(fieldCounts)),
Expressions.constant(withOrdinality),
Expressions.constant(
diff --git a/core/src/main/java/org/apache/calcite/rel/core/Uncollect.java
b/core/src/main/java/org/apache/calcite/rel/core/Uncollect.java
index 09b4d5822f..2d4c3620a7 100644
--- a/core/src/main/java/org/apache/calcite/rel/core/Uncollect.java
+++ b/core/src/main/java/org/apache/calcite/rel/core/Uncollect.java
@@ -169,21 +169,34 @@ public static RelDataType deriveUncollectRowType(RelNode
rel,
.build();
}
+ // With multiple collections, zip semantics pads shorter collections with
+ // NULL, so all output columns from a multi-collection UNNEST are nullable.
+ final boolean padNullable = fields.size() > 1;
+
for (int i = 0; i < fields.size(); i++) {
RelDataTypeField field = fields.get(i);
if (field.getType() instanceof MapSqlType) {
// This code is similar to SqlUnnestOperator::inferReturnType.
MapSqlType mapType = (MapSqlType) field.getType();
- builder.add(SqlUnnestOperator.MAP_KEY_COLUMN_NAME,
mapType.getKeyType());
- builder.add(SqlUnnestOperator.MAP_VALUE_COLUMN_NAME,
mapType.getValueType());
+ RelDataType keyType = padNullable
+ ? typeFactory.enforceTypeWithNullability(mapType.getKeyType(),
true)
+ : mapType.getKeyType();
+ RelDataType valueType = padNullable
+ ? typeFactory.enforceTypeWithNullability(mapType.getValueType(),
true)
+ : mapType.getValueType();
+ builder.add(SqlUnnestOperator.MAP_KEY_COLUMN_NAME, keyType);
+ builder.add(SqlUnnestOperator.MAP_VALUE_COLUMN_NAME, valueType);
} else {
RelDataType componentType = field.getType().getComponentType();
if (null == componentType) {
throw RESOURCE.unnestArgument().ex();
}
- boolean isNullable = componentType.isNullable();
+ boolean isNullable = componentType.isNullable() || padNullable;
if (requireAlias) {
- builder.add(itemAliases.get(i), componentType);
+ RelDataType colType = padNullable
+ ? typeFactory.enforceTypeWithNullability(componentType, true)
+ : componentType;
+ builder.add(itemAliases.get(i), colType);
} else if (componentType.isStruct()) {
for (RelDataTypeField fieldInfo : componentType.getFieldList()) {
RelDataType fieldType = fieldInfo.getType();
@@ -194,7 +207,10 @@ public static RelDataType deriveUncollectRowType(RelNode
rel,
}
} else {
// Element type is not a record, use the field name of the element
directly
- builder.add(field.getName(), componentType);
+ RelDataType colType = padNullable
+ ? typeFactory.enforceTypeWithNullability(componentType, true)
+ : componentType;
+ builder.add(field.getName(), colType);
}
}
}
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 90ed84c761..789fb9d6c7 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -7428,68 +7428,87 @@ public static Function1<List<Object>,
Enumerable<Object>> flatList() {
return inputList -> Linq4j.asEnumerable(inputList).select(v ->
structAccess(v, 0, null));
}
- public static Function1<Object, Enumerable<ComparableList<Comparable>>>
flatProduct(
+ /**
+ * Returns a function that, given a row containing one or more collection
+ * fields, produces an {@link Enumerable} of combined element rows using
+ * <em>zip</em> (positional pairing) semantics.
+ *
+ * <p>This is the standard semantics for SQL {@code UNNEST(a, b, ...)}: the
+ * i-th output row pairs element {@code a[i]} with element {@code b[i]}.
+ * Shorter collections are padded with {@code NULL}.
+ */
+ public static Function1<Object, Enumerable<ComparableList<Comparable>>>
flatZip(
final int[] fieldCounts, final boolean withOrdinality,
final FlatProductInputType[] inputTypes) {
if (fieldCounts.length == 1) {
if (!withOrdinality && inputTypes[0] == FlatProductInputType.SCALAR) {
+ // Simple unnest without ordinality
//noinspection unchecked
return (Function1) LIST_AS_ENUMERABLE;
} else {
- return row -> p2(new Object[] { row }, fieldCounts, withOrdinality,
- inputTypes);
+ // unnest with ordinality for a single scalar column
+ return row -> z2(new Object[] { row }, fieldCounts, withOrdinality,
inputTypes);
}
}
- return lists -> p2((Object[]) lists, fieldCounts, withOrdinality,
- inputTypes);
+ return lists -> z2((Object[]) lists, fieldCounts, withOrdinality,
inputTypes);
}
- private static Enumerable<FlatLists.ComparableList<Comparable>> p2(
+ /**
+ * Helper for {@link #flatZip}: unpacks each collection in {@code lists}
+ * into an enumerator and combines them using zip (positional) semantics,
+ * padding shorter collections with {@code NULL}.
+ *
+ * @param lists one element per collection (scalar list, struct list,
or map)
+ * @param fieldCounts output column count for each collection (-1 for a
collection of scalars)
+ * @param withOrdinality whether to append a 1-based ordinality column
+ * @param inputTypes type of elements in each collection (SCALAR, LIST, or
MAP)
+ */
+ @SuppressWarnings("rawtypes")
+ private static Enumerable<FlatLists.ComparableList<Comparable>> z2(
Object[] lists, int[] fieldCounts, boolean withOrdinality,
FlatProductInputType[] inputTypes) {
final List<Enumerator<List<Comparable>>> enumerators = new ArrayList<>();
+ final int[] widths = new int[lists.length];
int totalFieldCount = 0;
for (int i = 0; i < lists.length; i++) {
- int fieldCount = fieldCounts[i];
- FlatProductInputType inputType = inputTypes[i];
- Object inputObject = lists[i];
+ final int fieldCount = fieldCounts[i];
+ final FlatProductInputType inputType = inputTypes[i];
+ final Object inputObject = lists[i];
switch (inputType) {
case SCALAR:
@SuppressWarnings("unchecked") List<Comparable> list =
(List<Comparable>) inputObject;
- enumerators.add(
- Linq4j.transform(
- Linq4j.enumerator(list), FlatLists::of));
+ enumerators.add(Linq4j.transform(Linq4j.enumerator(list),
FlatLists::of));
+ widths[i] = 1;
break;
case LIST:
@SuppressWarnings("unchecked") List<List<Comparable>> listList =
(List<List<Comparable>>) inputObject;
enumerators.add(Linq4j.enumerator(listList));
+ widths[i] = fieldCount;
break;
case MAP:
@SuppressWarnings("unchecked") Map<Comparable, Comparable> map =
(Map<Comparable, Comparable>) inputObject;
Enumerator<Map.Entry<Comparable, Comparable>> enumerator =
Linq4j.enumerator(map.entrySet());
-
- Enumerator<List<Comparable>> transformed =
- Linq4j.transform(enumerator,
- e -> FlatLists.of(e.getKey(), e.getValue()));
- enumerators.add(transformed);
+ enumerators.add(Linq4j.transform(enumerator, e ->
FlatLists.of(e.getKey(), e.getValue())));
+ widths[i] = 2;
break;
default:
- break;
- }
- if (fieldCount < 0) {
- ++totalFieldCount;
- } else {
- totalFieldCount += fieldCount;
+ throw new IllegalArgumentException("Unknown input type: " + inputType);
}
+ totalFieldCount += (fieldCount < 0) ? 1 : fieldCount;
}
if (withOrdinality) {
++totalFieldCount;
}
- return product(enumerators, totalFieldCount, withOrdinality);
+ final int fieldCount = totalFieldCount;
+ return new AbstractEnumerable<FlatLists.ComparableList<Comparable>>() {
+ @Override public Enumerator<FlatLists.ComparableList<Comparable>>
enumerator() {
+ return new ZipPaddedEnumerator(enumerators, widths, fieldCount,
withOrdinality);
+ }
+ };
}
public static Object[] array(Object... args) {
@@ -7549,6 +7568,96 @@ public static <E extends Comparable>
Enumerable<FlatLists.ComparableList<E>> pro
};
}
+ /**
+ * Enumerates over the positional zip of the given collection enumerators,
+ * padding shorter collections with {@code NULL}.
+ */
+ @SuppressWarnings({"rawtypes", "unchecked"})
+ private static class ZipPaddedEnumerator
+ implements Enumerator<FlatLists.ComparableList<Comparable>> {
+ /** One enumerator per input collection; each yields one element row per
step.
+ * For a scalar collection the inner list has exactly one element. */
+ private final List<Enumerator<List<Comparable>>> enumerators;
+ /** Output column count contributed by each collection (parallel to {@link
#enumerators}). */
+ private final int[] widths;
+ /** {@code end_of_collection[i]} is {@code true} once collection {@code i}
is exhausted. */
+ private final boolean[] endOfCollection;
+ /** Preallocated output buffer where each result row is constructed. */
+ final @Nullable Object[] flatElements;
+ /** Reused {@link List} view over {@link #flatElements}, passed to {@link
FlatLists#of}
+ * to produce the final result for each output row. */
+ final List<@Nullable Object> list;
+ private final boolean withOrdinality;
+ /** 1-based counter incremented on each successful {@link #moveNext()}. */
+ private int currentOrdinality;
+
+ ZipPaddedEnumerator(List<Enumerator<List<Comparable>>> enumerators,
+ int[] widths, int fieldCount, boolean withOrdinality) {
+ this.enumerators = enumerators;
+ this.widths = widths;
+ this.withOrdinality = withOrdinality;
+ this.endOfCollection = new boolean[enumerators.size()];
+ flatElements = new Object[fieldCount];
+ list = Arrays.asList(flatElements);
+ }
+
+ @Override public boolean moveNext() {
+ boolean allCompleted = true;
+ for (int i = 0; i < enumerators.size(); i++) {
+ if (!endOfCollection[i]) {
+ endOfCollection[i] = !enumerators.get(i).moveNext();
+ }
+ allCompleted &= endOfCollection[i];
+ }
+ if (!allCompleted && withOrdinality) {
+ currentOrdinality++;
+ }
+ return !allCompleted;
+ }
+
+ @Override public FlatLists.ComparableList<Comparable> current() {
+ int column = 0;
+ for (int i = 0; i < enumerators.size(); i++) {
+ int width = widths[i];
+ if (!endOfCollection[i]) {
+ final Object elemRow = enumerators.get(i).current();
+ if (elemRow instanceof Object[]) {
+ final Object[] arr = (Object[]) elemRow;
+ for (int p = 0; p < width; p++) {
+ flatElements[column + p] = p < arr.length ? arr[p] : null;
+ }
+ } else {
+ final List lst = (List) elemRow;
+ for (int p = 0; p < width; p++) {
+ flatElements[column + p] = p < lst.size() ? lst.get(p) : null;
+ }
+ }
+ } else {
+ Arrays.fill(flatElements, column, column + width, null);
+ }
+ column += width;
+ }
+ if (withOrdinality) {
+ flatElements[column] = currentOrdinality;
+ }
+ return (FlatLists.ComparableList) FlatLists.of(list);
+ }
+
+ @Override public void reset() {
+ for (Enumerator<List<Comparable>> e : enumerators) {
+ e.reset();
+ }
+ Arrays.fill(endOfCollection, false);
+ currentOrdinality = 0;
+ }
+
+ @Override public void close() {
+ for (Enumerator<List<Comparable>> e : enumerators) {
+ e.close();
+ }
+ }
+ }
+
/**
* Implements the {@code .} (field access) operator on an object
* whose type is not known until runtime.
@@ -7644,7 +7753,7 @@ public enum JsonScope {
JSON_KEYS, JSON_KEYS_AND_VALUES, JSON_VALUES
}
- /** Type of argument passed into {@link #flatProduct}. */
+ /** Type of argument passed into {@link #flatZip}. */
public enum FlatProductInputType {
SCALAR, LIST, MAP
}
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlUnnestOperator.java
b/core/src/main/java/org/apache/calcite/sql/SqlUnnestOperator.java
index bd6023936c..af1753f8e8 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlUnnestOperator.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlUnnestOperator.java
@@ -83,13 +83,22 @@ public SqlUnnestOperator(boolean withOrdinality) {
assert type instanceof ArraySqlType || type instanceof MultisetSqlType
|| type instanceof MapSqlType;
// If a type is nullable, all field accesses inside the type are also
nullable
+ // With multiple collections, zip semantics pad shorter collections with
+ // NULL, so all output columns from a multi-collection UNNEST are
nullable.
+ final boolean padNullable = opBinding.getOperandCount() > 1;
if (type instanceof MapSqlType) {
MapSqlType mapType = (MapSqlType) type;
- builder.add(MAP_KEY_COLUMN_NAME, mapType.getKeyType());
- builder.add(MAP_VALUE_COLUMN_NAME, mapType.getValueType());
+ RelDataType keyType = padNullable
+ ? typeFactory.enforceTypeWithNullability(mapType.getKeyType(),
true)
+ : mapType.getKeyType();
+ RelDataType valueType = padNullable
+ ? typeFactory.enforceTypeWithNullability(mapType.getValueType(),
true)
+ : mapType.getValueType();
+ builder.add(MAP_KEY_COLUMN_NAME, keyType);
+ builder.add(MAP_VALUE_COLUMN_NAME, valueType);
} else {
RelDataType componentType = requireNonNull(type.getComponentType(),
"componentType");
- boolean isNullable = componentType.isNullable();
+ boolean isNullable = componentType.isNullable() || padNullable;
if (!allowAliasUnnestItems(opBinding) && componentType.isStruct()) {
for (RelDataTypeField field : componentType.getFieldList()) {
RelDataType fieldType = field.getType();
@@ -99,8 +108,10 @@ public SqlUnnestOperator(boolean withOrdinality) {
builder.add(field.getName(), fieldType);
}
} else {
- builder.add(SqlUtil.deriveAliasFromOrdinal(operand),
- componentType);
+ RelDataType colType = padNullable
+ ? typeFactory.enforceTypeWithNullability(componentType, true)
+ : componentType;
+ builder.add(SqlUtil.deriveAliasFromOrdinal(operand), colType);
}
}
}
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 c529c076e5..295d7d662e 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -327,7 +327,7 @@ public enum BuiltInMethod {
@SuppressWarnings("deprecation")
PAIR_LIST_COPY_OF(PairList.Helper.class, "copyOf", Object.class,
Object.class,
Object[].class),
- FLAT_PRODUCT(SqlFunctions.class, "flatProduct", int[].class, boolean.class,
+ FLAT_ZIP(SqlFunctions.class, "flatZip", int[].class, boolean.class,
FlatProductInputType[].class),
FLAT_LIST(SqlFunctions.class, "flatList"),
LIST_N(FlatLists.class, "copyOf", Comparable[].class),
diff --git a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
index ee1afe05cb..a3c004c441 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
@@ -2490,76 +2490,6 @@ void checkMultisetQueryWithSingleColumn() {
.returnsUnordered("A=[10, 20, 10, 10]");
}
- @Test void testUnnestArray() {
- CalciteAssert.that()
- .query("select*from unnest(array[1,2])")
- .returnsUnordered("EXPR$0=1",
- "EXPR$0=2");
- }
-
- @Test void testUnnestArrayWithOrdinality() {
- CalciteAssert.that()
- .query("select*from unnest(array[10,20]) with ordinality as t(i, o)")
- .returnsUnordered("I=10; O=1",
- "I=20; O=2");
- }
-
- @Test void testUnnestRecordType() {
- // unnest(RecordType(Array))
- CalciteAssert.that()
- .query("select * from unnest\n"
- + "(select t.x from (values array[10, 20], array[30, 40]) as
t(x))\n"
- + " with ordinality as t(a, o)")
- .returnsUnordered("A=10; O=1", "A=20; O=2",
- "A=30; O=1", "A=40; O=2");
-
- // unnest(RecordType(Multiset))
- CalciteAssert.that()
- .query("select * from unnest\n"
- + "(select t.x from (values multiset[10, 20], array[30, 40]) as
t(x))\n"
- + " with ordinality as t(a, o)")
- .returnsUnordered("A=10; O=1", "A=20; O=2",
- "A=30; O=1", "A=40; O=2");
-
- // unnest(RecordType(Map))
- CalciteAssert.that()
- .query("select * from unnest\n"
- + "(select t.x from (values map['a', 20], map['b', 30], map['c',
40]) as t(x))\n"
- + " with ordinality as t(a, b, o)")
- .returnsUnordered("A=a; B=20; O=1",
- "A=b; B=30; O=1",
- "A=c; B=40; O=1");
- }
-
- @Test void testUnnestMultiset() {
- CalciteAssert.that()
- .with(CalciteAssert.Config.REGULAR)
- .query("select*from unnest(multiset[1,2]) as t(c)")
- .returnsUnordered("C=1", "C=2");
- }
-
- @Test void testUnnestMultiset2() {
- CalciteAssert.that()
- .with(CalciteAssert.Config.REGULAR)
- .query("select*from unnest(\n"
- + " select \"employees\" from \"hr\".\"depts\"\n"
- + " where \"deptno\" = 10)")
- .returnsUnordered(
- "empid=100; deptno=10; name=Bill; salary=10000.0; commission=1000",
- "empid=150; deptno=10; name=Sebastian; salary=7000.0;
commission=null");
- }
-
- /** Test case for
- * <a
href="https://issues.apache.org/jira/browse/CALCITE-2391";>[CALCITE-2391]
- * Aggregate query with UNNEST or LATERAL fails with
- * ClassCastException</a>. */
- @Test void testAggUnnestColumn() {
- final String sql = "select count(d.\"name\") as c\n"
- + "from \"hr\".\"depts\" as d,\n"
- + " UNNEST(d.\"employees\") as e";
- CalciteAssert.hr().query(sql).returnsUnordered("C=3");
- }
-
@Test void testArrayElement() {
CalciteAssert.that()
.with(CalciteAssert.Config.REGULAR)
@@ -2599,122 +2529,6 @@ void checkMultisetQueryWithSingleColumn() {
"name=Theodore; deptno=10; M=120");
}
- /** Per SQL std, UNNEST is implicitly LATERAL. */
- @Test void testUnnestArrayColumn() {
- CalciteAssert.hr()
- .query("select d.\"name\", e.*\n"
- + "from \"hr\".\"depts\" as d,\n"
- + " UNNEST(d.\"employees\") as e")
- .returnsUnordered(
- "name=HR; empid=200; deptno=20; name0=Eric; salary=8000.0;
commission=500",
- "name=Sales; empid=100; deptno=10; name0=Bill; salary=10000.0;
commission=1000",
- "name=Sales; empid=150; deptno=10; name0=Sebastian; salary=7000.0;
commission=null");
- }
-
- @Test void testUnnestArrayScalarArray() {
- CalciteAssert.hr()
- .query("select d.\"name\", e.*\n"
- + "from \"hr\".\"depts\" as d,\n"
- + " UNNEST(d.\"employees\", array[1, 2]) as e")
- .returnsUnordered(
- "name=HR; empid=200; deptno=20; name0=Eric; salary=8000.0;
commission=500; EXPR$1=1",
- "name=HR; empid=200; deptno=20; name0=Eric; salary=8000.0;
commission=500; EXPR$1=2",
- "name=Sales; empid=100; deptno=10; name0=Bill; salary=10000.0;
commission=1000; EXPR$1=1",
- "name=Sales; empid=100; deptno=10; name0=Bill; salary=10000.0;
commission=1000; EXPR$1=2",
- "name=Sales; empid=150; deptno=10; name0=Sebastian; salary=7000.0;
commission=null; EXPR$1=1",
- "name=Sales; empid=150; deptno=10; name0=Sebastian; salary=7000.0;
commission=null; EXPR$1=2");
- }
-
- @Test void testUnnestArrayScalarArrayAliased() {
- CalciteAssert.hr()
- .query("select d.\"name\", e.*\n"
- + "from \"hr\".\"depts\" as d,\n"
- + " UNNEST(d.\"employees\", array[1, 2]) as e (ei, d, n, s, c,
i)\n"
- + "where ei + i > 151")
- .returnsUnordered(
- "name=HR; EI=200; D=20; N=Eric; S=8000.0; C=500; I=1",
- "name=HR; EI=200; D=20; N=Eric; S=8000.0; C=500; I=2",
- "name=Sales; EI=150; D=10; N=Sebastian; S=7000.0; C=null; I=2");
- }
-
- @Test void testUnnestArrayScalarArrayWithOrdinal() {
- CalciteAssert.hr()
- .query("select d.\"name\", e.*\n"
- + "from \"hr\".\"depts\" as d,\n"
- + " UNNEST(d.\"employees\", array[1, 2]) with ordinality as e (ei,
d, n, s, c, i, o)\n"
- + "where ei + i > 151")
- .returnsUnordered(
- "name=HR; EI=200; D=20; N=Eric; S=8000.0; C=500; I=1; O=1",
- "name=HR; EI=200; D=20; N=Eric; S=8000.0; C=500; I=2; O=2",
- "name=Sales; EI=150; D=10; N=Sebastian; S=7000.0; C=null; I=2;
O=4");
- }
-
- /** Test case for
- * <a
href="https://issues.apache.org/jira/browse/CALCITE-3498";>[CALCITE-3498]
- * Unnest operation's ordinality should be deterministic</a>. */
- @Test void testUnnestArrayWithDeterministicOrdinality() {
- CalciteAssert.that()
- .query("select v, o\n"
- + "from unnest(array[100, 200]) with ordinality as t1(v, o)\n"
- + "where v > 1")
- .returns("V=100; O=1\n"
- + "V=200; O=2\n");
-
- CalciteAssert.that()
- .query("with\n"
- + " x as (select * from unnest(array[100, 200]) with ordinality
as t1(v, o)), "
- + " y as (select * from unnest(array[1000, 2000]) with ordinality
as t2(v, o))\n"
- + "select x.o as o1, x.v as v1, y.o as o2, y.v as v2 "
- + "from x join y on x.o=y.o")
- .returnsUnordered(
- "O1=1; V1=100; O2=1; V2=1000",
- "O1=2; V1=200; O2=2; V2=2000");
- }
-
- /** Test case for
- * <a
href="https://issues.apache.org/jira/browse/CALCITE-1250";>[CALCITE-1250]
- * UNNEST applied to MAP data type</a>. */
- @Test void testUnnestItemsInMap() throws SQLException {
- Connection connection = DriverManager.getConnection("jdbc:calcite:");
- final String sql = "select * from unnest(MAP['a', 1, 'b', 2]) as um(k, v)";
- ResultSet resultSet = connection.createStatement().executeQuery(sql);
- final String expected = "K=a; V=1\n"
- + "K=b; V=2\n";
- assertThat(CalciteAssert.toString(resultSet), is(expected));
- connection.close();
- }
-
- @Test void testUnnestItemsInMapWithOrdinality() throws SQLException {
- Connection connection = DriverManager.getConnection("jdbc:calcite:");
- final String sql = "select *\n"
- + "from unnest(MAP['a', 1, 'b', 2]) with ordinality as um(k, v, i)";
- ResultSet resultSet = connection.createStatement().executeQuery(sql);
- final String expected = "K=a; V=1; I=1\n"
- + "K=b; V=2; I=2\n";
- assertThat(CalciteAssert.toString(resultSet), is(expected));
- connection.close();
- }
-
- @Test void testUnnestItemsInMapWithNoAliasAndAdditionalArgument()
- throws SQLException {
- Connection connection = DriverManager.getConnection("jdbc:calcite:");
- final String sql =
- "select * from unnest(MAP['a', 1, 'b', 2], array[5, 6, 7])";
- ResultSet resultSet = connection.createStatement().executeQuery(sql);
-
- List<String> map = FlatLists.of("KEY=a; VALUE=1", "KEY=b; VALUE=2");
- List<String> array = FlatLists.of(" EXPR$1=5", " EXPR$1=6", " EXPR$1=7");
-
- final StringBuilder b = new StringBuilder();
- for (List<String> row : Linq4j.product(FlatLists.of(map, array))) {
- b.append(row.get(0)).append(";").append(row.get(1)).append("\n");
- }
- final String expected = b.toString();
-
- assertThat(CalciteAssert.toString(resultSet), is(expected));
- connection.close();
- }
-
private CalciteAssert.AssertQuery withFoodMartQuery(int id)
throws IOException {
final FoodMartQuerySet set = FoodMartQuerySet.instance();
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 96c111bd39..959e2fabc2 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
@@ -17,7 +17,10 @@
package org.apache.calcite.test;
import org.apache.calcite.avatica.util.ByteString;
import org.apache.calcite.avatica.util.DateTimeUtils;
+import org.apache.calcite.linq4j.Enumerable;
+import org.apache.calcite.linq4j.function.Function1;
import org.apache.calcite.runtime.CalciteException;
+import org.apache.calcite.runtime.FlatLists;
import org.apache.calcite.runtime.SqlFunctions;
import org.apache.calcite.runtime.Utilities;
@@ -41,6 +44,8 @@
import static
org.apache.calcite.avatica.util.DateTimeUtils.dateStringToUnixDate;
import static
org.apache.calcite.avatica.util.DateTimeUtils.timeStringToUnixDate;
import static
org.apache.calcite.avatica.util.DateTimeUtils.timestampStringToUnixDate;
+import static
org.apache.calcite.runtime.SqlFunctions.FlatProductInputType.LIST;
+import static
org.apache.calcite.runtime.SqlFunctions.FlatProductInputType.SCALAR;
import static org.apache.calcite.runtime.SqlFunctions.arraysOverlap;
import static org.apache.calcite.runtime.SqlFunctions.charLength;
import static org.apache.calcite.runtime.SqlFunctions.concat;
@@ -2112,4 +2117,106 @@ private long sqlTimestamp(String str) {
() -> parse.parseTimestamp("%Y-%m-%d %H:%M:%S",
"2024-01-01 00:00:00", "Asia/Sanghai"));
}
+
+ // Tests for ZipPaddedEnumerator, accessed via the public
SqlFunctions.flatZip API.
+
+ /** Invokes {@link SqlFunctions#flatZip} over scalar collections and
collects output rows. */
+ @SuppressWarnings({"rawtypes", "unchecked"})
+ private static List<List<Object>> zipScalars(
+ boolean withOrdinality, List<Comparable>... inputs) {
+ final int n = inputs.length;
+ final int[] fieldCounts = new int[n];
+ Arrays.fill(fieldCounts, 1);
+ final SqlFunctions.FlatProductInputType[] types =
+ new SqlFunctions.FlatProductInputType[n];
+ Arrays.fill(types, SCALAR);
+ final Function1<Object, Enumerable<FlatLists.ComparableList<Comparable>>>
fn =
+ SqlFunctions.flatZip(fieldCounts, withOrdinality, types);
+ final Object arg = n == 1 ? inputs[0] : inputs;
+ final List<List<Object>> rows = new ArrayList<>();
+ for (FlatLists.ComparableList<Comparable> row : fn.apply(arg)) {
+ rows.add(new ArrayList<>(row));
+ }
+ return rows;
+ }
+
+ @Test void testZipPaddedSingleCollectionWithOrdinality() {
+ // Single scalar collection with ordinality uses ZipPaddedEnumerator, not
+ // the LIST_AS_ENUMERABLE shortcut.
+ List<List<Object>> rows = zipScalars(true, Arrays.asList(10, 20, 30));
+ assertThat(rows, hasSize(3));
+ assertThat(rows.get(0), is(list(10, 1)));
+ assertThat(rows.get(1), is(list(20, 2)));
+ assertThat(rows.get(2), is(list(30, 3)));
+ }
+
+ @Test void testZipPaddedEqualLength() {
+ List<List<Object>> rows =
+ zipScalars(false, Arrays.asList(10, 20), Arrays.asList(4, 5));
+ assertThat(rows, hasSize(2));
+ assertThat(rows.get(0), is(list(10, 4)));
+ assertThat(rows.get(1), is(list(20, 5)));
+ }
+
+ @Test void testZipPaddedFirstLonger() {
+ List<List<Object>> rows =
+ zipScalars(false, Arrays.asList(10, 20, 30), Arrays.asList(4, 5));
+ assertThat(rows, hasSize(3));
+ assertThat(rows.get(0), is(list(10, 4)));
+ assertThat(rows.get(1), is(list(20, 5)));
+ assertThat(rows.get(2), is(Arrays.asList(30, null)));
+ }
+
+ @Test void testZipPaddedSecondLonger() {
+ List<List<Object>> rows =
+ zipScalars(false, Arrays.asList(10), Arrays.asList(4, 5, 6));
+ assertThat(rows, hasSize(3));
+ assertThat(rows.get(0), is(list(10, 4)));
+ assertThat(rows.get(1), is(Arrays.asList(null, 5)));
+ assertThat(rows.get(2), is(Arrays.asList(null, 6)));
+ }
+
+ @Test void testZipPaddedBothEmpty() {
+ List<List<Object>> rows =
+ zipScalars(false, Collections.emptyList(), Collections.emptyList());
+ assertThat(rows, hasSize(0));
+ }
+
+ @Test void testZipPaddedOneEmpty() {
+ List<List<Object>> rows =
+ zipScalars(false, Arrays.asList(4, 5), Collections.emptyList());
+ assertThat(rows, hasSize(2));
+ assertThat(rows.get(0), is(Arrays.asList(4, null)));
+ assertThat(rows.get(1), is(Arrays.asList(5, null)));
+ }
+
+ @Test void testZipPaddedWithOrdinality() {
+ List<List<Object>> rows =
+ zipScalars(true, Arrays.asList(10, 20, 30), Arrays.asList(4, 5));
+ assertThat(rows, hasSize(3));
+ assertThat(rows.get(0), is(list(10, 4, 1)));
+ assertThat(rows.get(1), is(list(20, 5, 2)));
+ assertThat(rows.get(2), is(Arrays.asList(30, null, 3)));
+ }
+
+ @Test void testZipPaddedStructElements() {
+ // Two LIST (struct) collections of width 2: [(1,2),(3,4)] and [(10,20)]
+ // → [1,2,10,20], [3,4,null,null]
+ @SuppressWarnings({"rawtypes", "unchecked"})
+ final Function1<Object, Enumerable<FlatLists.ComparableList<Comparable>>>
fn =
+ SqlFunctions.flatZip(new int[]{2, 2}, false,
+ new SqlFunctions.FlatProductInputType[]{LIST, LIST});
+ final List<List<Comparable>> col1 =
+ Arrays.asList(FlatLists.of(1, 2), FlatLists.of(3, 4));
+ final List<List<Comparable>> col2 =
+ Collections.singletonList(FlatLists.of(10, 20));
+ final List<List<Object>> rows = new ArrayList<>();
+ for (FlatLists.ComparableList<Comparable> row
+ : fn.apply(new Object[]{col1, col2})) {
+ rows.add(new ArrayList<>(row));
+ }
+ assertThat(rows, hasSize(2));
+ assertThat(rows.get(0), is(list(1, 2, 10, 20)));
+ assertThat(rows.get(1), is(Arrays.asList(3, 4, null, null)));
+ }
}
diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
index 9bdd3177ff..4b4d2302de 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -2117,10 +2117,8 @@ void testLikeAndSimilarFails() {
.type("RecordType(BOOLEAN NOT NULL EXPR$0) NOT NULL");
sql("^values ('1'),(2)^")
.fails("Values passed to VALUES operator must have compatible types");
- if (TODO) {
- sql("values (1),(2.0),(3)")
- .columnType("ROWTYPE(DOUBLE)");
- }
+ sql("values (1),(2.0),(3)")
+ .columnType("DECIMAL(11, 1) NOT NULL");
}
@Test void testRow() {
@@ -14517,4 +14515,30 @@ private static SqlIdentifier
rewriteIdentifier(SqlIdentifier sqlIdentifier) {
+ "<MAP<ROW\\(INTEGER X, BIGINT Y\\), ROW\\(CHAR\\(1\\) ARRAY
EXPR\\$0\\)>>'\\. "
+ "Supported form\\(s\\): '<COMPARABLE_TYPE> =
<COMPARABLE_TYPE>'");
}
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7583";>[CALCITE-7583]
+ * UNNEST with multiple array arguments returns wrong result</a>.
+ *
+ * <p>When UNNEST is given multiple collection arguments, zip (positional)
+ * semantics pad shorter collections with NULL, so all output columns must be
+ * nullable even when the element types of the input arrays are not
nullable. */
+ @Test void testUnnestMultiArgNullability() {
+ // Single-arg UNNEST: element type is NOT NULL, result column is NOT NULL.
+ sql("select * from unnest(array[1, 2])")
+ .type("RecordType(INTEGER NOT NULL EXPR$0) NOT NULL");
+ // Multi-arg UNNEST: result columns are nullable (padded with NULL when
lengths differ).
+ sql("select * from unnest(array[1, 2], array[3, 4])")
+ .type("RecordType(INTEGER EXPR$0, INTEGER EXPR$1) NOT NULL");
+ // Ordinality column itself is always NOT NULL.
+ sql("select * from unnest(array[1, 2], array[3, 4]) with ordinality")
+ .type("RecordType(INTEGER EXPR$0, INTEGER EXPR$1,"
+ + " INTEGER NOT NULL ORDINALITY) NOT NULL");
+ // Different element types: both result columns are nullable.
+ sql("select * from unnest(array['a', 'b'], array[1, 2])")
+ .type("RecordType(CHAR(1) EXPR$0, INTEGER EXPR$1) NOT NULL");
+ // Struct array + scalar array: all result columns (including struct
fields) are nullable.
+ sql("select * from unnest(array[(1, 'a'), (2, 'b')], array[10, 20]) as
t(p, q, r)")
+ .type("RecordType(INTEGER P, CHAR(1) Q, INTEGER R) NOT NULL");
+ }
}
diff --git a/core/src/test/resources/sql/unnest.iq
b/core/src/test/resources/sql/unnest.iq
index 88488141ad..8defd3b01c 100644
--- a/core/src/test/resources/sql/unnest.iq
+++ b/core/src/test/resources/sql/unnest.iq
@@ -243,6 +243,370 @@ FROM UNNEST(array [0, 2, 4, 4, 5]) as x;
!ok
+# Tests for [CALCITE-7583] UNNEST with multiple array arguments returns wrong
result
+# 6 tests validated on Postgres
+
+# Multi-collection UNNEST uses zip (positional) semantics, not cartesian
+# product. Elements are paired by position; shorter collections are padded
+# with NULL.
+
+# Equal-length: zip and product give the same result.
+select *
+from unnest(array[10, 20, 30], array[1, 2, 3]) as t(a, b);
++----+---+
+| A | B |
++----+---+
+| 10 | 1 |
+| 20 | 2 |
+| 30 | 3 |
++----+---+
+(3 rows)
+
+!ok
+
+# First collection longer: second is padded with NULL for the extra rows.
+select *
+from unnest(array[10, 20, 30], array[1, 2]) as t(a, b);
++----+---+
+| A | B |
++----+---+
+| 10 | 1 |
+| 20 | 2 |
+| 30 | |
++----+---+
+(3 rows)
+
+!ok
+
+# Second collection longer: first is padded with NULL for the extra rows.
+select *
+from unnest(array[10, 20], array[1, 2, 3]) as t(a, b);
++----+---+
+| A | B |
++----+---+
+| 10 | 1 |
+| 20 | 2 |
+| | 3 |
++----+---+
+(3 rows)
+
+!ok
+
+# Ordinality counts all output rows, including NULL-padded ones.
+select *
+from unnest(array[10, 20, 30], array[1, 2]) with ordinality as t(a, b, o);
++----+---+---+
+| A | B | O |
++----+---+---+
+| 10 | 1 | 1 |
+| 20 | 2 | 2 |
+| 30 | | 3 |
++----+---+---+
+(3 rows)
+
+!ok
+
+# MAP + scalar array: MAP (2 entries) exhausts before the scalar (3 elements).
+# Postgres does not support MAP values, so this is not validated on Postgres
+select *
+from unnest(map['x', 10, 'y', 20], array[1, 2, 3]) as t(k, v, n);
++---+----+---+
+| K | V | N |
++---+----+---+
+| x | 10 | 1 |
+| y | 20 | 2 |
+| | | 3 |
++---+----+---+
+(3 rows)
+
+!ok
+
+# Struct array + scalar array: struct array has 3 elements, scalar has 2.
+# The third struct row is produced normally; scalar column is NULL.
+# Postgres requires a slightly different syntax, but produces the same result.
+select *
+from unnest(array[(1, 'a'), (2, 'b'), (3, 'c')], array[10, 20]) as t(x, y, z);
++---+---+----+
+| X | Y | Z |
++---+---+----+
+| 1 | a | 10 |
+| 2 | b | 20 |
+| 3 | c | |
++---+---+----+
+(3 rows)
+
+!ok
+
+# [CALCITE-7583] UNNEST with multiple array arguments returns wrong result
+# Three arrays of different lengths with ordinality: output has max(3,2,4)=4
rows.
+select *
+from unnest(array[10, 20, 30], array[1, 2], array['a', 'b', 'c', 'd'])
+with ordinality as t(x, y, z, o);
++----+---+---+---+
+| X | Y | Z | O |
++----+---+---+---+
+| 10 | 1 | a | 1 |
+| 20 | 2 | b | 2 |
+| 30 | | c | 3 |
+| | | d | 4 |
++----+---+---+---+
+(4 rows)
+
+!ok
+
+# Unnest a plain scalar array; default column name is EXPR$0.
+select * from unnest(array[1, 2]);
++--------+
+| EXPR$0 |
++--------+
+| 1 |
+| 2 |
++--------+
+(2 rows)
+
+!ok
+
+# Unnest with ordinality: explicit aliases i and o.
+select * from unnest(array[10, 20]) with ordinality as t(i, o);
++----+---+
+| I | O |
++----+---+
+| 10 | 1 |
+| 20 | 2 |
++----+---+
+(2 rows)
+
+!ok
+
+# Unnest a subquery that returns a RecordType(Array).
+# Ordinality resets to 1 for each input row.
+select * from unnest
+(select t.x from (values array[10, 20], array[30, 40]) as t(x))
+ with ordinality as t(a, o);
++----+---+
+| A | O |
++----+---+
+| 10 | 1 |
+| 20 | 2 |
+| 30 | 1 |
+| 40 | 2 |
++----+---+
+(4 rows)
+
+!ok
+
+# Unnest a subquery that returns a RecordType(Multiset).
+# Ordinality resets to 1 for each input row.
+select * from unnest
+(select t.x from (values multiset[10, 20], array[30, 40]) as t(x))
+ with ordinality as t(a, o);
++----+---+
+| A | O |
++----+---+
+| 10 | 1 |
+| 20 | 2 |
+| 30 | 1 |
+| 40 | 2 |
++----+---+
+(4 rows)
+
+!ok
+
+# Unnest a subquery that returns a RecordType(Map).
+# Each map is one row; ordinality is always 1 because each map has one entry.
+select * from unnest
+(select t.x from (values map['a', 20], map['b', 30], map['c', 40]) as t(x))
+ with ordinality as t(a, b, o);
++---+----+---+
+| A | B | O |
++---+----+---+
+| a | 20 | 1 |
+| b | 30 | 1 |
+| c | 40 | 1 |
++---+----+---+
+(3 rows)
+
+!ok
+
+# Unnest a multiset literal with an explicit column alias.
+select * from unnest(multiset[1, 2]) as t(c);
++---+
+| C |
++---+
+| 1 |
+| 2 |
++---+
+(2 rows)
+
+!ok
+
+# [CALCITE-3498] Unnest operation's ordinality should be deterministic.
+# A filter applied after WITH ORDINALITY must not disturb the ordinality
values.
+select v, o
+from unnest(array[100, 200]) with ordinality as t1(v, o)
+where v > 1;
++-----+---+
+| V | O |
++-----+---+
+| 100 | 1 |
+| 200 | 2 |
++-----+---+
+(2 rows)
+
+!ok
+
+# [CALCITE-3498] Unnest operation's ordinality should be deterministic.
+with
+ x as (select * from unnest(array[100, 200]) with ordinality as t1(v, o)),
+ y as (select * from unnest(array[1000, 2000]) with ordinality as t2(v, o))
+select x.o as o1, x.v as v1, y.o as o2, y.v as v2
+from x join y on x.o = y.o;
++----+-----+----+------+
+| O1 | V1 | O2 | V2 |
++----+-----+----+------+
+| 1 | 100 | 1 | 1000 |
+| 2 | 200 | 2 | 2000 |
++----+-----+----+------+
+(2 rows)
+
+!ok
+
+# [CALCITE-1250] UNNEST applied to MAP data type.
+select * from unnest(MAP['a', 1, 'b', 2]) as um(k, v);
++---+---+
+| K | V |
++---+---+
+| a | 1 |
+| b | 2 |
++---+---+
+(2 rows)
+
+!ok
+
+select *
+from unnest(MAP['a', 1, 'b', 2]) with ordinality as um(k, v, i);
++---+---+---+
+| K | V | I |
++---+---+---+
+| a | 1 | 1 |
+| b | 2 | 2 |
++---+---+---+
+(2 rows)
+
+!ok
+
+# [CALCITE-7583] UNNEST with multiple array arguments returns wrong result
+# Validated on Postgres
+# MAP has 2 entries; array has 3 elements; the third row pads MAP columns with
NULL.
+select * from unnest(MAP['a', 1, 'b', 2], array[5, 6, 7]);
++-----+-------+--------+
+| KEY | VALUE | EXPR$1 |
++-----+-------+--------+
+| a | 1 | 5 |
+| b | 2 | 6 |
+| | | 7 |
++-----+-------+--------+
+(3 rows)
+
+!ok
+
+!use hr
+
+# Unnest a subquery result that returns an array-of-struct column
(hr.depts.employees).
+select * from unnest(
+ select "employees" from "hr"."depts"
+ where "deptno" = 10);
++-------+--------+-----------+---------+------------+
+| empid | deptno | name | salary | commission |
++-------+--------+-----------+---------+------------+
+| 100 | 10 | Bill | 10000.0 | 1000 |
+| 150 | 10 | Sebastian | 7000.0 | |
++-------+--------+-----------+---------+------------+
+(2 rows)
+
+!ok
+
+# [CALCITE-2391] Aggregate query with UNNEST or LATERAL fails with
ClassCastException.
+# Total employee count across all depts: Sales(2) + Marketing(0) + HR(1) = 3.
+select count(d."name") as c
+from "hr"."depts" as d,
+UNNEST(d."employees") as e;
++---+
+| C |
++---+
+| 3 |
++---+
+(1 row)
+
+!ok
+
+# Per SQL standard, UNNEST is implicitly LATERAL.
+select d."name", e.*
+from "hr"."depts" as d,
+UNNEST(d."employees") as e;
++-------+-------+--------+-----------+---------+------------+
+| name | empid | deptno | name0 | salary | commission |
++-------+-------+--------+-----------+---------+------------+
+| HR | 200 | 20 | Eric | 8000.0 | 500 |
+| Sales | 100 | 10 | Bill | 10000.0 | 1000 |
+| Sales | 150 | 10 | Sebastian | 7000.0 | |
++-------+-------+--------+-----------+---------+------------+
+(3 rows)
+
+!ok
+
+# [CALCITE-7583] UNNEST with multiple array arguments returns wrong result
+# Validated on Postgres
+select d."name", e.*
+from "hr"."depts" as d,
+ UNNEST(d."employees", array[1, 2]) as e;
++-----------+-------+--------+-----------+---------+------------+--------+
+| name | empid | deptno | name0 | salary | commission | EXPR$1 |
++-----------+-------+--------+-----------+---------+------------+--------+
+| HR | 200 | 20 | Eric | 8000.0 | 500 | 1 |
+| HR | | | | | | 2 |
+| Marketing | | | | | | 1 |
+| Marketing | | | | | | 2 |
+| Sales | 100 | 10 | Bill | 10000.0 | 1000 | 1 |
+| Sales | 150 | 10 | Sebastian | 7000.0 | | 2 |
++-----------+-------+--------+-----------+---------+------------+--------+
+(6 rows)
+
+!ok
+
+# [CALCITE-7583] UNNEST with multiple array arguments returns wrong result
+# Validated on Postgres
+select d."name", e.*
+from "hr"."depts" as d,
+ UNNEST(d."employees", array[1, 2]) as e (ei, d, n, s, c, i)
+where ei + i > 151;
++-------+-----+----+-----------+--------+-----+---+
+| name | EI | D | N | S | C | I |
++-------+-----+----+-----------+--------+-----+---+
+| HR | 200 | 20 | Eric | 8000.0 | 500 | 1 |
+| Sales | 150 | 10 | Sebastian | 7000.0 | | 2 |
++-------+-----+----+-----------+--------+-----+---+
+(2 rows)
+
+!ok
+
+# [CALCITE-7583] UNNEST with multiple array arguments returns wrong result
+# Validated on Postgres
+# Same query with WITH ORDINALITY added.
+select d."name", e.*
+from "hr"."depts" as d,
+ UNNEST(d."employees", array[1, 2]) with ordinality as e (ei, d, n, s, c, i, o)
+where ei + i > 151;
++-------+-----+----+-----------+--------+-----+---+---+
+| name | EI | D | N | S | C | I | O |
++-------+-----+----+-----------+--------+-----+---+---+
+| HR | 200 | 20 | Eric | 8000.0 | 500 | 1 | 1 |
+| Sales | 150 | 10 | Sebastian | 7000.0 | | 2 | 2 |
++-------+-----+----+-----------+--------+-----+---+---+
+(2 rows)
+
+!ok
+
!use bookstore
# [CALCITE-4773] RelDecorrelator's RemoveSingleAggregateRule can produce
result with wrong row type
