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 d9af5c187f [CALCITE-7670] Uncollect should support LEFT JOIN UNNEST
d9af5c187f is described below
commit d9af5c187f338739d7243bf91fc730796347b8a3
Author: Mihai Budiu <[email protected]>
AuthorDate: Tue Aug 4 15:44:49 2026 -0700
[CALCITE-7670] Uncollect should support LEFT JOIN UNNEST
Signed-off-by: Mihai Budiu <[email protected]>
---
.../adapter/enumerable/EnumerableUncollect.java | 30 +-
.../enumerable/EnumerableUncollectRule.java | 3 +-
.../apache/calcite/interpreter/UncollectNode.java | 15 +
.../org/apache/calcite/rel/core/Uncollect.java | 47 ++-
.../calcite/rel/logical/ToLogicalConverter.java | 2 +-
.../apache/calcite/rel/mutable/MutableRels.java | 4 +-
.../calcite/rel/mutable/MutableUncollect.java | 32 ++-
.../org/apache/calcite/rel/rules/CoreRules.java | 6 +
.../rel/rules/CorrelateUncollectOuterRule.java | 105 +++++++
.../calcite/rel/rules/UnnestDecorrelateRule.java | 16 +-
.../org/apache/calcite/runtime/SqlFunctions.java | 131 ++++++++-
.../apache/calcite/sql2rel/SqlToRelConverter.java | 4 +-
.../java/org/apache/calcite/tools/RelBuilder.java | 30 +-
.../org/apache/calcite/util/BuiltInMethod.java | 3 +-
.../calcite/sql2rel/RelFieldTrimmerTest.java | 2 +-
.../org/apache/calcite/test/SqlFunctionsTest.java | 124 +++++++-
core/src/test/resources/sql/unnest.iq | 316 +++++++++++++++++++++
17 files changed, 822 insertions(+), 48 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 8f193a4ace..143cabad99 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
@@ -51,16 +51,20 @@ public EnumerableUncollect(RelOptCluster cluster,
RelTraitSet traitSet,
* <p>Use {@link #create} unless you know what you're doing. */
public EnumerableUncollect(RelOptCluster cluster, RelTraitSet traitSet,
RelNode child, boolean withOrdinality) {
- this(cluster, traitSet, child, withOrdinality, true);
+ this(cluster, traitSet, child, withOrdinality, true, false);
}
/** Creates an EnumerableUncollect.
*
- * <p>Use {@link #create} unless you know what you're doing. */
+ * <p>Use {@link #create} unless you know what you're doing.
+ *
+ * @param isOuter If true, an empty or NULL collection yields one row of
+ * NULLs (LEFT JOIN); if false, it yields no rows (INNER) */
public EnumerableUncollect(RelOptCluster cluster, RelTraitSet traitSet,
- RelNode child, boolean withOrdinality, boolean expandStructFields) {
+ RelNode child, boolean withOrdinality, boolean expandStructFields,
+ boolean isOuter) {
super(cluster, traitSet, child, withOrdinality, Collections.emptyList(),
- expandStructFields);
+ expandStructFields, isOuter);
assert getConvention() instanceof EnumerableConvention;
assert getConvention() == child.getConvention();
}
@@ -90,18 +94,20 @@ public static EnumerableUncollect create(RelTraitSet
traitSet, RelNode input,
* @param expandStructFields If true, a collection whose element type is a
struct
* produces one output column per struct field; if
false,
* a single column typed as the whole element
+ * @param isOuter If true, an empty or NULL collection yields one
row of
+ * NULLs (LEFT JOIN); if false, it yields no rows
(INNER)
*/
public static EnumerableUncollect create(RelTraitSet traitSet, RelNode input,
- boolean withOrdinality, boolean expandStructFields) {
+ boolean withOrdinality, boolean expandStructFields, boolean isOuter) {
final RelOptCluster cluster = input.getCluster();
return new EnumerableUncollect(cluster, traitSet, input, withOrdinality,
- expandStructFields);
+ expandStructFields, isOuter);
}
@Override public EnumerableUncollect copy(RelTraitSet traitSet,
RelNode newInput) {
return new EnumerableUncollect(getCluster(), traitSet, newInput,
- withOrdinality, expandStructFields);
+ withOrdinality, expandStructFields, isOuter);
}
@Override public Result implement(EnumerableRelImplementor implementor,
Prefer pref) {
@@ -136,8 +142,11 @@ public static EnumerableUncollect create(RelTraitSet
traitSet, RelNode input,
&& !withOrdinality) {
// Solves CALCITE-4063: if we are processing a single field, which
is a struct with a
// single item inside, and no ordinality; the result must be a
scalar, hence use a
- // special lambda that does not return lists, but the (single)
items within those lists
- lambdaForStructWithSingleItem =
Expressions.call(BuiltInMethod.FLAT_LIST.method);
+ // special lambda that does not return lists, but the (single)
items within those
+ // lists. The outer variant returns one NULL scalar for an empty
or NULL collection.
+ lambdaForStructWithSingleItem =
+ Expressions.call(isOuter ? BuiltInMethod.FLAT_LIST_OUTER.method
+ : BuiltInMethod.FLAT_LIST.method);
} else {
fieldCounts.add(elementType.getFieldCount());
inputTypes.add(FlatProductInputType.LIST);
@@ -161,7 +170,8 @@ public static EnumerableUncollect create(RelTraitSet
traitSet, RelNode input,
Expressions.constant(Ints.toArray(fieldCounts)),
Expressions.constant(withOrdinality),
Expressions.constant(
- inputTypes.toArray(new FlatProductInputType[0])));
+ inputTypes.toArray(new FlatProductInputType[0])),
+ Expressions.constant(isOuter));
builder.add(
Expressions.return_(null,
Expressions.call(child_,
diff --git
a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableUncollectRule.java
b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableUncollectRule.java
index 95a9237c22..906404c3ab 100644
---
a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableUncollectRule.java
+++
b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableUncollectRule.java
@@ -49,6 +49,7 @@ protected EnumerableUncollectRule(Config config) {
convert(input,
input.getTraitSet().replace(EnumerableConvention.INSTANCE));
return EnumerableUncollect.create(traitSet, newInput,
- uncollect.withOrdinality, uncollect.expandStructFields);
+ uncollect.withOrdinality, uncollect.expandStructFields,
+ uncollect.isOuter);
}
}
diff --git
a/core/src/main/java/org/apache/calcite/interpreter/UncollectNode.java
b/core/src/main/java/org/apache/calcite/interpreter/UncollectNode.java
index 01f4d578f4..725f577c4a 100644
--- a/core/src/main/java/org/apache/calcite/interpreter/UncollectNode.java
+++ b/core/src/main/java/org/apache/calcite/interpreter/UncollectNode.java
@@ -33,15 +33,26 @@ public UncollectNode(Compiler compiler, Uncollect
uncollect) {
}
@Override public void run() throws InterruptedException {
+ // Under isOuter an empty or NULL collection still produces one row, with
+ // every column NULL.
+ final int width = rel.getRowType().getFieldCount();
Row row = null;
while ((row = source.receive()) != null) {
for (Object value : row.getValues()) {
if (value == null) {
+ if (rel.isOuter) {
+ sink.send(Row.of(new Object[width]));
+ continue;
+ }
throw new NullPointerException("NULL value for unnest.");
}
int i = 1;
if (value instanceof List) {
List list = (List) value;
+ if (list.isEmpty() && rel.isOuter) {
+ sink.send(Row.of(new Object[width]));
+ continue;
+ }
for (Object o : list) {
if (rel.withOrdinality) {
sink.send(Row.of(o, i++));
@@ -51,6 +62,10 @@ public UncollectNode(Compiler compiler, Uncollect uncollect)
{
}
} else if (value instanceof Map) {
Map map = (Map) value;
+ if (map.isEmpty() && rel.isOuter) {
+ sink.send(Row.of(new Object[width]));
+ continue;
+ }
for (Object key : map.keySet()) {
if (rel.withOrdinality) {
sink.send(Row.of(key, map.get(key), i++));
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 e607509ddc..039a17efbf 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
@@ -56,10 +56,20 @@
* output column per struct field; if {@code false} it produces a single
* column typed as the whole element (Trino semantics). Maps always expand
* into a key and a value column, regardless of this flag.
+ *
+ * <p>{@code isOuter} controls what happens to an empty or {@code NULL}
+ * collection: if {@code true} (LEFT JOIN semantics) one row is emitted with
+ * every element column set to {@code NULL}; if {@code false} (INNER
+ * semantics) no row is emitted. Every element column is therefore nullable
+ * when {@code isOuter}.
*/
public class Uncollect extends SingleRel {
public final boolean withOrdinality;
+ /** If true, an empty or NULL collection yields a single row whose element
+ * columns are all NULL, rather than no rows at all. */
+ public final boolean isOuter;
+
/** If true, a collection whose element type is a struct expands into one
* output column per struct field; if false, it produces a single column
* typed as the whole element. */
@@ -90,7 +100,8 @@ public Uncollect(RelOptCluster cluster, RelTraitSet
traitSet, RelNode input,
// Non-empty item aliases historically implied that struct elements are not
// expanded (Presto dialect), so this constructor derives
// {@code expandStructFields} from their absence.
- this(cluster, traitSet, input, withOrdinality, itemAliases,
itemAliases.isEmpty());
+ this(cluster, traitSet, input, withOrdinality, itemAliases,
itemAliases.isEmpty(),
+ false);
}
/** Creates an Uncollect.
@@ -101,14 +112,18 @@ public Uncollect(RelOptCluster cluster, RelTraitSet
traitSet, RelNode input,
* @param expandStructFields If true, a collection whose element type is a
struct
* produces one output column per struct field; if
false,
* a single column typed as the whole element
+ * @param isOuter If true, an empty or NULL collection yields one
row of
+ * NULLs (LEFT JOIN); if false, it yields no rows
(INNER)
*/
@SuppressWarnings("method.invocation.invalid")
public Uncollect(RelOptCluster cluster, RelTraitSet traitSet, RelNode input,
- boolean withOrdinality, List<String> itemAliases, boolean
expandStructFields) {
+ boolean withOrdinality, List<String> itemAliases, boolean
expandStructFields,
+ boolean isOuter) {
super(cluster, traitSet, input);
this.withOrdinality = withOrdinality;
this.itemAliases = ImmutableList.copyOf(itemAliases);
this.expandStructFields = expandStructFields;
+ this.isOuter = isOuter;
requireNonNull(deriveRowType(), "invalid child rowType");
}
@@ -118,7 +133,8 @@ public Uncollect(RelOptCluster cluster, RelTraitSet
traitSet, RelNode input,
public Uncollect(RelInput input) {
this(input.getCluster(), input.getTraitSet(), input.getInput(),
input.getBoolean("withOrdinality", false), Collections.emptyList(),
- input.getBoolean("expandStructFields", true));
+ input.getBoolean("expandStructFields", true),
+ input.getBoolean("isOuter", false));
}
/**
@@ -151,16 +167,19 @@ public static Uncollect create(
* @param expandStructFields If true, a collection whose element type is a
struct
* produces one output column per struct field; if
false,
* a single column typed as the whole element
+ * @param isOuter If true, an empty or NULL collection yields one
row of
+ * NULLs (LEFT JOIN); if false, it yields no rows
(INNER)
*/
public static Uncollect create(
RelTraitSet traitSet,
RelNode input,
boolean withOrdinality,
List<String> itemAliases,
- boolean expandStructFields) {
+ boolean expandStructFields,
+ boolean isOuter) {
final RelOptCluster cluster = input.getCluster();
return new Uncollect(cluster, traitSet, input, withOrdinality, itemAliases,
- expandStructFields);
+ expandStructFields, isOuter);
}
//~ Methods ----------------------------------------------------------------
@@ -172,7 +191,8 @@ public static Uncollect create(
@Override public RelWriter explainTerms(RelWriter pw) {
return super.explainTerms(pw)
.itemIf("withOrdinality", withOrdinality, withOrdinality)
- .itemIf("expandStructFields", expandStructFields, !expandStructFields);
+ .itemIf("expandStructFields", expandStructFields, !expandStructFields)
+ .itemIf("isOuter", isOuter, isOuter);
}
@Override public final RelNode copy(RelTraitSet traitSet,
@@ -183,7 +203,7 @@ public static Uncollect create(
public RelNode copy(RelTraitSet traitSet, RelNode input) {
assert traitSet.containsIfApplicable(Convention.NONE);
return new Uncollect(getCluster(), traitSet, input, withOrdinality,
itemAliases,
- expandStructFields);
+ expandStructFields, isOuter);
}
/**
@@ -287,7 +307,18 @@ public static RelDataType deriveUncollectRowType(RelNode
rel,
builder.add(SqlUnnestOperator.ORDINALITY_COLUMN_NAME,
SqlTypeName.INTEGER);
}
- return builder.build();
+ final RelDataType rowType = builder.build();
+ if (!isOuter) {
+ return rowType;
+ }
+ // Under isOuter an empty or NULL collection yields a row of NULLs, so
+ // every output column is nullable, including the ordinality column.
+ final RelDataTypeFactory.Builder outerBuilder = typeFactory.builder();
+ for (RelDataTypeField field : rowType.getFieldList()) {
+ outerBuilder.add(field.getName(),
+ typeFactory.createTypeWithNullability(field.getType(), true));
+ }
+ return outerBuilder.build();
}
/** Gets the aliases for the unnest items. */
diff --git
a/core/src/main/java/org/apache/calcite/rel/logical/ToLogicalConverter.java
b/core/src/main/java/org/apache/calcite/rel/logical/ToLogicalConverter.java
index 4ff564f1fd..b15f06b6ea 100644
--- a/core/src/main/java/org/apache/calcite/rel/logical/ToLogicalConverter.java
+++ b/core/src/main/java/org/apache/calcite/rel/logical/ToLogicalConverter.java
@@ -190,7 +190,7 @@ public ToLogicalConverter(RelBuilder relBuilder) {
final RelNode input = visit(uncollect.getInput());
return Uncollect.create(input.getTraitSet(), input,
uncollect.withOrdinality, uncollect.getItemAliases(),
- uncollect.expandStructFields);
+ uncollect.expandStructFields, uncollect.isOuter);
}
throw new AssertionError("Need to implement logical converter for "
diff --git a/core/src/main/java/org/apache/calcite/rel/mutable/MutableRels.java
b/core/src/main/java/org/apache/calcite/rel/mutable/MutableRels.java
index 176be5cfec..092d45c8fe 100644
--- a/core/src/main/java/org/apache/calcite/rel/mutable/MutableRels.java
+++ b/core/src/main/java/org/apache/calcite/rel/mutable/MutableRels.java
@@ -257,7 +257,7 @@ public static RelNode fromMutable(MutableRel node,
RelBuilder relBuilder) {
final MutableUncollect uncollect = (MutableUncollect) node;
final RelNode child = fromMutable(uncollect.getInput(), relBuilder);
return Uncollect.create(child.getTraitSet(), child,
uncollect.withOrdinality,
- Collections.emptyList(), uncollect.expandStructFields);
+ Collections.emptyList(), uncollect.expandStructFields,
uncollect.isOuter);
}
case WINDOW: {
final MutableWindow window = (MutableWindow) node;
@@ -379,7 +379,7 @@ public static MutableRel toMutable(RelNode rel) {
final Uncollect uncollect = (Uncollect) rel;
final MutableRel input = toMutable(uncollect.getInput());
return MutableUncollect.of(uncollect.getRowType(), input,
- uncollect.withOrdinality, uncollect.expandStructFields);
+ uncollect.withOrdinality, uncollect.expandStructFields,
uncollect.isOuter);
}
if (rel instanceof Window) {
final Window window = (Window) rel;
diff --git
a/core/src/main/java/org/apache/calcite/rel/mutable/MutableUncollect.java
b/core/src/main/java/org/apache/calcite/rel/mutable/MutableUncollect.java
index bae3854f69..0dc09b2a00 100644
--- a/core/src/main/java/org/apache/calcite/rel/mutable/MutableUncollect.java
+++ b/core/src/main/java/org/apache/calcite/rel/mutable/MutableUncollect.java
@@ -26,12 +26,15 @@
public class MutableUncollect extends MutableSingleRel {
public final boolean withOrdinality;
public final boolean expandStructFields;
+ public final boolean isOuter;
private MutableUncollect(RelDataType rowType,
- MutableRel input, boolean withOrdinality, boolean expandStructFields) {
+ MutableRel input, boolean withOrdinality, boolean expandStructFields,
+ boolean isOuter) {
super(MutableRelType.UNCOLLECT, rowType, input);
this.withOrdinality = withOrdinality;
this.expandStructFields = expandStructFields;
+ this.isOuter = isOuter;
}
/**
@@ -44,7 +47,7 @@ private MutableUncollect(RelDataType rowType,
*/
public static MutableUncollect of(RelDataType rowType,
MutableRel input, boolean withOrdinality) {
- return of(rowType, input, withOrdinality, true);
+ return of(rowType, input, withOrdinality, true, false);
}
/**
@@ -59,10 +62,25 @@ public static MutableUncollect of(RelDataType rowType,
* struct field; if false, a single column
* typed as the whole element
*/
+ /**
+ * Creates a MutableUncollect.
+ *
+ * @param rowType Row type
+ * @param input Input relational expression
+ * @param withOrdinality Whether the output contains an extra
+ * {@code ORDINALITY} column
+ * @param expandStructFields If true, a collection whose element type
+ * is a struct produces one output column per
+ * struct field; if false, a single column
+ * typed as the whole element
+ * @param isOuter If true, an empty or NULL collection yields one
+ * row of NULLs; if false, it yields no rows
+ */
public static MutableUncollect of(RelDataType rowType,
- MutableRel input, boolean withOrdinality, boolean expandStructFields) {
+ MutableRel input, boolean withOrdinality, boolean expandStructFields,
+ boolean isOuter) {
return new MutableUncollect(rowType, input, withOrdinality,
- expandStructFields);
+ expandStructFields, isOuter);
}
@Override public boolean equals(@Nullable Object obj) {
@@ -70,21 +88,23 @@ public static MutableUncollect of(RelDataType rowType,
|| obj instanceof MutableUncollect
&& withOrdinality == ((MutableUncollect) obj).withOrdinality
&& expandStructFields == ((MutableUncollect) obj).expandStructFields
+ && isOuter == ((MutableUncollect) obj).isOuter
&& input.equals(((MutableUncollect) obj).input);
}
@Override public int hashCode() {
- return Objects.hash(input, withOrdinality, expandStructFields);
+ return Objects.hash(input, withOrdinality, expandStructFields, isOuter);
}
@Override public StringBuilder digest(StringBuilder buf) {
return buf.append("Uncollect(withOrdinality: ").append(withOrdinality)
.append(", expandStructFields: ").append(expandStructFields)
+ .append(", isOuter: ").append(isOuter)
.append(")");
}
@Override public MutableRel clone() {
return MutableUncollect.of(rowType, input.clone(), withOrdinality,
- expandStructFields);
+ expandStructFields, isOuter);
}
}
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java
b/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java
index 98cbeaae21..f827843cc6 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/CoreRules.java
@@ -1008,6 +1008,12 @@ private CoreRules() {}
public static final AggregateRemoveLiteralAggRule
AGGREGATE_REMOVE_LITERAL_AGG =
AggregateRemoveLiteralAggRule.Config.DEFAULT.toRule();
+ /** Rule that moves the outer join semantics of a {@link Correlate} over an
+ * {@link Uncollect} onto the {@code Uncollect}, leaving an inner
+ * {@code Correlate} that {@link #UNNEST_DECORRELATE} may then remove. */
+ public static final CorrelateUncollectOuterRule CORRELATE_UNCOLLECT_OUTER =
+ CorrelateUncollectOuterRule.Config.DEFAULT.toRule();
+
/** Rule that converts a {@link Correlate} after an {@link Uncollect} into a
simple
* Uncollect, if possible. */
public static final RelOptRule UNNEST_DECORRELATE =
diff --git
a/core/src/main/java/org/apache/calcite/rel/rules/CorrelateUncollectOuterRule.java
b/core/src/main/java/org/apache/calcite/rel/rules/CorrelateUncollectOuterRule.java
new file mode 100644
index 0000000000..6cbf781c5f
--- /dev/null
+++
b/core/src/main/java/org/apache/calcite/rel/rules/CorrelateUncollectOuterRule.java
@@ -0,0 +1,105 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.calcite.rel.rules;
+
+import org.apache.calcite.plan.RelOptRuleCall;
+import org.apache.calcite.plan.RelRule;
+import org.apache.calcite.rel.RelNode;
+import org.apache.calcite.rel.core.Correlate;
+import org.apache.calcite.rel.core.JoinRelType;
+import org.apache.calcite.rel.core.Project;
+import org.apache.calcite.rel.core.Uncollect;
+import org.apache.calcite.rel.logical.LogicalValues;
+
+import org.immutables.value.Value;
+
+/**
+ * Rule that moves the outer join semantics of a {@link Correlate} over an
+ * {@link Uncollect} onto the {@code Uncollect} itself.
+ *
+ * <p>Input plan:
+ * <pre>
+ * Correlate(cor=[$cor0], joinType=[left])
+ * left (any RelNode)
+ * Uncollect(isOuter=[any_boolean])
+ * Project($cor0.f, ...)
+ * LogicalValues(tuples=[[{ 0 }]])
+ * </pre>
+ *
+ * <p>Converted to:
+ * <pre>
+ * Correlate(cor=[$cor0], joinType=[inner])
+ * left
+ * Uncollect(isOuter=[true])
+ * Project($cor0.f, ...)
+ * LogicalValues(tuples=[[{ 0 }]])
+ * </pre>
+ *
+ * @see CoreRules#CORRELATE_UNCOLLECT_OUTER
+ */
[email protected]
+public class CorrelateUncollectOuterRule
+ extends RelRule<CorrelateUncollectOuterRule.Config>
+ implements TransformationRule {
+
+ protected CorrelateUncollectOuterRule(Config config) {
+ super(config);
+ }
+
+ @Override public boolean matches(RelOptRuleCall call) {
+ final Correlate correlate = call.rel(0);
+ if (correlate.getJoinType() != JoinRelType.LEFT) {
+ return false;
+ }
+ // Expect "LogicalValues { 0 }"
+ final LogicalValues values = call.rel(4);
+ return values.getTuples().size() == 1;
+ }
+
+ @Override public void onMatch(RelOptRuleCall call) {
+ final Correlate correlate = call.rel(0);
+ final Uncollect uncollect = call.rel(2);
+
+ // Note: this is correct even if uncollect(isOuter=[true]) already
+ final Uncollect outerUncollect =
+ Uncollect.create(uncollect.getTraitSet(), uncollect.getInput(),
+ uncollect.withOrdinality, uncollect.getItemAliases(),
+ uncollect.expandStructFields, true);
+ final RelNode newCorrelate =
+ correlate.copy(correlate.getTraitSet(), correlate.getLeft(),
+ outerUncollect, correlate.getCorrelationId(),
+ correlate.getRequiredColumns(), JoinRelType.INNER);
+ call.transformTo(newCorrelate);
+ }
+
+ /** Rule configuration. */
+ @Value.Immutable
+ public interface Config extends RelRule.Config {
+ Config DEFAULT = ImmutableCorrelateUncollectOuterRule.Config.of()
+ .withOperandSupplier(b0 ->
+ b0.operand(Correlate.class).inputs(
+ b1 -> b1.operand(RelNode.class).anyInputs(),
+ b2 -> b2.operand(Uncollect.class)
+ .oneInput(b3 -> b3.operand(Project.class)
+ .oneInput(b4 -> b4.operand(LogicalValues.class)
+ .anyInputs()))));
+
+ @Override default CorrelateUncollectOuterRule toRule() {
+ return new CorrelateUncollectOuterRule(this);
+ }
+ }
+}
diff --git
a/core/src/main/java/org/apache/calcite/rel/rules/UnnestDecorrelateRule.java
b/core/src/main/java/org/apache/calcite/rel/rules/UnnestDecorrelateRule.java
index 71005b52fd..50dbc5e8f5 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/UnnestDecorrelateRule.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/UnnestDecorrelateRule.java
@@ -22,6 +22,7 @@
import org.apache.calcite.rel.RelNode;
import org.apache.calcite.rel.core.Correlate;
import org.apache.calcite.rel.core.CorrelationId;
+import org.apache.calcite.rel.core.JoinRelType;
import org.apache.calcite.rel.core.Project;
import org.apache.calcite.rel.core.Uncollect;
import org.apache.calcite.rel.logical.LogicalValues;
@@ -44,6 +45,8 @@
/** Convert representations of a projected Unnest that use LogicalCorrelate
into
* simple Unnest representations.
*
+ * @see CorrelateUncollectOuterRule
+ *
* <p>Original plan:
* LogicalProject // only uses rightmost columns of correlate, outerProject
* LogicalCorrelate(correlation=[$cor0], joinType=[inner],
requiredColumns=[{...}])
@@ -97,6 +100,11 @@ private boolean extractFieldReferences(
@Override public void onMatch(RelOptRuleCall call) {
Project outerProject = call.rel(0);
Correlate cor = call.rel(1);
+ if (cor.getJoinType() != JoinRelType.INNER) {
+ // Removing the correlate is only sound for INNER.
+ // A LEFT correlate must first be converted by
CorrelateUncollectOuterRule.
+ return;
+ }
CorrelationId corId = cor.getCorrelationId();
RelNode left = call.rel(2);
@@ -116,6 +124,11 @@ private boolean extractFieldReferences(
Uncollect uncollect = call.rel(uncollectIndex);
Project project = call.rel(uncollectIndex + 1);
+ // Expect "LogicalValues { 0 }"
+ LogicalValues values = call.rel(uncollectIndex + 2);
+ if (values.getTuples().size() != 1) {
+ return;
+ }
List<RexNode> projects = project.getProjects();
if (projects.size() != 1) {
@@ -143,7 +156,8 @@ private boolean extractFieldReferences(
}
}
builder.project(requireNonNull(field, "field"))
- .uncollect(uncollect.getItemAliases(), uncollect.withOrdinality);
+ .uncollect(uncollect.getItemAliases(), uncollect.withOrdinality,
+ uncollect.expandStructFields, uncollect.isOuter);
if (innerProject != null) {
builder.project(innerProject.getProjects());
}
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 d2bd353052..b797264a8b 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -216,6 +216,24 @@ public class SqlFunctions {
a0 -> a0 == null ? Linq4j.emptyEnumerable()
: Linq4j.asEnumerable(a0).<@Nullable
Object>select(SqlFunctions::structValue);
+ /** Single NULL element, the outer-mode result for an empty or NULL
+ * collection. */
+ private static final List<@Nullable Object> SINGLE_NULL =
+ Collections.singletonList(null);
+
+ /** Like {@link #LIST_AS_ENUMERABLE}, but for outer join mode: an empty or
NULL
+ * collection yields one NULL element rather than no elements. */
+ private static final Function1<List<Object>, Enumerable<@Nullable Object>>
+ OUTER_LIST_AS_ENUMERABLE =
+ a0 -> a0 == null || a0.isEmpty() ? Linq4j.asEnumerable(SINGLE_NULL)
+ : Linq4j.asEnumerable(a0);
+
+ /** Like {@link #STRUCT_LIST_AS_ENUMERABLE}, but for outer join mode. */
+ private static final Function1<List<Object>, Enumerable<@Nullable Object>>
+ OUTER_STRUCT_LIST_AS_ENUMERABLE =
+ a0 -> a0 == null || a0.isEmpty() ? Linq4j.asEnumerable(SINGLE_NULL)
+ : Linq4j.asEnumerable(a0).<@Nullable
Object>select(SqlFunctions::structValue);
+
/** Converts one element of a collection of structs to its Object[] struct
* value. Elements arrive as List or as Object[]; null elements stay null. */
@SuppressWarnings("rawtypes")
@@ -7683,8 +7701,22 @@ public static String arrayToString(List list, String
delimiter, @Nullable String
* Function that, given a certain List containing single-item structs (i.e.
arrays / lists with
* a single item), builds an Enumerable that returns those single items
inside the structs.
*/
- public static Function1<List<Object>, Enumerable<Object>> flatList() {
- return inputList -> Linq4j.asEnumerable(inputList).select(v ->
structAccess(v, 0, null));
+ public static Function1<List<Object>, Enumerable<@Nullable Object>>
flatList() {
+ // A NULL collection unnests to no rows, like an empty one.
+ return inputList -> inputList == null ? Linq4j.emptyEnumerable()
+ : Linq4j.asEnumerable(inputList)
+ .<@Nullable Object>select(v -> structAccess(v, 0, null));
+ }
+
+ /**
+ * Variant of {@link #flatList} for outer mode: an empty or {@code NULL}
+ * collection yields one {@code NULL} element rather than no elements.
+ */
+ public static Function1<List<Object>, Enumerable<@Nullable Object>>
flatListOuter() {
+ return inputList -> inputList == null || inputList.isEmpty()
+ ? Linq4j.asEnumerable(SINGLE_NULL)
+ : Linq4j.asEnumerable(inputList)
+ .<@Nullable Object>select(v -> structAccess(v, 0, null));
}
/**
@@ -7695,28 +7727,34 @@ public static Function1<List<Object>,
Enumerable<Object>> flatList() {
* <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}.
+ *
+ * <p>When {@code outer}, a row whose collections are all empty or
+ * {@code NULL} still produces one output row, with every element column set
+ * to {@code NULL}. This is the LEFT JOIN semantics of {@code Uncollect}.
*/
public static Function1<Object, Enumerable<ComparableList<Comparable>>>
flatZip(
final int[] fieldCounts, final boolean withOrdinality,
- final FlatProductInputType[] inputTypes) {
+ final FlatProductInputType[] inputTypes, final boolean outer) {
if (fieldCounts.length == 1) {
if (!withOrdinality && inputTypes[0] == FlatProductInputType.SCALAR) {
// Simple unnest without ordinality
//noinspection unchecked
- return (Function1) LIST_AS_ENUMERABLE;
+ return outer ? (Function1) OUTER_LIST_AS_ENUMERABLE
+ : (Function1) LIST_AS_ENUMERABLE;
} else if (!withOrdinality && inputTypes[0] ==
FlatProductInputType.STRUCT) {
// A single collection of structs kept whole, without ordinality: the
// output row type has a single (ROW-typed) column, so PhysTypeImpl
// optimizes the row format down to SCALAR, under which rows are bare
// struct values rather than singleton lists.
//noinspection unchecked
- return (Function1) STRUCT_LIST_AS_ENUMERABLE;
+ return (Function1) (outer ? OUTER_STRUCT_LIST_AS_ENUMERABLE
+ : STRUCT_LIST_AS_ENUMERABLE);
} else {
// unnest with ordinality for a single column
- return row -> z2(new Object[] { row }, fieldCounts, withOrdinality,
inputTypes);
+ return row -> z2(new Object[] { row }, fieldCounts, withOrdinality,
inputTypes, outer);
}
}
- return lists -> z2((Object[]) lists, fieldCounts, withOrdinality,
inputTypes);
+ return lists -> z2((Object[]) lists, fieldCounts, withOrdinality,
inputTypes, outer);
}
/**
@@ -7729,11 +7767,13 @@ public static Function1<Object,
Enumerable<ComparableList<Comparable>>> flatZip(
* of scalars or of structs kept whole)
* @param withOrdinality whether to append a 1-based ordinality column
* @param inputTypes type of elements in each collection (SCALAR, LIST,
STRUCT, or MAP)
+ * @param outer whether to emit one all-NULL row when every
collection is
+ * empty or NULL, rather than no rows
*/
@SuppressWarnings("rawtypes")
private static Enumerable<FlatLists.ComparableList<Comparable>> z2(
Object[] lists, int[] fieldCounts, boolean withOrdinality,
- FlatProductInputType[] inputTypes) {
+ FlatProductInputType[] inputTypes, boolean outer) {
final List<Enumerator<List<Comparable>>> enumerators = new ArrayList<>();
final int[] widths = new int[lists.length];
int totalFieldCount = 0;
@@ -7741,6 +7781,15 @@ private static
Enumerable<FlatLists.ComparableList<Comparable>> z2(
final int fieldCount = fieldCounts[i];
final FlatProductInputType inputType = inputTypes[i];
final Object inputObject = lists[i];
+ if (inputObject == null) {
+ // A NULL collection contributes no elements, like an empty one. Under
+ // outer mode the wrapper below turns "no elements at all" into the
+ // single NULL-padded row.
+ enumerators.add(Linq4j.emptyEnumerator());
+ widths[i] = fieldCount < 0 ? 1 : fieldCount;
+ totalFieldCount += widths[i];
+ continue;
+ }
switch (inputType) {
case SCALAR:
@SuppressWarnings("unchecked") List<Comparable> list =
@@ -7782,13 +7831,77 @@ private static
Enumerable<FlatLists.ComparableList<Comparable>> z2(
++totalFieldCount;
}
final int fieldCount = totalFieldCount;
+ if (!outer) {
+ return new AbstractEnumerable<FlatLists.ComparableList<Comparable>>() {
+ @Override public Enumerator<FlatLists.ComparableList<Comparable>>
enumerator() {
+ return new ZipPaddedEnumerator(enumerators, widths, fieldCount,
withOrdinality);
+ }
+ };
+ }
+ @SuppressWarnings("unchecked") final FlatLists.ComparableList<Comparable>
nullRow =
+ (FlatLists.ComparableList)
FlatLists.of(Collections.nCopies(fieldCount, null));
return new AbstractEnumerable<FlatLists.ComparableList<Comparable>>() {
@Override public Enumerator<FlatLists.ComparableList<Comparable>>
enumerator() {
- return new ZipPaddedEnumerator(enumerators, widths, fieldCount,
withOrdinality);
+ return new DefaultIfEmptyEnumerator(
+ new ZipPaddedEnumerator(enumerators, widths, fieldCount,
withOrdinality),
+ nullRow);
}
};
}
+ /** Enumerator that yields the rows of another enumerator, or a single
+ * default row if that enumerator yields none.
+ *
+ * <p>This is the {@code defaultIfEmpty} operation of LINQ. It implements
+ * the outer (LEFT JOIN) semantics of {@code Uncollect}, where the default
+ * row is all NULL. */
+ @SuppressWarnings("rawtypes")
+ private static class DefaultIfEmptyEnumerator
+ implements Enumerator<FlatLists.ComparableList<Comparable>> {
+ private final Enumerator<FlatLists.ComparableList<Comparable>> inner;
+ private final FlatLists.ComparableList<Comparable> defaultRow;
+ /** Whether {@link #inner} has yielded at least one row. */
+ private boolean innerMoved;
+ /** Whether {@link #defaultRow} is the row currently being returned. */
+ private boolean onDefaultRow;
+
+ DefaultIfEmptyEnumerator(
+ Enumerator<FlatLists.ComparableList<Comparable>> inner,
+ FlatLists.ComparableList<Comparable> defaultRow) {
+ this.inner = inner;
+ this.defaultRow = defaultRow;
+ }
+
+ @Override public boolean moveNext() {
+ if (onDefaultRow) {
+ return false;
+ }
+ if (inner.moveNext()) {
+ innerMoved = true;
+ return true;
+ }
+ if (innerMoved) {
+ return false;
+ }
+ onDefaultRow = true;
+ return true;
+ }
+
+ @Override public FlatLists.ComparableList<Comparable> current() {
+ return onDefaultRow ? defaultRow : inner.current();
+ }
+
+ @Override public void reset() {
+ inner.reset();
+ innerMoved = false;
+ onDefaultRow = false;
+ }
+
+ @Override public void close() {
+ inner.close();
+ }
+ }
+
public static Object[] array(Object... args) {
return args;
}
diff --git
a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
index 5c524eee17..357441d316 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -2899,7 +2899,7 @@ private void convertUnnest(Blackboard bb, SqlCall call,
@Nullable List<String> f
uncollect = relBuilder
.push(child)
.project(exprs)
- .uncollect(itemAliases, operator.withOrdinality)
+ .uncollect(itemAliases, operator.withOrdinality,
itemAliases.isEmpty(), false)
.let(r -> fieldNames == null ? r : r.rename(fieldNames))
.build();
} else {
@@ -2908,7 +2908,7 @@ private void convertUnnest(Blackboard bb, SqlCall call,
@Nullable List<String> f
uncollect = relBuilder
.push(child)
.project(exprs)
- .uncollect(Collections.emptyList(), operator.withOrdinality)
+ .uncollect(Collections.emptyList(), operator.withOrdinality, true,
false)
.let(r -> fieldNames == null ? r : r.rename(fieldNames))
.build();
}
diff --git a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
index 92c5a41415..7cc7437c62 100644
--- a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
+++ b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
@@ -2383,8 +2383,34 @@ public RelBuilder projectNamed(Iterable<? extends
RexNode> nodes,
* @param itemAliases Operand item aliases, never null
* @param withOrdinality If {@code withOrdinality}, the output contains an
extra
* {@code ORDINALITY} column
+ *
+ * @deprecated Use
+ * {@link #uncollect(List, boolean, boolean, boolean)}, which controls every
+ * flag explicitly. This overload derives {@code expandStructFields} from the
+ * item aliases, which cannot express a collection of structs that is kept
+ * whole without aliases, and it cannot create an outer {@code Uncollect}.
*/
+ @Deprecated // to be removed before 2.0
public RelBuilder uncollect(List<String> itemAliases, boolean
withOrdinality) {
+ return uncollect(itemAliases, withOrdinality,
+ requireNonNull(itemAliases, "itemAliases").isEmpty(), false);
+ }
+
+ /**
+ * Creates an {@link Uncollect} with given item aliases, with explicit
control
+ * over every flag.
+ *
+ * @param itemAliases Operand item aliases, never null
+ * @param withOrdinality If {@code withOrdinality}, the output contains an
extra
+ * {@code ORDINALITY} column
+ * @param expandStructFields If true, a collection whose element type is a
struct
+ * produces one output column per struct field; if false, a single column
typed
+ * as the whole element
+ * @param isOuter If {@code isOuter}, an empty or NULL collection yields one
row
+ * of NULLs (LEFT JOIN); otherwise it yields no rows (INNER)
+ */
+ public RelBuilder uncollect(List<String> itemAliases, boolean withOrdinality,
+ boolean expandStructFields, boolean isOuter) {
Frame frame = stack.pop();
stack.push(
new Frame(
@@ -2393,7 +2419,9 @@ public RelBuilder uncollect(List<String> itemAliases,
boolean withOrdinality) {
cluster.traitSetOf(Convention.NONE),
frame.rel,
withOrdinality,
- requireNonNull(itemAliases, "itemAliases"))));
+ requireNonNull(itemAliases, "itemAliases"),
+ expandStructFields,
+ isOuter)));
return this;
}
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 8e1e190548..bbce7d8de4 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -334,8 +334,9 @@ public enum BuiltInMethod {
PAIR_LIST_COPY_OF(PairList.Helper.class, "copyOf", Object.class,
Object.class,
Object[].class),
FLAT_ZIP(SqlFunctions.class, "flatZip", int[].class, boolean.class,
- FlatProductInputType[].class),
+ FlatProductInputType[].class, boolean.class),
FLAT_LIST(SqlFunctions.class, "flatList"),
+ FLAT_LIST_OUTER(SqlFunctions.class, "flatListOuter"),
LIST_N(FlatLists.class, "copyOf", Comparable[].class),
LIST1(FlatLists.class, "ofSingle", Object.class),
LIST2(FlatLists.class, "of", Object.class, Object.class),
diff --git
a/core/src/test/java/org/apache/calcite/sql2rel/RelFieldTrimmerTest.java
b/core/src/test/java/org/apache/calcite/sql2rel/RelFieldTrimmerTest.java
index 86623ccb0a..535dc07b16 100644
--- a/core/src/test/java/org/apache/calcite/sql2rel/RelFieldTrimmerTest.java
+++ b/core/src/test/java/org/apache/calcite/sql2rel/RelFieldTrimmerTest.java
@@ -597,7 +597,7 @@ public static Frameworks.ConfigBuilder config() {
.project(
builder.call(SqlStdOperatorTable.ARRAY_VALUE_CONSTRUCTOR,
builder.field(v.get(), "DEPTNO"), builder.field(v.get(),
"DEPTNO")))
- .uncollect(Collections.emptyList(), false)
+ .uncollect(Collections.emptyList(), false, true, false)
.correlate(JoinRelType.LEFT, v.get().id, builder.field(2, 0, "DEPTNO"))
.aggregate(builder.groupKey("ENAME"),
builder.max(builder.field("EMPNO")))
.build();
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 508008deb0..08d684f031 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
@@ -2175,7 +2175,7 @@ private static List<List<Object>> zipScalars(
new SqlFunctions.FlatProductInputType[n];
Arrays.fill(types, SCALAR);
final Function1<Object, Enumerable<FlatLists.ComparableList<Comparable>>>
fn =
- SqlFunctions.flatZip(fieldCounts, withOrdinality, types);
+ SqlFunctions.flatZip(fieldCounts, withOrdinality, types, false);
final Object arg = n == 1 ? inputs[0] : inputs;
final List<List<Object>> rows = new ArrayList<>();
for (FlatLists.ComparableList<Comparable> row : fn.apply(arg)) {
@@ -2249,7 +2249,7 @@ private static List<List<Object>> zipScalars(
@SuppressWarnings({"rawtypes", "unchecked"})
final Function1<Object, Enumerable<FlatLists.ComparableList<Comparable>>>
fn =
SqlFunctions.flatZip(new int[]{2, 2}, false,
- new SqlFunctions.FlatProductInputType[]{LIST, LIST});
+ new SqlFunctions.FlatProductInputType[]{LIST, LIST}, false);
final List<List<Comparable>> col1 =
Arrays.asList(FlatLists.of(1, 2), FlatLists.of(3, 4));
final List<List<Comparable>> col2 =
@@ -2280,7 +2280,7 @@ private static List<List<Comparable>> rowArray() {
SqlFunctions.flatZip(
new int[]{-1, -1}, // one output column per collection
false, // no ordinality
- new SqlFunctions.FlatProductInputType[]{STRUCT, SCALAR});
+ new SqlFunctions.FlatProductInputType[]{STRUCT, SCALAR}, false);
final List<List<Object>> rows = new ArrayList<>();
for (FlatLists.ComparableList<Comparable> row
@@ -2304,7 +2304,7 @@ private static List<List<Comparable>> rowArray() {
SqlFunctions.flatZip(
new int[]{2, -1}, // two columns from the struct, one scalar column
false, // no ordinality
- new SqlFunctions.FlatProductInputType[]{LIST, SCALAR});
+ new SqlFunctions.FlatProductInputType[]{LIST, SCALAR}, false);
final List<List<Object>> rows = new ArrayList<>();
for (FlatLists.ComparableList<Comparable> row
@@ -2329,7 +2329,7 @@ private static List<List<Comparable>> rowArray() {
final Function1<Object, Enumerable<FlatLists.ComparableList<Comparable>>>
fn =
SqlFunctions.flatZip(
new int[]{-1}, false,
- new SqlFunctions.FlatProductInputType[]{STRUCT});
+ new SqlFunctions.FlatProductInputType[]{STRUCT}, false);
final List<Object> rows = new ArrayList<>();
for (Object row : (Enumerable) fn.apply(rowArray())) {
@@ -2344,4 +2344,118 @@ private static List<List<Comparable>> rowArray() {
// UNNEST of a null array yields no rows.
assertThat(((Enumerable) fn.apply(null)).any(), is(false));
}
+
+ // Tests for the outer mode of flatZip (Uncollect.isOuter): an empty or
+ // NULL collection produces one all-NULL output row instead of none.
+
+ @SuppressWarnings({"rawtypes", "unchecked"})
+ @Test void testFlatZipOuterScalar() {
+ // Models SELECT u.x FROM t LEFT JOIN UNNEST(t.arr) AS u(x) ON TRUE
+ final Function1<Object, Enumerable<FlatLists.ComparableList<Comparable>>>
fn =
+ SqlFunctions.flatZip(new int[]{-1}, false,
+ new SqlFunctions.FlatProductInputType[]{SCALAR}, true);
+
+ // arr = [1, 2]
+ assertThat(((Enumerable) fn.apply(Arrays.asList(1, 2))).toList(),
+ is(Arrays.asList(1, 2)));
+ // arr = []
+ assertThat(((Enumerable) fn.apply(Collections.emptyList())).toList(),
+ is(Collections.singletonList(null)));
+ // arr = NULL
+ assertThat(((Enumerable) fn.apply(null)).toList(),
+ is(Collections.singletonList(null)));
+ }
+
+ @SuppressWarnings({"rawtypes", "unchecked"})
+ @Test void testFlatZipOuterWholeStruct() {
+ // Models, under PRESTO conformance (struct elements kept whole),
+ // SELECT u.s FROM t LEFT JOIN UNNEST(t.arr) AS u(s) ON TRUE.
+ final Function1<Object, Enumerable<FlatLists.ComparableList<Comparable>>>
fn =
+ SqlFunctions.flatZip(new int[]{-1}, false,
+ new SqlFunctions.FlatProductInputType[]{STRUCT}, true);
+
+ // arr = [ROW(1, 'x'), ROW(2, 'y')]
+ final List<Object> rows = ((Enumerable) fn.apply(rowArray())).toList();
+ assertThat(rows, hasSize(2));
+ assertArrayEquals(new Object[]{1, "x"}, (Object[]) rows.get(0));
+ // arr = []
+ assertThat(((Enumerable) fn.apply(Collections.emptyList())).toList(),
+ is(Collections.singletonList(null)));
+ // arr = NULL
+ assertThat(((Enumerable) fn.apply(null)).toList(),
+ is(Collections.singletonList(null)));
+ }
+
+ @SuppressWarnings({"rawtypes", "unchecked"})
+ @Test void testFlatZipOuterWithOrdinality() {
+ // Models SELECT u.x, u.o
+ // FROM t LEFT JOIN UNNEST(t.arr) WITH ORDINALITY AS u(x, o) ON TRUE
+ final Function1<Object, Enumerable<FlatLists.ComparableList<Comparable>>>
fn =
+ SqlFunctions.flatZip(new int[]{-1}, true,
+ new SqlFunctions.FlatProductInputType[]{SCALAR}, true);
+
+ // arr = [7, 8]
+ final List<List<Object>> rows = new ArrayList<>();
+ for (FlatLists.ComparableList<Comparable> row
+ : fn.apply(Arrays.asList(7, 8))) {
+ rows.add(new ArrayList<>(row));
+ }
+ assertThat(rows,
+ is(Arrays.asList(Arrays.asList(7, 1), Arrays.asList(8, 2))));
+
+ // arr = []; the padded row has a NULL ordinal
+ rows.clear();
+ for (FlatLists.ComparableList<Comparable> row
+ : fn.apply(Collections.emptyList())) {
+ rows.add(new ArrayList<>(row));
+ }
+ assertThat(rows, is(Collections.singletonList(Arrays.asList(null, null))));
+
+ // arr = NULL
+ rows.clear();
+ for (FlatLists.ComparableList<Comparable> row : fn.apply(null)) {
+ rows.add(new ArrayList<>(row));
+ }
+ assertThat(rows, is(Collections.singletonList(Arrays.asList(null, null))));
+ }
+
+ @SuppressWarnings({"rawtypes", "unchecked"})
+ @Test void testFlatZipOuterMultipleCollections() {
+ // Models SELECT u.x, u.y FROM t LEFT JOIN UNNEST(t.a, t.b) AS u(x, y) ON
TRUE
+ final Function1<Object, Enumerable<FlatLists.ComparableList<Comparable>>>
fn =
+ SqlFunctions.flatZip(new int[]{-1, -1}, false,
+ new SqlFunctions.FlatProductInputType[]{SCALAR, SCALAR}, true);
+
+ // (a, b) = ([], [7]): zip pads a
+ final List<List<Object>> rows = new ArrayList<>();
+ for (FlatLists.ComparableList<Comparable> row
+ : fn.apply(new Object[]{Collections.emptyList(), Arrays.asList(7)})) {
+ rows.add(new ArrayList<>(row));
+ }
+ assertThat(rows, is(Collections.singletonList(Arrays.asList(null, 7))));
+
+ // (a, b) = ([], NULL): one all-NULL row
+ rows.clear();
+ for (FlatLists.ComparableList<Comparable> row
+ : fn.apply(new Object[]{Collections.emptyList(), null})) {
+ rows.add(new ArrayList<>(row));
+ }
+ assertThat(rows, is(Collections.singletonList(Arrays.asList(null, null))));
+ }
+
+ @Test void testFlatListOuter() {
+ // Models SELECT u.x FROM t LEFT JOIN UNNEST(t.arr) AS u(x) ON TRUE
+ // for arr ROW(a INTEGER) ARRAY
+ final Function1<List<Object>, Enumerable<Object>> fn =
+ (Function1) SqlFunctions.flatListOuter();
+ // arr = [ROW(1), ROW(2)]
+ assertThat(fn.apply(Arrays.asList(FlatLists.of(1),
FlatLists.of(2))).toList(),
+ is(Arrays.asList(1, 2)));
+ // arr = []
+ assertThat(fn.apply(Collections.emptyList()).toList(),
+ is(Collections.singletonList(null)));
+ // arr = NULL
+ assertThat(fn.apply(null).toList(),
+ is(Collections.singletonList(null)));
+ }
}
diff --git a/core/src/test/resources/sql/unnest.iq
b/core/src/test/resources/sql/unnest.iq
index 054234fcbe..c4650d77d3 100644
--- a/core/src/test/resources/sql/unnest.iq
+++ b/core/src/test/resources/sql/unnest.iq
@@ -815,4 +815,320 @@ SELECT * FROM UNNEST(ARRAY[
!ok
+# Tests for [CALCITE-7670] Uncollect should support LEFT JOIN UNNEST
+!use scott
+
+# LEFT JOIN UNNEST
+# Validated on PostgreSQL 14: same result.
+WITH t(id, arr) AS (
+ SELECT 1, ARRAY[10, 20] FROM (VALUES (0)) AS z(k)
+ UNION ALL
+ SELECT 2, ARRAY(SELECT 1 FROM (VALUES (0)) AS z(k) WHERE FALSE)
+ FROM (VALUES (0)) AS z(k)
+ UNION ALL
+ SELECT 3, CAST(NULL AS INTEGER ARRAY) FROM (VALUES (0)) AS z(k))
+SELECT t.id, u.x
+FROM t LEFT JOIN UNNEST(t.arr) AS u(x) ON TRUE
+ORDER BY t.id, u.x;
++----+----+
+| ID | X |
++----+----+
+| 1 | 10 |
+| 1 | 20 |
+| 2 | |
+| 3 | |
++----+----+
+(4 rows)
+
+!ok
+
+# An inner UNNEST still drops the rows.
+# Validated on PostgreSQL 14: same result.
+WITH t(id, arr) AS (
+ SELECT 1, ARRAY[10, 20] FROM (VALUES (0)) AS z(k)
+ UNION ALL
+ SELECT 3, CAST(NULL AS INTEGER ARRAY) FROM (VALUES (0)) AS z(k))
+SELECT t.id, u.x
+FROM t, UNNEST(t.arr) AS u(x)
+ORDER BY t.id, u.x;
++----+----+
+| ID | X |
++----+----+
+| 1 | 10 |
+| 1 | 20 |
++----+----+
+(2 rows)
+
+!ok
+
+# WITH ORDINALITY: the NULL row from OUTER JOIN has no ordinal.
+# Validated on PostgreSQL 14: same result.
+WITH t(id, arr) AS (
+ SELECT 1, ARRAY[10, 20] FROM (VALUES (0)) AS z(k)
+ UNION ALL
+ SELECT 3, CAST(NULL AS INTEGER ARRAY) FROM (VALUES (0)) AS z(k))
+SELECT t.id, u.x, u.o
+FROM t LEFT JOIN UNNEST(t.arr) WITH ORDINALITY AS u(x, o) ON TRUE
+ORDER BY t.id, u.x;
++----+----+---+
+| ID | X | O |
++----+----+---+
+| 1 | 10 | 1 |
+| 1 | 20 | 2 |
+| 3 | | |
++----+----+---+
+(3 rows)
+
+!ok
+
+# A filtering ON condition: the left row survives even when the condition
+# rejects every element of a non-empty collection. The padding must come
+# from the join, not from the collection being empty.
+# Validated on PostgreSQL 14: same result.
+WITH t(id, arr) AS (
+ SELECT 1, ARRAY[10] FROM (VALUES (0)) AS z(k)
+ UNION ALL
+ SELECT 2, ARRAY[20, 30] FROM (VALUES (0)) AS z(k))
+SELECT t.id, u.x
+FROM t LEFT JOIN UNNEST(t.arr) AS u(x) ON u.x > 15
+ORDER BY t.id, u.x;
++----+----+
+| ID | X |
++----+----+
+| 1 | |
+| 2 | 20 |
+| 2 | 30 |
++----+----+
+(3 rows)
+
+!ok
+
+# A NATURAL LEFT JOIN's derived condition compares common columns, so it can
+# reject rows. Here t.x = 1 matches no element, and the left row survives.
+# The condition becomes a Filter between the Correlate and the Uncollect,
+# which is what stops CorrelateUncollectOuterRule from matching this shape.
+# Validated on PostgreSQL 14: same result.
+SELECT *
+FROM (VALUES (1)) AS t(x)
+NATURAL LEFT JOIN UNNEST(ARRAY[2, 3]) AS u(x);
++---+
+| X |
++---+
+| 1 |
++---+
+(1 row)
+
+!ok
+
+# Multi-collection UNNEST and LEFT JOIN
+# Validated on PostgreSQL 14: same result.
+WITH t(id, a, b) AS (
+ SELECT 1, ARRAY[10, 20], ARRAY['p'] FROM (VALUES (0)) AS z(k)
+ UNION ALL
+ SELECT 3, CAST(NULL AS INTEGER ARRAY), CAST(NULL AS VARCHAR ARRAY)
+ FROM (VALUES (0)) AS z(k))
+SELECT t.id, u.x, u.y
+FROM t LEFT JOIN UNNEST(t.a, t.b) AS u(x, y) ON TRUE
+ORDER BY t.id, u.x;
++----+----+---+
+| ID | X | Y |
++----+----+---+
+| 1 | 10 | p |
+| 1 | 20 | |
+| 3 | | |
++----+----+---+
+(3 rows)
+
+!ok
+
+# MAP collection and LEFT JOIN
+# Postgres does not support MAP values, so this is not validated on Postgres.
+WITH t(id, m) AS (
+ SELECT 1, MAP['a', 10, 'b', 20] FROM (VALUES (0)) AS z(k)
+ UNION ALL
+ SELECT 3, CAST(NULL AS MAP<VARCHAR, INTEGER>) FROM (VALUES (0)) AS z(k))
+SELECT t.id, u.k, u.v
+FROM t LEFT JOIN UNNEST(t.m) AS u(k, v) ON TRUE
+ORDER BY t.id, u.k;
++----+---+----+
+| ID | K | V |
++----+---+----+
+| 1 | a | 10 |
+| 1 | b | 20 |
+| 3 | | |
++----+---+----+
+(3 rows)
+
+!ok
+
+# An array of single-field ROW values expands to a single scalar column.
+# Validated on PostgreSQL 14 using a named composite type (Postgres cannot
+# type an anonymous ROW array): same result.
+WITH t(id, arr) AS (
+ SELECT 1, ARRAY[ROW(10), ROW(20)] FROM (VALUES (0)) AS z(k)
+ UNION ALL
+ SELECT 3, CAST(NULL AS ROW(a INTEGER) ARRAY) FROM (VALUES (0)) AS z(k))
+SELECT t.id, u.x
+FROM t LEFT JOIN UNNEST(t.arr) AS u(x) ON TRUE
+ORDER BY t.id, u.x;
++----+----+
+| ID | X |
++----+----+
+| 1 | 10 |
+| 1 | 20 |
+| 3 | |
++----+----+
+(3 rows)
+
+!ok
+
+# With LEFT JOIN every element column and the ordinality column must be
nullable
+WITH t(id, arr) AS (
+ SELECT 1, ARRAY[10, 20] FROM (VALUES (0)) AS z(k))
+SELECT t.id, u.x, u.o
+FROM t LEFT JOIN UNNEST(t.arr) WITH ORDINALITY AS u(x, o) ON TRUE;
+ID INTEGER(10) NOT NULL
+X INTEGER(10)
+O INTEGER(10)
+!type
+
+!use hr
+
+# Struct elements, expanded into one column per field (standard semantics):
+# dept 30 has no employees.
+# validated on PostgreSQL 14 with an equivalent composite type.
+SELECT d."deptno", e."empid", e."name"
+FROM "hr"."depts" AS d
+LEFT JOIN UNNEST(d."employees") AS e ON TRUE
+ORDER BY d."deptno", e."empid";
++--------+-------+-----------+
+| deptno | empid | name |
++--------+-------+-----------+
+| 10 | 100 | Bill |
+| 10 | 150 | Sebastian |
+| 30 | | |
+| 40 | 200 | Eric |
++--------+-------+-----------+
+(4 rows)
+
+!ok
+
+!use hr-presto
+
+# Struct elements kept whole (Trino semantics, PRESTO conformance).
+SELECT d."deptno", e."emp"
+FROM "hr"."depts" AS d
+LEFT JOIN UNNEST(d."employees") AS e("emp") ON TRUE
+ORDER BY d."deptno";
++--------+------------------------------------+
+| deptno | emp |
++--------+------------------------------------+
+| 10 | {100, 10, Bill, 10000.0, 1000} |
+| 10 | {150, 10, Sebastian, 7000.0, null} |
+| 30 | |
+| 40 | {200, 20, Eric, 8000.0, 500} |
++--------+------------------------------------+
+(4 rows)
+
+!ok
+
+# The same query with CorrelateUncollectOuterRule
+!set hep-rules "
++CoreRules.CORRELATE_UNCOLLECT_OUTER"
+SELECT d."deptno", e."emp"
+FROM "hr"."depts" AS d
+LEFT JOIN UNNEST(d."employees") AS e("emp") ON TRUE
+ORDER BY d."deptno";
++--------+------------------------------------+
+| deptno | emp |
++--------+------------------------------------+
+| 10 | {100, 10, Bill, 10000.0, 1000} |
+| 10 | {150, 10, Sebastian, 7000.0, null} |
+| 30 | |
+| 40 | {200, 20, Eric, 8000.0, 500} |
++--------+------------------------------------+
+(4 rows)
+
+!ok
+
+!use scott
+
+# CorrelateUncollectOuterRule converts the LEFT correlate over an Uncollect
+# into an INNER correlate over an outer Uncollect; UnnestDecorrelateRule
+# then eliminates the correlate entirely, padding included.
+!set hep-rules "
++CoreRules.CORRELATE_UNCOLLECT_OUTER
++CoreRules.UNNEST_DECORRELATE"
+
+# Validated on PostgreSQL 14: same result.
+WITH t(id, arr) AS (
+ SELECT 1, ARRAY[10, 20] FROM (VALUES (0)) AS z(k)
+ UNION ALL
+ SELECT 3, CAST(NULL AS INTEGER ARRAY) FROM (VALUES (0)) AS z(k))
+SELECT u.x
+FROM t LEFT JOIN UNNEST(t.arr) AS u(x) ON TRUE
+ORDER BY u.x;
++----+
+| X |
++----+
+| 10 |
+| 20 |
+| |
++----+
+(3 rows)
+
+!ok
+
+# The correlate is gone
+EnumerableSort(sort0=[$0], dir0=[ASC])
+ EnumerableUncollect(isOuter=[true])
+ EnumerableUnion(all=[true])
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[10], expr#2=[20],
expr#3=[ARRAY($t1, $t2)], EXPR$1=[$t3])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0..1=[{inputs}], EXPR$1=[$t1])
+ EnumerableValues(tuples=[[{ 3, null }]])
+!plan
+
+# The same elimination for an array of single-field ROW values.
+WITH t(id, arr) AS (
+ SELECT 1, ARRAY[ROW(10), ROW(20)] FROM (VALUES (0)) AS z(k)
+ UNION ALL
+ SELECT 3, CAST(NULL AS ROW(a INTEGER) ARRAY) FROM (VALUES (0)) AS z(k))
+SELECT u.x
+FROM t LEFT JOIN UNNEST(t.arr) AS u(x) ON TRUE
+ORDER BY u.x;
++----+
+| X |
++----+
+| 10 |
+| 20 |
+| |
++----+
+(3 rows)
+
+!ok
+
+# UnnestDecorrelateRule alone must not fire on a LEFT correlate
+!set hep-rules "
++CoreRules.UNNEST_DECORRELATE"
+
+# Validated on PostgreSQL 14: same result.
+WITH t(id, arr) AS (
+ SELECT 1, ARRAY[10, 20] FROM (VALUES (0)) AS z(k)
+ UNION ALL
+ SELECT 3, CAST(NULL AS INTEGER ARRAY) FROM (VALUES (0)) AS z(k))
+SELECT u.x
+FROM t LEFT JOIN UNNEST(t.arr) AS u(x) ON TRUE
+ORDER BY u.x;
++----+
+| X |
++----+
+| 10 |
+| 20 |
+| |
++----+
+(3 rows)
+
+!ok
+
# End unnest.iq
