This is an automated email from the ASF dual-hosted git repository.
englefly 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 bd07f89bd1c [fix](nereids) fix distribute expr list (#39148)
bd07f89bd1c is described below
commit bd07f89bd1ce502f62acb2ccec8e48e888a729b5
Author: xzj7019 <[email protected]>
AuthorDate: Thu Aug 15 18:35:16 2024 +0800
[fix](nereids) fix distribute expr list (#39148)
## Proposed changes
1. Replace outputProperties with requestProperties as physical plan's
physicalProperties.
2. Refine physical broadcast hash join's output properties with
equivalence expr sets information.
---------
Co-authored-by: zhongjian.xzj
<[email protected]>
Co-authored-by: zhongjian.xzj <[email protected]>
---
.../org/apache/doris/nereids/NereidsPlanner.java | 3 +-
.../properties/ChildOutputPropertyDeriver.java | 50 +++++++-
.../properties/ChildOutputPropertyDeriverTest.java | 36 +++---
.../data/nereids_p0/hint/test_leading.out | 4 +-
.../suites/nereids_p0/join/test_join.groovy | 1 -
.../distribute/distribution_expr.groovy | 130 +++++++++++++++++++++
.../suites/query_p0/join/test_join.groovy | 1 -
.../suites/tpcds_sf1_unique_p1/spill/q32.groovy | 1 -
.../suites/tpcds_sf1_unique_p1/sql/q24_1.sql | 92 +++++++--------
.../suites/tpcds_sf1_unique_p1/sql/q24_2.sql | 93 ++++++++-------
.../suites/tpcds_sf1_unique_p1/sql/q32.sql | 1 -
.../suites/tpcds_sf1_unique_p1/sql/q92.sql | 1 -
.../suites/tpch_sf0.1_unique_p1/sql/q02.sql | 1 -
.../tpch_unique_sql_zstd_bucket1_p0/sql/q02.sql | 2 +-
14 files changed, 296 insertions(+), 120 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/NereidsPlanner.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/NereidsPlanner.java
index 78d2025f40d..87a2003f9f9 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/NereidsPlanner.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/NereidsPlanner.java
@@ -482,8 +482,7 @@ public class NereidsPlanner extends Planner {
// add groupExpression to plan so that we could print group id in
plan.treeString()
plan = plan.withGroupExpression(Optional.of(groupExpression));
PhysicalPlan physicalPlan = ((PhysicalPlan)
plan).withPhysicalPropertiesAndStats(
- groupExpression.getOutputProperties(physicalProperties),
- groupExpression.getOwnerGroup().getStatistics());
+ physicalProperties,
groupExpression.getOwnerGroup().getStatistics());
return physicalPlan;
} catch (Exception e) {
if (e instanceof AnalysisException &&
e.getMessage().contains("Failed to choose best plan")) {
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/properties/ChildOutputPropertyDeriver.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/properties/ChildOutputPropertyDeriver.java
index 21efad32563..4f4a8bebfdf 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/properties/ChildOutputPropertyDeriver.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/properties/ChildOutputPropertyDeriver.java
@@ -258,8 +258,30 @@ public class ChildOutputPropertyDeriver extends
PlanVisitor<PhysicalProperties,
// broadcast
if (rightOutputProperty.getDistributionSpec() instanceof
DistributionSpecReplicated) {
- DistributionSpec parentDistributionSpec =
leftOutputProperty.getDistributionSpec();
- return new PhysicalProperties(parentDistributionSpec);
+ DistributionSpec leftDistributionSpec =
leftOutputProperty.getDistributionSpec();
+ // if left side is hash distribute and the key can satisfy the
join keys, then mock
+ // a right side hash spec with the corresponding join keys, to
filling the returning spec
+ // with refined EquivalenceExprIds.
+ if (leftDistributionSpec instanceof DistributionSpecHash
+ && !(hashJoin.isMarkJoin() &&
hashJoin.getHashJoinConjuncts().isEmpty())
+ && !hashJoin.getHashConjunctsExprIds().first.isEmpty()
+ && !hashJoin.getHashConjunctsExprIds().second.isEmpty()
+ && hashJoin.getHashConjunctsExprIds().first.size()
+ == hashJoin.getHashConjunctsExprIds().second.size()
+ && leftDistributionSpec.satisfy(
+ new
DistributionSpecHash(hashJoin.getHashConjunctsExprIds().first,
ShuffleType.REQUIRE))) {
+ DistributionSpecHash mockedRightHashSpec =
mockAnotherSideSpecFromConjuncts(
+ hashJoin, (DistributionSpecHash) leftDistributionSpec);
+ if (SessionVariable.canUseNereidsDistributePlanner()) {
+ return computeShuffleJoinOutputProperties(hashJoin,
+ (DistributionSpecHash) leftDistributionSpec,
mockedRightHashSpec);
+ } else {
+ return legacyComputeShuffleJoinOutputProperties(hashJoin,
+ (DistributionSpecHash) leftDistributionSpec,
mockedRightHashSpec);
+ }
+ } else {
+ return new PhysicalProperties(leftDistributionSpec);
+ }
}
// shuffle
@@ -581,6 +603,30 @@ public class ChildOutputPropertyDeriver extends
PlanVisitor<PhysicalProperties,
}
}
+ private DistributionSpecHash mockAnotherSideSpecFromConjuncts(
+ PhysicalHashJoin<? extends Plan, ? extends Plan> hashJoin,
DistributionSpecHash oneSideSpec) {
+ List<ExprId> leftExprIds = hashJoin.getHashConjunctsExprIds().first;
+ List<ExprId> rightExprIds = hashJoin.getHashConjunctsExprIds().second;
+ Preconditions.checkState(!leftExprIds.isEmpty() &&
!rightExprIds.isEmpty()
+ && leftExprIds.size() == rightExprIds.size(), "invalid hash
join conjuncts");
+ List<ExprId> anotherSideOrderedExprIds = Lists.newArrayList();
+ for (ExprId exprId : oneSideSpec.getOrderedShuffledColumns()) {
+ int index = leftExprIds.indexOf(exprId);
+ if (index == -1) {
+ Set<ExprId> equivalentExprIds =
oneSideSpec.getEquivalenceExprIdsOf(exprId);
+ for (ExprId id : equivalentExprIds) {
+ index = leftExprIds.indexOf(id);
+ if (index >= 0) {
+ break;
+ }
+ }
+ Preconditions.checkState(index >= 0, "can't find exprId in
equivalence set");
+ }
+ anotherSideOrderedExprIds.add(rightExprIds.get(index));
+ }
+ return new DistributionSpecHash(anotherSideOrderedExprIds,
oneSideSpec.getShuffleType());
+ }
+
private boolean isSameHashValue(DataType originType, DataType castType) {
if (originType.isStringLikeType() && (castType.isVarcharType() ||
castType.isStringType())
&& (castType.width() >= originType.width() || castType.width()
< 0)) {
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/properties/ChildOutputPropertyDeriverTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/properties/ChildOutputPropertyDeriverTest.java
index e6a7e601c24..91738e3cca4 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/nereids/properties/ChildOutputPropertyDeriverTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/properties/ChildOutputPropertyDeriverTest.java
@@ -19,14 +19,17 @@ package org.apache.doris.nereids.properties;
import org.apache.doris.catalog.ColocateTableIndex;
import org.apache.doris.catalog.Env;
+import org.apache.doris.common.IdGenerator;
import org.apache.doris.common.Pair;
import org.apache.doris.nereids.hint.DistributeHint;
import org.apache.doris.nereids.memo.Group;
import org.apache.doris.nereids.memo.GroupExpression;
+import org.apache.doris.nereids.memo.GroupId;
import org.apache.doris.nereids.properties.DistributionSpecHash.ShuffleType;
import org.apache.doris.nereids.trees.expressions.AssertNumRowsElement;
import org.apache.doris.nereids.trees.expressions.EqualTo;
import org.apache.doris.nereids.trees.expressions.ExprId;
+import org.apache.doris.nereids.trees.expressions.Slot;
import org.apache.doris.nereids.trees.expressions.SlotReference;
import org.apache.doris.nereids.trees.expressions.functions.agg.AggregateParam;
import org.apache.doris.nereids.trees.plans.AggMode;
@@ -63,6 +66,7 @@ import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
+import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;
@@ -529,21 +533,25 @@ class ChildOutputPropertyDeriverTest {
}
@Test
- void testBroadcastJoin() {
- new MockUp<JoinUtils>() {
- @Mock
- Pair<List<ExprId>, List<ExprId>> getOnClauseUsedSlots(
- AbstractPhysicalJoin<? extends Plan, ? extends Plan> join)
{
- return Pair.of(Lists.newArrayList(new ExprId(0)),
Lists.newArrayList(new ExprId(2)));
- }
- };
-
+ void testBroadcastJoin(@Injectable LogicalProperties p1, @Injectable
GroupPlan p2) {
+ SlotReference leftSlot = new SlotReference(new ExprId(0), "left",
IntegerType.INSTANCE, false, Collections.emptyList());
+ SlotReference rightSlot = new SlotReference(new ExprId(2), "right",
IntegerType.INSTANCE, false, Collections.emptyList());
+ List<Slot> leftOutput = new ArrayList<>();
+ List<Slot> rightOutput = new ArrayList<>();
+ leftOutput.add(leftSlot);
+ rightOutput.add(rightSlot);
+ LogicalProperties leftProperties = new LogicalProperties(() ->
leftOutput, () -> DataTrait.EMPTY_TRAIT);
+ LogicalProperties rightProperties = new LogicalProperties(() ->
rightOutput, () -> DataTrait.EMPTY_TRAIT);
+
+ IdGenerator<GroupId> idGenerator = GroupId.createGenerator();
+ GroupPlan leftGroupPlan = new GroupPlan(new
Group(idGenerator.getNextId(), leftProperties));
+ GroupPlan rightGroupPlan = new GroupPlan(new
Group(idGenerator.getNextId(), rightProperties));
PhysicalHashJoin<GroupPlan, GroupPlan> join = new
PhysicalHashJoin<>(JoinType.INNER_JOIN,
Lists.newArrayList(new EqualTo(
- new SlotReference(new ExprId(0), "left",
IntegerType.INSTANCE, false, Collections.emptyList()),
- new SlotReference(new ExprId(2), "right",
IntegerType.INSTANCE, false,
- Collections.emptyList()))),
- ExpressionUtils.EMPTY_CONDITION, new
DistributeHint(DistributeType.NONE), Optional.empty(), logicalProperties,
groupPlan, groupPlan);
+ leftSlot, rightSlot
+ )),
+ ExpressionUtils.EMPTY_CONDITION, new
DistributeHint(DistributeType.NONE),
+ Optional.empty(), logicalProperties, leftGroupPlan,
rightGroupPlan);
GroupExpression groupExpression = new GroupExpression(join);
new Group(null, groupExpression, null);
@@ -572,7 +580,7 @@ class ChildOutputPropertyDeriverTest {
DistributionSpecHash actual = (DistributionSpecHash)
result.getDistributionSpec();
Assertions.assertEquals(ShuffleType.NATURAL, actual.getShuffleType());
// check merged
- Assertions.assertEquals(2, actual.getExprIdToEquivalenceSet().size());
+ Assertions.assertEquals(3, actual.getExprIdToEquivalenceSet().size());
}
@Test
diff --git a/regression-test/data/nereids_p0/hint/test_leading.out
b/regression-test/data/nereids_p0/hint/test_leading.out
index 7d397578838..7b0c9c4bcb0 100644
--- a/regression-test/data/nereids_p0/hint/test_leading.out
+++ b/regression-test/data/nereids_p0/hint/test_leading.out
@@ -2166,7 +2166,7 @@ PhysicalResultSink
--hashAgg[GLOBAL]
----PhysicalDistribute[DistributionSpecGather]
------hashAgg[LOCAL]
---------hashJoin[INNER_JOIN shuffle] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
+--------hashJoin[INNER_JOIN bucketShuffle] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
----------hashJoin[INNER_JOIN broadcast] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
------------PhysicalOlapScan[t1]
------------PhysicalOlapScan[t2]
@@ -2614,7 +2614,7 @@ PhysicalResultSink
--hashAgg[GLOBAL]
----PhysicalDistribute[DistributionSpecGather]
------hashAgg[LOCAL]
---------hashJoin[INNER_JOIN shuffle] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
+--------hashJoin[INNER_JOIN bucketShuffle] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
----------hashJoin[INNER_JOIN broadcast] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
------------PhysicalOlapScan[t1]
------------PhysicalOlapScan[t2]
diff --git a/regression-test/suites/nereids_p0/join/test_join.groovy
b/regression-test/suites/nereids_p0/join/test_join.groovy
index 0bf27f430f7..5336c33913b 100644
--- a/regression-test/suites/nereids_p0/join/test_join.groovy
+++ b/regression-test/suites/nereids_p0/join/test_join.groovy
@@ -37,7 +37,6 @@ suite("test_join", "nereids_p0") {
qt_join1 """select sum(t1.k1), sum(t1.k3), max(t1.k5), max(t2.k4) from
${tbName1} t1 inner join ${tbName2} t2 on t1.k1 = t2.k1 and
t1.k6 is not null and t2.k6 is not null"""
qt_join2 """select k1, k2, k3 from ${tbName1} where k7 is not null order
by 1 desc, 2 desc, 3 desc limit 10"""
- sql "set enable_local_shuffle=false;"
qt_join3 """select c.k1, c.k8 from ${tbName2} d join (select a.k1 as k1,
a.k8 from ${tbName1} a join ${tbName2} b on (a.k1=b.k1)) c
on c.k1 = d.k1 order by 1, 2"""
qt_join4 """select a.k1, b.k1 from ${tbName2} a join (select k1, k2 from
${tbName1} order by k1 limit 10) b
diff --git
a/regression-test/suites/nereids_syntax_p0/distribute/distribution_expr.groovy
b/regression-test/suites/nereids_syntax_p0/distribute/distribution_expr.groovy
new file mode 100644
index 00000000000..55f6fd78402
--- /dev/null
+++
b/regression-test/suites/nereids_syntax_p0/distribute/distribution_expr.groovy
@@ -0,0 +1,130 @@
+// 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("distribution_expr") {
+ multi_sql """
+ drop table if exists
table_100_undef_partitions2_keys3_properties4_distributed_by52;
+ drop table if exists
table_6_undef_partitions2_keys3_properties4_distributed_by53;
+ drop table if exists
table_7_undef_partitions2_keys3_properties4_distributed_by5;
+ drop table if exists
table_8_undef_partitions2_keys3_properties4_distributed_by5;
+
+ create table
table_100_undef_partitions2_keys3_properties4_distributed_by52 (
+ `pk` int,
+ `col_int_undef_signed` int ,
+ `col_varchar_10__undef_signed` varchar(10) ,
+ `col_varchar_1024__undef_signed` varchar(1024) MAX
+ ) engine=olap
+ AGGREGATE KEY(pk, col_int_undef_signed, col_varchar_10__undef_signed)
+ distributed by hash(pk) buckets 10
+ properties("replication_num" = "1");
+ insert into
table_100_undef_partitions2_keys3_properties4_distributed_by52(pk,col_int_undef_signed,col_varchar_10__undef_signed,col_varchar_1024__undef_signed)
values
(0,null,'k',"mean"),(1,null,"I'll","who"),(2,null,"i",'v'),(3,null,"really",'w'),(4,null,"when",'e'),(5,9,"what","who"),(6,5,"been","like"),(7,null,"and","ok"),(8,3,'t',"then"),(9,null,'y',"up"),(10,8,'b',"think"),(11,0,"if",'l'),(12,null,"there",'q'),(13,null,"out",'q'),(14,3,'c','h'),(15,2,"out","yes"),(16,null,'t',"thin
[...]
+
+
+ create table
table_6_undef_partitions2_keys3_properties4_distributed_by53 (
+ `pk` int,
+ `col_varchar_10__undef_signed` varchar(10) ,
+ `col_int_undef_signed` int ,
+ `col_varchar_1024__undef_signed` varchar(1024)
+ ) engine=olap
+ DUPLICATE KEY(pk, col_varchar_10__undef_signed)
+ distributed by hash(pk) buckets 10
+ properties("replication_num" = "1");
+ insert into
table_6_undef_partitions2_keys3_properties4_distributed_by53(pk,col_int_undef_signed,col_varchar_10__undef_signed,col_varchar_1024__undef_signed)
values
(0,0,"think","she"),(1,null,"was",'r'),(2,8,'g',"i"),(3,9,'s',"he's"),(4,4,"they",'n'),(5,null,"time","really");
+
+ create table
table_7_undef_partitions2_keys3_properties4_distributed_by5 (
+ `col_int_undef_signed` int/*agg_type_placeholder*/ ,
+ `col_varchar_10__undef_signed` varchar(10)/*agg_type_placeholder*/ ,
+ `col_varchar_1024__undef_signed` varchar(1024)/*agg_type_placeholder*/
,
+ `pk` int/*agg_type_placeholder*/
+ ) engine=olap
+ distributed by hash(pk) buckets 10
+ properties("replication_num" = "1");
+ insert into
table_7_undef_partitions2_keys3_properties4_distributed_by5(pk,col_int_undef_signed,col_varchar_10__undef_signed,col_varchar_1024__undef_signed)
values
(0,7,'y','g'),(1,null,'t',"can"),(2,8,"would",'l'),(3,null,"will","he"),(4,null,'k',"I'll"),(5,null,'m',"ok"),(6,null,'s',"that");
+
+ create table
table_8_undef_partitions2_keys3_properties4_distributed_by5 (
+ `col_int_undef_signed` int/*agg_type_placeholder*/ ,
+ `col_varchar_10__undef_signed` varchar(10)/*agg_type_placeholder*/ ,
+ `col_varchar_1024__undef_signed` varchar(1024)/*agg_type_placeholder*/
,
+ `pk` int/*agg_type_placeholder*/
+ ) engine=olap
+ distributed by hash(pk) buckets 10
+ properties("replication_num" = "1");
+ insert into
table_8_undef_partitions2_keys3_properties4_distributed_by5(pk,col_int_undef_signed,col_varchar_10__undef_signed,col_varchar_1024__undef_signed)
values
(0,4,'d',"she"),(1,3,"okay",'e'),(2,null,'s',"as"),(3,null,"you","up"),(4,3,'f','q'),(5,null,'f','s'),(6,null,'h',"time"),(7,8,'o',"i");
+
+ set enable_nereids_distribute_planner=true;
+ set enable_pipeline_x_engine=true;
+ set disable_join_reorder=true;
+ set enable_local_shuffle=true;
+ set force_to_local_shuffle=true;
+ """
+
+ explain {
+ sql """
+ SELECT *
+ FROM
+ (SELECT alias3.`pk` from
table_100_undef_partitions2_keys3_properties4_distributed_by52 AS alias4 INNER
JOIN
+
table_6_undef_partitions2_keys3_properties4_distributed_by53 AS alias3
+ ON alias3.`pk` = alias4.`pk`
+ WHERE (alias3.`pk` < alias4.`pk` OR alias3.`pk` <= 4 )
+ ) tmp2
+ INNER JOIN[shuffle]
+ (select alias1.pk from
table_7_undef_partitions2_keys3_properties4_distributed_by5 AS alias1
+ LEFT JOIN
table_8_undef_partitions2_keys3_properties4_distributed_by5 AS alias2
+ ON alias1.`col_varchar_10__undef_signed` =
alias2.`col_varchar_1024__undef_signed`) tmp1
+ ON tmp1 . `pk` = tmp2 . `pk`;
+ """
+ contains "BUCKET_SHUFFLE"
+ contains "distribute expr lists: pk[#22]"
+ contains "distribute expr lists: pk[#11]"
+ }
+
+ multi_sql """
+ drop table if exists baseall;
+ drop table if exists test;
+ CREATE TABLE IF NOT EXISTS `baseall` (
+ `k1` tinyint(4) null comment ""
+ ) engine=olap
+ DISTRIBUTED BY HASH(`k1`) BUCKETS 3 properties("replication_num" =
"1");
+
+ CREATE TABLE IF NOT EXISTS `test` (
+ `k1` tinyint(4) null comment ""
+ ) engine=olap
+ DISTRIBUTED BY HASH(`k1`) BUCKETS 3 properties("replication_num" =
"1");
+
+ insert into baseall values (1);
+ insert into baseall values (2);
+ insert into baseall values (3);
+ insert into test values (1);
+ insert into test values (2);
+ insert into test values (3);
+
+ set enable_nereids_distribute_planner=true;
+ set enable_pipeline_x_engine=true;
+ set disable_join_reorder=true;
+ set enable_local_shuffle=true;
+ set force_to_local_shuffle=true;
+ """
+
+ explain {
+ sql """
+ select tmp.k1 from baseall d join (select a.k1 as k1 from
baseall b join test a on (a.k1=b.k1)) tmp on tmp.k1 = d.k1;
+ """
+ contains "COLOCATE"
+ contains "distribute expr lists: k1[#5]"
+ contains "distribute expr lists: k1[#4]"
+ }
+}
diff --git a/regression-test/suites/query_p0/join/test_join.groovy
b/regression-test/suites/query_p0/join/test_join.groovy
index 459187fa55a..e75878cb032 100644
--- a/regression-test/suites/query_p0/join/test_join.groovy
+++ b/regression-test/suites/query_p0/join/test_join.groovy
@@ -36,7 +36,6 @@ suite("test_join", "query,p0") {
qt_join1 """select sum(t1.k1), sum(t1.k3), max(t1.k5), max(t2.k4) from
test t1 inner join baseall t2 on t1.k1 = t2.k1 and
t1.k6 is not null and t2.k6 is not null"""
qt_join2 """select k1, k2, k3 from test where k7 is not null order by 1
desc, 2 desc, 3 desc limit 10"""
- sql "set enable_local_shuffle=false;"
qt_join3 """select c.k1, c.k8 from baseall d join (select a.k1 as k1, a.k8
from test a join baseall b on (a.k1=b.k1)) c
on c.k1 = d.k1 order by 1, 2"""
qt_join4 """select a.k1, b.k1 from baseall a join (select k1, k2 from test
order by k1 limit 10) b
diff --git a/regression-test/suites/tpcds_sf1_unique_p1/spill/q32.groovy
b/regression-test/suites/tpcds_sf1_unique_p1/spill/q32.groovy
index 7f20c11664e..61752998ac1 100644
--- a/regression-test/suites/tpcds_sf1_unique_p1/spill/q32.groovy
+++ b/regression-test/suites/tpcds_sf1_unique_p1/spill/q32.groovy
@@ -24,7 +24,6 @@ suite("q32_spill") {
sql """
use regression_test_tpcds_sf1_unique_p1;
"""
- sql "set enable_local_shuffle=false"
qt_q32 """
SELECT sum(cs_ext_discount_amt) 'excess discount amount'
FROM
diff --git a/regression-test/suites/tpcds_sf1_unique_p1/sql/q24_1.sql
b/regression-test/suites/tpcds_sf1_unique_p1/sql/q24_1.sql
index e3188d8380c..65571698e1e 100644
--- a/regression-test/suites/tpcds_sf1_unique_p1/sql/q24_1.sql
+++ b/regression-test/suites/tpcds_sf1_unique_p1/sql/q24_1.sql
@@ -1,46 +1,46 @@
--- WITH
--- ssales AS (
--- SELECT
--- c_last_name
--- , c_first_name
--- , s_store_name
--- , ca_state
--- , s_state
--- , i_color
--- , i_current_price
--- , i_manager_id
--- , i_units
--- , i_size
--- , sum(ss_net_paid) netpaid
--- FROM
--- store_sales
--- , store_returns
--- , store
--- , item
--- , customer
--- , customer_address
--- WHERE (ss_ticket_number = sr_ticket_number)
--- AND (ss_item_sk = sr_item_sk)
--- AND (ss_customer_sk = c_customer_sk)
--- AND (ss_item_sk = i_item_sk)
--- AND (ss_store_sk = s_store_sk)
--- AND (c_birth_country = upper(ca_country))
--- AND (s_zip = ca_zip)
--- AND (s_market_id = 8)
--- GROUP BY c_last_name, c_first_name, s_store_name, ca_state, s_state,
i_color, i_current_price, i_manager_id, i_units, i_size
--- )
--- SELECT
--- c_last_name
--- , c_first_name
--- , s_store_name
--- , sum(netpaid) paid
--- FROM
--- ssales
--- WHERE (i_color = 'pale')
--- GROUP BY c_last_name, c_first_name, s_store_name
--- HAVING (sum(netpaid) > (
--- SELECT (CAST('0.05' AS DECIMAL(5,2)) * avg(netpaid))
--- FROM
--- ssales
--- ))
--- ORDER BY c_last_name, c_first_name, s_store_name
+WITH
+ ssales AS (
+ SELECT
+ c_last_name
+ , c_first_name
+ , s_store_name
+ , ca_state
+ , s_state
+ , i_color
+ , i_current_price
+ , i_manager_id
+ , i_units
+ , i_size
+ , sum(ss_net_paid) netpaid
+ FROM
+ store_sales
+ , store_returns
+ , store
+ , item
+ , customer
+ , customer_address
+ WHERE (ss_ticket_number = sr_ticket_number)
+ AND (ss_item_sk = sr_item_sk)
+ AND (ss_customer_sk = c_customer_sk)
+ AND (ss_item_sk = i_item_sk)
+ AND (ss_store_sk = s_store_sk)
+ AND (c_birth_country = upper(ca_country))
+ AND (s_zip = ca_zip)
+ AND (s_market_id = 8)
+ GROUP BY c_last_name, c_first_name, s_store_name, ca_state, s_state,
i_color, i_current_price, i_manager_id, i_units, i_size
+)
+SELECT
+ c_last_name
+, c_first_name
+, s_store_name
+, sum(netpaid) paid
+FROM
+ ssales
+WHERE (i_color = 'pale')
+GROUP BY c_last_name, c_first_name, s_store_name
+HAVING (sum(netpaid) > (
+ SELECT (CAST('0.05' AS DECIMAL(5,2)) * avg(netpaid))
+ FROM
+ ssales
+ ))
+ORDER BY c_last_name, c_first_name, s_store_name
diff --git a/regression-test/suites/tpcds_sf1_unique_p1/sql/q24_2.sql
b/regression-test/suites/tpcds_sf1_unique_p1/sql/q24_2.sql
index 2df00d4d987..db04507c9c9 100644
--- a/regression-test/suites/tpcds_sf1_unique_p1/sql/q24_2.sql
+++ b/regression-test/suites/tpcds_sf1_unique_p1/sql/q24_2.sql
@@ -1,47 +1,46 @@
--- WITH
--- ssales AS (
--- SELECT
--- c_last_name
--- , c_first_name
--- , s_store_name
--- , ca_state
--- , s_state
--- , i_color
--- , i_current_price
--- , i_manager_id
--- , i_units
--- , i_size
--- , sum(ss_net_paid) netpaid
--- FROM
--- store_sales
--- , store_returns
--- , store
--- , item
--- , customer
--- , customer_address
--- WHERE (ss_ticket_number = sr_ticket_number)
--- AND (ss_item_sk = sr_item_sk)
--- AND (ss_customer_sk = c_customer_sk)
--- AND (ss_item_sk = i_item_sk)
--- AND (ss_store_sk = s_store_sk)
--- AND (c_birth_country = upper(ca_country))
--- AND (s_zip = ca_zip)
--- AND (s_market_id = 8)
--- GROUP BY c_last_name, c_first_name, s_store_name, ca_state, s_state,
i_color, i_current_price, i_manager_id, i_units, i_size
--- )
--- SELECT
--- /*+ SET_VAR(enable_local_shuffle=false) */
--- c_last_name
--- , c_first_name
--- , s_store_name
--- , sum(netpaid) paid
--- FROM
--- ssales
--- WHERE (i_color = 'chiffon')
--- GROUP BY c_last_name, c_first_name, s_store_name
--- HAVING (sum(netpaid) > (
--- SELECT (CAST('0.05' AS DECIMAL(5,2)) * avg(netpaid))
--- FROM
--- ssales
--- ))
--- ORDER BY c_last_name, c_first_name, s_store_name
+WITH
+ ssales AS (
+ SELECT
+ c_last_name
+ , c_first_name
+ , s_store_name
+ , ca_state
+ , s_state
+ , i_color
+ , i_current_price
+ , i_manager_id
+ , i_units
+ , i_size
+ , sum(ss_net_paid) netpaid
+ FROM
+ store_sales
+ , store_returns
+ , store
+ , item
+ , customer
+ , customer_address
+ WHERE (ss_ticket_number = sr_ticket_number)
+ AND (ss_item_sk = sr_item_sk)
+ AND (ss_customer_sk = c_customer_sk)
+ AND (ss_item_sk = i_item_sk)
+ AND (ss_store_sk = s_store_sk)
+ AND (c_birth_country = upper(ca_country))
+ AND (s_zip = ca_zip)
+ AND (s_market_id = 8)
+ GROUP BY c_last_name, c_first_name, s_store_name, ca_state, s_state,
i_color, i_current_price, i_manager_id, i_units, i_size
+)
+SELECT
+ c_last_name
+, c_first_name
+, s_store_name
+, sum(netpaid) paid
+FROM
+ ssales
+WHERE (i_color = 'chiffon')
+GROUP BY c_last_name, c_first_name, s_store_name
+HAVING (sum(netpaid) > (
+ SELECT (CAST('0.05' AS DECIMAL(5,2)) * avg(netpaid))
+ FROM
+ ssales
+ ))
+ORDER BY c_last_name, c_first_name, s_store_name
diff --git a/regression-test/suites/tpcds_sf1_unique_p1/sql/q32.sql
b/regression-test/suites/tpcds_sf1_unique_p1/sql/q32.sql
index 7aaf9c269d9..c80c6c142a1 100644
--- a/regression-test/suites/tpcds_sf1_unique_p1/sql/q32.sql
+++ b/regression-test/suites/tpcds_sf1_unique_p1/sql/q32.sql
@@ -1,5 +1,4 @@
SELECT
-/*+ SET_VAR(enable_local_shuffle=false) */
sum(cs_ext_discount_amt) 'excess discount amount'
FROM
catalog_sales
diff --git a/regression-test/suites/tpcds_sf1_unique_p1/sql/q92.sql
b/regression-test/suites/tpcds_sf1_unique_p1/sql/q92.sql
index c4cb3a0881d..a03f1b9dc18 100644
--- a/regression-test/suites/tpcds_sf1_unique_p1/sql/q92.sql
+++ b/regression-test/suites/tpcds_sf1_unique_p1/sql/q92.sql
@@ -1,5 +1,4 @@
SELECT
-/*+ SET_VAR(enable_local_shuffle=false) */
sum(ws_ext_discount_amt) 'Excess Discount Amount'
FROM
web_sales
diff --git a/regression-test/suites/tpch_sf0.1_unique_p1/sql/q02.sql
b/regression-test/suites/tpch_sf0.1_unique_p1/sql/q02.sql
index 49ae6a177fc..8c60ec19293 100644
--- a/regression-test/suites/tpch_sf0.1_unique_p1/sql/q02.sql
+++ b/regression-test/suites/tpch_sf0.1_unique_p1/sql/q02.sql
@@ -1,6 +1,5 @@
-- tables: part,supplier,partsupp,nation,region
SELECT
-/*+ SET_VAR(enable_local_shuffle=false) */
s_acctbal,
s_name,
n_name,
diff --git a/regression-test/suites/tpch_unique_sql_zstd_bucket1_p0/sql/q02.sql
b/regression-test/suites/tpch_unique_sql_zstd_bucket1_p0/sql/q02.sql
index e7f55f0fe76..31ac56d7f0c 100644
--- a/regression-test/suites/tpch_unique_sql_zstd_bucket1_p0/sql/q02.sql
+++ b/regression-test/suites/tpch_unique_sql_zstd_bucket1_p0/sql/q02.sql
@@ -1,6 +1,6 @@
-- tables: part,supplier,partsupp,nation,region
-SELECT /*+ SET_VAR(enable_local_shuffle=false) */
+SELECT
s_acctbal,
s_name,
n_name,
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
