This is an automated email from the ASF dual-hosted git repository. yiguolei pushed a commit to branch branch-2.1 in repository https://gitbox.apache.org/repos/asf/doris.git
commit e560f316921d34f1a4180ce5090e688c6e98815d Author: 谢健 <[email protected]> AuthorDate: Fri Jan 19 11:18:52 2024 +0800 [fix](Nereids): fix eliminate join test for pk-fk constraint (#30094) --- .../data/nereids_rules_p0/pkfk/eliminate_inner.out | 251 ++++++++------------- .../nereids_rules_p0/pkfk/eliminate_inner.groovy | 16 +- 2 files changed, 102 insertions(+), 165 deletions(-) diff --git a/regression-test/data/nereids_rules_p0/pkfk/eliminate_inner.out b/regression-test/data/nereids_rules_p0/pkfk/eliminate_inner.out index bb2b48ac37d..d1e060e7dd0 100644 --- a/regression-test/data/nereids_rules_p0/pkfk/eliminate_inner.out +++ b/regression-test/data/nereids_rules_p0/pkfk/eliminate_inner.out @@ -4,152 +4,129 @@ simple_case -- !shape -- PhysicalResultSink ---PhysicalDistribute[DistributionSpecGather] -----hashJoin[INNER_JOIN] hashCondition=((pkt.pk = fkt_not_null.fk)) otherCondition=() -------PhysicalOlapScan[pkt] -------PhysicalDistribute[DistributionSpecHash] ---------PhysicalOlapScan[fkt_not_null] +--PhysicalOlapScan[fkt_not_null] -- !res -- +1 John +2 Alice +3 Bob -- !name -- with_pk_col -- !shape -- PhysicalResultSink ---PhysicalDistribute[DistributionSpecGather] -----hashJoin[INNER_JOIN] hashCondition=((pkt.pk = fkt_not_null.fk)) otherCondition=() -------PhysicalOlapScan[pkt] -------PhysicalDistribute[DistributionSpecHash] ---------PhysicalOlapScan[fkt_not_null] +--PhysicalOlapScan[fkt_not_null] -- !res -- +1 John 1 +2 Alice 2 +3 Bob 3 -- !name -- with_pk_col -- !shape -- PhysicalResultSink ---PhysicalDistribute[DistributionSpecGather] -----hashJoin[INNER_JOIN] hashCondition=((pkt.pk = fkt_not_null.fk)) otherCondition=() -------PhysicalOlapScan[pkt] -------PhysicalDistribute[DistributionSpecHash] ---------hashJoin[INNER_JOIN] hashCondition=((fkt_not_null1.fk = fkt_not_null2.fk)) otherCondition=() -----------PhysicalOlapScan[fkt_not_null] -----------PhysicalOlapScan[fkt_not_null] +--hashJoin[INNER_JOIN] hashCondition=((pk = fkt_not_null2.fk)) otherCondition=() +----PhysicalOlapScan[fkt_not_null] +----PhysicalOlapScan[fkt_not_null] -- !res -- +1 John 1 +2 Alice 2 +3 Bob 3 -- !name -- with_pk_col -- !shape -- PhysicalResultSink ---PhysicalDistribute[DistributionSpecGather] -----hashJoin[INNER_JOIN] hashCondition=((pkt.pk = fkt_not_null.fk)) otherCondition=() -------filter((pkt.pk > 1)) ---------PhysicalOlapScan[pkt] -------PhysicalDistribute[DistributionSpecHash] ---------hashJoin[INNER_JOIN] hashCondition=((fkt_not_null1.fk = fkt_not_null2.fk)) otherCondition=() -----------filter((fkt_not_null1.fk > 1)) -------------PhysicalOlapScan[fkt_not_null] -----------filter((fkt_not_null2.fk > 1)) -------------PhysicalOlapScan[fkt_not_null] +--hashJoin[INNER_JOIN] hashCondition=((pk = fkt_not_null2.fk)) otherCondition=() +----filter((fkt_not_null1.fk > 1)) +------PhysicalOlapScan[fkt_not_null] +----filter((fkt_not_null2.fk > 1)) +------PhysicalOlapScan[fkt_not_null] -- !res -- +2 Alice 2 +3 Bob 3 -- !name -- with_pk_col -- !shape -- PhysicalResultSink ---PhysicalDistribute[DistributionSpecGather] -----hashJoin[INNER_JOIN] hashCondition=((pkt.pk = fkt_not_null.fk)) otherCondition=() -------PhysicalOlapScan[pkt] -------PhysicalDistribute[DistributionSpecHash] ---------hashAgg[LOCAL] -----------PhysicalOlapScan[fkt_not_null] +--hashAgg[LOCAL] +----PhysicalOlapScan[fkt_not_null] -- !res -- +1 1 +2 2 +3 3 -- !name -- with_pk_col -- !shape -- PhysicalResultSink ---PhysicalDistribute[DistributionSpecGather] -----hashJoin[INNER_JOIN] hashCondition=((pkt.pk = fkt_not_null.fk)) otherCondition=() -------PhysicalOlapScan[pkt] -------PhysicalDistribute[DistributionSpecHash] ---------hashAgg[GLOBAL] -----------PhysicalDistribute[DistributionSpecHash] -------------hashAgg[LOCAL] ---------------PhysicalUnion -----------------PhysicalDistribute[DistributionSpecExecutionAny] -------------------PhysicalOlapScan[fkt_not_null] -----------------PhysicalDistribute[DistributionSpecExecutionAny] -------------------PhysicalOlapScan[fkt_not_null] +--hashJoin[INNER_JOIN] hashCondition=((pkt.pk = fkt_not_null.fk)) otherCondition=() +----PhysicalOlapScan[pkt] +----hashAgg[GLOBAL] +------hashAgg[LOCAL] +--------PhysicalUnion +----------PhysicalOlapScan[fkt_not_null] +----------PhysicalOlapScan[fkt_not_null] -- !res -- +1 John 1 +2 Alice 2 +3 Bob 3 -- !name -- fk with window -- !shape -- PhysicalResultSink ---PhysicalDistribute[DistributionSpecGather] -----hashJoin[INNER_JOIN] hashCondition=((pkt.pk = fkt_not_null.fk)) otherCondition=() -------PhysicalOlapScan[pkt] -------PhysicalDistribute[DistributionSpecHash] ---------PhysicalWindow -----------PhysicalQuickSort[LOCAL_SORT] -------------PhysicalOlapScan[fkt_not_null] +--PhysicalWindow +----PhysicalQuickSort[LOCAL_SORT] +------PhysicalOlapScan[fkt_not_null] -- !res -- +1 1 1 +2 1 2 +3 1 3 -- !name -- fk with limit -- !shape -- PhysicalResultSink ---PhysicalDistribute[DistributionSpecGather] -----hashJoin[INNER_JOIN] hashCondition=((pkt.pk = fkt_not_null.fk)) otherCondition=() -------PhysicalOlapScan[pkt] -------PhysicalDistribute[DistributionSpecHash] ---------PhysicalLimit[GLOBAL] -----------PhysicalDistribute[DistributionSpecGather] -------------PhysicalLimit[LOCAL] ---------------PhysicalOlapScan[fkt_not_null] +--PhysicalLimit[GLOBAL] +----PhysicalLimit[LOCAL] +------PhysicalOlapScan[fkt_not_null] -- !res -- +1 1 -- !name -- pk with filter that same as fk -- !shape -- PhysicalResultSink ---PhysicalDistribute[DistributionSpecGather] -----hashJoin[INNER_JOIN] hashCondition=((pkt.pk = fkt_not_null.fk)) otherCondition=() -------filter((pkt.pk = 1)) ---------PhysicalOlapScan[pkt] -------PhysicalDistribute[DistributionSpecHash] ---------filter((fkt_not_null.fk = 1)) -----------PhysicalOlapScan[fkt_not_null] +--filter((fkt_not_null.fk = 1)) +----PhysicalOlapScan[fkt_not_null] -- !res -- +1 John 1 -- !name -- pk with filter that included same as fk -- !shape -- PhysicalResultSink ---PhysicalDistribute[DistributionSpecGather] -----hashJoin[INNER_JOIN] hashCondition=((pkt.pk = fkt_not_null.fk)) otherCondition=() -------filter((pkt.pk = 1)) ---------PhysicalOlapScan[pkt] -------PhysicalDistribute[DistributionSpecHash] ---------filter((cast(f as DOUBLE) = 1.0) and (fkt_not_null.fk = 1)) -----------PhysicalOlapScan[fkt_not_null] +--filter((cast(f as DOUBLE) = 1.0) and (fkt_not_null.fk = 1)) +----PhysicalOlapScan[fkt_not_null] -- !res -- @@ -158,13 +135,11 @@ pk with filter that not same as fk -- !shape -- PhysicalResultSink ---PhysicalDistribute[DistributionSpecGather] -----hashJoin[INNER_JOIN] hashCondition=((pkt.pk = fkt_not_null.fk)) otherCondition=() -------filter((cast(p as DOUBLE) = 1.0) and (pkt.pk = 1)) ---------PhysicalOlapScan[pkt] -------PhysicalDistribute[DistributionSpecHash] ---------filter((cast(f as DOUBLE) = 1.0) and (fkt_not_null.fk = 1)) -----------PhysicalOlapScan[fkt_not_null] +--hashJoin[INNER_JOIN] hashCondition=((pkt.pk = fkt_not_null.fk)) otherCondition=() +----filter((cast(p as DOUBLE) = 1.0) and (pkt.pk = 1)) +------PhysicalOlapScan[pkt] +----filter((cast(f as DOUBLE) = 1.0) and (fkt_not_null.fk = 1)) +------PhysicalOlapScan[fkt_not_null] -- !res -- @@ -173,129 +148,96 @@ simple_case -- !shape -- PhysicalResultSink ---PhysicalDistribute[DistributionSpecGather] -----filter(( not fk IS NULL)) -------PhysicalOlapScan[fkt] +--filter(( not fk IS NULL)) +----PhysicalOlapScan[fkt] -- !res -- 1 John -1 John -2 Alice 2 Alice -3 Bob -- !name -- with_pk_col -- !shape -- PhysicalResultSink ---PhysicalDistribute[DistributionSpecGather] -----filter(( not fk IS NULL)) -------PhysicalOlapScan[fkt] +--filter(( not fk IS NULL)) +----PhysicalOlapScan[fkt] -- !res -- 1 John 1 -1 John 1 2 Alice 2 -2 Alice 2 -3 Bob 3 -- !name -- with_pk_col -- !shape -- PhysicalResultSink ---PhysicalDistribute[DistributionSpecGather] -----hashJoin[INNER_JOIN] hashCondition=((pk = fkt2.fk)) otherCondition=() -------filter(( not fk IS NULL)) ---------PhysicalOlapScan[fkt] +--hashJoin[INNER_JOIN] hashCondition=((pk = fkt2.fk)) otherCondition=() +----filter(( not fk IS NULL)) ------PhysicalOlapScan[fkt] +----PhysicalOlapScan[fkt] -- !res -- 1 John 1 -1 John 1 -1 John 1 -1 John 1 -2 Alice 2 2 Alice 2 -2 Alice 2 -2 Alice 2 -3 Bob 3 -- !name -- with_pk_col -- !shape -- PhysicalResultSink ---PhysicalDistribute[DistributionSpecGather] -----hashJoin[INNER_JOIN] hashCondition=((pk = fkt2.fk)) otherCondition=() -------filter(( not fk IS NULL) and (fkt1.fk > 1)) ---------PhysicalOlapScan[fkt] -------filter((fkt2.fk > 1)) ---------PhysicalOlapScan[fkt] +--hashJoin[INNER_JOIN] hashCondition=((pk = fkt2.fk)) otherCondition=() +----filter(( not fk IS NULL) and (fkt1.fk > 1)) +------PhysicalOlapScan[fkt] +----filter((fkt2.fk > 1)) +------PhysicalOlapScan[fkt] -- !res -- 2 Alice 2 -2 Alice 2 -2 Alice 2 -2 Alice 2 -3 Bob 3 -- !name -- with_pk_col -- !shape -- PhysicalResultSink ---PhysicalDistribute[DistributionSpecGather] -----hashAgg[LOCAL] -------filter(( not fk IS NULL)) ---------PhysicalOlapScan[fkt] +--hashAgg[LOCAL] +----filter(( not fk IS NULL)) +------PhysicalOlapScan[fkt] -- !res -- 1 1 2 2 -3 3 -- !name -- with_pk_col -- !shape -- PhysicalResultSink ---PhysicalDistribute[DistributionSpecGather] -----hashJoin[INNER_JOIN] hashCondition=((pkt.pk = fkt.fk)) otherCondition=() -------PhysicalOlapScan[pkt] -------PhysicalDistribute[DistributionSpecHash] ---------hashAgg[GLOBAL] -----------PhysicalDistribute[DistributionSpecHash] -------------hashAgg[LOCAL] ---------------PhysicalUnion -----------------PhysicalDistribute[DistributionSpecExecutionAny] -------------------PhysicalOlapScan[fkt] -----------------PhysicalDistribute[DistributionSpecExecutionAny] -------------------PhysicalOlapScan[fkt] +--hashJoin[INNER_JOIN] hashCondition=((pkt.pk = fkt.fk)) otherCondition=() +----PhysicalOlapScan[pkt] +----hashAgg[GLOBAL] +------hashAgg[LOCAL] +--------PhysicalUnion +----------PhysicalOlapScan[fkt] +----------PhysicalOlapScan[fkt] -- !res -- 1 John 1 2 Alice 2 -3 Bob 3 -- !name -- fk with window -- !shape -- PhysicalResultSink ---PhysicalDistribute[DistributionSpecGather] -----PhysicalWindow -------PhysicalQuickSort[LOCAL_SORT] ---------filter(( not fk IS NULL)) -----------PhysicalOlapScan[fkt] +--PhysicalWindow +----PhysicalQuickSort[LOCAL_SORT] +------filter(( not fk IS NULL)) +--------PhysicalOlapScan[fkt] -- !res -- 1 1 1 -1 2 1 2 1 2 -2 2 2 -3 1 3 -- !name -- fk with limit @@ -304,34 +246,29 @@ fk with limit PhysicalResultSink --filter(( not fk IS NULL)) ----PhysicalLimit[GLOBAL] -------PhysicalDistribute[DistributionSpecGather] ---------PhysicalLimit[LOCAL] -----------PhysicalOlapScan[fkt] +------PhysicalLimit[LOCAL] +--------PhysicalOlapScan[fkt] -- !res -- -1 1 -- !name -- pk with filter that same as fk -- !shape -- PhysicalResultSink ---PhysicalDistribute[DistributionSpecGather] -----filter(( not fk IS NULL) and (fkt.fk = 1)) -------PhysicalOlapScan[fkt] +--filter(( not fk IS NULL) and (fkt.fk = 1)) +----PhysicalOlapScan[fkt] -- !res -- 1 John 1 -1 John 1 -- !name -- pk with filter that included same as fk -- !shape -- PhysicalResultSink ---PhysicalDistribute[DistributionSpecGather] -----filter(( not fk IS NULL) and (cast(f as DOUBLE) = 1.0) and (fkt.fk = 1)) -------PhysicalOlapScan[fkt] +--filter(( not fk IS NULL) and (cast(f as DOUBLE) = 1.0) and (fkt.fk = 1)) +----PhysicalOlapScan[fkt] -- !res -- @@ -340,13 +277,11 @@ pk with filter that not same as fk -- !shape -- PhysicalResultSink ---PhysicalDistribute[DistributionSpecGather] -----hashJoin[INNER_JOIN] hashCondition=((pkt.pk = fkt.fk)) otherCondition=() -------filter((cast(p as DOUBLE) = 1.0) and (pkt.pk = 1)) ---------PhysicalOlapScan[pkt] -------PhysicalDistribute[DistributionSpecHash] ---------filter((cast(f as DOUBLE) = 1.0) and (fkt.fk = 1)) -----------PhysicalOlapScan[fkt] +--hashJoin[INNER_JOIN] hashCondition=((pkt.pk = fkt.fk)) otherCondition=() +----filter((cast(p as DOUBLE) = 1.0) and (pkt.pk = 1)) +------PhysicalOlapScan[pkt] +----filter((cast(f as DOUBLE) = 1.0) and (fkt.fk = 1)) +------PhysicalOlapScan[fkt] -- !res -- diff --git a/regression-test/suites/nereids_rules_p0/pkfk/eliminate_inner.groovy b/regression-test/suites/nereids_rules_p0/pkfk/eliminate_inner.groovy index 5280847a53a..6819c06d608 100644 --- a/regression-test/suites/nereids_rules_p0/pkfk/eliminate_inner.groovy +++ b/regression-test/suites/nereids_rules_p0/pkfk/eliminate_inner.groovy @@ -20,7 +20,6 @@ suite("eliminate_inner") { sql "set runtime_filter_mode=OFF"; sql "SET ignore_shape_nodes='PhysicalDistribute,PhysicalProject'" sql "SET enable_fallback_to_original_planner=false" - sql "SET ignore_shape_nodes='PhysicalDistribute[DistributionSpecGather], PhysicalDistribute[DistributionSpecHash],PhysicalDistribute[DistributionSpecExecutionAny],PhysicalProject'" sql "SET disable_join_reorder=true" sql """ @@ -30,6 +29,9 @@ suite("eliminate_inner") { sql """ DROP TABLE IF EXISTS fkt """ + sql """ + DROP TABLE IF EXISTS fkt_not_null + """ sql """ CREATE TABLE IF NOT EXISTS pkt( @@ -70,7 +72,7 @@ suite("eliminate_inner") { INSERT INTO fkt VALUES (1, 'John'), (2, 'Alice'), (null, 'Bob'); """ sql """ - INSERT INTO fkt VALUES (1, 'John'), (2, 'Alice'), (3, 'Bob'); + INSERT INTO fkt_not_null VALUES (1, 'John'), (2, 'Alice'), (3, 'Bob'); """ sql """ alter table pkt add constraint pk primary key (pk) @@ -78,15 +80,15 @@ suite("eliminate_inner") { sql """ alter table fkt add constraint fk foreign key (fk) references pkt(pk) """ + sql """ + alter table fkt_not_null add constraint fk foreign key (fk) references pkt(pk) + """ def check_shape_res = { sql, name -> qt_name "select \"${name}\"" qt_shape "explain shape plan ${sql}" order_qt_res "${sql}" } - def simple_case = """ - select * from pkt inner join fkt on pkt.pk = fkt.fk; - """ - // nullable + // not nullable check_shape_res("select fkt_not_null.* from pkt inner join fkt_not_null on pkt.pk = fkt_not_null.fk;", "simple_case") check_shape_res("select fkt_not_null.*, pkt.pk from pkt inner join fkt_not_null on pkt.pk = fkt_not_null.fk;", "with_pk_col") check_shape_res("select fkt_not_null.*, pkt.pk from pkt inner join (select fkt_not_null1.* from fkt_not_null as fkt_not_null1 join fkt_not_null as fkt_not_null2 on fkt_not_null1.fk = fkt_not_null2.fk) fkt_not_null on pkt.pk = fkt_not_null.fk;", "with_pk_col") @@ -99,7 +101,7 @@ suite("eliminate_inner") { check_shape_res("select fkt_not_null.*, pkt.pk from pkt inner join fkt_not_null on pkt.pk = fkt_not_null.fk where pkt.pk = 1 and fkt_not_null.fk = 1 and fkt_not_null.f = 1;", "pk with filter that included same as fk") check_shape_res("select fkt_not_null.*, pkt.pk from pkt inner join fkt_not_null on pkt.pk = fkt_not_null.fk where pkt.p = 1 and fkt_not_null.fk = 1 and fkt_not_null.f = 1;;", "pk with filter that not same as fk") - // not nullable + // nullable check_shape_res("select fkt.* from pkt inner join fkt on pkt.pk = fkt.fk;", "simple_case") check_shape_res("select fkt.*, pkt.pk from pkt inner join fkt on pkt.pk = fkt.fk;", "with_pk_col") check_shape_res("select fkt.*, pkt.pk from pkt inner join (select fkt1.* from fkt as fkt1 join fkt as fkt2 on fkt1.fk = fkt2.fk) fkt on pkt.pk = fkt.fk;", "with_pk_col") --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
