This is an automated email from the ASF dual-hosted git repository.
dataroaring pushed a commit to branch branch-3.0
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-3.0 by this push:
new 28bf5ed5fad [fix](cases) remove all unstable cases in hint directory
(#41383)
28bf5ed5fad is described below
commit 28bf5ed5fad3544b8aa52369f283c56c1365704e
Author: LiBinfeng <46676950+libinfeng...@users.noreply.github.com>
AuthorDate: Wed Oct 9 10:01:53 2024 +0800
[fix](cases) remove all unstable cases in hint directory (#41383)
remove unstable cases in hint, unstable cases was caused by distribute
type unstable in join
---
.../data/nereids_p0/hint/fix_leading.out | 8 +-
.../data/nereids_p0/hint/multi_leading.out | 658 +++++++++++++--------
.../data/nereids_p0/hint/test_leading.out | 240 --------
.../suites/nereids_p0/hint/fix_leading.groovy | 6 +-
.../suites/nereids_p0/hint/multi_leading.groovy | 21 +-
.../suites/nereids_p0/hint/test_leading.groovy | 17 -
6 files changed, 407 insertions(+), 543 deletions(-)
diff --git a/regression-test/data/nereids_p0/hint/fix_leading.out
b/regression-test/data/nereids_p0/hint/fix_leading.out
index 372ffad30a3..998e7fe3f8a 100644
--- a/regression-test/data/nereids_p0/hint/fix_leading.out
+++ b/regression-test/data/nereids_p0/hint/fix_leading.out
@@ -11,8 +11,8 @@ PhysicalResultSink
------PhysicalOlapScan[t4]
Hint log:
-Used: leading({ t1 t2 } { t3 t4 } )
-UnUsed:
+Used: leading({ t1 broadcast t2 } broadcast { t3 broadcast t4 } )
+UnUsed:
SyntaxError:
-- !select2_1_1 --
@@ -261,7 +261,7 @@ PhysicalResultSink
--------PhysicalOlapScan[t6]
Hint log:
-Used: leading(t1 { { t2 t3 } { t4 t5 } } t6 )
-UnUsed:
+Used: leading(t1 broadcast { { t2 broadcast t3 } broadcast { t4 broadcast t5 }
} broadcast t6 )
+UnUsed:
SyntaxError:
diff --git a/regression-test/data/nereids_p0/hint/multi_leading.out
b/regression-test/data/nereids_p0/hint/multi_leading.out
index 08b6b83ed58..be90f0ef094 100644
--- a/regression-test/data/nereids_p0/hint/multi_leading.out
+++ b/regression-test/data/nereids_p0/hint/multi_leading.out
@@ -1,61 +1,4 @@
-- This file is automatically generated. You should know what you did if you
want to edit this
--- !sql1_2 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------hashJoin[INNER_JOIN broadcast] hashCondition=((cte.c1 = t1.c1))
otherCondition=()
-----------filter((t1.c1 > 300))
-------------PhysicalOlapScan[t1]
-----------hashJoin[INNER_JOIN broadcast] hashCondition=((cte.c1 = t2.c2))
otherCondition=()
-------------filter((t2.c2 > 300))
---------------PhysicalOlapScan[t2]
-------------filter((cte.c1 > 300))
---------------PhysicalOlapScan[t1]
-
-Hint log:
-Used: leading(t2 t1 ) leading(t1 cte )
-UnUsed:
-SyntaxError:
-
--- !sql1_3 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------hashJoin[INNER_JOIN colocated] hashCondition=((cte.c1 = t1.c1))
otherCondition=()
-----------filter((t1.c1 > 300))
-------------PhysicalOlapScan[t1]
-----------hashJoin[INNER_JOIN broadcast] hashCondition=((cte.c1 = t2.c2))
otherCondition=()
-------------filter((cte.c1 > 300))
---------------PhysicalOlapScan[t1]
-------------filter((t2.c2 > 300))
---------------PhysicalOlapScan[t2]
-
-Hint log:
-Used: leading(t1 t2 ) leading(t1 cte )
-UnUsed:
-SyntaxError:
-
--- !sql1_4 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------hashJoin[INNER_JOIN colocated] hashCondition=((cte.c1 = t1.c1))
otherCondition=()
-----------filter((t1.c1 > 300))
-------------PhysicalOlapScan[t1]
-----------hashJoin[INNER_JOIN broadcast] hashCondition=((cte.c1 = t2.c2))
otherCondition=()
-------------filter((cte.c1 > 300))
---------------PhysicalOlapScan[t1]
-------------filter((t2.c2 > 300))
---------------PhysicalOlapScan[t2]
-
-Hint log:
-Used: leading(t1 t2 ) leading(t1 cte )
-UnUsed:
-SyntaxError:
-
-- !sql1_res_1 --
1686
@@ -68,54 +11,6 @@ SyntaxError:
-- !sql1_res_4 --
1686
--- !sql2_2 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------hashJoin[INNER_JOIN broadcast] hashCondition=((alias1.c1 = t3.c3))
otherCondition=()
-----------PhysicalOlapScan[t3]
-----------hashJoin[INNER_JOIN broadcast] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
-------------PhysicalOlapScan[t1]
-------------PhysicalOlapScan[t2]
-
-Hint log:
-Used: leading(t3 alias1 )
-UnUsed:
-SyntaxError:
-
--- !sql2_3 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------hashJoin[INNER_JOIN broadcast] hashCondition=((alias1.c1 = t3.c3))
otherCondition=()
-----------hashJoin[INNER_JOIN broadcast] hashCondition=((alias1.c1 = t2.c2))
otherCondition=()
-------------PhysicalOlapScan[t2]
-------------PhysicalOlapScan[t1]
-----------PhysicalOlapScan[t3]
-
-Hint log:
-Used: leading(t2 t1 )
-UnUsed:
-SyntaxError:
-
--- !sql2_4 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------hashJoin[INNER_JOIN broadcast] hashCondition=((alias1.c1 = t3.c3))
otherCondition=()
-----------PhysicalOlapScan[t3]
-----------hashJoin[INNER_JOIN broadcast] hashCondition=((alias1.c1 = t2.c2))
otherCondition=()
-------------PhysicalOlapScan[t2]
-------------PhysicalOlapScan[t1]
-
-Hint log:
-Used: leading(t2 t1 ) leading(t3 alias1 )
-UnUsed:
-SyntaxError:
-
-- !sql2_res_1 --
3103
@@ -128,66 +23,6 @@ SyntaxError:
-- !sql2_res_4 --
3103
--- !sql3_2 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------hashJoin[INNER_JOIN broadcast] hashCondition=((alias1.c1 = cte.c11))
otherCondition=()
-----------hashJoin[INNER_JOIN broadcast] hashCondition=((alias1.c1 = t3.c3))
otherCondition=()
-------------PhysicalOlapScan[t3]
-------------hashJoin[INNER_JOIN broadcast] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
---------------PhysicalOlapScan[t1]
---------------PhysicalOlapScan[t2]
-----------hashJoin[INNER_JOIN broadcast] hashCondition=((cte.c1 = t2.c2))
otherCondition=()
-------------PhysicalOlapScan[t2]
-------------PhysicalOlapScan[t1]
-
-Hint log:
-Used: leading(t2 t1 ) leading(t3 alias1 cte )
-UnUsed:
-SyntaxError:
-
--- !sql3_3 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------hashJoin[INNER_JOIN broadcast] hashCondition=((alias1.c1 = cte.c11))
otherCondition=()
-----------hashJoin[INNER_JOIN broadcast] hashCondition=((alias1.c1 = t3.c3))
otherCondition=()
-------------hashJoin[INNER_JOIN broadcast] hashCondition=((alias1.c1 = t2.c2))
otherCondition=()
---------------PhysicalOlapScan[t2]
---------------PhysicalOlapScan[t1]
-------------PhysicalOlapScan[t3]
-----------hashJoin[INNER_JOIN broadcast] hashCondition=((cte.c1 = t2.c2))
otherCondition=()
-------------PhysicalOlapScan[t1]
-------------PhysicalOlapScan[t2]
-
-Hint log:
-Used: leading(t2 t1 )
-UnUsed:
-SyntaxError:
-
--- !sql3_4 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------hashJoin[INNER_JOIN broadcast] hashCondition=((alias1.c1 = cte.c11))
otherCondition=()
-----------hashJoin[INNER_JOIN broadcast] hashCondition=((alias1.c1 = t3.c3))
otherCondition=()
-------------PhysicalOlapScan[t3]
-------------hashJoin[INNER_JOIN broadcast] hashCondition=((alias1.c1 = t2.c2))
otherCondition=()
---------------PhysicalOlapScan[t2]
---------------PhysicalOlapScan[t1]
-----------hashJoin[INNER_JOIN broadcast] hashCondition=((cte.c1 = t2.c2))
otherCondition=()
-------------PhysicalOlapScan[t2]
-------------PhysicalOlapScan[t1]
-
-Hint log:
-Used: leading(t2 t1 ) leading(t2 t1 ) leading(t3 alias1 cte )
-UnUsed:
-SyntaxError:
-
-- !sql3_res_1 --
10411
@@ -200,78 +35,6 @@ SyntaxError:
-- !sql3_res_4 --
10411
--- !sql4_1 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------hashJoin[INNER_JOIN broadcast] hashCondition=((alias1.c1 = t3.c3))
otherCondition=()
-----------PhysicalOlapScan[t3]
-----------hashJoin[INNER_JOIN broadcast] hashCondition=((t1.c1 = alias2.c2))
otherCondition=()
-------------PhysicalOlapScan[t1]
-------------hashJoin[INNER_JOIN broadcast] hashCondition=((t2.c2 = t4.c4))
otherCondition=()
---------------PhysicalOlapScan[t2]
---------------PhysicalOlapScan[t4]
-
-Hint log:
-Used: leading(t3 alias1 )
-UnUsed:
-SyntaxError:
-
--- !sql4_2 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------hashJoin[INNER_JOIN broadcast] hashCondition=((alias1.c1 = t3.c3))
otherCondition=()
-----------hashJoin[INNER_JOIN broadcast] hashCondition=((alias1.c1 =
alias2.c2)) otherCondition=()
-------------hashJoin[INNER_JOIN broadcast] hashCondition=((t2.c2 = t4.c4))
otherCondition=()
---------------PhysicalOlapScan[t2]
---------------PhysicalOlapScan[t4]
-------------PhysicalOlapScan[t1]
-----------PhysicalOlapScan[t3]
-
-Hint log:
-Used: leading(alias2 t1 )
-UnUsed:
-SyntaxError:
-
--- !sql4_3 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------hashJoin[INNER_JOIN broadcast] hashCondition=((alias1.c1 = t3.c3))
otherCondition=()
-----------hashJoin[INNER_JOIN broadcast] hashCondition=((t1.c1 = alias2.c2))
otherCondition=()
-------------PhysicalOlapScan[t1]
-------------hashJoin[INNER_JOIN broadcast] hashCondition=((t2.c2 = t4.c4))
otherCondition=()
---------------PhysicalOlapScan[t4]
---------------PhysicalOlapScan[t2]
-----------PhysicalOlapScan[t3]
-
-Hint log:
-Used: leading(t4 t2 )
-UnUsed:
-SyntaxError:
-
--- !sql4_4 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------hashJoin[INNER_JOIN broadcast] hashCondition=((alias1.c1 = t3.c3))
otherCondition=()
-----------PhysicalOlapScan[t3]
-----------hashJoin[INNER_JOIN broadcast] hashCondition=((alias1.c1 =
alias2.c2)) otherCondition=()
-------------hashJoin[INNER_JOIN broadcast] hashCondition=((t2.c2 = t4.c4))
otherCondition=()
---------------PhysicalOlapScan[t2]
---------------PhysicalOlapScan[t4]
-------------PhysicalOlapScan[t1]
-
-Hint log:
-Used: leading(alias2 t1 ) leading(t3 alias1 )
-UnUsed:
-SyntaxError:
-
-- !sql4_res_0 --
6224
@@ -297,26 +60,403 @@ SyntaxError:
6224
-- !sql5_2 --
-PhysicalCteAnchor ( cteId=CTEId#0 )
---PhysicalCteProducer ( cteId=CTEId#0 )
-----PhysicalOlapScan[t1]
---PhysicalResultSink
-----PhysicalDistribute[DistributionSpecGather]
-------NestedLoopJoin[INNER_JOIN](cast(sum(c11) as DOUBLE) > (cast(0.05 as
DOUBLE) * avg(c11)))
---------hashAgg[GLOBAL]
-----------PhysicalDistribute[DistributionSpecHash]
-------------hashAgg[LOCAL]
---------------PhysicalDistribute[DistributionSpecExecutionAny]
-----------------PhysicalCteConsumer ( cteId=CTEId#0 )
---------hashAgg[GLOBAL]
-----------PhysicalDistribute[DistributionSpecGather]
-------------hashAgg[LOCAL]
---------------hashJoin[INNER_JOIN shuffle] hashCondition=((t1.c1 = cte.c11))
otherCondition=()
-----------------PhysicalCteConsumer ( cteId=CTEId#0 )
-----------------PhysicalOlapScan[t1]
-
-Hint log:
-Used: leading(cte t1 )
-UnUsed:
-SyntaxError:
+2
+10
+11
+12
+18
+21
+28
+29
+31
+32
+40
+41
+47
+53
+54
+58
+62
+68
+70
+77
+80
+81
+87
+94
+96
+97
+106
+121
+129
+131
+135
+142
+151
+154
+155
+158
+160
+162
+165
+170
+173
+178
+184
+186
+188
+189
+190
+192
+193
+203
+205
+209
+214
+215
+218
+221
+225
+228
+236
+237
+238
+241
+242
+243
+246
+247
+251
+253
+255
+256
+257
+259
+260
+262
+267
+269
+271
+276
+279
+281
+286
+291
+296
+305
+308
+314
+318
+322
+323
+327
+331
+339
+340
+344
+346
+351
+363
+374
+380
+386
+388
+389
+390
+393
+394
+399
+400
+409
+410
+420
+421
+423
+424
+425
+428
+433
+434
+435
+441
+446
+449
+458
+461
+462
+467
+474
+478
+486
+488
+489
+490
+499
+0
+1
+3
+8
+13
+17
+27
+30
+34
+35
+36
+43
+44
+61
+63
+69
+71
+72
+73
+76
+79
+82
+84
+85
+89
+90
+92
+93
+98
+100
+111
+113
+114
+122
+124
+127
+137
+143
+147
+148
+149
+150
+153
+159
+168
+169
+171
+175
+179
+181
+197
+201
+202
+204
+206
+217
+220
+227
+229
+240
+261
+265
+268
+270
+273
+275
+278
+280
+285
+287
+288
+293
+294
+297
+299
+301
+303
+310
+311
+313
+315
+317
+319
+326
+329
+332
+333
+336
+343
+345
+358
+359
+370
+371
+372
+373
+376
+378
+385
+391
+398
+405
+406
+414
+430
+432
+445
+447
+452
+454
+455
+456
+459
+464
+465
+468
+477
+481
+482
+492
+494
+495
+500
+4
+6
+7
+14
+16
+20
+23
+25
+33
+37
+38
+39
+45
+46
+48
+49
+51
+57
+59
+60
+64
+65
+67
+83
+88
+99
+101
+102
+104
+107
+108
+109
+112
+117
+118
+119
+120
+123
+125
+128
+130
+132
+134
+136
+138
+140
+141
+152
+157
+161
+163
+166
+172
+174
+194
+195
+199
+200
+210
+212
+213
+216
+223
+226
+230
+231
+232
+233
+235
+248
+249
+252
+254
+258
+263
+264
+266
+272
+274
+282
+284
+290
+298
+300
+302
+304
+312
+316
+321
+324
+325
+334
+335
+338
+341
+347
+348
+352
+353
+357
+362
+366
+367
+368
+369
+375
+379
+381
+382
+383
+387
+396
+397
+402
+404
+407
+408
+411
+412
+415
+417
+418
+419
+427
+429
+431
+439
+440
+443
+444
+448
+453
+457
+460
+463
+466
+470
+473
+476
+480
+484
+487
+497
+\N
diff --git a/regression-test/data/nereids_p0/hint/test_leading.out
b/regression-test/data/nereids_p0/hint/test_leading.out
index 7b0c9c4bcb0..3ab63f84213 100644
--- a/regression-test/data/nereids_p0/hint/test_leading.out
+++ b/regression-test/data/nereids_p0/hint/test_leading.out
@@ -2289,198 +2289,6 @@ Used: leading(t2 shuffle { t3 broadcast t1 } )
UnUsed:
SyntaxError:
--- !select93_1 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------hashJoin[INNER_JOIN broadcast] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
-----------hashJoin[INNER_JOIN broadcast] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
-------------PhysicalOlapScan[t1]
-------------PhysicalOlapScan[t2]
-----------PhysicalOlapScan[t3]
-
-Hint log:
-Used: leading(t1 t2 broadcast t3 )
-UnUsed:
-SyntaxError:
-
--- !select93_2 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------hashJoin[INNER_JOIN broadcast] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
-----------PhysicalOlapScan[t1]
-----------hashJoin[INNER_JOIN broadcast] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
-------------PhysicalOlapScan[t2]
-------------PhysicalOlapScan[t3]
-
-Hint log:
-Used: leading(t1 { t2 broadcast t3 } )
-UnUsed:
-SyntaxError:
-
--- !select93_3 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------hashJoin[INNER_JOIN broadcast] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
-----------PhysicalOlapScan[t1]
-----------hashJoin[INNER_JOIN broadcast] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
-------------PhysicalOlapScan[t3]
-------------PhysicalOlapScan[t2]
-
-Hint log:
-Used: leading(t1 { t3 broadcast t2 } )
-UnUsed:
-SyntaxError:
-
--- !select93_4 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------hashJoin[INNER_JOIN broadcast] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
-----------hashJoin[INNER_JOIN broadcast] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
-------------PhysicalOlapScan[t2]
-------------PhysicalOlapScan[t1]
-----------PhysicalOlapScan[t3]
-
-Hint log:
-Used: leading(t2 t1 broadcast t3 )
-UnUsed:
-SyntaxError:
-
--- !select93_5 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------hashJoin[INNER_JOIN broadcast] hashCondition=((t1.c1 = t2.c2) and
(t2.c2 = t3.c3)) otherCondition=()
-----------PhysicalOlapScan[t2]
-----------hashJoin[INNER_JOIN broadcast] hashCondition=((t1.c1 = t3.c3))
otherCondition=()
-------------PhysicalOlapScan[t1]
-------------PhysicalOlapScan[t3]
-
-Hint log:
-Used: leading(t2 { t1 broadcast t3 } )
-UnUsed:
-SyntaxError:
-
--- !select93_6 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------hashJoin[INNER_JOIN broadcast] hashCondition=((t1.c1 = t2.c2) and
(t2.c2 = t3.c3)) otherCondition=()
-----------PhysicalOlapScan[t2]
-----------hashJoin[INNER_JOIN broadcast] hashCondition=((t1.c1 = t3.c3))
otherCondition=()
-------------PhysicalOlapScan[t3]
-------------PhysicalOlapScan[t1]
-
-Hint log:
-Used: leading(t2 { t3 broadcast t1 } )
-UnUsed:
-SyntaxError:
-
--- !select94_2 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------hashJoin[INNER_JOIN broadcast] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
-----------hashJoin[INNER_JOIN bucketShuffle] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
-------------PhysicalOlapScan[t1]
-------------PhysicalOlapScan[t2]
-----------PhysicalOlapScan[t3]
-
-Hint log:
-Used: leading(t1 shuffle t2 t3 )
-UnUsed:
-SyntaxError:
-
--- !select94_2 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------hashJoin[INNER_JOIN bucketShuffle] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
-----------PhysicalOlapScan[t1]
-----------hashJoin[INNER_JOIN broadcast] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
-------------PhysicalOlapScan[t2]
-------------PhysicalOlapScan[t3]
-
-Hint log:
-Used: leading(t1 shuffle { t2 t3 } )
-UnUsed:
-SyntaxError:
-
--- !select94_2 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------hashJoin[INNER_JOIN bucketShuffle] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
-----------PhysicalOlapScan[t1]
-----------hashJoin[INNER_JOIN broadcast] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
-------------PhysicalOlapScan[t3]
-------------PhysicalOlapScan[t2]
-
-Hint log:
-Used: leading(t1 shuffle { t3 t2 } )
-UnUsed:
-SyntaxError:
-
--- !select94_2 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------hashJoin[INNER_JOIN broadcast] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
-----------hashJoin[INNER_JOIN bucketShuffle] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
-------------PhysicalOlapScan[t2]
-------------PhysicalOlapScan[t1]
-----------PhysicalOlapScan[t3]
-
-Hint log:
-Used: leading(t2 shuffle t1 t3 )
-UnUsed:
-SyntaxError:
-
--- !select94_2 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------hashJoin[INNER_JOIN bucketShuffle] hashCondition=((t1.c1 = t2.c2) and
(t2.c2 = t3.c3)) otherCondition=()
-----------PhysicalOlapScan[t2]
-----------hashJoin[INNER_JOIN broadcast] hashCondition=((t1.c1 = t3.c3))
otherCondition=()
-------------PhysicalOlapScan[t1]
-------------PhysicalOlapScan[t3]
-
-Hint log:
-Used: leading(t2 shuffle { t1 t3 } )
-UnUsed:
-SyntaxError:
-
--- !select94_2 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------hashJoin[INNER_JOIN bucketShuffle] hashCondition=((t1.c1 = t2.c2) and
(t2.c2 = t3.c3)) otherCondition=()
-----------PhysicalOlapScan[t2]
-----------hashJoin[INNER_JOIN broadcast] hashCondition=((t1.c1 = t3.c3))
otherCondition=()
-------------PhysicalOlapScan[t3]
-------------PhysicalOlapScan[t1]
-
-Hint log:
-Used: leading(t2 shuffle { t3 t1 } )
-UnUsed:
-SyntaxError:
-
-- !select95_1 --
PhysicalResultSink
--hashAgg[GLOBAL]
@@ -2497,54 +2305,6 @@ Used: leading(t1 broadcast t2 broadcast t3 )
UnUsed:
SyntaxError:
--- !select95_4 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------hashJoin[INNER_JOIN broadcast] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
-----------hashJoin[INNER_JOIN broadcast] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
-------------PhysicalOlapScan[t2]
-------------PhysicalOlapScan[t1]
-----------PhysicalOlapScan[t3]
-
-Hint log:
-Used: leading(t2 broadcast t1 t3 )
-UnUsed:
-SyntaxError:
-
--- !select95_8 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------hashJoin[INNER_JOIN broadcast] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
-----------PhysicalOlapScan[t3]
-----------hashJoin[INNER_JOIN broadcast] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
-------------PhysicalOlapScan[t1]
-------------PhysicalOlapScan[t2]
-
-Hint log:
-Used: leading(t3 broadcast { t1 t2 } )
-UnUsed:
-SyntaxError:
-
--- !select95_9 --
-PhysicalResultSink
---hashAgg[GLOBAL]
-----PhysicalDistribute[DistributionSpecGather]
-------hashAgg[LOCAL]
---------hashJoin[INNER_JOIN broadcast] hashCondition=((t2.c2 = t3.c3))
otherCondition=()
-----------PhysicalOlapScan[t3]
-----------hashJoin[INNER_JOIN broadcast] hashCondition=((t1.c1 = t2.c2))
otherCondition=()
-------------PhysicalOlapScan[t2]
-------------PhysicalOlapScan[t1]
-
-Hint log:
-Used: leading(t3 broadcast { t2 t1 } )
-UnUsed:
-SyntaxError:
-
-- !select96_1 --
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 d70d39c75b3..d30d2b60a2b 100644
--- a/regression-test/suites/nereids_p0/hint/fix_leading.groovy
+++ b/regression-test/suites/nereids_p0/hint/fix_leading.groovy
@@ -103,7 +103,7 @@ suite("fix_leading") {
}
// bug fix 1: {t1 t2}{t3 t4} miss levels
- qt_select1 """explain shape plan select /*+ leading({t1 t2}{t3 t4}) */ *
from t1 join t2 on c2 = c2 join t3 on c1 = c3 join t4 on c1 = c4;"""
+ qt_select1 """explain shape plan select /*+ leading({t1 broadcast t2}
broadcast {t3 broadcast t4}) */ * from t1 join t2 on c2 = c2 join t3 on c1 = c3
join t4 on c1 = c4;"""
// bug fix 2: fix left outer join without edge with other tables
// left join + left join
@@ -196,7 +196,7 @@ suite("fix_leading") {
// check left right join result
qt_select4_1 """select count(*) from t1 left join t2 on c1 > 500 and c2
>500 right join t3 on c3 > 500 and c1 < 200;"""
qt_select4_2 """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;"""
- 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;"""
+ qt_select4_3 """explain shape plan select /*+ leading(t1 broadcast t2
broadcast 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
explain {
@@ -205,7 +205,7 @@ suite("fix_leading") {
}
// check brace problem
- qt_select6_1 """explain shape plan select /*+ leading(t1 {{t2 t3}{t4 t5}}
t6) */ count(*) from t1 join t2 on c1 = c2 join t3 on c1 = c3 join t4 on c1 =
c4 join t5 on c1 = c5 join t6 on c1 = c6;"""
+ qt_select6_1 """explain shape plan select /*+ leading(t1 broadcast {{t2
broadcast t3} broadcast {t4 broadcast t5}} broadcast t6) */ count(*) from t1
join t2 on c1 = c2 join t3 on c1 = c3 join t4 on c1 = c4 join t5 on c1 = c5
join t6 on c1 = c6;"""
// check filter in duplicated aliasName
explain {
diff --git a/regression-test/suites/nereids_p0/hint/multi_leading.groovy
b/regression-test/suites/nereids_p0/hint/multi_leading.groovy
index 048c2e25498..fe63db1b636 100644
--- a/regression-test/suites/nereids_p0/hint/multi_leading.groovy
+++ b/regression-test/suites/nereids_p0/hint/multi_leading.groovy
@@ -81,41 +81,22 @@ suite("multi_leading") {
time 10000
}
- // test cte inline
- qt_sql1_2 """explain shape plan with cte as (select /*+ leading(t2 t1) */
c11, c1 from t1 join t2 on c1 = c2) select /*+ leading(t1 cte)*/ count(*) from
cte,t1 where cte.c1 = t1.c1 and t1.c1 > 300;"""
- qt_sql1_3 """explain shape plan with cte as (select /*+ leading(t1 t2) */
c11, c1 from t1 join t2 on c1 = c2) select /*+ leading(t1 cte)*/ count(*) from
cte,t1 where cte.c1 = t1.c1 and t1.c1 > 300;"""
- qt_sql1_4 """explain shape plan with cte as (select /*+ leading(t1 t2) */
c11, c1 from t1 join t2 on c1 = c2) select /*+ leading(t1 cte)*/ count(*) from
cte,t1 where cte.c1 = t1.c1 and t1.c1 > 300;"""
-
qt_sql1_res_1 """with cte as (select c11, c1 from t1 join t2 on c1 = c2)
select count(*) from cte,t1 where cte.c1 = t1.c1 and t1.c1 > 300;"""
qt_sql1_res_2 """with cte as (select /*+ leading(t2 t1) */ c11, c1 from t1
join t2 on c1 = c2) select /*+ leading(t1 cte)*/ count(*) from cte,t1 where
cte.c1 = t1.c1 and t1.c1 > 300;"""
qt_sql1_res_3 """with cte as (select /*+ leading(t1 t2) */ c11, c1 from t1
join t2 on c1 = c2) select /*+ leading(t1 cte)*/ count(*) from cte,t1 where
cte.c1 = t1.c1 and t1.c1 > 300;"""
qt_sql1_res_4 """with cte as (select /*+ leading(t1 t2) */ c11, c1 from t1
join t2 on c1 = c2) select /*+ leading(t1 cte)*/ count(*) from cte,t1 where
cte.c1 = t1.c1 and t1.c1 > 300;"""
- // test subquery alone
- qt_sql2_2 """explain shape plan select /*+ leading(t3 alias1) */ count(*)
from (select c1, c11 from t1 join t2 on c1 = c2) as alias1 join t3 on alias1.c1
= t3.c3;"""
- qt_sql2_3 """explain shape plan select count(*) from (select /*+
leading(t2 t1) */ c1, c11 from t1 join t2 on c1 = c2) as alias1 join t3 on
alias1.c1 = t3.c3;"""
- qt_sql2_4 """explain shape plan select /*+ leading(t3 alias1) */ count(*)
from (select /*+ leading(t2 t1) */ c1, c11 from t1 join t2 on c1 = c2) as
alias1 join t3 on alias1.c1 = t3.c3;"""
-
qt_sql2_res_1 """select count(*) from (select c1, c11 from t1 join t2 on
c1 = c2) as alias1 join t3 on alias1.c1 = t3.c3;"""
qt_sql2_res_2 """select /*+ leading(t3 alias1) */ count(*) from (select
c1, c11 from t1 join t2 on c1 = c2) as alias1 join t3 on alias1.c1 = t3.c3;"""
qt_sql2_res_3 """select count(*) from (select /*+ leading(t2 t1) */ c1,
c11 from t1 join t2 on c1 = c2) as alias1 join t3 on alias1.c1 = t3.c3;"""
qt_sql2_res_4 """select /*+ leading(t3 alias1) */ count(*) from (select
/*+ leading(t2 t1) */ c1, c11 from t1 join t2 on c1 = c2) as alias1 join t3 on
alias1.c1 = t3.c3;"""
- // test subquery + cte
- qt_sql3_2 """explain shape plan with cte as (select /*+ leading(t2 t1) */
c11, c1 from t1 join t2 on c1 = c2) select /*+ leading(t3 alias1 cte) */
count(*) from (select c1, c11 from t1 join t2 on c1 = c2) as alias1 join t3 on
alias1.c1 = t3.c3 join cte on alias1.c1 = cte.c11;;"""
- qt_sql3_3 """explain shape plan with cte as (select c11, c1 from t1 join
t2 on c1 = c2) select count(*) from (select /*+ leading(t2 t1) */ c1, c11 from
t1 join t2 on c1 = c2) as alias1 join t3 on alias1.c1 = t3.c3 join cte on
alias1.c1 = cte.c11;;"""
- qt_sql3_4 """explain shape plan with cte as (select /*+ leading(t2 t1) */
c11, c1 from t1 join t2 on c1 = c2) select /*+ leading(t3 alias1 cte) */
count(*) from (select /*+ leading(t2 t1) */ c1, c11 from t1 join t2 on c1 = c2)
as alias1 join t3 on alias1.c1 = t3.c3 join cte on alias1.c1 = cte.c11;;"""
-
qt_sql3_res_1 """with cte as (select c11, c1 from t1 join t2 on c1 = c2)
select count(*) from (select c1, c11 from t1 join t2 on c1 = c2) as alias1 join
t3 on alias1.c1 = t3.c3 join cte on alias1.c1 = cte.c11;;"""
qt_sql3_res_2 """with cte as (select /*+ leading(t2 t1) */ c11, c1 from t1
join t2 on c1 = c2) select /*+ leading(t3 alias1 cte) */ count(*) from (select
c1, c11 from t1 join t2 on c1 = c2) as alias1 join t3 on alias1.c1 = t3.c3 join
cte on alias1.c1 = cte.c11;;"""
qt_sql3_res_3 """with cte as (select c11, c1 from t1 join t2 on c1 = c2)
select count(*) from (select /*+ leading(t2 t1) */ c1, c11 from t1 join t2 on
c1 = c2) as alias1 join t3 on alias1.c1 = t3.c3 join cte on alias1.c1 =
cte.c11;;"""
qt_sql3_res_4 """with cte as (select /*+ leading(t2 t1) */ c11, c1 from t1
join t2 on c1 = c2) select /*+ leading(t3 alias1 cte) */ count(*) from (select
/*+ leading(t2 t1) */ c1, c11 from t1 join t2 on c1 = c2) as alias1 join t3 on
alias1.c1 = t3.c3 join cte on alias1.c1 = cte.c11;;"""
// test multi level subqueries
- qt_sql4_1 """explain shape plan 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;"""
- 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;"""
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")
@@ -139,5 +120,5 @@ suite("multi_leading") {
qt_sql4_res_7 """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;"""
// use cte in scalar query
- 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 )"""
+ qt_sql5_2 """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 )"""
}
diff --git a/regression-test/suites/nereids_p0/hint/test_leading.groovy
b/regression-test/suites/nereids_p0/hint/test_leading.groovy
index 0013eb4e63c..5c46e827d25 100644
--- a/regression-test/suites/nereids_p0/hint/test_leading.groovy
+++ b/regression-test/suites/nereids_p0/hint/test_leading.groovy
@@ -986,20 +986,6 @@ suite("test_leading") {
qt_select92_5 """explain shape plan select /*+ leading(t2 shuffle {t1
broadcast t3}) */ count(*) from t1 join t2 on c1 = c2 join t3 on c2 = c3;"""
qt_select92_6 """explain shape plan select /*+ leading(t2 shuffle {t3
broadcast t1}) */ count(*) from t1 join t2 on c1 = c2 join t3 on c2 = c3;"""
- qt_select93_1 """explain shape plan select /*+ leading(t1 t2 broadcast t3)
*/ count(*) from t1 join t2 on c1 = c2 join t3 on c2 = c3;"""
- qt_select93_2 """explain shape plan select /*+ leading(t1 {t2 broadcast
t3}) */ count(*) from t1 join t2 on c1 = c2 join t3 on c2 = c3;"""
- qt_select93_3 """explain shape plan select /*+ leading(t1 {t3 broadcast
t2}) */ count(*) from t1 join t2 on c1 = c2 join t3 on c2 = c3;"""
- qt_select93_4 """explain shape plan select /*+ leading(t2 t1 broadcast t3)
*/ count(*) from t1 join t2 on c1 = c2 join t3 on c2 = c3;"""
- qt_select93_5 """explain shape plan select /*+ leading(t2 {t1 broadcast
t3}) */ count(*) from t1 join t2 on c1 = c2 join t3 on c2 = c3;"""
- qt_select93_6 """explain shape plan select /*+ leading(t2 {t3 broadcast
t1}) */ count(*) from t1 join t2 on c1 = c2 join t3 on c2 = c3;"""
-
- qt_select94_2 """explain shape plan select /*+ leading(t1 shuffle t2 t3)
*/ count(*) from t1 join t2 on c1 = c2 join t3 on c2 = c3;"""
- qt_select94_2 """explain shape plan select /*+ leading(t1 shuffle {t2 t3})
*/ count(*) from t1 join t2 on c1 = c2 join t3 on c2 = c3;"""
- qt_select94_2 """explain shape plan select /*+ leading(t1 shuffle {t3 t2})
*/ count(*) from t1 join t2 on c1 = c2 join t3 on c2 = c3;"""
- qt_select94_2 """explain shape plan select /*+ leading(t2 shuffle t1 t3)
*/ count(*) from t1 join t2 on c1 = c2 join t3 on c2 = c3;"""
- qt_select94_2 """explain shape plan select /*+ leading(t2 shuffle {t1 t3})
*/ count(*) from t1 join t2 on c1 = c2 join t3 on c2 = c3;"""
- qt_select94_2 """explain shape plan select /*+ leading(t2 shuffle {t3 t1})
*/ count(*) from t1 join t2 on c1 = c2 join t3 on c2 = c3;"""
-
// outer join
qt_select95_1 """explain shape plan select /*+ leading(t1 broadcast t2
broadcast t3) */ count(*) from t1 left outer join t2 on c1 = c2 join t3 on c2 =
c3;"""
explain {
@@ -1010,7 +996,6 @@ suite("test_leading") {
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: 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;"""
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: leading(t2 broadcast { t1 t3 })")
@@ -1023,8 +1008,6 @@ suite("test_leading") {
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;"""
explain {
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
