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 2b4f4ca7961e768f6a42fda10bf61d82186ebda1
Author: LiBinfeng <46676950+libinfeng...@users.noreply.github.com>
AuthorDate: Thu Apr 25 16:27:37 2024 +0800
[Fix](nereids) fix cases unstable of hint (#34101)
fix cases unstable of hint, remove unused cases and project nodes and use
string contains in order to avoid unstable problem.
---
.../data/nereids_p0/hint/test_leading.out | 507 ---------------------
.../suites/nereids_p0/hint/fix_leading.groovy | 6 +-
.../suites/nereids_p0/hint/multi_leading.groovy | 17 +-
.../suites/nereids_p0/hint/test_leading.groovy | 110 ++++-
4 files changed, 105 insertions(+), 535 deletions(-)
diff --git a/regression-test/data/nereids_p0/hint/test_leading.out
b/regression-test/data/nereids_p0/hint/test_leading.out
index 53195035880..31df4aafe73 100644
--- a/regression-test/data/nereids_p0/hint/test_leading.out
+++ b/regression-test/data/nereids_p0/hint/test_leading.out
@@ -2165,25 +2165,6 @@ Used: [broadcast]_2
UnUsed:
SyntaxError:
--- !select90_2 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------PhysicalProject
-----------hashJoin[RIGHT_OUTER_JOIN] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------PhysicalOlapScan[t1]
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------PhysicalOlapScan[t2]
-
-Hint log:
-Used:
-UnUsed: [broadcast]_2
-SyntaxError:
-
-- !select90_3 --
PhysicalResultSink
--hashAgg[GLOBAL]
@@ -2208,30 +2189,6 @@ Used: [broadcast]_2 [shuffle]_3
UnUsed:
SyntaxError:
--- !select90_4 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------PhysicalProject
-----------hashJoin[INNER_JOIN] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
-------------PhysicalProject
---------------hashJoin[RIGHT_OUTER_JOIN] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------PhysicalOlapScan[t1]
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------PhysicalOlapScan[t2]
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------PhysicalOlapScan[t3]
-
-Hint log:
-Used: [shuffle]_3
-UnUsed: [broadcast]_2
-SyntaxError:
-
-- !select90_5 --
PhysicalResultSink
--hashAgg[GLOBAL]
@@ -2256,125 +2213,6 @@ Used: [broadcast]_2 [shuffle]_3
UnUsed:
SyntaxError:
--- !select90_6 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------PhysicalProject
-----------hashJoin[RIGHT_OUTER_JOIN] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
-------------------PhysicalProject
---------------------PhysicalOlapScan[t1]
-------------------PhysicalDistribute[DistributionSpecHash]
---------------------PhysicalProject
-----------------------PhysicalOlapScan[t2]
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------PhysicalOlapScan[t3]
-
-Hint log:
-Used: [shuffle]_2
-UnUsed: [broadcast]_3
-SyntaxError:
-
--- !select91_1 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------PhysicalProject
-----------hashJoin[INNER_JOIN] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
-------------PhysicalProject
---------------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
-----------------PhysicalProject
-------------------PhysicalOlapScan[t1]
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------PhysicalOlapScan[t2]
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------PhysicalOlapScan[t3]
-
-Hint log:
-Used: leading(t1 t2 t3 )
-UnUsed: [broadcast]_2 [shuffle]_3
-SyntaxError:
-
--- !select91_2 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------PhysicalProject
-----------hashJoin[INNER_JOIN] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
-------------PhysicalProject
---------------hashJoin[RIGHT_OUTER_JOIN] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------PhysicalOlapScan[t1]
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------PhysicalOlapScan[t2]
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------PhysicalOlapScan[t3]
-
-Hint log:
-Used: leading(t1 t2 t3 )
-UnUsed: [broadcast]_2 [shuffle]_3
-SyntaxError:
-
--- !select91_3 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------PhysicalProject
-----------hashJoin[RIGHT_OUTER_JOIN] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
-------------------PhysicalProject
---------------------PhysicalOlapScan[t1]
-------------------PhysicalDistribute[DistributionSpecHash]
---------------------PhysicalProject
-----------------------PhysicalOlapScan[t2]
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------PhysicalOlapScan[t3]
-
-Hint log:
-Used: leading(t1 t2 t3 )
-UnUsed: [broadcast]_2 [shuffle]_3
-SyntaxError:
-
--- !select91_4 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------PhysicalProject
-----------hashJoin[RIGHT_OUTER_JOIN] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
-------------------PhysicalProject
---------------------PhysicalOlapScan[t1]
-------------------PhysicalDistribute[DistributionSpecHash]
---------------------PhysicalProject
-----------------------PhysicalOlapScan[t2]
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------PhysicalOlapScan[t3]
-
-Hint log:
-Used: leading(t1 t2 t3 )
-UnUsed: [shuffle]_2 [broadcast]_3
-SyntaxError:
-
-- !select92_1 --
PhysicalResultSink
--hashAgg[GLOBAL]
@@ -2806,52 +2644,6 @@ Used: leading(t1 broadcast t2 t3 )
UnUsed:
SyntaxError:
--- !select95_2 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------PhysicalProject
-----------hashJoin[INNER_JOIN] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
-------------PhysicalProject
---------------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
-----------------PhysicalProject
-------------------PhysicalOlapScan[t1]
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------PhysicalOlapScan[t2]
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------PhysicalOlapScan[t3]
-
-Hint log:
-Used:
-UnUsed: [broadcast]_2 leading(t1 broadcast { t2 t3 })
-SyntaxError:
-
--- !select95_3 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------PhysicalProject
-----------hashJoin[INNER_JOIN] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
-------------PhysicalProject
---------------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
-----------------PhysicalProject
-------------------PhysicalOlapScan[t1]
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------PhysicalOlapScan[t2]
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------PhysicalOlapScan[t3]
-
-Hint log:
-Used:
-UnUsed: [broadcast]_2 leading(t1 broadcast { t3 t2 })
-SyntaxError:
-
-- !select95_4 --
PhysicalResultSink
--hashAgg[GLOBAL]
@@ -2875,75 +2667,6 @@ Used: leading(t2 broadcast t1 t3 )
UnUsed:
SyntaxError:
--- !select95_5 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------PhysicalProject
-----------hashJoin[INNER_JOIN] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
-------------PhysicalProject
---------------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
-----------------PhysicalProject
-------------------PhysicalOlapScan[t1]
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------PhysicalOlapScan[t2]
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------PhysicalOlapScan[t3]
-
-Hint log:
-Used:
-UnUsed: [broadcast]_2 leading(t2 broadcast { t1 t3 })
-SyntaxError:
-
--- !select95_6 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------PhysicalProject
-----------hashJoin[INNER_JOIN] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
-------------PhysicalProject
---------------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
-----------------PhysicalProject
-------------------PhysicalOlapScan[t1]
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------PhysicalOlapScan[t2]
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------PhysicalOlapScan[t3]
-
-Hint log:
-Used:
-UnUsed: [broadcast]_2 leading(t2 broadcast { t3 t1 })
-SyntaxError:
-
--- !select95_7 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------PhysicalProject
-----------hashJoin[INNER_JOIN] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
-------------PhysicalProject
---------------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
-----------------PhysicalProject
-------------------PhysicalOlapScan[t1]
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------PhysicalOlapScan[t2]
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------PhysicalOlapScan[t3]
-
-Hint log:
-Used:
-UnUsed: leading(t3 broadcast t1 t2)
-SyntaxError:
-
-- !select95_8 --
PhysicalResultSink
--hashAgg[GLOBAL]
@@ -3013,52 +2736,6 @@ Used: leading(t1 shuffle t2 broadcast t3 )
UnUsed:
SyntaxError:
--- !select96_2 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------PhysicalProject
-----------hashJoin[INNER_JOIN] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
-------------PhysicalProject
---------------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
-----------------PhysicalProject
-------------------PhysicalOlapScan[t1]
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------PhysicalOlapScan[t2]
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------PhysicalOlapScan[t3]
-
-Hint log:
-Used:
-UnUsed: [shuffle]_2 [broadcast]_3 leading(t1 shuffle { t2 broadcast t3 })
-SyntaxError:
-
--- !select96_3 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------PhysicalProject
-----------hashJoin[INNER_JOIN] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
-------------PhysicalProject
---------------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
-----------------PhysicalProject
-------------------PhysicalOlapScan[t1]
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------PhysicalOlapScan[t2]
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------PhysicalOlapScan[t3]
-
-Hint log:
-Used:
-UnUsed: [shuffle]_2 [broadcast]_3 leading(t1 shuffle { t3 broadcast t2 })
-SyntaxError:
-
-- !select96_4 --
PhysicalResultSink
--hashAgg[GLOBAL]
@@ -3082,75 +2759,6 @@ Used: leading(t2 shuffle t1 broadcast t3 )
UnUsed:
SyntaxError:
--- !select96_5 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------PhysicalProject
-----------hashJoin[INNER_JOIN] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
-------------PhysicalProject
---------------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
-----------------PhysicalProject
-------------------PhysicalOlapScan[t1]
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------PhysicalOlapScan[t2]
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------PhysicalOlapScan[t3]
-
-Hint log:
-Used:
-UnUsed: [shuffle]_2 leading(t2 shuffle { t1 broadcast t3 })
-SyntaxError:
-
--- !select96_6 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------PhysicalProject
-----------hashJoin[INNER_JOIN] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
-------------PhysicalProject
---------------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
-----------------PhysicalProject
-------------------PhysicalOlapScan[t1]
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------PhysicalOlapScan[t2]
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------PhysicalOlapScan[t3]
-
-Hint log:
-Used:
-UnUsed: [shuffle]_2 leading(t2 shuffle { t3 broadcast t1 })
-SyntaxError:
-
--- !select96_7 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------PhysicalProject
-----------hashJoin[INNER_JOIN] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
-------------PhysicalProject
---------------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
-----------------PhysicalProject
-------------------PhysicalOlapScan[t1]
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------PhysicalOlapScan[t2]
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------PhysicalOlapScan[t3]
-
-Hint log:
-Used:
-UnUsed: [broadcast]_3 leading(t3 shuffle t1 broadcast t2)
-SyntaxError:
-
-- !select96_8 --
PhysicalResultSink
--hashAgg[GLOBAL]
@@ -3221,52 +2829,6 @@ Used: leading(t1 broadcast t2 shuffle t3 )
UnUsed:
SyntaxError:
--- !select97_2 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------PhysicalProject
-----------hashJoin[INNER_JOIN] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
-------------PhysicalProject
---------------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
-----------------PhysicalProject
-------------------PhysicalOlapScan[t1]
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------PhysicalOlapScan[t2]
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------PhysicalOlapScan[t3]
-
-Hint log:
-Used:
-UnUsed: [broadcast]_2 [shuffle]_3 leading(t1 broadcast { t2 shuffle t3 })
-SyntaxError:
-
--- !select97_3 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------PhysicalProject
-----------hashJoin[INNER_JOIN] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
-------------PhysicalProject
---------------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
-----------------PhysicalProject
-------------------PhysicalOlapScan[t1]
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------PhysicalOlapScan[t2]
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------PhysicalOlapScan[t3]
-
-Hint log:
-Used:
-UnUsed: [broadcast]_2 [shuffle]_3 leading(t1 broadcast { t3 shuffle t2 })
-SyntaxError:
-
-- !select97_4 --
PhysicalResultSink
--hashAgg[GLOBAL]
@@ -3290,75 +2852,6 @@ Used: leading(t2 broadcast t1 shuffle t3 )
UnUsed:
SyntaxError:
--- !select97_5 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------PhysicalProject
-----------hashJoin[INNER_JOIN] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
-------------PhysicalProject
---------------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
-----------------PhysicalProject
-------------------PhysicalOlapScan[t1]
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------PhysicalOlapScan[t2]
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------PhysicalOlapScan[t3]
-
-Hint log:
-Used:
-UnUsed: [broadcast]_2 leading(t2 broadcast { t1 shuffle t3 })
-SyntaxError:
-
--- !select97_6 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------PhysicalProject
-----------hashJoin[INNER_JOIN] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
-------------PhysicalProject
---------------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
-----------------PhysicalProject
-------------------PhysicalOlapScan[t1]
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------PhysicalOlapScan[t2]
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------PhysicalOlapScan[t3]
-
-Hint log:
-Used:
-UnUsed: [broadcast]_2 leading(t2 broadcast { t3 shuffle t1 })
-SyntaxError:
-
--- !select97_7 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------PhysicalProject
-----------hashJoin[INNER_JOIN] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
-------------PhysicalProject
---------------hashJoin[INNER_JOIN] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
-----------------PhysicalProject
-------------------PhysicalOlapScan[t1]
-----------------PhysicalDistribute[DistributionSpecHash]
-------------------PhysicalProject
---------------------PhysicalOlapScan[t2]
-------------PhysicalDistribute[DistributionSpecHash]
---------------PhysicalProject
-----------------PhysicalOlapScan[t3]
-
-Hint log:
-Used:
-UnUsed: [shuffle]_3 leading(t3 broadcast t1 shuffle t2)
-SyntaxError:
-
-- !select97_8 --
PhysicalResultSink
--hashAgg[GLOBAL]
diff --git a/regression-test/suites/nereids_p0/hint/fix_leading.groovy
b/regression-test/suites/nereids_p0/hint/fix_leading.groovy
index 49823e97698..4c88fb2ac30 100644
--- a/regression-test/suites/nereids_p0/hint/fix_leading.groovy
+++ b/regression-test/suites/nereids_p0/hint/fix_leading.groovy
@@ -172,6 +172,8 @@ suite("fix_leading") {
qt_select4_3 """explain shape plan select /*+ leading(t1 t2 t3)*/ count(*)
from t1 left join t2 on c1 > 500 and c2 >500 right join t3 on c3 > 500 and c1 <
200;"""
// check whether we have all tables
- qt_select5_1 """explain shape plan select /*+ leading(t1 t2)*/ count(*)
from t1 left join t2 on c1 > 500 and c2 >500 right join t3 on c3 > 500 and c1 <
200;"""
-
+ explain {
+ sql """shape plan select /*+ leading(t1 t2)*/ count(*) from t1 left
join t2 on c1 > 500 and c2 >500 right join t3 on c3 > 500 and c1 < 200;"""
+ contains("SyntaxError: leading(t1 t2) Msg:leading should have all
tables in query block, missing tables: t3")
+ }
}
diff --git a/regression-test/suites/nereids_p0/hint/multi_leading.groovy
b/regression-test/suites/nereids_p0/hint/multi_leading.groovy
index bd567ecdefa..f716b5c98d0 100644
--- a/regression-test/suites/nereids_p0/hint/multi_leading.groovy
+++ b/regression-test/suites/nereids_p0/hint/multi_leading.groovy
@@ -113,9 +113,18 @@ suite("multi_leading") {
qt_sql4_2 """explain shape plan select count(*) from (select /*+
leading(alias2 t1) */ c1, c11 from t1 join (select c2, c22 from t2 join t4 on
c2 = c4) as alias2 on c1 = alias2.c2) as alias1 join t3 on alias1.c1 = t3.c3;"""
qt_sql4_3 """explain shape plan select count(*) from (select c1, c11 from
t1 join (select /*+ leading(t4 t2) */ c2, c22 from t2 join t4 on c2 = c4) as
alias2 on c1 = alias2.c2) as alias1 join t3 on alias1.c1 = t3.c3;"""
qt_sql4_4 """explain shape plan select /*+ leading(t3 alias1) */ count(*)
from (select /*+ leading(alias2 t1) */ c1, c11 from t1 join (select c2, c22
from t2 join t4 on c2 = c4) as alias2 on c1 = alias2.c2) as alias1 join t3 on
alias1.c1 = t3.c3;"""
- qt_sql4_5 """explain shape plan select /*+ leading(t3 alias1) */ count(*)
from (select c1, c11 from t1 join (select /*+ leading(t4 t2) */ c2, c22 from t2
join t4 on c2 = c4) as alias2 on c1 = alias2.c2) as alias1 join t3 on alias1.c1
= t3.c3;"""
- qt_sql4_6 """explain shape plan select count(*) from (select /*+
leading(alias2 t1) */ c1, c11 from t1 join (select /*+ leading(t4 t2) */ c2,
c22 from t2 join t4 on c2 = c4) as alias2 on c1 = alias2.c2) as alias1 join t3
on alias1.c1 = t3.c3;"""
- qt_sql4_7 """explain shape plan select /*+ leading(t3 alias1) */ count(*)
from (select /*+ leading(alias2 t1) */ c1, c11 from t1 join (select /*+
leading(t4 t2) */ c2, c22 from t2 join t4 on c2 = c4) as alias2 on c1 =
alias2.c2) as alias1 join t3 on alias1.c1 = t3.c3;"""
+ explain {
+ sql """shape plan select /*+ leading(t3 alias1) */ count(*) from
(select c1, c11 from t1 join (select /*+ leading(t4 t2) */ c2, c22 from t2 join
t4 on c2 = c4) as alias2 on c1 = alias2.c2) as alias1 join t3 on alias1.c1 =
t3.c3;"""
+ contains("SyntaxError: leading(t4 t2) Msg:one query block can only
have one leading clause")
+ }
+ explain {
+ sql """shape plan select count(*) from (select /*+ leading(alias2 t1)
*/ c1, c11 from t1 join (select /*+ leading(t4 t2) */ c2, c22 from t2 join t4
on c2 = c4) as alias2 on c1 = alias2.c2) as alias1 join t3 on alias1.c1 =
t3.c3;"""
+ contains("SyntaxError: leading(t4 t2) Msg:one query block can only
have one leading clause")
+ }
+ explain {
+ sql """shape plan select /*+ leading(t3 alias1) */ count(*) from
(select /*+ leading(alias2 t1) */ c1, c11 from t1 join (select /*+ leading(t4
t2) */ c2, c22 from t2 join t4 on c2 = c4) as alias2 on c1 = alias2.c2) as
alias1 join t3 on alias1.c1 = t3.c3;"""
+ contains("UnUsed: leading(alias2 t1)")
+ }
qt_sql4_res_0 """select count(*) from (select c1, c11 from t1 join (select
c2, c22 from t2 join t4 on c2 = c4) as alias2 on c1 = alias2.c2) as alias1 join
t3 on alias1.c1 = t3.c3;"""
qt_sql4_res_1 """select /*+ leading(t3 alias1) */ count(*) from (select
c1, c11 from t1 join (select c2, c22 from t2 join t4 on c2 = c4) as alias2 on
c1 = alias2.c2) as alias1 join t3 on alias1.c1 = t3.c3;"""
@@ -129,4 +138,4 @@ suite("multi_leading") {
// use cte in scalar query
qt_sql5_1 """explain shape plan with cte as (select c11, c1 from t1)
SELECT c1 FROM cte group by c1 having sum(cte.c11) > (select 0.05 * avg(t1.c11)
from t1 join cte on t1.c1 = cte.c11 )"""
qt_sql5_2 """explain shape plan with cte as (select c11, c1 from t1)
SELECT c1 FROM cte group by c1 having sum(cte.c11) > (select /*+ leading(cte
t1) */ 0.05 * avg(t1.c11) from t1 join cte on t1.c1 = cte.c11 )"""
-}
\ No newline at end of file
+}
diff --git a/regression-test/suites/nereids_p0/hint/test_leading.groovy
b/regression-test/suites/nereids_p0/hint/test_leading.groovy
index a9ad4aa705c..d1e11144b52 100644
--- a/regression-test/suites/nereids_p0/hint/test_leading.groovy
+++ b/regression-test/suites/nereids_p0/hint/test_leading.groovy
@@ -936,19 +936,40 @@ suite("test_leading") {
// used
qt_select90_1 """explain shape plan select count(*) from t1 join
[broadcast] t2 on c1 = c2;"""
// unused
- qt_select90_2 """explain shape plan select count(*) from t1 right outer
join [broadcast] t2 on c1 = c2;"""
+ explain {
+ sql """shape plan select count(*) from t1 right outer join [broadcast]
t2 on c1 = c2;"""
+ contains("UnUsed: [broadcast]_2")
+ }
// only distribute hint + multi hints
qt_select90_3 """explain shape plan select count(*) from t1 join
[broadcast] t2 on c1 = c2 join[shuffle] t3 on c2 = c3;"""
- qt_select90_4 """explain shape plan select count(*) from t1 right outer
join [broadcast] t2 on c1 = c2 join[shuffle] t3 on c2 = c3;"""
+ explain {
+ sql """shape plan select count(*) from t1 right outer join [broadcast]
t2 on c1 = c2 join[shuffle] t3 on c2 = c3;"""
+ contains("UnUsed: [broadcast]_2")
+ }
qt_select90_5 """explain shape plan select count(*) from t1 join
[broadcast] t2 on c1 = c2 right outer join[shuffle] t3 on c2 = c3;"""
- qt_select90_6 """explain shape plan select count(*) from t1 join [shuffle]
t2 on c1 = c2 right outer join[broadcast] t3 on c2 = c3;"""
+ explain {
+ sql """shape plan select count(*) from t1 join [shuffle] t2 on c1 = c2
right outer join[broadcast] t3 on c2 = c3;"""
+ contains("UnUsed: [broadcast]_3")
+ }
// leading + distribute hint outside leading + single hint
- qt_select91_1 """explain shape plan select /*+ leading(t1 t2 t3) */
count(*) from t1 join [broadcast] t2 on c1 = c2 join[shuffle] t3 on c2 = c3;"""
- qt_select91_2 """explain shape plan select /*+ leading(t1 t2 t3) */
count(*) from t1 right outer join [broadcast] t2 on c1 = c2 join[shuffle] t3 on
c2 = c3;"""
- qt_select91_3 """explain shape plan select /*+ leading(t1 t2 t3) */
count(*) from t1 join [broadcast] t2 on c1 = c2 right outer join[shuffle] t3 on
c2 = c3;"""
- qt_select91_4 """explain shape plan select /*+ leading(t1 t2 t3) */
count(*) from t1 join [shuffle] t2 on c1 = c2 right outer join[broadcast] t3 on
c2 = c3;"""
+ explain {
+ sql """shape plan select /*+ leading(t1 t2 t3) */ count(*) from t1
join [broadcast] t2 on c1 = c2 join[shuffle] t3 on c2 = c3;"""
+ contains("UnUsed: [broadcast]_2 [shuffle]_3")
+ }
+ explain {
+ sql """shape plan select /*+ leading(t1 t2 t3) */ count(*) from t1
right outer join [broadcast] t2 on c1 = c2 join[shuffle] t3 on c2 = c3;"""
+ contains("UnUsed: [broadcast]_2 [shuffle]_3")
+ }
+ explain {
+ sql """shape plan select /*+ leading(t1 t2 t3) */ count(*) from t1
join [broadcast] t2 on c1 = c2 right outer join[shuffle] t3 on c2 = c3;"""
+ contains("UnUsed: [broadcast]_2 [shuffle]_3")
+ }
+ explain {
+ sql """shape plan select /*+ leading(t1 t2 t3) */ count(*) from t1
join [shuffle] t2 on c1 = c2 right outer join[broadcast] t3 on c2 = c3;"""
+ contains("UnUsed: [shuffle]_2 [broadcast]_3")
+ }
// leading + distribute hint inside leading + single hint
// inner join
@@ -975,32 +996,77 @@ suite("test_leading") {
// outer join
qt_select95_1 """explain shape plan select /*+ leading(t1 broadcast t2 t3)
*/ count(*) from t1 left outer join t2 on c1 = c2 join t3 on c2 = c3;"""
- qt_select95_2 """explain shape plan select /*+ leading(t1 broadcast {t2
t3}) */ count(*) from t1 left outer join t2 on c1 = c2 join t3 on c2 = c3;"""
- qt_select95_3 """explain shape plan select /*+ leading(t1 broadcast {t3
t2}) */ count(*) from t1 left outer join t2 on c1 = c2 join t3 on c2 = c3;"""
+ explain {
+ sql """shape plan select /*+ leading(t1 broadcast {t2 t3}) */ count(*)
from t1 left outer join t2 on c1 = c2 join t3 on c2 = c3;"""
+ contains("UnUsed: [broadcast]_2 leading(t1 broadcast { t2 t3 })")
+ }
+ explain {
+ sql """shape plan select /*+ leading(t1 broadcast {t3 t2}) */ count(*)
from t1 left outer join t2 on c1 = c2 join t3 on c2 = c3;"""
+ contains("UnUsed: [broadcast]_2 leading(t1 broadcast { t3 t2 })")
+ }
qt_select95_4 """explain shape plan select /*+ leading(t2 broadcast t1 t3)
*/ count(*) from t1 left outer join t2 on c1 = c2 join t3 on c2 = c3;"""
- qt_select95_5 """explain shape plan select /*+ leading(t2 broadcast {t1
t3}) */ count(*) from t1 left outer join t2 on c1 = c2 join t3 on c2 = c3;"""
- qt_select95_6 """explain shape plan select /*+ leading(t2 broadcast {t3
t1}) */ count(*) from t1 left outer join t2 on c1 = c2 join t3 on c2 = c3;"""
- qt_select95_7 """explain shape plan select /*+ leading(t3 broadcast t1 t2)
*/ count(*) from t1 left outer join t2 on c1 = c2 join t3 on c2 = c3;"""
+ explain {
+ sql """shape plan select /*+ leading(t2 broadcast {t1 t3}) */ count(*)
from t1 left outer join t2 on c1 = c2 join t3 on c2 = c3;"""
+ contains("UnUsed: [broadcast]_2 leading(t2 broadcast { t1 t3 })")
+ }
+ explain {
+ sql """shape plan select /*+ leading(t2 broadcast {t3 t1}) */ count(*)
from t1 left outer join t2 on c1 = c2 join t3 on c2 = c3;"""
+ contains("UnUsed: [broadcast]_2 leading(t2 broadcast { t3 t1 })")
+ }
+ explain {
+ sql """shape plan select /*+ leading(t3 broadcast t1 t2) */ count(*)
from t1 left outer join t2 on c1 = c2 join t3 on c2 = c3;"""
+ contains("UnUsed: leading(t3 broadcast t1 t2)")
+ }
qt_select95_8 """explain shape plan select /*+ leading(t3 broadcast {t1
t2}) */ count(*) from t1 left outer join t2 on c1 = c2 join t3 on c2 = c3;"""
qt_select95_9 """explain shape plan select /*+ leading(t3 broadcast {t2
t1}) */ count(*) from t1 left outer join t2 on c1 = c2 join t3 on c2 = c3;"""
qt_select96_1 """explain shape plan select /*+ leading(t1 shuffle t2
broadcast t3) */ count(*) from t1 left outer join t2 on c1 = c2 join t3 on c2 =
c3;"""
- qt_select96_2 """explain shape plan select /*+ leading(t1 shuffle {t2
broadcast t3}) */ count(*) from t1 left outer join t2 on c1 = c2 join t3 on c2
= c3;"""
- qt_select96_3 """explain shape plan select /*+ leading(t1 shuffle {t3
broadcast t2}) */ count(*) from t1 left outer join t2 on c1 = c2 join t3 on c2
= c3;"""
+ explain {
+ sql """shape plan select /*+ leading(t1 shuffle {t2 broadcast t3}) */
count(*) from t1 left outer join t2 on c1 = c2 join t3 on c2 = c3;"""
+ contains("UnUsed: [shuffle]_2 [broadcast]_3 leading(t1 shuffle { t2
broadcast t3 })")
+ }
+ explain {
+ sql """shape plan select /*+ leading(t1 shuffle {t3 broadcast t2}) */
count(*) from t1 left outer join t2 on c1 = c2 join t3 on c2 = c3;"""
+ contains("UnUsed: [shuffle]_2 [broadcast]_3 leading(t1 shuffle { t3
broadcast t2 })")
+ }
qt_select96_4 """explain shape plan select /*+ leading(t2 shuffle t1
broadcast t3) */ count(*) from t1 left outer join t2 on c1 = c2 join t3 on c2 =
c3;"""
- qt_select96_5 """explain shape plan select /*+ leading(t2 shuffle {t1
broadcast t3}) */ count(*) from t1 left outer join t2 on c1 = c2 join t3 on c2
= c3;"""
- qt_select96_6 """explain shape plan select /*+ leading(t2 shuffle {t3
broadcast t1}) */ count(*) from t1 left outer join t2 on c1 = c2 join t3 on c2
= c3;"""
- qt_select96_7 """explain shape plan select /*+ leading(t3 shuffle t1
broadcast t2) */ count(*) from t1 left outer join t2 on c1 = c2 join t3 on c2 =
c3;"""
+ explain {
+ sql """shape plan select /*+ leading(t2 shuffle {t1 broadcast t3}) */
count(*) from t1 left outer join t2 on c1 = c2 join t3 on c2 = c3;"""
+ contains("UnUsed: [shuffle]_2 leading(t2 shuffle { t1 broadcast t3
})")
+ }
+ explain {
+ sql """shape plan select /*+ leading(t2 shuffle {t3 broadcast t1}) */
count(*) from t1 left outer join t2 on c1 = c2 join t3 on c2 = c3;"""
+ contains("UnUsed: [shuffle]_2 leading(t2 shuffle { t3 broadcast t1
})")
+ }
+ explain {
+ sql """shape plan select /*+ leading(t3 shuffle t1 broadcast t2) */
count(*) from t1 left outer join t2 on c1 = c2 join t3 on c2 = c3;"""
+ contains("UnUsed: [broadcast]_3 leading(t3 shuffle t1 broadcast t2)")
+ }
qt_select96_8 """explain shape plan select /*+ leading(t3 shuffle {t1
broadcast t2}) */ count(*) from t1 left outer join t2 on c1 = c2 join t3 on c2
= c3;"""
qt_select96_9 """explain shape plan select /*+ leading(t3 shuffle {t2
broadcast t1}) */ count(*) from t1 left outer join t2 on c1 = c2 join t3 on c2
= c3;"""
qt_select97_1 """explain shape plan select /*+ leading(t1 broadcast t2
shuffle t3) */ count(*) from t1 left outer join t2 on c1 = c2 join t3 on c2 =
c3;"""
- qt_select97_2 """explain shape plan select /*+ leading(t1 broadcast {t2
shuffle t3}) */ count(*) from t1 left outer join t2 on c1 = c2 join t3 on c2 =
c3;"""
- qt_select97_3 """explain shape plan select /*+ leading(t1 broadcast {t3
shuffle t2}) */ count(*) from t1 left outer join t2 on c1 = c2 join t3 on c2 =
c3;"""
+ explain {
+ sql """shape plan select /*+ leading(t1 broadcast {t2 shuffle t3}) */
count(*) from t1 left outer join t2 on c1 = c2 join t3 on c2 = c3;"""
+ contains("UnUsed: [broadcast]_2 [shuffle]_3 leading(t1 broadcast { t2
shuffle t3 })")
+ }
+ explain {
+ sql """shape plan select /*+ leading(t1 broadcast {t3 shuffle t2}) */
count(*) from t1 left outer join t2 on c1 = c2 join t3 on c2 = c3;"""
+ contains("UnUsed: [broadcast]_2 [shuffle]_3 leading(t1 broadcast { t3
shuffle t2 })")
+ }
qt_select97_4 """explain shape plan select /*+ leading(t2 broadcast t1
shuffle t3) */ count(*) from t1 left outer join t2 on c1 = c2 join t3 on c2 =
c3;"""
- qt_select97_5 """explain shape plan select /*+ leading(t2 broadcast {t1
shuffle t3}) */ count(*) from t1 left outer join t2 on c1 = c2 join t3 on c2 =
c3;"""
- qt_select97_6 """explain shape plan select /*+ leading(t2 broadcast {t3
shuffle t1}) */ count(*) from t1 left outer join t2 on c1 = c2 join t3 on c2 =
c3;"""
- qt_select97_7 """explain shape plan select /*+ leading(t3 broadcast t1
shuffle t2) */ count(*) from t1 left outer join t2 on c1 = c2 join t3 on c2 =
c3;"""
+ explain {
+ sql """shape plan select /*+ leading(t2 broadcast {t1 shuffle t3}) */
count(*) from t1 left outer join t2 on c1 = c2 join t3 on c2 = c3;"""
+ contains("UnUsed: [broadcast]_2 leading(t2 broadcast { t1 shuffle t3
})")
+ }
+ explain {
+ sql """shape plan select /*+ leading(t2 broadcast {t3 shuffle t1}) */
count(*) from t1 left outer join t2 on c1 = c2 join t3 on c2 = c3;"""
+ contains("UnUsed: [broadcast]_2 leading(t2 broadcast { t3 shuffle t1
})")
+ }
+ explain {
+ sql """shape plan select /*+ leading(t3 broadcast t1 shuffle t2) */
count(*) from t1 left outer join t2 on c1 = c2 join t3 on c2 = c3;"""
+ contains("UnUsed: [shuffle]_3 leading(t3 broadcast t1 shuffle t2)")
+ }
qt_select97_8 """explain shape plan select /*+ leading(t3 broadcast {t1
shuffle t2}) */ count(*) from t1 left outer join t2 on c1 = c2 join t3 on c2 =
c3;"""
qt_select97_9 """explain shape plan select /*+ leading(t3 broadcast {t2
shuffle t1}) */ count(*) from t1 left outer join t2 on c1 = c2 join t3 on c2 =
c3;"""
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
