This is an automated email from the ASF dual-hosted git repository.
morrySnow pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 9c9447fc969 [opt](infer predicate) Infer IN predicate from OR when
slot is shared by sibling conjuncts (#69921)
9c9447fc969 is described below
commit 9c9447fc96922cc51d5a8f9260ab0f3a9704fb88
Author: Lijia Liu <[email protected]>
AuthorDate: Fri Nov 6 00:05:03 2026 +0800
[opt](infer predicate) Infer IN predicate from OR when slot is shared by
sibling conjuncts (#69921)
OrToIn extract mode dropped "slot = c" / "slot IN (...)" candidates
whose slot is also referenced by sibling conjuncts, e.g. an IN inferred
from a kept inner OR. So "spm IN (...)" could not be inferred from
nested multi-column ORs, and parquet lazy materialization decoded large
JSON columns on nearly all rows.
Skip the independence check for plain-slot candidates in extract mode
only. The inferred IN is implied by the OR and the OR is kept, so
results are unchanged. Add FE config
enable_or_to_in_shared_slot_inference (default true, mutable) as a
rollback switch covering only this change.
---------
Co-authored-by: 刘立家 <[email protected]>
Co-authored-by: liutang123 <[email protected]>
Co-authored-by: Claude Code <[email protected]>
---
.../nereids/rules/expression/rules/OrToIn.java | 151 ++++++++++++++---
.../doris/nereids/rules/rewrite/OrToInTest.java | 180 ++++++++++++++++++++-
.../or_to_in/or_to_in_complex_type.out | 13 ++
.../tpcds_sf100/noStatsRfPrune/query41.out | 2 +-
.../tpcds_sf100/no_stats_shape/query41.out | 2 +-
.../shape_check/tpcds_sf100/rf_prune/query41.out | 2 +-
.../data/shape_check/tpcds_sf100/shape/query41.out | 2 +-
.../shape_check/tpcds_sf1000/dphyper/query41.out | 2 +-
.../data/shape_check/tpcds_sf1000/hint/query41.out | 2 +-
.../shape_check/tpcds_sf1000/shape/query41.out | 2 +-
.../tpcds_sf1000_constraints/shape/query41.out | 2 +-
.../tpcds_sf1000_nopkfk/shape/query41.out | 2 +-
.../shape_check/tpcds_sf10t_orc/shape/query41.out | 2 +-
.../or_to_in/or_to_in_complex_type.groovy | 98 +++++++++++
14 files changed, 428 insertions(+), 34 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/OrToIn.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/OrToIn.java
index f356834fc26..adc55d58d5c 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/OrToIn.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/OrToIn.java
@@ -18,6 +18,7 @@
package org.apache.doris.nereids.rules.expression.rules;
import org.apache.doris.common.Pair;
+import org.apache.doris.nereids.analyzer.Unbound;
import org.apache.doris.nereids.rules.expression.ExpressionRewrite;
import org.apache.doris.nereids.rules.expression.ExpressionRewriteContext;
import org.apache.doris.nereids.rules.expression.ExpressionRewriteRule;
@@ -29,6 +30,7 @@ import org.apache.doris.nereids.trees.expressions.InPredicate;
import org.apache.doris.nereids.trees.expressions.Or;
import org.apache.doris.nereids.trees.expressions.Slot;
import org.apache.doris.nereids.trees.expressions.literal.Literal;
+import org.apache.doris.nereids.types.DataType;
import org.apache.doris.nereids.util.ExpressionUtils;
import com.google.common.collect.ImmutableList;
@@ -36,6 +38,7 @@ import com.google.common.collect.ImmutableList.Builder;
import com.google.common.collect.Lists;
import com.google.common.collect.Sets;
+import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.LinkedHashSet;
import java.util.List;
@@ -59,6 +62,11 @@ public class OrToIn {
* case 2: from (a=1) or (a=2), "a in (1,2)" is inferred, the inferred
expr is equivalent to the original expr
* - replaceMode/extractMode: output a in (1, 2)
*
+ * case 3 (extractMode only): the candidate slot is also referenced by
sibling conjuncts of the same disjunct,
+ * e.g. "(a=1 and b=1) or (a in (2, 3) and ((a=2 and c=1) or (a=3 and
c=2)))".
+ * - replaceMode: output origin expr
+ * - extractMode: output a in (1, 2, 3) and (origin expr)
+ *
* extractMode only used for filter, the inferred In-predicate could be
pushed down.
*/
public enum Mode {
@@ -241,12 +249,128 @@ public class OrToIn {
return true;
}
+ /**
+ * Whether conjuncts.get(idx) may contribute an "X = literal" / "X IN
(literals)" candidate.
+ *
+ * Replace mode may substitute the inferred In-predicate for the original
OR, so it keeps the
+ * historical requirement that the candidate does not share input slots
with sibling conjuncts.
+ *
+ * Extract mode never drops the original OR, it only appends "X IN (union
of literals)". When X is a
+ * plain slot (deterministic, evaluated once per row), every disjunct
implies its own top-level
+ * "X = c" / "X IN (...)", so the whole OR implies "X IN (union)" no
matter whether X is also
+ * referenced by sibling conjuncts. Under three-valued logic "X IN (union)
AND or" is equivalent to
+ * "or": or=TRUE implies X IN (union)=TRUE, and X IN (union)=FALSE implies
every disjunct is FALSE.
+ * A typical shape where the independence check blocks a useful pushdown:
+ * (spm='a' AND ...) OR (... AND spm IN ('c','d') AND ((spm='c' AND ...)
OR (spm='d' AND ...)))
+ * Here "spm IN ('c','d')" was inferred from the inner OR, which is kept
and still references spm.
+ */
+ private boolean isCandidateConjunct(int idx, List<Expression> conjuncts) {
+ if (mode == Mode.extractMode &&
isSlotCompareWithLiterals(conjuncts.get(idx))) {
+ return true;
+ }
+ return independentConjunct(idx, conjuncts);
+ }
+
+ private static boolean isSlotCompareWithLiterals(Expression conjunct) {
+ if (conjunct instanceof EqualTo) {
+ EqualTo eq = (EqualTo) conjunct;
+ return (eq.left() instanceof Slot && eq.right() instanceof Literal
+ && isInferenceSafeScalarType(eq.left())
+ && isInferenceSafeScalarType(eq.right()))
+ || (eq.right() instanceof Slot && eq.left() instanceof
Literal
+ && isInferenceSafeScalarType(eq.right())
+ && isInferenceSafeScalarType(eq.left()));
+ }
+ if (conjunct instanceof InPredicate) {
+ InPredicate inPredicate = (InPredicate) conjunct;
+ if (!(inPredicate.getCompareExpr() instanceof Slot)
+ ||
!isInferenceSafeScalarType(inPredicate.getCompareExpr())) {
+ return false;
+ }
+ for (Expression option : inPredicate.getOptions()) {
+ if (!(option instanceof Literal) ||
!isInferenceSafeScalarType(option)) {
+ return false;
+ }
+ }
+ return true;
+ }
+ return false;
+ }
+
+ /**
+ * Whether the type of expr admits the relaxation.
+ *
+ * Both sides are inspected when possible: the slot decides how "=" is
evaluated and the literal
+ * decides what IN puts into its hash set, so a type is only safe when
neither side vetoes it.
+ */
+ private static boolean isInferenceSafeScalarType(Expression expr) {
+ if (expr instanceof Unbound) {
+ // UnboundSlot throws on getDataType(). Planning always binds
slots before this rule
+ // runs, so an unknown type only shows up in unit tests; do not
let it veto.
+ return true;
+ }
+ return isInferenceSafeScalarType(expr.getDataType());
+ }
+
+ /**
+ * Whether "X = c" and "X IN (c1, c2, ...)" agree for this type, which the
three-valued
+ * argument above depends on.
+ *
+ * Only plain scalar types are accepted. Complex types (ARRAY/STRUCT/MAP)
are excluded because
+ * their IN is executed by FunctionCollectionIn, which drops the null map
of the left column
+ * instead of propagating it. "a IN ([1], [2])" then returns a non-null
FALSE for a NULL row
+ * where "a = [1]" returns NULL, so "a IN (...) AND or" is no longer
equivalent to "or" and a
+ * NULL-sensitive parent (IS NULL / NOT / CASE / ...) changes the query
result. Equality on a
+ * nullable ARRAY is also legal in Doris, so the shape is reachable in
practice.
+ *
+ * Floating point is excluded as a precaution: "X = c" and the hash-based
IN may disagree on
+ * -0.0 and NaN, which would break the "or=TRUE implies X IN (union)=TRUE"
direction.
+ *
+ * Rejecting a type is never a loss: the conjunct simply falls back to
independentConjunct()
+ * and gets exactly the pre-change behavior.
+ */
+ private static boolean isInferenceSafeScalarType(DataType type) {
+ return type.isIntegralType() // TINYINT / SMALLINT / INT / BIGINT /
LARGEINT
+ || type.isDecimalLikeType() // DECIMALV2 / DECIMALV3
+ || type.isDateLikeType() // DATE / DATETIME / DATEV2 /
DATETIMEV2 / TIMESTAMP_NS / TIMESTAMPTZ
+ || type.isTimeType() // TIMEV2
+ || type.isStringLikeType() // CHAR / VARCHAR / STRING
+ || type.isBooleanType();
+ }
+
+ /**
+ * Register "compareExpr -> literals" as a candidate of the current
disjunct.
+ *
+ * A second candidate for the same compareExpr (e.g. "A=1 and A in (1,
2)") is a pattern for the
+ * SimplifyRange rule, so OrToIn gives up on that expression. Historically
that was done by clearing
+ * every candidate of the disjunct and stopping, which was harmless
because the independence check
+ * never let two conjuncts on the same slot get here. The shared-slot
relaxation makes it reachable,
+ * and clearing everything would also throw away unrelated slots (e.g. "b
= 10" in
+ * "b = 10 and a = 1 and a in (1, 2)"), losing an inference that existed
before the relaxation.
+ * So only the conflicting compareExpr is dropped, and it is remembered in
{@code conflicted} so that
+ * a third conjunct on it cannot bring it back. Dropping a candidate only
weakens the appended
+ * In-predicate, never its correctness.
+ */
+ private static void addCandidate(Map<Expression, Set<Literal>> candidates,
Set<Expression> conflicted,
+ Expression compareExpr, Set<Literal> literals) {
+ if (conflicted.contains(compareExpr)) {
+ return;
+ }
+ if (!candidates.containsKey(compareExpr)) {
+ candidates.put(compareExpr, literals);
+ return;
+ }
+ candidates.remove(compareExpr);
+ conflicted.add(compareExpr);
+ }
+
private Map<Expression, Set<Literal>> getCandidates(Expression disjunct) {
List<Expression> conjuncts =
ExpressionUtils.extractConjunction(disjunct);
Map<Expression, Set<Literal>> candidates = new LinkedHashMap<>();
+ Set<Expression> conflicted = new HashSet<>();
// collect candidates from the first disjunction
for (int idx = 0; idx < conjuncts.size(); idx++) {
- if (!independentConjunct(idx, conjuncts)) {
+ if (!isCandidateConjunct(idx, conjuncts)) {
continue;
}
// find pattern: A=1 / A in (1, 2, 3 ...)
@@ -264,18 +388,9 @@ public class OrToIn {
literal = (Literal) eq.left();
}
if (compareExpr != null) {
- Set<Literal> literals = candidates.get(compareExpr);
- if (literals == null) {
- literals = Sets.newHashSet();
- literals.add(literal);
- candidates.put(compareExpr, literals);
- } else {
- // pattern like (A=1 and A=2) should be processed by
SimplifyRange rule
- // OrToIn rule does apply to this expression
- candidates.clear();
- break;
-
- }
+ Set<Literal> literals = Sets.newHashSet();
+ literals.add(literal);
+ addCandidate(candidates, conflicted, compareExpr,
literals);
}
} else if (conjunct instanceof InPredicate) {
InPredicate inPredicate = (InPredicate) conjunct;
@@ -291,15 +406,7 @@ public class OrToIn {
}
if (allLiteralOpts) {
- Set<Literal> alreadyMappedLiterals =
candidates.get(inPredicate.getCompareExpr());
- if (alreadyMappedLiterals == null) {
- candidates.put(inPredicate.getCompareExpr(),
literalOptions);
- } else {
- // pattern like (A=1 and A in (1, 2)) should be
processed by SimplifyRange rule
- // OrToIn rule does apply to this expression
- candidates.clear();
- break;
- }
+ addCandidate(candidates, conflicted,
inPredicate.getCompareExpr(), literalOptions);
}
}
}
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/OrToInTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/OrToInTest.java
index 796e0d3ac27..c7a1050be7a 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/OrToInTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/OrToInTest.java
@@ -19,11 +19,33 @@ package org.apache.doris.nereids.rules.rewrite;
import org.apache.doris.nereids.rules.expression.ExpressionRewriteTestHelper;
import org.apache.doris.nereids.rules.expression.rules.OrToIn;
+import org.apache.doris.nereids.trees.expressions.And;
+import org.apache.doris.nereids.trees.expressions.EqualTo;
import org.apache.doris.nereids.trees.expressions.Expression;
+import org.apache.doris.nereids.trees.expressions.InPredicate;
+import org.apache.doris.nereids.trees.expressions.IsNull;
+import org.apache.doris.nereids.trees.expressions.Not;
+import org.apache.doris.nereids.trees.expressions.Or;
+import org.apache.doris.nereids.trees.expressions.SlotReference;
+import org.apache.doris.nereids.trees.expressions.literal.ArrayLiteral;
+import org.apache.doris.nereids.trees.expressions.literal.DoubleLiteral;
+import org.apache.doris.nereids.trees.expressions.literal.IntegerLiteral;
+import org.apache.doris.nereids.types.ArrayType;
+import org.apache.doris.nereids.types.DoubleType;
+import org.apache.doris.nereids.types.IntegerType;
+import org.apache.doris.nereids.util.ExpressionUtils;
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.ImmutableSet;
+import com.google.common.collect.Lists;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
+import java.util.LinkedHashSet;
+import java.util.List;
+import java.util.Set;
+import java.util.stream.Collectors;
+
class OrToInTest extends ExpressionRewriteTestHelper {
@Test
@@ -167,11 +189,14 @@ class OrToInTest extends ExpressionRewriteTestHelper {
@Test
void test14() {
- // no rewrite, because of "f(a)"
+ // "a = 1" shares slot a with "f(a) = 2". The OR still implies "a in
(1, 3)", so extract mode
+ // appends it and keeps the original OR; replace mode must not touch
the OR.
String expr = "(a=1 and f(a)=2) or a=3";
Expression expression = PARSER.parseExpression(expr);
Expression rewritten =
OrToIn.EXTRACT_MODE_INSTANCE.rewriteTree(expression, context);
- Assertions.assertEquals("OR[AND[(a = 1),(f(a) = 2)],(a = 3)]",
rewritten.toSql());
+ Assertions.assertEquals("AND[a IN (1, 3),OR[AND[(a = 1),(f(a) = 2)],(a
= 3)]]", rewritten.toSql());
+ Expression replaced =
OrToIn.REPLACE_MODE_INSTANCE.rewriteTree(expression, context);
+ Assertions.assertEquals("OR[AND[(a = 1),(f(a) = 2)],(a = 3)]",
replaced.toSql());
}
@Test
@@ -199,4 +224,155 @@ class OrToInTest extends ExpressionRewriteTestHelper {
Expression rewritten =
OrToIn.EXTRACT_MODE_INSTANCE.rewriteTree(expression, context);
System.out.println(rewritten);
}
+
+ @Test
+ void testInferFromDisjunctWithDependentConjuncts() {
+ // Same shape as the homepage-rank query on dwd_traffic_events_di:
+ // (spm=S1 and ep=P1 and ev=E1) or (spm=S2 and ep=P2 and ev=E2)
+ // or (f(j)=.. and g(j) in (..) and ((spm=S3 and ep=P1 and ev=E1) or
(spm=S4 and ep=P2 and ev=E2)))
+ // The inner OR is rewritten first and yields "spm in (3, 4)" etc.
next to the kept inner OR, so these
+ // In-predicates share slots with their sibling. They must still take
part in the outer inference.
+ String expr = "(spm=1 and ep=1 and ev=1) or (spm=2 and ep=2 and ev=2)"
+ + " or (f(j)=1 and g(j) in (1, 2) and ((spm=3 and ep=1 and
ev=1) or (spm=4 and ep=2 and ev=2)))";
+ Expression expression = PARSER.parseExpression(expr);
+ // InferInPredicateFromOr calls rewriteTree(Expression) on filter
predicates
+ Expression rewritten =
OrToIn.EXTRACT_MODE_INSTANCE.rewriteTree(expression);
+ List<Expression> conjuncts =
ExpressionUtils.extractConjunction(rewritten);
+ assertHasInPredicate(conjuncts, "spm", "1", "2", "3", "4");
+ assertHasInPredicate(conjuncts, "ep", "1", "2");
+ assertHasInPredicate(conjuncts, "ev", "1", "2");
+ // exactly 3 inferred In-predicates + the original OR. f(j) / g(j) are
not plain slots and
+ // share j with each other, so they are never inferred to the top
level.
+ Assertions.assertEquals(4, conjuncts.size(), conjuncts.toString());
+ Assertions.assertEquals(1,
conjuncts.stream().filter(Or.class::isInstance).count());
+
+ assertFixedPoint(rewritten);
+
+ // replace mode must keep the original OR because the inferred
In-predicates are not equivalent to it
+ Expression replaced =
OrToIn.REPLACE_MODE_INSTANCE.rewriteTree(expression);
+ Assertions.assertTrue(replaced instanceof Or, replaced.toSql());
+ }
+
+ @Test
+ void testNonSlotCompareExprStillRequiresIndependence() {
+ // f(a) is not a plain slot, keep the conservative independence check
even in extract mode
+ String expr = "(f(a)=1 and a>0) or f(a)=2";
+ Expression expression = PARSER.parseExpression(expr);
+ Expression rewritten =
OrToIn.EXTRACT_MODE_INSTANCE.rewriteTree(expression);
+ Assertions.assertEquals("OR[AND[(f(a) = 1),(a > 0)],(f(a) = 2)]",
rewritten.toSql());
+ }
+
+ @Test
+ void testSlotSharedWithOtherPredicate() {
+ // "a > 0" and "a is not null" share slot a with "a = 1" / "a in (2,
3)"
+ String expr = "(a=1 and a>0) or (a in (2, 3) and a is not null)";
+ Expression expression = PARSER.parseExpression(expr);
+ Expression rewritten =
OrToIn.EXTRACT_MODE_INSTANCE.rewriteTree(expression);
+ List<Expression> conjuncts =
ExpressionUtils.extractConjunction(rewritten);
+ assertHasInPredicate(conjuncts, "a", "1", "2", "3");
+ Assertions.assertEquals(2, conjuncts.size(), conjuncts.toString());
+ Assertions.assertEquals(1,
conjuncts.stream().filter(Or.class::isInstance).count());
+ assertFixedPoint(rewritten);
+ }
+
+ @Test
+ void testDuplicateSlotCandidateOnlyDropsConflictingSlot() {
+ // The inner OR is rewritten first and appends "a in (1, 2)" next to
it, so the first outer disjunct
+ // holds "a = 1" and "a in (1, 2)" on the same slot. That slot is a
pattern for SimplifyRange and is
+ // dropped, but "b = 10" must still be inferred.
+ String expr = "(b=10 and a=1 and ((a=1 and c=100) or (a=2 and c=200)))
or (b=20 and a=3)";
+ // InferInPredicateFromOr calls rewriteTree(Expression) on filter
predicates
+ Expression rewritten =
OrToIn.EXTRACT_MODE_INSTANCE.rewriteTree(PARSER.parseExpression(expr));
+ List<Expression> conjuncts =
ExpressionUtils.extractConjunction(rewritten);
+ assertHasInPredicate(conjuncts, "b", "10", "20");
+ Assertions.assertEquals(1,
conjuncts.stream().filter(Or.class::isInstance).count(), conjuncts.toString());
+ // the conflicting slot is dropped, not inferred from a half-collected
candidate
+ Assertions.assertFalse(conjuncts.stream().anyMatch(c -> c instanceof
InPredicate
+ && ((InPredicate) c).getCompareExpr().toSql().equals("a")),
conjuncts.toString());
+ assertFixedPoint(rewritten);
+ }
+
+ @Test
+ void testConflictingSlotIsNotRevivedByLaterConjunct() {
+ // Three conjuncts on slot a: after "a = 1" and "a in (1, 2)"
conflict, the later "a in (1, 3)"
+ // must not register a again. Each single conjunct is implied by the
disjunct, so re-registering
+ // would still be sound, but the outcome would then depend on the
order the conjuncts are written
+ // in. Slot b is unrelated and is still inferred.
+ String expr = "(b=10 and a=1 and a in (1, 2) and a in (1, 3)) or (b=20
and a=3)";
+ Expression rewritten =
OrToIn.EXTRACT_MODE_INSTANCE.rewriteTree(PARSER.parseExpression(expr));
+ List<Expression> conjuncts =
ExpressionUtils.extractConjunction(rewritten);
+ assertHasInPredicate(conjuncts, "b", "10", "20");
+ Assertions.assertFalse(conjuncts.stream().anyMatch(c -> c instanceof
InPredicate
+ && ((InPredicate) c).getCompareExpr().toSql().equals("a")),
conjuncts.toString());
+ Assertions.assertEquals(1,
conjuncts.stream().filter(Or.class::isInstance).count());
+ assertFixedPoint(rewritten);
+ }
+
+ @Test
+ void testDuplicateSlotCandidateWithoutUnrelatedSlotLeavesOrAlone() {
+ // Two conjuncts on slot a and no other candidate slot in the first
disjunct: no candidate survives,
+ // so the OR is left alone (same end result as the old "clear
everything and stop" branch).
+ String expr = "(a=1 and a in (1, 2)) or (a=3 and b=1)";
+ Expression rewritten =
OrToIn.EXTRACT_MODE_INSTANCE.rewriteTree(PARSER.parseExpression(expr));
+ Assertions.assertTrue(rewritten instanceof Or, rewritten.toSql());
+ }
+
+ @Test
+ void testArrayCompareKeepsIndependenceCheck() {
+ // "a = [1]" shares slot a with "size(a) > 0", so only the relaxed
extract path could infer
+ // "a IN ([1], [2])". ARRAY is not inference-safe
(FunctionCollectionIn drops the null map of
+ // the left column), so the OR must be left alone.
+ SlotReference a = new SlotReference("a",
ArrayType.of(IntegerType.INSTANCE), true);
+ Expression eq1 = new EqualTo(a, new ArrayLiteral(ImmutableList.of(new
IntegerLiteral(1))));
+ Expression eq2 = new EqualTo(a, new ArrayLiteral(ImmutableList.of(new
IntegerLiteral(2))));
+ Expression or = new Or(new And(eq1, new Not(new IsNull(a))), eq2);
+ Expression rewritten = OrToIn.EXTRACT_MODE_INSTANCE.rewriteTree(or);
+ Assertions.assertTrue(rewritten instanceof Or, rewritten.toSql());
+ Assertions.assertEquals(0,
ExpressionUtils.extractConjunction(rewritten).stream()
+ .filter(InPredicate.class::isInstance).count(),
rewritten.toSql());
+ }
+
+ @Test
+ void testFloatCompareKeepsIndependenceCheck() {
+ // "X = c" and the hash-based IN may disagree on -0.0 / NaN, so
floating point is not relaxed.
+ SlotReference d = new SlotReference("d", DoubleType.INSTANCE, true);
+ Expression or = new Or(
+ new And(new EqualTo(d, new DoubleLiteral(1.0)), new Not(new
IsNull(d))),
+ new EqualTo(d, new DoubleLiteral(2.0)));
+ Expression rewritten = OrToIn.EXTRACT_MODE_INSTANCE.rewriteTree(or);
+ Assertions.assertTrue(rewritten instanceof Or, rewritten.toSql());
+ }
+
+ @Test
+ void testScalarTypesStillRelaxed() {
+ // 'S' -> STRING, 'I' -> INT, 'C' -> DATEV2, 'M' -> DECIMALV3, 'B' ->
BOOLEAN.
+ // These keep the relaxation, so the target "spm IN (...)" inference
is unaffected.
+ for (String expr : Lists.newArrayList("(Sa='x' and Sa>'a') or Sa='y'",
+ "(Ia=1 and Ia>0) or Ia=2", "(Ma=1 and Ma>0) or Ma=2")) {
+ Expression rewritten =
OrToIn.EXTRACT_MODE_INSTANCE.rewriteTree(PARSER.parseExpression(expr));
+ Assertions.assertFalse(rewritten instanceof Or, expr + " -> " +
rewritten.toSql());
+ }
+ }
+
+ // InferInPredicateFromOr stores the extracted conjuncts as a set and is
re-applied by a top-down
+ // rewrite job until the filter no longer changes, so a second application
must keep the same set.
+ private static void assertFixedPoint(Expression firstRewritten) {
+ Set<Expression> firstPass = new
LinkedHashSet<>(ExpressionUtils.extractConjunction(firstRewritten));
+ Expression secondInput =
ExpressionUtils.and(Lists.newArrayList(firstPass));
+ Set<Expression> secondPass = new
LinkedHashSet<>(ExpressionUtils.extractConjunction(
+ OrToIn.EXTRACT_MODE_INSTANCE.rewriteTree(secondInput)));
+ Assertions.assertEquals(firstPass, secondPass);
+ }
+
+ private static void assertHasInPredicate(List<Expression> conjuncts,
String slotName, String... values) {
+ Set<String> expectedValues = ImmutableSet.copyOf(values);
+ boolean found = conjuncts.stream()
+ .filter(InPredicate.class::isInstance)
+ .map(InPredicate.class::cast)
+ .anyMatch(in -> in.getCompareExpr().toSql().equals(slotName)
+ && in.getOptions().size() == expectedValues.size()
+ &&
in.getOptions().stream().map(Expression::toSql).collect(Collectors.toSet())
+ .equals(expectedValues));
+ Assertions.assertTrue(found, "expect " + slotName + " IN " +
expectedValues + " in " + conjuncts);
+ }
}
diff --git
a/regression-test/data/nereids_rules_p0/or_to_in/or_to_in_complex_type.out
b/regression-test/data/nereids_rules_p0/or_to_in/or_to_in_complex_type.out
new file mode 100644
index 00000000000..25dedd3c3e6
--- /dev/null
+++ b/regression-test/data/nereids_rules_p0/or_to_in/or_to_in_complex_type.out
@@ -0,0 +1,13 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !array_shared_slot_is_null --
+1
+
+-- !scalar_shared_slot_is_null --
+4
+
+-- !scalar_shared_slot_where --
+1
+
+-- !array_shared_slot_where --
+2
+
diff --git
a/regression-test/data/shape_check/tpcds_sf100/noStatsRfPrune/query41.out
b/regression-test/data/shape_check/tpcds_sf100/noStatsRfPrune/query41.out
index 12dad98df5b..bfcf8199cff 100644
--- a/regression-test/data/shape_check/tpcds_sf100/noStatsRfPrune/query41.out
+++ b/regression-test/data/shape_check/tpcds_sf100/noStatsRfPrune/query41.out
@@ -18,6 +18,6 @@ PhysicalResultSink
------------------------PhysicalDistribute[DistributionSpecHash]
--------------------------hashAgg[LOCAL]
----------------------------PhysicalProject
-------------------------------filter(OR[AND[(item.i_category =
'Men'),item.i_size IN ('N/A', 'economy', 'large', 'medium'),OR[AND[item.i_size
IN ('economy', 'medium'),item.i_color IN ('dodger', 'indian', 'spring',
'tan'),item.i_units IN ('Bunch', 'Carton', 'Tsp',
'Unknown'),OR[AND[item.i_color IN ('dodger', 'tan'),item.i_units IN ('Bunch',
'Tsp')],AND[item.i_color IN ('indian', 'spring'),item.i_units IN ('Carton',
'Unknown')]]],AND[item.i_size IN ('N/A', 'large'),item.i_color IN ('blue', [...]
+------------------------------filter(OR[AND[(item.i_category =
'Men'),item.i_size IN ('N/A', 'economy', 'large', 'medium'),item.i_color IN
('blue', 'chartreuse', 'dodger', 'indian', 'peru', 'saddle', 'spring',
'tan'),item.i_units IN ('Bunch', 'Carton', 'Each', 'Gram', 'Oz', 'Pallet',
'Tsp', 'Unknown'),OR[AND[item.i_size IN ('economy', 'medium'),item.i_color IN
('dodger', 'indian', 'spring', 'tan'),item.i_units IN ('Bunch', 'Carton',
'Tsp', 'Unknown'),OR[AND[item.i_color IN ('dodger', 'ta [...]
--------------------------------PhysicalOlapScan[item]
diff --git
a/regression-test/data/shape_check/tpcds_sf100/no_stats_shape/query41.out
b/regression-test/data/shape_check/tpcds_sf100/no_stats_shape/query41.out
index 12dad98df5b..bfcf8199cff 100644
--- a/regression-test/data/shape_check/tpcds_sf100/no_stats_shape/query41.out
+++ b/regression-test/data/shape_check/tpcds_sf100/no_stats_shape/query41.out
@@ -18,6 +18,6 @@ PhysicalResultSink
------------------------PhysicalDistribute[DistributionSpecHash]
--------------------------hashAgg[LOCAL]
----------------------------PhysicalProject
-------------------------------filter(OR[AND[(item.i_category =
'Men'),item.i_size IN ('N/A', 'economy', 'large', 'medium'),OR[AND[item.i_size
IN ('economy', 'medium'),item.i_color IN ('dodger', 'indian', 'spring',
'tan'),item.i_units IN ('Bunch', 'Carton', 'Tsp',
'Unknown'),OR[AND[item.i_color IN ('dodger', 'tan'),item.i_units IN ('Bunch',
'Tsp')],AND[item.i_color IN ('indian', 'spring'),item.i_units IN ('Carton',
'Unknown')]]],AND[item.i_size IN ('N/A', 'large'),item.i_color IN ('blue', [...]
+------------------------------filter(OR[AND[(item.i_category =
'Men'),item.i_size IN ('N/A', 'economy', 'large', 'medium'),item.i_color IN
('blue', 'chartreuse', 'dodger', 'indian', 'peru', 'saddle', 'spring',
'tan'),item.i_units IN ('Bunch', 'Carton', 'Each', 'Gram', 'Oz', 'Pallet',
'Tsp', 'Unknown'),OR[AND[item.i_size IN ('economy', 'medium'),item.i_color IN
('dodger', 'indian', 'spring', 'tan'),item.i_units IN ('Bunch', 'Carton',
'Tsp', 'Unknown'),OR[AND[item.i_color IN ('dodger', 'ta [...]
--------------------------------PhysicalOlapScan[item]
diff --git a/regression-test/data/shape_check/tpcds_sf100/rf_prune/query41.out
b/regression-test/data/shape_check/tpcds_sf100/rf_prune/query41.out
index 12dad98df5b..bfcf8199cff 100644
--- a/regression-test/data/shape_check/tpcds_sf100/rf_prune/query41.out
+++ b/regression-test/data/shape_check/tpcds_sf100/rf_prune/query41.out
@@ -18,6 +18,6 @@ PhysicalResultSink
------------------------PhysicalDistribute[DistributionSpecHash]
--------------------------hashAgg[LOCAL]
----------------------------PhysicalProject
-------------------------------filter(OR[AND[(item.i_category =
'Men'),item.i_size IN ('N/A', 'economy', 'large', 'medium'),OR[AND[item.i_size
IN ('economy', 'medium'),item.i_color IN ('dodger', 'indian', 'spring',
'tan'),item.i_units IN ('Bunch', 'Carton', 'Tsp',
'Unknown'),OR[AND[item.i_color IN ('dodger', 'tan'),item.i_units IN ('Bunch',
'Tsp')],AND[item.i_color IN ('indian', 'spring'),item.i_units IN ('Carton',
'Unknown')]]],AND[item.i_size IN ('N/A', 'large'),item.i_color IN ('blue', [...]
+------------------------------filter(OR[AND[(item.i_category =
'Men'),item.i_size IN ('N/A', 'economy', 'large', 'medium'),item.i_color IN
('blue', 'chartreuse', 'dodger', 'indian', 'peru', 'saddle', 'spring',
'tan'),item.i_units IN ('Bunch', 'Carton', 'Each', 'Gram', 'Oz', 'Pallet',
'Tsp', 'Unknown'),OR[AND[item.i_size IN ('economy', 'medium'),item.i_color IN
('dodger', 'indian', 'spring', 'tan'),item.i_units IN ('Bunch', 'Carton',
'Tsp', 'Unknown'),OR[AND[item.i_color IN ('dodger', 'ta [...]
--------------------------------PhysicalOlapScan[item]
diff --git a/regression-test/data/shape_check/tpcds_sf100/shape/query41.out
b/regression-test/data/shape_check/tpcds_sf100/shape/query41.out
index 12dad98df5b..bfcf8199cff 100644
--- a/regression-test/data/shape_check/tpcds_sf100/shape/query41.out
+++ b/regression-test/data/shape_check/tpcds_sf100/shape/query41.out
@@ -18,6 +18,6 @@ PhysicalResultSink
------------------------PhysicalDistribute[DistributionSpecHash]
--------------------------hashAgg[LOCAL]
----------------------------PhysicalProject
-------------------------------filter(OR[AND[(item.i_category =
'Men'),item.i_size IN ('N/A', 'economy', 'large', 'medium'),OR[AND[item.i_size
IN ('economy', 'medium'),item.i_color IN ('dodger', 'indian', 'spring',
'tan'),item.i_units IN ('Bunch', 'Carton', 'Tsp',
'Unknown'),OR[AND[item.i_color IN ('dodger', 'tan'),item.i_units IN ('Bunch',
'Tsp')],AND[item.i_color IN ('indian', 'spring'),item.i_units IN ('Carton',
'Unknown')]]],AND[item.i_size IN ('N/A', 'large'),item.i_color IN ('blue', [...]
+------------------------------filter(OR[AND[(item.i_category =
'Men'),item.i_size IN ('N/A', 'economy', 'large', 'medium'),item.i_color IN
('blue', 'chartreuse', 'dodger', 'indian', 'peru', 'saddle', 'spring',
'tan'),item.i_units IN ('Bunch', 'Carton', 'Each', 'Gram', 'Oz', 'Pallet',
'Tsp', 'Unknown'),OR[AND[item.i_size IN ('economy', 'medium'),item.i_color IN
('dodger', 'indian', 'spring', 'tan'),item.i_units IN ('Bunch', 'Carton',
'Tsp', 'Unknown'),OR[AND[item.i_color IN ('dodger', 'ta [...]
--------------------------------PhysicalOlapScan[item]
diff --git a/regression-test/data/shape_check/tpcds_sf1000/dphyper/query41.out
b/regression-test/data/shape_check/tpcds_sf1000/dphyper/query41.out
index da8ef93bfa0..4619303a182 100644
--- a/regression-test/data/shape_check/tpcds_sf1000/dphyper/query41.out
+++ b/regression-test/data/shape_check/tpcds_sf1000/dphyper/query41.out
@@ -18,6 +18,6 @@ PhysicalResultSink
------------------------PhysicalDistribute[DistributionSpecHash]
--------------------------hashAgg[LOCAL]
----------------------------PhysicalProject
-------------------------------filter(OR[AND[(item.i_category =
'Men'),item.i_size IN ('N/A', 'economy', 'large', 'small'),OR[AND[item.i_size
IN ('economy', 'small'),item.i_color IN ('firebrick', 'maroon', 'sienna',
'smoke'),item.i_units IN ('Case', 'Cup', 'Each', 'Ounce'),OR[AND[item.i_color
IN ('maroon', 'smoke'),item.i_units IN ('Case', 'Ounce')],AND[item.i_color IN
('firebrick', 'sienna'),item.i_units IN ('Cup', 'Each')]]],AND[item.i_size IN
('N/A', 'large'),item.i_color IN ('papaya', [...]
+------------------------------filter(OR[AND[(item.i_category =
'Men'),item.i_size IN ('N/A', 'economy', 'large', 'small'),item.i_color IN
('firebrick', 'maroon', 'papaya', 'peach', 'powder', 'sienna', 'sky',
'smoke'),item.i_units IN ('Bundle', 'Carton', 'Case', 'Cup', 'Dozen', 'Each',
'Lb', 'Ounce'),OR[AND[item.i_size IN ('economy', 'small'),item.i_color IN
('firebrick', 'maroon', 'sienna', 'smoke'),item.i_units IN ('Case', 'Cup',
'Each', 'Ounce'),OR[AND[item.i_color IN ('maroon', 'smoke [...]
--------------------------------PhysicalOlapScan[item]
diff --git a/regression-test/data/shape_check/tpcds_sf1000/hint/query41.out
b/regression-test/data/shape_check/tpcds_sf1000/hint/query41.out
index da8ef93bfa0..4619303a182 100644
--- a/regression-test/data/shape_check/tpcds_sf1000/hint/query41.out
+++ b/regression-test/data/shape_check/tpcds_sf1000/hint/query41.out
@@ -18,6 +18,6 @@ PhysicalResultSink
------------------------PhysicalDistribute[DistributionSpecHash]
--------------------------hashAgg[LOCAL]
----------------------------PhysicalProject
-------------------------------filter(OR[AND[(item.i_category =
'Men'),item.i_size IN ('N/A', 'economy', 'large', 'small'),OR[AND[item.i_size
IN ('economy', 'small'),item.i_color IN ('firebrick', 'maroon', 'sienna',
'smoke'),item.i_units IN ('Case', 'Cup', 'Each', 'Ounce'),OR[AND[item.i_color
IN ('maroon', 'smoke'),item.i_units IN ('Case', 'Ounce')],AND[item.i_color IN
('firebrick', 'sienna'),item.i_units IN ('Cup', 'Each')]]],AND[item.i_size IN
('N/A', 'large'),item.i_color IN ('papaya', [...]
+------------------------------filter(OR[AND[(item.i_category =
'Men'),item.i_size IN ('N/A', 'economy', 'large', 'small'),item.i_color IN
('firebrick', 'maroon', 'papaya', 'peach', 'powder', 'sienna', 'sky',
'smoke'),item.i_units IN ('Bundle', 'Carton', 'Case', 'Cup', 'Dozen', 'Each',
'Lb', 'Ounce'),OR[AND[item.i_size IN ('economy', 'small'),item.i_color IN
('firebrick', 'maroon', 'sienna', 'smoke'),item.i_units IN ('Case', 'Cup',
'Each', 'Ounce'),OR[AND[item.i_color IN ('maroon', 'smoke [...]
--------------------------------PhysicalOlapScan[item]
diff --git a/regression-test/data/shape_check/tpcds_sf1000/shape/query41.out
b/regression-test/data/shape_check/tpcds_sf1000/shape/query41.out
index da8ef93bfa0..4619303a182 100644
--- a/regression-test/data/shape_check/tpcds_sf1000/shape/query41.out
+++ b/regression-test/data/shape_check/tpcds_sf1000/shape/query41.out
@@ -18,6 +18,6 @@ PhysicalResultSink
------------------------PhysicalDistribute[DistributionSpecHash]
--------------------------hashAgg[LOCAL]
----------------------------PhysicalProject
-------------------------------filter(OR[AND[(item.i_category =
'Men'),item.i_size IN ('N/A', 'economy', 'large', 'small'),OR[AND[item.i_size
IN ('economy', 'small'),item.i_color IN ('firebrick', 'maroon', 'sienna',
'smoke'),item.i_units IN ('Case', 'Cup', 'Each', 'Ounce'),OR[AND[item.i_color
IN ('maroon', 'smoke'),item.i_units IN ('Case', 'Ounce')],AND[item.i_color IN
('firebrick', 'sienna'),item.i_units IN ('Cup', 'Each')]]],AND[item.i_size IN
('N/A', 'large'),item.i_color IN ('papaya', [...]
+------------------------------filter(OR[AND[(item.i_category =
'Men'),item.i_size IN ('N/A', 'economy', 'large', 'small'),item.i_color IN
('firebrick', 'maroon', 'papaya', 'peach', 'powder', 'sienna', 'sky',
'smoke'),item.i_units IN ('Bundle', 'Carton', 'Case', 'Cup', 'Dozen', 'Each',
'Lb', 'Ounce'),OR[AND[item.i_size IN ('economy', 'small'),item.i_color IN
('firebrick', 'maroon', 'sienna', 'smoke'),item.i_units IN ('Case', 'Cup',
'Each', 'Ounce'),OR[AND[item.i_color IN ('maroon', 'smoke [...]
--------------------------------PhysicalOlapScan[item]
diff --git
a/regression-test/data/shape_check/tpcds_sf1000_constraints/shape/query41.out
b/regression-test/data/shape_check/tpcds_sf1000_constraints/shape/query41.out
index d033b973b6c..706dcf578ac 100644
---
a/regression-test/data/shape_check/tpcds_sf1000_constraints/shape/query41.out
+++
b/regression-test/data/shape_check/tpcds_sf1000_constraints/shape/query41.out
@@ -18,6 +18,6 @@ PhysicalResultSink
------------------------PhysicalDistribute[DistributionSpecHash]
--------------------------hashAgg[LOCAL]
----------------------------PhysicalProject
-------------------------------filter(OR[AND[(item.i_category =
'Men'),item.i_size IN ('N/A', 'economy', 'large', 'small'),OR[AND[item.i_size
IN ('economy', 'small'),item.i_color IN ('firebrick', 'maroon', 'sienna',
'smoke'),item.i_units IN ('Case', 'Cup', 'Each', 'Ounce'),OR[AND[item.i_color
IN ('maroon', 'smoke'),item.i_units IN ('Case', 'Ounce')],AND[item.i_color IN
('firebrick', 'sienna'),item.i_units IN ('Cup', 'Each')]]],AND[item.i_size IN
('N/A', 'large'),item.i_color IN ('papaya', [...]
+------------------------------filter(OR[AND[(item.i_category =
'Men'),item.i_size IN ('N/A', 'economy', 'large', 'small'),item.i_color IN
('firebrick', 'maroon', 'papaya', 'peach', 'powder', 'sienna', 'sky',
'smoke'),item.i_units IN ('Bundle', 'Carton', 'Case', 'Cup', 'Dozen', 'Each',
'Lb', 'Ounce'),OR[AND[item.i_size IN ('economy', 'small'),item.i_color IN
('firebrick', 'maroon', 'sienna', 'smoke'),item.i_units IN ('Case', 'Cup',
'Each', 'Ounce'),OR[AND[item.i_color IN ('maroon', 'smoke [...]
--------------------------------PhysicalOlapScan[item]
diff --git
a/regression-test/data/shape_check/tpcds_sf1000_nopkfk/shape/query41.out
b/regression-test/data/shape_check/tpcds_sf1000_nopkfk/shape/query41.out
index da8ef93bfa0..4619303a182 100644
--- a/regression-test/data/shape_check/tpcds_sf1000_nopkfk/shape/query41.out
+++ b/regression-test/data/shape_check/tpcds_sf1000_nopkfk/shape/query41.out
@@ -18,6 +18,6 @@ PhysicalResultSink
------------------------PhysicalDistribute[DistributionSpecHash]
--------------------------hashAgg[LOCAL]
----------------------------PhysicalProject
-------------------------------filter(OR[AND[(item.i_category =
'Men'),item.i_size IN ('N/A', 'economy', 'large', 'small'),OR[AND[item.i_size
IN ('economy', 'small'),item.i_color IN ('firebrick', 'maroon', 'sienna',
'smoke'),item.i_units IN ('Case', 'Cup', 'Each', 'Ounce'),OR[AND[item.i_color
IN ('maroon', 'smoke'),item.i_units IN ('Case', 'Ounce')],AND[item.i_color IN
('firebrick', 'sienna'),item.i_units IN ('Cup', 'Each')]]],AND[item.i_size IN
('N/A', 'large'),item.i_color IN ('papaya', [...]
+------------------------------filter(OR[AND[(item.i_category =
'Men'),item.i_size IN ('N/A', 'economy', 'large', 'small'),item.i_color IN
('firebrick', 'maroon', 'papaya', 'peach', 'powder', 'sienna', 'sky',
'smoke'),item.i_units IN ('Bundle', 'Carton', 'Case', 'Cup', 'Dozen', 'Each',
'Lb', 'Ounce'),OR[AND[item.i_size IN ('economy', 'small'),item.i_color IN
('firebrick', 'maroon', 'sienna', 'smoke'),item.i_units IN ('Case', 'Cup',
'Each', 'Ounce'),OR[AND[item.i_color IN ('maroon', 'smoke [...]
--------------------------------PhysicalOlapScan[item]
diff --git a/regression-test/data/shape_check/tpcds_sf10t_orc/shape/query41.out
b/regression-test/data/shape_check/tpcds_sf10t_orc/shape/query41.out
index 9f24d6d0ca0..ee0504be27f 100644
--- a/regression-test/data/shape_check/tpcds_sf10t_orc/shape/query41.out
+++ b/regression-test/data/shape_check/tpcds_sf10t_orc/shape/query41.out
@@ -18,6 +18,6 @@ PhysicalResultSink
------------------------PhysicalDistribute[DistributionSpecHash]
--------------------------hashAgg[LOCAL]
----------------------------PhysicalProject
-------------------------------filter(OR[AND[(item.i_category =
'Men'),item.i_size IN ('N/A', 'large', 'medium', 'small'),OR[AND[item.i_size IN
('large', 'medium'),item.i_color IN ('cornflower', 'cyan', 'firebrick',
'maroon'),item.i_units IN ('Each', 'N/A', 'Oz', 'Pound'),OR[AND[item.i_color IN
('cornflower', 'firebrick'),item.i_units IN ('Oz', 'Pound')],AND[item.i_color
IN ('cyan', 'maroon'),item.i_units IN ('Each', 'N/A')]]],AND[item.i_size IN
('N/A', 'small'),item.i_color IN ('lavender [...]
+------------------------------filter(OR[AND[(item.i_category =
'Men'),item.i_size IN ('N/A', 'large', 'medium', 'small'),item.i_color IN
('cornflower', 'cyan', 'firebrick', 'lavender', 'magenta', 'maroon', 'papaya',
'slate'),item.i_units IN ('Bundle', 'Carton', 'Cup', 'Each', 'N/A', 'Oz',
'Pallet', 'Pound'),OR[AND[item.i_size IN ('large', 'medium'),item.i_color IN
('cornflower', 'cyan', 'firebrick', 'maroon'),item.i_units IN ('Each', 'N/A',
'Oz', 'Pound'),OR[AND[item.i_color IN ('cornflo [...]
--------------------------------PhysicalOlapScan[item]
diff --git
a/regression-test/suites/nereids_rules_p0/or_to_in/or_to_in_complex_type.groovy
b/regression-test/suites/nereids_rules_p0/or_to_in/or_to_in_complex_type.groovy
new file mode 100644
index 00000000000..ba5520cd08e
--- /dev/null
+++
b/regression-test/suites/nereids_rules_p0/or_to_in/or_to_in_complex_type.groovy
@@ -0,0 +1,98 @@
+
+// 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.
+
+suite("or_to_in_complex_type") {
+ sql "SET enable_nereids_planner=true"
+ sql "SET enable_fallback_to_original_planner=false"
+ sql "drop table if exists or_to_in_array_null"
+
+ sql """
+ CREATE TABLE or_to_in_array_null (
+ id INT,
+ a ARRAY<INT> NULL,
+ k INT
+ ) DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES ("replication_num" = "1")
+ """
+
+ sql """
+ INSERT INTO or_to_in_array_null VALUES
+ (1, NULL, 0),
+ (2, [1], 0),
+ (3, [3], 0)
+ """
+
+ // OrToIn in extract mode relaxes the independence check for "slot =
literal" candidates so
+ // that a top-level "slot IN (...)" can still be inferred when the slot is
shared with a
+ // sibling conjunct. That relaxation is only sound while "=" and IN agree
on NULL.
+ //
+ // For ARRAY, IN is executed by FunctionCollectionIn, which unwraps the
left nullable column
+ // without merging its null map: for a = NULL it returns a non-null FALSE
where "a = [1]"
+ // returns NULL. So "a IN ([1], [2]) AND (<original OR>)" is no longer
equivalent to the
+ // original OR, and a NULL-sensitive parent changes the result.
+ //
+ // Correct answer: only id=1, whose OR evaluates to NULL.
+ // With the array candidate admitted, the added IN yields FALSE for id=1,
so
+ // "FALSE AND NULL" makes IS NULL false and the row disappears.
+ qt_array_shared_slot_is_null """
+ SELECT id FROM or_to_in_array_null
+ WHERE ((a = [1] AND size(a) > k) OR a = [2]) IS NULL
+ ORDER BY id
+ """
+
+ // The same relaxation must still fire for scalar types, otherwise the
optimization loses
+ // its value for the STRING/INT predicates it was written for.
+ // id=4 is the NULL row: the inferred "s IN ('x', 'y')" keeps NULL, so the
OR stays NULL.
+ sql "drop table if exists or_to_in_scalar_null"
+ sql """
+ CREATE TABLE or_to_in_scalar_null (
+ id INT,
+ s VARCHAR(16) NULL,
+ k INT
+ ) DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES ("replication_num" = "1")
+ """
+ sql """
+ INSERT INTO or_to_in_scalar_null VALUES
+ (1, 'x', 0),
+ (2, 'other', 0),
+ (3, 'z', 0),
+ (4, NULL, 0)
+ """
+ qt_scalar_shared_slot_is_null """
+ SELECT id FROM or_to_in_scalar_null
+ WHERE ((s = 'x' AND length(s) > k) OR s = 'y') IS NULL
+ ORDER BY id
+ """
+
+ // The inferred IN is only appended, never substituted, so the plain WHERE
form must return
+ // exactly the rows the OR selects.
+ qt_scalar_shared_slot_where """
+ SELECT id FROM or_to_in_scalar_null
+ WHERE (s = 'x' AND length(s) > k) OR s = 'y'
+ ORDER BY id
+ """
+
+ qt_array_shared_slot_where """
+ SELECT id FROM or_to_in_array_null
+ WHERE (a = [1] AND size(a) > k) OR a = [2]
+ ORDER BY id
+ """
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
