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 88c719233a [opt](nereids) convert OR expression to IN expression
(#21326)
88c719233a is described below
commit 88c719233ae486570f754c23e918e9d6412f1e1b
Author: AKIRA <33112463+kikyou1...@users.noreply.github.com>
AuthorDate: Wed Jul 12 10:53:06 2023 +0800
[opt](nereids) convert OR expression to IN expression (#21326)
Add new rule named "OrToIn", used to convert multi equalTo which has same
slot and compare to a literal of disjunction to a InPredicate so that it could
be pushdown to storage engine.
for example:
```sql
col1 = 1 or col1 = 2 or col1 = 3 and (col2 = 4)
col1 = 1 and col1 = 3 and col2 = 3 or col2 = 4
(col1 = 1 or col1 = 2) and (col2 = 3 or col2 = 4)
```
would be converted to
```sql
col1 in (1, 2) or col1 = 3 and (col2 = 4)
col1 = 1 and col1 = 3 and col2 = 3 or col2 = 4
(col1 in (1, 2) and (col2 in (3, 4)))
```
---
.../expression/AbstractExpressionRewriteRule.java | 2 +-
.../rules/expression/ExpressionOptimization.java | 5 +-
.../rules/expression/ExpressionRewriteContext.java | 2 +
.../rules/expression/ExpressionRewriteRule.java | 4 +-
.../nereids/rules/expression/rules/OrToIn.java | 149 +++++++++++++++++++++
.../nereids/datasets/ssb/SSBJoinReorderTest.java | 8 +-
.../doris/nereids/rules/rewrite/OrToInTest.java | 93 +++++++++++++
.../nereids_tpcds_shape_sf100_p0/shape/query11.out | 41 +++---
.../nereids_tpcds_shape_sf100_p0/shape/query4.out | 54 ++++----
.../nereids_tpcds_shape_sf100_p0/shape/query41.out | 2 +-
.../nereids_tpcds_shape_sf100_p0/shape/query46.out | 2 +-
.../nereids_tpcds_shape_sf100_p0/shape/query74.out | 12 +-
.../nereids_tpcds_shape_sf100_p0/shape/query75.out | 6 +-
13 files changed, 313 insertions(+), 67 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/AbstractExpressionRewriteRule.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/AbstractExpressionRewriteRule.java
index 852abfc176..5abd9228ba 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/AbstractExpressionRewriteRule.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/AbstractExpressionRewriteRule.java
@@ -24,7 +24,7 @@ import
org.apache.doris.nereids.trees.expressions.visitor.DefaultExpressionRewri
* Base class of expression rewrite rule.
*/
public abstract class AbstractExpressionRewriteRule extends
DefaultExpressionRewriter<ExpressionRewriteContext>
- implements ExpressionRewriteRule {
+ implements ExpressionRewriteRule<ExpressionRewriteContext> {
@Override
public Expression rewrite(Expression expr, ExpressionRewriteContext ctx) {
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/ExpressionOptimization.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/ExpressionOptimization.java
index 50c0af4402..78676ca307 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/ExpressionOptimization.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/ExpressionOptimization.java
@@ -19,6 +19,7 @@ package org.apache.doris.nereids.rules.expression;
import org.apache.doris.nereids.rules.expression.rules.DistinctPredicatesRule;
import org.apache.doris.nereids.rules.expression.rules.ExtractCommonFactorRule;
+import org.apache.doris.nereids.rules.expression.rules.OrToIn;
import
org.apache.doris.nereids.rules.expression.rules.SimplifyComparisonPredicate;
import
org.apache.doris.nereids.rules.expression.rules.SimplifyDecimalV3Comparison;
import org.apache.doris.nereids.rules.expression.rules.SimplifyRange;
@@ -36,7 +37,9 @@ public class ExpressionOptimization extends ExpressionRewrite
{
DistinctPredicatesRule.INSTANCE,
SimplifyComparisonPredicate.INSTANCE,
SimplifyDecimalV3Comparison.INSTANCE,
- SimplifyRange.INSTANCE
+ SimplifyRange.INSTANCE,
+ OrToIn.INSTANCE
+
);
private static final ExpressionRuleExecutor EXECUTOR = new
ExpressionRuleExecutor(OPTIMIZE_REWRITE_RULES);
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/ExpressionRewriteContext.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/ExpressionRewriteContext.java
index 2c1963060b..cb50e0d287 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/ExpressionRewriteContext.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/ExpressionRewriteContext.java
@@ -23,9 +23,11 @@ import org.apache.doris.nereids.CascadesContext;
* expression rewrite context.
*/
public class ExpressionRewriteContext {
+
public final CascadesContext cascadesContext;
public ExpressionRewriteContext(CascadesContext cascadesContext) {
this.cascadesContext = cascadesContext;
}
+
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/ExpressionRewriteRule.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/ExpressionRewriteRule.java
index 1672d8c480..fa80d56d0c 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/ExpressionRewriteRule.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/ExpressionRewriteRule.java
@@ -22,6 +22,6 @@ import org.apache.doris.nereids.trees.expressions.Expression;
/**
* The interface of expression rewrite rule.
*/
-public interface ExpressionRewriteRule {
- Expression rewrite(Expression expr, ExpressionRewriteContext ctx);
+public interface ExpressionRewriteRule<T> {
+ Expression rewrite(Expression expr, T ctx);
}
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
new file mode 100644
index 0000000000..a54d5f5369
--- /dev/null
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/OrToIn.java
@@ -0,0 +1,149 @@
+// 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.doris.nereids.rules.expression.rules;
+
+import org.apache.doris.nereids.rules.expression.ExpressionRewriteContext;
+import org.apache.doris.nereids.rules.expression.ExpressionRewriteRule;
+import org.apache.doris.nereids.rules.expression.rules.OrToIn.OrToInContext;
+import org.apache.doris.nereids.trees.expressions.And;
+import org.apache.doris.nereids.trees.expressions.CompoundPredicate;
+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.NamedExpression;
+import org.apache.doris.nereids.trees.expressions.literal.Literal;
+import
org.apache.doris.nereids.trees.expressions.visitor.DefaultExpressionRewriter;
+import org.apache.doris.nereids.util.ExpressionUtils;
+
+import com.google.common.collect.ImmutableList;
+
+import java.util.ArrayList;
+import java.util.Collections;
+import java.util.HashMap;
+import java.util.HashSet;
+import java.util.List;
+import java.util.Map;
+import java.util.Set;
+
+/**
+ * Used to convert multi equalTo which has same slot and compare to a literal
of disjunction to a InPredicate so that
+ * it could be push down to storage engine.
+ * example:
+ * col1 = 1 or col1 = 2 or col1 = 3 and (col2 = 4)
+ * col1 = 1 and col1 = 3 and col2 = 3 or col2 = 4
+ * (col1 = 1 or col1 = 2) and (col2 = 3 or col2 = 4)
+ * <p>
+ * would be converted to:
+ * col1 in (1, 2) or col1 = 3 and (col2 = 4)
+ * col1 = 1 and col1 = 3 and col2 = 3 or col2 = 4
+ * (col1 in (1, 2) and (col2 in (3, 4)))
+ * The generic type declaration and the overridden 'rewrite' function in this
class may appear unconventional
+ * because we need to maintain a map passed between methods in this class. But
the owner of this module prohibits
+ * adding any additional rule-specific fields to the default
ExpressionRewriteContext. However, the entire expression
+ * rewrite framework always passes an ExpressionRewriteContext of type context
to all rules.
+ */
+public class OrToIn extends DefaultExpressionRewriter<OrToInContext> implements
+ ExpressionRewriteRule<ExpressionRewriteContext> {
+
+ public static final OrToIn INSTANCE = new OrToIn();
+
+ private static final int REWRITE_OR_TO_IN_PREDICATE_THRESHOLD = 2;
+
+ @Override
+ public Expression rewrite(Expression expr, ExpressionRewriteContext ctx) {
+ return expr.accept(this, new OrToInContext());
+ }
+
+ @Override
+ public Expression visitCompoundPredicate(CompoundPredicate
compoundPredicate, OrToInContext context) {
+ if (compoundPredicate instanceof And) {
+ return
compoundPredicate.withChildren(compoundPredicate.child(0).accept(new OrToIn(),
+ new OrToInContext()),
+ compoundPredicate.child(1).accept(new OrToIn(),
+ new OrToInContext()));
+ }
+ List<Expression> expressions =
ExpressionUtils.extractDisjunction(compoundPredicate);
+ for (Expression expression : expressions) {
+ if (expression instanceof EqualTo) {
+ addSlotToLiteralMap((EqualTo) expression, context);
+ }
+ }
+ List<Expression> rewrittenOr = new ArrayList<>();
+ for (Map.Entry<NamedExpression, Set<Literal>> entry :
context.slotNameToLiteral.entrySet()) {
+ Set<Literal> literals = entry.getValue();
+ if (literals.size() >= REWRITE_OR_TO_IN_PREDICATE_THRESHOLD) {
+ InPredicate inPredicate = new InPredicate(entry.getKey(),
ImmutableList.copyOf(entry.getValue()));
+ rewrittenOr.add(inPredicate);
+ }
+ }
+ for (Expression expression : expressions) {
+ if (!ableToConvertToIn(expression, context)) {
+ rewrittenOr.add(expression);
+ }
+ }
+
+ return ExpressionUtils.or(rewrittenOr);
+ }
+
+ private void addSlotToLiteralMap(EqualTo equal, OrToInContext context) {
+ Expression left = equal.left();
+ Expression right = equal.right();
+ if (left instanceof NamedExpression && right instanceof Literal) {
+ addSlotToLiteral((NamedExpression) left, (Literal) right, context);
+ }
+ if (right instanceof NamedExpression && left instanceof Literal) {
+ addSlotToLiteral((NamedExpression) right, (Literal) left, context);
+ }
+ }
+
+ private boolean ableToConvertToIn(Expression expression, OrToInContext
context) {
+ if (!(expression instanceof EqualTo)) {
+ return false;
+ }
+ EqualTo equalTo = (EqualTo) expression;
+ Expression left = equalTo.left();
+ Expression right = equalTo.right();
+ NamedExpression namedExpression = null;
+ if (left instanceof NamedExpression && right instanceof Literal) {
+ namedExpression = (NamedExpression) left;
+ }
+ if (right instanceof NamedExpression && left instanceof Literal) {
+ namedExpression = (NamedExpression) right;
+ }
+ return namedExpression != null
+ && findSizeOfLiteralThatEqualToSameSlotInOr(namedExpression,
context)
+ >= REWRITE_OR_TO_IN_PREDICATE_THRESHOLD;
+ }
+
+ public void addSlotToLiteral(NamedExpression namedExpression, Literal
literal, OrToInContext context) {
+ Set<Literal> literals =
context.slotNameToLiteral.computeIfAbsent(namedExpression, k -> new
HashSet<>());
+ literals.add(literal);
+ }
+
+ public int findSizeOfLiteralThatEqualToSameSlotInOr(NamedExpression
namedExpression, OrToInContext context) {
+ return context.slotNameToLiteral.getOrDefault(namedExpression,
Collections.emptySet()).size();
+ }
+
+ /**
+ * Context of OrToIn
+ */
+ public static class OrToInContext {
+ public final Map<NamedExpression, Set<Literal>> slotNameToLiteral =
new HashMap<>();
+
+ }
+}
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/datasets/ssb/SSBJoinReorderTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/datasets/ssb/SSBJoinReorderTest.java
index 70140e8994..49c3636a4f 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/nereids/datasets/ssb/SSBJoinReorderTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/datasets/ssb/SSBJoinReorderTest.java
@@ -39,7 +39,7 @@ public class SSBJoinReorderTest extends SSBTestBase
implements MemoPatternMatchS
ImmutableList.of(
"(c_region = 'AMERICA')",
"(s_region = 'AMERICA')",
- "((p_mfgr = 'MFGR#1') OR (p_mfgr = 'MFGR#2'))"
+ "p_mfgr IN ('MFGR#2', 'MFGR#1')"
)
);
}
@@ -55,10 +55,10 @@ public class SSBJoinReorderTest extends SSBTestBase
implements MemoPatternMatchS
"(lo_partkey = p_partkey)"
),
ImmutableList.of(
- "((d_year = 1997) OR (d_year = 1998))",
+ "d_year IN (1997, 1998)",
"(c_region = 'AMERICA')",
"(s_region = 'AMERICA')",
- "((p_mfgr = 'MFGR#1') OR (p_mfgr = 'MFGR#2'))"
+ "p_mfgr IN ('MFGR#2', 'MFGR#1')"
)
);
}
@@ -74,7 +74,7 @@ public class SSBJoinReorderTest extends SSBTestBase
implements MemoPatternMatchS
"(lo_partkey = p_partkey)"
),
ImmutableList.of(
- "((d_year = 1997) OR (d_year = 1998))",
+ "d_year IN (1997, 1998)",
"(s_nation = 'UNITED STATES')",
"(p_category = 'MFGR#14')"
)
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
new file mode 100644
index 0000000000..651c330c55
--- /dev/null
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/rewrite/OrToInTest.java
@@ -0,0 +1,93 @@
+// 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.doris.nereids.rules.rewrite;
+
+import org.apache.doris.nereids.rules.expression.ExpressionRewriteContext;
+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.Expression;
+import org.apache.doris.nereids.trees.expressions.InPredicate;
+import org.apache.doris.nereids.trees.expressions.NamedExpression;
+import org.apache.doris.nereids.trees.expressions.literal.Literal;
+
+import com.google.common.collect.ImmutableSet;
+import org.junit.jupiter.api.Assertions;
+import org.junit.jupiter.api.Test;
+
+import java.util.List;
+import java.util.Set;
+
+public class OrToInTest extends ExpressionRewriteTestHelper {
+
+ @Test
+ public void test1() {
+ String expr = "col1 = 1 or col1 = 2 or col1 = 3 and (col2 = 4)";
+ Expression expression = PARSER.parseExpression(expr);
+ Expression rewritten = new OrToIn().rewrite(expression, new
ExpressionRewriteContext(null));
+ Set<InPredicate> inPredicates = rewritten.collect(e -> e instanceof
InPredicate);
+ Assertions.assertEquals(1, inPredicates.size());
+ InPredicate inPredicate = inPredicates.iterator().next();
+ NamedExpression namedExpression = (NamedExpression)
inPredicate.getCompareExpr();
+ Assertions.assertEquals("col1", namedExpression.getName());
+ List<Expression> options = inPredicate.getOptions();
+ Assertions.assertEquals(2, options.size());
+ Set<Integer> opVals = ImmutableSet.of(1, 2);
+ for (Expression op : options) {
+ Literal literal = (Literal) op;
+ Assertions.assertTrue(opVals.contains(((Byte)
literal.getValue()).intValue()));
+ }
+ Set<And> ands = rewritten.collect(e -> e instanceof And);
+ Assertions.assertEquals(1, ands.size());
+ And and = ands.iterator().next();
+ Assertions.assertEquals("((col1 = 3) AND (col2 = 4))", and.toSql());
+ }
+
+ @Test
+ public void test2() {
+ String expr = "col1 = 1 and col1 = 3 and col2 = 3 or col2 = 4";
+ Expression expression = PARSER.parseExpression(expr);
+ Expression rewritten = new OrToIn().rewrite(expression, new
ExpressionRewriteContext(null));
+ Assertions.assertEquals("((((col1 = 1) AND (col1 = 3)) AND (col2 = 3))
OR (col2 = 4))",
+ rewritten.toSql());
+ }
+
+ @Test
+ public void test3() {
+ String expr = "(col1 = 1 or col1 = 2) and (col2 = 3 or col2 = 4)";
+ Expression expression = PARSER.parseExpression(expr);
+ Expression rewritten = new OrToIn().rewrite(expression, new
ExpressionRewriteContext(null));
+ List<InPredicate> inPredicates = rewritten.collectToList(e -> e
instanceof InPredicate);
+ Assertions.assertEquals(2, inPredicates.size());
+ InPredicate in1 = inPredicates.get(0);
+ Assertions.assertEquals("col1", ((NamedExpression)
in1.getCompareExpr()).getName());
+ Set<Integer> opVals1 = ImmutableSet.of(1, 2);
+ for (Expression op : in1.getOptions()) {
+ Literal literal = (Literal) op;
+ Assertions.assertTrue(opVals1.contains(((Byte)
literal.getValue()).intValue()));
+ }
+ InPredicate in2 = inPredicates.get(1);
+ Assertions.assertEquals("col2", ((NamedExpression)
in2.getCompareExpr()).getName());
+ Set<Integer> opVals2 = ImmutableSet.of(3, 4);
+ for (Expression op : in2.getOptions()) {
+ Literal literal = (Literal) op;
+ Assertions.assertTrue(opVals2.contains(((Byte)
literal.getValue()).intValue()));
+ }
+ }
+
+}
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query11.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query11.out
index dee0257716..5e4621c1bd 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query11.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query11.out
@@ -11,15 +11,15 @@ CteAnchor[cteId= ( CTEId#4=] )
----------------hashJoin[INNER_JOIN](customer.c_customer_sk =
store_sales.ss_customer_sk)
------------------hashJoin[INNER_JOIN](store_sales.ss_sold_date_sk =
date_dim.d_date_sk)
--------------------PhysicalProject
-----------------------filter((('s' = 'w') OR ('s' = 's')))
+----------------------filter('s' IN ('s', 'w'))
------------------------PhysicalOlapScan[store_sales]
--------------------PhysicalDistribute
----------------------PhysicalProject
-------------------------filter(((date_dim.d_year = 2001) OR (date_dim.d_year =
2002))(('s' = 'w') OR ('s' = 's')))
+------------------------filter('s' IN ('s', 'w')d_year IN (2001, 2002))
--------------------------PhysicalOlapScan[date_dim]
------------------PhysicalDistribute
--------------------PhysicalProject
-----------------------filter((('s' = 'w') OR ('s' = 's')))
+----------------------filter('s' IN ('s', 'w'))
------------------------PhysicalOlapScan[customer]
------PhysicalProject
--------hashAgg[GLOBAL]
@@ -30,38 +30,37 @@ CteAnchor[cteId= ( CTEId#4=] )
------------------PhysicalDistribute
--------------------hashJoin[INNER_JOIN](web_sales.ws_sold_date_sk =
date_dim.d_date_sk)
----------------------PhysicalProject
-------------------------filter((('w' = 'w') OR ('w' = 's')))
+------------------------filter('w' IN ('s', 'w'))
--------------------------PhysicalOlapScan[web_sales]
----------------------PhysicalDistribute
------------------------PhysicalProject
---------------------------filter(((date_dim.d_year = 2001) OR (date_dim.d_year
= 2002))(('w' = 'w') OR ('w' = 's')))
+--------------------------filter(d_year IN (2001, 2002)'w' IN ('s', 'w'))
----------------------------PhysicalOlapScan[date_dim]
------------------PhysicalDistribute
--------------------PhysicalProject
-----------------------filter((('w' = 'w') OR ('w' = 's')))
+----------------------filter('w' IN ('s', 'w'))
------------------------PhysicalOlapScan[customer]
--PhysicalTopN
----PhysicalDistribute
------PhysicalTopN
--------PhysicalProject
-----------hashJoin[INNER_JOIN](t_s_secyear.customer_id =
t_s_firstyear.customer_id)(CASE WHEN (year_total > 0.00) THEN (cast(year_total
as DECIMALV3(38, 8)) / year_total) ELSE 0.000000 END > CASE WHEN (year_total >
0.00) THEN (cast(year_total as DECIMALV3(38, 8)) / year_total) ELSE 0.000000
END)
-------------PhysicalDistribute
---------------PhysicalProject
-----------------filter((t_s_secyear.sale_type = 's')(t_s_secyear.dyear = 2002))
-------------------CteConsumer[cteId= ( CTEId#4=] )
-------------PhysicalProject
+----------hashJoin[INNER_JOIN](t_s_firstyear.customer_id =
t_w_secyear.customer_id)(CASE WHEN (year_total > 0.00) THEN (cast(year_total as
DECIMALV3(38, 8)) / year_total) ELSE 0.000000 END > CASE WHEN (year_total >
0.00) THEN (cast(year_total as DECIMALV3(38, 8)) / year_total) ELSE 0.000000
END)
+------------hashJoin[INNER_JOIN](t_s_secyear.customer_id =
t_s_firstyear.customer_id)
--------------hashJoin[INNER_JOIN](t_s_firstyear.customer_id =
t_w_firstyear.customer_id)
-----------------hashJoin[INNER_JOIN](t_s_firstyear.customer_id =
t_w_secyear.customer_id)
-------------------PhysicalDistribute
---------------------PhysicalProject
-----------------------filter((t_w_secyear.dyear = 2002)(t_w_secyear.sale_type
= 'w'))
-------------------------CteConsumer[cteId= ( CTEId#4=] )
-------------------PhysicalDistribute
---------------------PhysicalProject
-----------------------filter((t_s_firstyear.dyear =
2001)(t_s_firstyear.sale_type = 's')(t_s_firstyear.year_total > 0.00))
-------------------------CteConsumer[cteId= ( CTEId#4=] )
+----------------PhysicalDistribute
+------------------PhysicalProject
+--------------------filter((t_s_firstyear.dyear =
2001)(t_s_firstyear.sale_type = 's')(t_s_firstyear.year_total > 0.00))
+----------------------CteConsumer[cteId= ( CTEId#4=] )
----------------PhysicalDistribute
------------------PhysicalProject
--------------------filter((t_w_firstyear.year_total >
0.00)(t_w_firstyear.sale_type = 'w')(t_w_firstyear.dyear = 2001))
----------------------CteConsumer[cteId= ( CTEId#4=] )
+--------------PhysicalDistribute
+----------------PhysicalProject
+------------------filter((t_s_secyear.sale_type = 's')(t_s_secyear.dyear =
2002))
+--------------------CteConsumer[cteId= ( CTEId#4=] )
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------filter((t_w_secyear.dyear = 2002)(t_w_secyear.sale_type = 'w'))
+------------------CteConsumer[cteId= ( CTEId#4=] )
diff --git a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query4.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query4.out
index b1ba26b82e..897f72cdd6 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query4.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query4.out
@@ -12,15 +12,15 @@ CteAnchor[cteId= ( CTEId#6=] )
------------------PhysicalProject
--------------------hashJoin[INNER_JOIN](customer.c_customer_sk =
store_sales.ss_customer_sk)
----------------------PhysicalProject
-------------------------filter(((('s' = 'c') OR ('s' = 's')) OR ('s' = 'w')))
+------------------------filter('s' IN ('c', 's', 'w'))
--------------------------PhysicalOlapScan[store_sales]
----------------------PhysicalDistribute
------------------------PhysicalProject
---------------------------filter(((('s' = 'c') OR ('s' = 's')) OR ('s' = 'w')))
+--------------------------filter('s' IN ('c', 's', 'w'))
----------------------------PhysicalOlapScan[customer]
------------------PhysicalDistribute
--------------------PhysicalProject
-----------------------filter(((date_dim.d_year = 1999) OR (date_dim.d_year =
2000))((('s' = 'c') OR ('s' = 's')) OR ('s' = 'w')))
+----------------------filter(d_year IN (2000, 1999)'s' IN ('c', 's', 'w'))
------------------------PhysicalOlapScan[date_dim]
------PhysicalProject
--------hashAgg[GLOBAL]
@@ -31,15 +31,15 @@ CteAnchor[cteId= ( CTEId#6=] )
------------------PhysicalProject
--------------------hashJoin[INNER_JOIN](customer.c_customer_sk =
catalog_sales.cs_bill_customer_sk)
----------------------PhysicalProject
-------------------------filter(((('c' = 'c') OR ('c' = 's')) OR ('c' = 'w')))
+------------------------filter('c' IN ('c', 's', 'w'))
--------------------------PhysicalOlapScan[catalog_sales]
----------------------PhysicalDistribute
------------------------PhysicalProject
---------------------------filter(((('c' = 'c') OR ('c' = 's')) OR ('c' = 'w')))
+--------------------------filter('c' IN ('c', 's', 'w'))
----------------------------PhysicalOlapScan[customer]
------------------PhysicalDistribute
--------------------PhysicalProject
-----------------------filter(((date_dim.d_year = 1999) OR (date_dim.d_year =
2000))((('c' = 'c') OR ('c' = 's')) OR ('c' = 'w')))
+----------------------filter('c' IN ('c', 's', 'w')d_year IN (2000, 1999))
------------------------PhysicalOlapScan[date_dim]
------PhysicalProject
--------hashAgg[GLOBAL]
@@ -50,43 +50,27 @@ CteAnchor[cteId= ( CTEId#6=] )
------------------PhysicalProject
--------------------hashJoin[INNER_JOIN](customer.c_customer_sk =
web_sales.ws_bill_customer_sk)
----------------------PhysicalProject
-------------------------filter(((('w' = 'c') OR ('w' = 's')) OR ('w' = 'w')))
+------------------------filter('w' IN ('c', 's', 'w'))
--------------------------PhysicalOlapScan[web_sales]
----------------------PhysicalDistribute
------------------------PhysicalProject
---------------------------filter(((('w' = 'c') OR ('w' = 's')) OR ('w' = 'w')))
+--------------------------filter('w' IN ('c', 's', 'w'))
----------------------------PhysicalOlapScan[customer]
------------------PhysicalDistribute
--------------------PhysicalProject
-----------------------filter(((date_dim.d_year = 1999) OR (date_dim.d_year =
2000))((('w' = 'c') OR ('w' = 's')) OR ('w' = 'w')))
+----------------------filter('w' IN ('c', 's', 'w')d_year IN (2000, 1999))
------------------------PhysicalOlapScan[date_dim]
--PhysicalTopN
----PhysicalDistribute
------PhysicalTopN
--------PhysicalProject
----------hashJoin[INNER_JOIN](t_s_firstyear.customer_id =
t_w_secyear.customer_id)(CASE WHEN (year_total > 0.000000) THEN
(cast(year_total as DECIMALV3(38, 16)) / year_total) ELSE NULL END > CASE WHEN
(year_total > 0.000000) THEN (cast(year_total as DECIMALV3(38, 16)) /
year_total) ELSE NULL END)
-------------PhysicalDistribute
---------------PhysicalProject
-----------------filter((t_w_secyear.sale_type = 'w')(t_w_secyear.dyear = 2000))
-------------------CteConsumer[cteId= ( CTEId#6=] )
------------PhysicalProject
--------------hashJoin[INNER_JOIN](t_s_firstyear.customer_id =
t_w_firstyear.customer_id)
-----------------PhysicalDistribute
-------------------PhysicalProject
---------------------filter((t_w_firstyear.dyear =
1999)(t_w_firstyear.sale_type = 'w')(t_w_firstyear.year_total > 0.000000))
-----------------------CteConsumer[cteId= ( CTEId#6=] )
----------------PhysicalProject
-------------------hashJoin[INNER_JOIN](t_s_secyear.customer_id =
t_s_firstyear.customer_id)(CASE WHEN (year_total > 0.000000) THEN
(cast(year_total as DECIMALV3(38, 16)) / year_total) ELSE NULL END > CASE WHEN
(year_total > 0.000000) THEN (cast(year_total as DECIMALV3(38, 16)) /
year_total) ELSE NULL END)
---------------------PhysicalDistribute
-----------------------PhysicalProject
-------------------------filter((t_s_secyear.sale_type = 's')(t_s_secyear.dyear
= 2000))
---------------------------CteConsumer[cteId= ( CTEId#6=] )
+------------------hashJoin[INNER_JOIN](t_s_firstyear.customer_id =
t_c_secyear.customer_id)(CASE WHEN (year_total > 0.000000) THEN
(cast(year_total as DECIMALV3(38, 16)) / year_total) ELSE NULL END > CASE WHEN
(year_total > 0.000000) THEN (cast(year_total as DECIMALV3(38, 16)) /
year_total) ELSE NULL END)
--------------------PhysicalProject
-----------------------hashJoin[INNER_JOIN](t_s_firstyear.customer_id =
t_c_secyear.customer_id)
-------------------------PhysicalDistribute
---------------------------PhysicalProject
-----------------------------filter((t_c_secyear.sale_type =
'c')(t_c_secyear.dyear = 2000))
-------------------------------CteConsumer[cteId= ( CTEId#6=] )
+----------------------hashJoin[INNER_JOIN](t_s_secyear.customer_id =
t_s_firstyear.customer_id)
------------------------hashJoin[INNER_JOIN](t_s_firstyear.customer_id =
t_c_firstyear.customer_id)
--------------------------PhysicalDistribute
----------------------------PhysicalProject
@@ -96,4 +80,20 @@ CteAnchor[cteId= ( CTEId#6=] )
----------------------------PhysicalProject
------------------------------filter((t_c_firstyear.year_total >
0.000000)(t_c_firstyear.dyear = 1999)(t_c_firstyear.sale_type = 'c'))
--------------------------------CteConsumer[cteId= ( CTEId#6=] )
+------------------------PhysicalDistribute
+--------------------------PhysicalProject
+----------------------------filter((t_s_secyear.sale_type =
's')(t_s_secyear.dyear = 2000))
+------------------------------CteConsumer[cteId= ( CTEId#6=] )
+--------------------PhysicalDistribute
+----------------------PhysicalProject
+------------------------filter((t_c_secyear.sale_type = 'c')(t_c_secyear.dyear
= 2000))
+--------------------------CteConsumer[cteId= ( CTEId#6=] )
+----------------PhysicalDistribute
+------------------PhysicalProject
+--------------------filter((t_w_firstyear.dyear =
1999)(t_w_firstyear.sale_type = 'w')(t_w_firstyear.year_total > 0.000000))
+----------------------CteConsumer[cteId= ( CTEId#6=] )
+------------PhysicalDistribute
+--------------PhysicalProject
+----------------filter((t_w_secyear.sale_type = 'w')(t_w_secyear.dyear = 2000))
+------------------CteConsumer[cteId= ( CTEId#6=] )
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query41.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query41.out
index 69943b851c..1d86178c4c 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query41.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query41.out
@@ -18,6 +18,6 @@ PhysicalTopN
------------------------PhysicalDistribute
--------------------------hashAgg[LOCAL]
----------------------------PhysicalProject
-------------------------------filter(((((((((cast(i_category as VARCHAR(*)) =
'Women') AND ((cast(i_color as VARCHAR(*)) = 'gainsboro') OR (cast(i_color as
VARCHAR(*)) = 'aquamarine'))) AND ((cast(i_units as VARCHAR(*)) = 'Ounce') OR
(cast(i_units as VARCHAR(*)) = 'Dozen'))) AND ((cast(i_size as VARCHAR(*)) =
'medium') OR (cast(i_size as VARCHAR(*)) = 'economy'))) OR ((((cast(i_category
as VARCHAR(*)) = 'Women') AND ((cast(i_color as VARCHAR(*)) = 'chiffon') OR
(cast(i_color as VARCHAR(* [...]
+------------------------------filter((((((((((((cast(i_category as VARCHAR(*))
= 'Women') AND ((cast(i_color as VARCHAR(*)) = 'gainsboro') OR (cast(i_color as
VARCHAR(*)) = 'aquamarine'))) AND ((cast(i_units as VARCHAR(*)) = 'Ounce') OR
(cast(i_units as VARCHAR(*)) = 'Dozen'))) AND ((cast(i_size as VARCHAR(*)) =
'medium') OR (cast(i_size as VARCHAR(*)) = 'economy'))) OR ((((cast(i_category
as VARCHAR(*)) = 'Women') AND ((cast(i_color as VARCHAR(*)) = 'chiffon') OR
(cast(i_color as VARCHA [...]
--------------------------------PhysicalOlapScan[item]
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query46.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query46.out
index f1c91e1f35..bd24b2de19 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query46.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query46.out
@@ -19,7 +19,7 @@ PhysicalTopN
--------------------------------PhysicalOlapScan[store_sales]
------------------------------PhysicalDistribute
--------------------------------PhysicalProject
-----------------------------------filter(((date_dim.d_dow = 0) OR
(date_dim.d_dow = 6))d_year IN (1999, 2000, 2001))
+----------------------------------filter(d_dow IN (0, 6)d_year IN (1999, 2000,
2001))
------------------------------------PhysicalOlapScan[date_dim]
----------------------------PhysicalDistribute
------------------------------PhysicalProject
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query74.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query74.out
index 07a0ec06da..661f999867 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query74.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query74.out
@@ -11,15 +11,15 @@ CteAnchor[cteId= ( CTEId#4=] )
----------------hashJoin[INNER_JOIN](customer.c_customer_sk =
store_sales.ss_customer_sk)
------------------hashJoin[INNER_JOIN](store_sales.ss_sold_date_sk =
date_dim.d_date_sk)
--------------------PhysicalProject
-----------------------filter((('s' = 's') OR ('s' = 'w')))
+----------------------filter('s' IN ('s', 'w'))
------------------------PhysicalOlapScan[store_sales]
--------------------PhysicalDistribute
----------------------PhysicalProject
-------------------------filter(((date_dim.d_year = 1999) OR (date_dim.d_year =
2000))(('s' = 's') OR ('s' = 'w')))
+------------------------filter('s' IN ('s', 'w')d_year IN (2000, 1999))
--------------------------PhysicalOlapScan[date_dim]
------------------PhysicalDistribute
--------------------PhysicalProject
-----------------------filter((('s' = 's') OR ('s' = 'w')))
+----------------------filter('s' IN ('s', 'w'))
------------------------PhysicalOlapScan[customer]
------PhysicalProject
--------hashAgg[GLOBAL]
@@ -30,15 +30,15 @@ CteAnchor[cteId= ( CTEId#4=] )
------------------PhysicalDistribute
--------------------hashJoin[INNER_JOIN](web_sales.ws_sold_date_sk =
date_dim.d_date_sk)
----------------------PhysicalProject
-------------------------filter((('w' = 's') OR ('w' = 'w')))
+------------------------filter('w' IN ('s', 'w'))
--------------------------PhysicalOlapScan[web_sales]
----------------------PhysicalDistribute
------------------------PhysicalProject
---------------------------filter(((date_dim.d_year = 1999) OR (date_dim.d_year
= 2000))(('w' = 's') OR ('w' = 'w')))
+--------------------------filter(d_year IN (2000, 1999)'w' IN ('s', 'w'))
----------------------------PhysicalOlapScan[date_dim]
------------------PhysicalDistribute
--------------------PhysicalProject
-----------------------filter((('w' = 's') OR ('w' = 'w')))
+----------------------filter('w' IN ('s', 'w'))
------------------------PhysicalOlapScan[customer]
--PhysicalTopN
----PhysicalDistribute
diff --git
a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query75.out
b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query75.out
index 1b278e9990..0ad25cc5ec 100644
--- a/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query75.out
+++ b/regression-test/data/nereids_tpcds_shape_sf100_p0/shape/query75.out
@@ -23,7 +23,7 @@ CteAnchor[cteId= ( CTEId#3=] )
--------------------------------PhysicalOlapScan[item]
------------------------PhysicalDistribute
--------------------------PhysicalProject
-----------------------------filter(((date_dim.d_year = 1998) OR
(date_dim.d_year = 1999)))
+----------------------------filter(d_year IN (1998, 1999))
------------------------------PhysicalOlapScan[date_dim]
--------------PhysicalDistribute
----------------PhysicalProject
@@ -41,7 +41,7 @@ CteAnchor[cteId= ( CTEId#3=] )
--------------------------------PhysicalOlapScan[item]
------------------------PhysicalDistribute
--------------------------PhysicalProject
-----------------------------filter(((date_dim.d_year = 1998) OR
(date_dim.d_year = 1999)))
+----------------------------filter(d_year IN (1998, 1999))
------------------------------PhysicalOlapScan[date_dim]
--------------PhysicalDistribute
----------------PhysicalProject
@@ -59,7 +59,7 @@ CteAnchor[cteId= ( CTEId#3=] )
--------------------------------PhysicalOlapScan[item]
------------------------PhysicalDistribute
--------------------------PhysicalProject
-----------------------------filter(((date_dim.d_year = 1998) OR
(date_dim.d_year = 1999)))
+----------------------------filter(d_year IN (1998, 1999))
------------------------------PhysicalOlapScan[date_dim]
--PhysicalTopN
----PhysicalDistribute
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
