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
commit 9537ea7ebfe8e0d211bccfa5eaa55b65659b2cb3 Author: LiBinfeng <[email protected]> AuthorDate: Thu May 16 10:58:11 2024 +0800 [Fix](Nereids) fix leading with cte and same subqueryalias name (#34838) fix leading with cte and same subqueryalias name Example: with tbl1 as select t1.c1 from t1 select tbl2.c2 from (select / * + leading(t2 tbl1) * / tbl1.c1, t2.c2 from tbl1 join t2) as tbl2 join t3; Reason: in this case, before getting analyzed preprocess would change subquery tbl2 to cte plan, and this cte plan should be in upper level cte plan, but not in logical result sink plan --- .../processor/pre/PullUpSubqueryAliasToCTE.java | 11 +- .../data/nereids_p0/hint/multi_leading.out | 2 +- regression-test/data/nereids_p0/hint/t5.csv | 1000 ++++++++++++++++++++ regression-test/data/nereids_p0/hint/t6.csv | 1000 ++++++++++++++++++++ .../suites/nereids_p0/hint/fix_leading.groovy | 45 + 5 files changed, 2056 insertions(+), 2 deletions(-) diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/processor/pre/PullUpSubqueryAliasToCTE.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/processor/pre/PullUpSubqueryAliasToCTE.java index 0101a346478..8e8889f5e62 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/nereids/processor/pre/PullUpSubqueryAliasToCTE.java +++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/processor/pre/PullUpSubqueryAliasToCTE.java @@ -79,6 +79,15 @@ public class PullUpSubqueryAliasToCTE extends PlanPreprocessor { subQueryAlias = new LogicalSubQueryAlias<>(subQueryAlias.getAlias(), newSubQueryAlias); } } - return visitChildren(this, logicalCTE, context); + Plan cte = visitChildren(this, logicalCTE, context); + if (!aliasQueries.isEmpty()) { + LogicalCTE newLogicalCTE = (LogicalCTE) cte; + List<LogicalSubQueryAlias<Plan>> subQueryAliasesOfCte = new ArrayList<>(); + subQueryAliasesOfCte.addAll(logicalCTE.getAliasQueries()); + subQueryAliasesOfCte.addAll(aliasQueries); + aliasQueries = new ArrayList<>(); + return new LogicalCTE<>(subQueryAliasesOfCte, (LogicalPlan) newLogicalCTE.child()); + } + return cte; } } diff --git a/regression-test/data/nereids_p0/hint/multi_leading.out b/regression-test/data/nereids_p0/hint/multi_leading.out index 4f7056de310..241522a192b 100644 --- a/regression-test/data/nereids_p0/hint/multi_leading.out +++ b/regression-test/data/nereids_p0/hint/multi_leading.out @@ -254,7 +254,7 @@ PhysicalResultSink ----------------PhysicalOlapScan[t1] Hint log: -Used: leading(t2 t1 ) leading(t2 t1 ) leading(t2 t1 ) leading(t3 alias1 cte ) +Used: leading(t2 t1 ) leading(t2 t1 ) leading(t3 alias1 cte ) UnUsed: SyntaxError: diff --git a/regression-test/data/nereids_p0/hint/t5.csv b/regression-test/data/nereids_p0/hint/t5.csv new file mode 100644 index 00000000000..cfae19cec05 --- /dev/null +++ b/regression-test/data/nereids_p0/hint/t5.csv @@ -0,0 +1,1000 @@ +379|311 +347|\N +319|233 +286|355 +30|154 +38|418 +270|294 +\N|162 +241|248 +299|102 +87|154 +482|337 +385|165 +291|308 +49|153 +394|392 +250|249 +487|186 +35|\N +309|167 +427|157 +178|428 +436|112 +412|56 +144|218 +33|488 +48|142 +455|403 +211|232 +355|185 +167|294 +238|116 +191|86 +160|399 +288|321 +494|442 +7|220 +291|486 +496|321 +292|351 +231|214 +392|266 +299|324 +212|176 +17|34 +242|341 +452|27 +\N|155 +363|258 +363|268 +221|\N +361|358 +21|251 +209|232 +439|209 +25|423 +283|271 +364|104 +105|19 +212|115 +224|250 +342|376 +261|27 +19|88 +213|372 +268|426 +102|466 +126|97 +305|312 +476|355 +409|318 +215|375 +234|318 +353|451 +313|11 +296|315 +393|244 +285|46 +289|209 +420|264 +346|482 +317|258 +437|72 +48|468 +88|22 +498|102 +496|85 +456|\N +389|419 +22|144 +473|313 +110|399 +87|240 +235|178 +265|182 +\N|169 +69|69 +222|334 +495|211 +16|95 +98|453 +191|18 +206|248 +433|202 +445|138 +498|391 +65|494 +305|278 +436|219 +\N|217 +150|206 +467|439 +56|381 +174|231 +64|57 +33|228 +241|290 +215|120 +230|228 +306|323 +108|287 +310|70 +372|171 +400|167 +74|32 +468|\N +258|15 +167|164 +237|256 +273|28 +167|333 +289|50 +73|461 +16|56 +332|181 +133|353 +302|265 +154|35 +500|432 +223|456 +327|434 +306|387 +473|144 +93|300 +106|121 +52|152 +108|456 +277|55 +445|451 +244|253 +123|257 +425|473 +131|341 +370|161 +330|78 +321|306 +127|66 +429|130 +\N|34 +4|260 +106|166 +298|407 +366|55 +451|\N +495|384 +178|59 +52|220 +182|165 +336|469 +56|147 +36|151 +17|475 +451|469 +189|404 +124|487 +3|165 +305|392 +43|375 +12|321 +25|\N +371|227 +283|457 +413|125 +\N|251 +101|358 +\N|340 +198|463 +78|407 +168|378 +361|357 +135|37 +38|333 +256|122 +352|37 +21|261 +270|149 +6|466 +488|\N +249|388 +60|297 +\N|399 +466|303 +198|344 +415|314 +500|243 +385|439 +159|286 +130|244 +457|370 +157|206 +249|442 +302|250 +76|475 +112|11 +\N|33 +467|327 +285|229 +365|486 +171|481 +285|82 +225|329 +11|380 +211|328 +387|109 +473|221 +435|297 +352|454 +340|292 +73|346 +183|125 +397|458 +454|369 +227|326 +68|1 +336|63 +\N|40 +94|478 +118|237 +109|161 +370|395 +33|253 +238|285 +406|40 +207|247 +4|453 +346|1 +177|286 +355|48 +348|462 +308|334 +46|6 +220|237 +67|293 +187|117 +178|53 +459|101 +10|31 +444|55 +408|272 +340|34 +14|461 +121|36 +255|494 +497|345 +142|227 +90|423 +101|2 +451|281 +335|230 +166|256 +\N|\N +53|460 +65|429 +417|212 +330|472 +170|215 +216|206 +135|455 +401|48 +99|294 +\N|396 +283|370 +27|184 +472|255 +145|471 +183|335 +\N|140 +284|131 +\N|313 +125|326 +117|31 +347|191 +329|81 +25|431 +494|142 +37|\N +\N|\N +485|226 +264|217 +439|208 +457|243 +9|105 +202|408 +102|349 +446|286 +32|\N +416|13 +437|138 +181|189 +24|166 +448|240 +120|89 +\N|262 +\N|123 +305|120 +\N|88 +476|247 +360|5 +476|162 +305|401 +135|264 +435|376 +64|402 +187|198 +377|240 +\N|146 +430|87 +157|50 +282|397 +\N|57 +140|333 +312|196 +113|355 +10|385 +57|73 +175|\N +421|89 +186|149 +384|184 +363|406 +189|348 +436|\N +374|275 +123|276 +85|465 +\N|89 +416|328 +30|252 +254|\N +243|359 +197|382 +283|273 +30|198 +276|433 +300|132 +446|368 +205|493 +472|229 +\N|443 +207|475 +136|154 +138|374 +71|27 +65|261 +337|116 +290|499 +326|321 +323|226 +286|201 +65|\N +478|56 +\N|403 +313|36 +324|411 +123|131 +148|429 +400|373 +371|125 +476|452 +19|491 +105|7 +457|93 +113|473 +243|489 +130|23 +43|418 +466|422 +485|270 +88|419 +\N|366 +454|205 +384|473 +26|104 +280|45 +59|144 +225|154 +\N|420 +362|4 +268|315 +\N|306 +119|41 +88|392 +437|73 +208|38 +\N|396 +137|121 +248|485 +203|76 +121|72 +365|306 +406|62 +14|134 +418|152 +233|224 +286|342 +408|405 +387|306 +313|468 +459|6 +89|120 +452|375 +4|117 +398|150 +91|80 +401|360 +396|148 +39|222 +137|485 +77|361 +480|\N +121|423 +90|147 +3|299 +462|81 +262|457 +417|218 +\N|493 +64|372 +91|332 +69|\N +\N|70 +136|422 +51|465 +341|342 +485|104 +419|123 +377|326 +\N|196 +468|232 +298|210 +319|412 +258|112 +312|481 +9|332 +222|475 +263|18 +191|178 +336|246 +323|158 +133|165 +158|40 +257|352 +7|47 +245|234 +100|405 +19|263 +189|85 +309|244 +113|404 +92|208 +171|102 +425|305 +\N|48 +455|398 +449|96 +\N|260 +376|121 +85|439 +84|255 +141|491 +3|483 +284|168 +289|81 +45|106 +61|123 +304|210 +304|139 +393|106 +266|410 +364|23 +\N|274 +\N|88 +208|43 +239|277 +455|\N +494|102 +207|61 +137|286 +88|451 +486|317 +259|432 +387|165 +126|263 +272|395 +25|82 +425|0 +140|256 +108|101 +115|379 +259|92 +243|\N +115|460 +97|197 +\N|203 +199|1 +275|275 +35|432 +220|94 +99|96 +120|85 +44|156 +373|95 +351|425 +475|410 +219|\N +114|150 +51|\N +234|376 +266|293 +280|132 +132|122 +450|475 +232|493 +278|110 +301|273 +147|136 +279|50 +311|179 +430|393 +14|82 +205|133 +198|343 +139|145 +470|142 +5|191 +429|351 +54|\N +485|24 +203|\N +359|146 +35|288 +126|234 +47|392 +170|487 +361|125 +322|335 +54|345 +453|9 +268|64 +117|402 +467|241 +\N|203 +431|490 +332|258 +322|\N +123|76 +313|200 +354|272 +\N|231 +102|444 +266|\N +226|463 +66|377 +194|\N +310|362 +209|314 +239|346 +317|130 +112|444 +12|254 +350|172 +369|402 +482|398 +427|171 +65|409 +304|135 +254|416 +79|359 +204|286 +\N|87 +175|159 +272|306 +265|441 +54|40 +336|311 +173|97 +84|220 +184|383 +196|192 +27|98 +162|4 +104|181 +153|320 +153|\N +106|329 +34|70 +228|227 +284|144 +453|284 +287|199 +82|\N +53|104 +436|340 +344|256 +410|215 +145|373 +95|427 +71|241 +101|436 +69|203 +389|231 +290|134 +279|103 +7|256 +405|132 +498|371 +\N|3 +438|101 +15|17 +338|297 +299|140 +62|\N +188|\N +138|55 +445|135 +425|26 +\N|398 +341|331 +160|313 +\N|76 +355|228 +163|5 +111|217 +62|151 +460|495 +411|437 +200|421 +163|\N +440|426 +151|281 +493|352 +352|103 +445|465 +176|380 +\N|198 +195|219 +468|\N +380|439 +61|307 +403|388 +\N|359 +69|410 +354|456 +491|80 +310|374 +162|426 +277|81 +176|463 +226|469 +446|79 +477|26 +86|231 +379|456 +414|251 +373|114 +499|416 +490|104 +65|237 +\N|493 +450|189 +1|432 +33|178 +160|158 +480|362 +135|420 +73|286 +149|402 +244|472 +305|181 +227|98 +412|209 +59|255 +49|198 +105|483 +445|\N +59|24 +237|178 +453|37 +54|111 +465|456 +94|395 +406|420 +413|38 +85|436 +61|57 +15|437 +95|\N +460|155 +471|230 +482|499 +302|389 +\N|456 +168|157 +284|386 +385|211 +461|490 +65|229 +335|128 +415|420 +374|12 +315|90 +103|226 +20|277 +101|233 +237|180 +101|399 +268|242 +164|249 +373|4 +81|141 +325|221 +\N|\N +438|302 +206|339 +30|90 +\N|\N +30|149 +49|183 +13|83 +83|27 +411|\N +91|495 +260|239 +435|329 +330|457 +318|284 +298|327 +230|50 +68|139 +55|120 +40|117 +185|\N +351|483 +242|199 +45|243 +341|491 +38|473 +289|474 +301|486 +59|272 +477|157 +108|379 +\N|232 +223|9 +388|187 +\N|211 +436|47 +500|410 +361|383 +228|455 +26|13 +\N|459 +396|153 +348|387 +420|39 +34|402 +183|249 +323|80 +36|147 +114|246 +457|383 +262|425 +253|460 +284|287 +48|486 +452|478 +327|362 +253|475 +190|217 +124|11 +67|485 +451|27 +478|256 +331|371 +88|451 +247|177 +479|221 +405|169 +85|315 +292|232 +100|44 +131|302 +\N|21 +204|284 +104|411 +482|444 +248|233 +425|159 +297|414 +18|385 +112|454 +22|164 +210|314 +267|116 +263|446 +171|294 +186|423 +181|339 +303|277 +51|485 +290|67 +\N|127 +63|143 +387|\N +\N|211 +286|69 +14|499 +100|430 +179|441 +473|22 +74|271 +106|294 +339|172 +58|423 +193|219 +267|59 +126|178 +356|185 +121|130 +413|103 +370|496 +364|196 +178|5 +124|437 +446|127 +151|313 +451|468 +419|106 +99|291 +\N|439 +327|493 +386|146 +\N|163 +\N|94 +327|278 +66|386 +\N|492 +461|146 +61|164 +273|146 +398|479 +499|65 +68|20 +163|65 +\N|58 +180|454 +401|5 +109|312 +268|475 +297|362 +137|178 +59|297 +74|\N +250|38 +137|244 +330|355 +456|62 +\N|292 +419|432 +166|214 +176|72 +167|459 +469|308 +425|299 +134|371 +98|336 +228|387 +409|119 +205|313 +308|317 +443|125 +259|432 +\N|95 +143|395 +224|416 +217|108 +29|386 +224|30 +378|43 +83|343 +43|202 +159|376 +495|425 +395|205 +459|161 +402|500 +304|84 +362|392 +176|187 +443|459 +226|185 +21|445 +500|\N +299|74 +0|208 +18|72 +483|320 +436|181 +338|47 +25|301 +461|54 +420|325 +219|148 +271|320 +378|45 +131|\N +97|142 +300|147 +165|228 +173|322 +337|247 +11|214 +325|199 +205|195 +\N|412 +211|36 +91|85 +281|97 +212|246 +258|354 +185|208 +11|310 +493|2 +238|327 +\N|480 +281|193 +442|373 +342|50 +357|171 +151|255 +477|273 +\N|251 +492|424 +185|325 +450|401 +241|435 +260|280 +500|122 +413|51 +76|360 +163|69 +186|200 +232|15 +259|179 +199|59 +261|82 +210|257 +453|275 +116|48 +464|382 +\N|485 +11|110 +302|324 +14|54 +85|1 +163|83 +\N|238 +356|244 +465|55 +329|455 +162|471 +316|459 +223|363 +258|321 +495|472 +373|56 +38|471 +36|430 +161|88 +459|258 +418|195 +105|319 +91|407 +365|55 +86|433 +240|467 +363|163 +343|68 +233|297 +484|282 diff --git a/regression-test/data/nereids_p0/hint/t6.csv b/regression-test/data/nereids_p0/hint/t6.csv new file mode 100644 index 00000000000..a473205388b --- /dev/null +++ b/regression-test/data/nereids_p0/hint/t6.csv @@ -0,0 +1,1000 @@ +141|422 +310|497 +10|317 +427|312 +286|263 +180|19 +265|224 +436|342 +455|379 +285|357 +490|79 +222|239 +124|22 +270|\N +190|266 +207|78 +380|63 +\N|9 +191|150 +6|385 +236|\N +8|182 +454|285 +141|160 +267|341 +215|322 +337|423 +488|105 +171|137 +120|314 +342|33 +295|351 +173|212 +80|2 +336|248 +383|423 +\N|122 +484|444 +\N|217 +134|10 +61|225 +362|115 +\N|\N +96|129 +404|194 +446|310 +127|299 +307|178 +477|428 +285|109 +434|292 +366|71 +384|469 +455|82 +220|423 +442|402 +355|474 +468|427 +435|90 +91|277 +61|463 +318|417 +\N|144 +328|150 +448|253 +69|258 +0|494 +93|195 +4|71 +16|374 +417|348 +463|\N +487|228 +479|399 +333|183 +109|17 +\N|343 +403|407 +141|136 +476|253 +396|287 +141|83 +363|181 +13|361 +\N|124 +235|49 +479|63 +316|428 +306|376 +293|364 +230|326 +237|361 +462|236 +139|245 +401|135 +\N|99 +13|194 +122|297 +81|332 +465|\N +51|367 +102|\N +221|\N +110|374 +97|198 +39|224 +330|365 +421|335 +72|164 +366|375 +360|375 +19|429 +282|179 +235|84 +465|198 +144|448 +335|108 +412|216 +267|328 +390|197 +388|266 +305|30 +427|131 +104|\N +284|349 +48|268 +404|81 +483|304 +371|62 +\N|345 +435|321 +137|26 +252|173 +389|394 +346|193 +37|140 +97|67 +175|343 +304|398 +219|471 +231|128 +\N|387 +167|411 +181|223 +166|240 +480|286 +293|365 +247|319 +267|363 +372|65 +239|32 +197|95 +24|\N +410|131 +180|196 +385|474 +175|272 +35|322 +170|46 +410|399 +168|456 +9|384 +262|493 +468|194 +141|227 +420|384 +95|183 +201|18 +320|456 +212|424 +69|280 +365|121 +25|\N +162|36 +479|223 +0|102 +101|277 +68|254 +343|150 +241|245 +367|360 +143|329 +241|157 +297|69 +369|281 +293|374 +450|170 +236|117 +226|327 +2|390 +101|278 +138|74 +91|311 +286|86 +59|218 +308|269 +203|370 +\N|446 +219|139 +212|175 +254|243 +135|104 +384|413 +231|234 +49|458 +300|183 +4|491 +177|105 +71|362 +154|188 +10|321 +266|\N +5|270 +160|202 +249|216 +472|21 +457|362 +481|291 +192|122 +203|353 +176|492 +\N|24 +419|114 +21|376 +300|185 +154|500 +12|64 +197|251 +75|367 +116|335 +47|278 +160|183 +52|403 +138|300 +343|301 +71|226 +32|485 +134|\N +449|236 +94|193 +240|414 +99|88 +138|372 +70|219 +6|348 +486|466 +99|27 +274|\N +394|500 +319|139 +401|242 +67|160 +4|93 +376|200 +471|447 +480|274 +\N|163 +401|469 +115|156 +26|286 +20|370 +167|410 +189|\N +69|411 +67|339 +193|66 +381|296 +159|341 +123|153 +\N|373 +123|190 +195|212 +20|354 +498|61 +164|154 +413|449 +34|246 +329|236 +\N|399 +287|299 +324|208 +138|32 +213|299 +256|264 +339|6 +95|\N +434|\N +335|331 +316|85 +355|436 +450|123 +345|343 +220|343 +165|160 +426|\N +155|385 +353|286 +398|294 +8|103 +326|384 +494|398 +247|120 +\N|435 +328|20 +436|156 +111|363 +91|456 +111|217 +24|470 +62|240 +147|\N +190|307 +384|364 +496|105 +286|225 +96|352 +452|258 +155|258 +288|86 +419|185 +366|45 +351|388 +68|431 +337|421 +381|408 +370|274 +279|98 +303|323 +77|90 +248|\N +234|20 +472|494 +186|257 +34|184 +40|321 +53|160 +368|123 +\N|164 +224|346 +80|474 +121|47 +483|68 +48|77 +42|334 +2|192 +368|231 +288|314 +290|168 +453|382 +239|324 +29|157 +57|21 +39|37 +201|208 +478|444 +270|59 +201|195 +435|425 +498|85 +169|224 +80|370 +251|224 +58|\N +463|89 +368|433 +60|128 +452|407 +19|239 +373|211 +85|468 +339|444 +141|133 +458|172 +218|486 +374|246 +14|246 +108|378 +181|39 +124|450 +272|209 +265|370 +324|61 +172|\N +\N|285 +338|118 +77|315 +101|281 +345|476 +95|172 +32|150 +91|475 +447|327 +447|29 +19|54 +143|\N +362|142 +181|479 +219|11 +451|127 +244|275 +493|155 +490|435 +254|30 +480|159 +45|97 +299|494 +90|141 +201|340 +338|71 +166|325 +429|499 +160|424 +497|412 +267|362 +139|69 +165|67 +393|332 +101|311 +415|196 +156|376 +118|259 +140|307 +316|361 +37|196 +402|285 +28|372 +139|321 +206|159 +308|161 +453|95 +189|166 +132|82 +367|413 +266|219 +359|177 +\N|248 +82|428 +230|97 +246|418 +138|\N +38|447 +91|292 +2|33 +296|228 +181|189 +\N|28 +138|283 +35|39 +84|113 +416|248 +183|\N +444|371 +290|278 +465|173 +31|268 +383|456 +474|21 +410|423 +192|468 +306|202 +62|263 +282|283 +472|43 +23|431 +154|339 +201|156 +309|163 +180|311 +1|276 +478|382 +217|441 +\N|409 +13|267 +471|464 +282|390 +264|441 +28|269 +270|174 +217|46 +232|23 +272|164 +21|299 +460|69 +177|387 +406|269 +32|157 +175|238 +303|21 +408|122 +357|441 +329|162 +356|205 +84|108 +1|375 +131|301 +354|433 +94|283 +427|141 +389|370 +463|145 +454|446 +372|\N +278|284 +316|63 +160|261 +399|275 +187|241 +480|266 +57|181 +324|266 +147|36 +228|306 +52|188 +120|52 +468|310 +68|458 +394|134 +\N|325 +144|217 +41|215 +73|226 +399|374 +278|77 +288|337 +451|64 +381|495 +265|348 +385|360 +111|479 +336|89 +179|472 +163|211 +106|465 +495|10 +294|455 +215|428 +209|131 +161|297 +437|292 +244|119 +11|442 +242|498 +151|0 +491|112 +302|262 +17|442 +337|499 +353|69 +339|52 +165|290 +15|494 +447|69 +343|141 +250|45 +309|164 +148|377 +226|469 +138|426 +66|260 +115|458 +272|17 +61|233 +62|500 +361|252 +181|143 +283|387 +333|460 +190|480 +103|89 +87|30 +80|104 +320|456 +259|\N +40|458 +403|327 +461|216 +168|394 +159|358 +401|138 +133|101 +308|353 +94|120 +30|25 +408|54 +120|58 +469|202 +276|217 +187|319 +11|22 +199|481 +\N|83 +118|187 +178|18 +259|230 +86|374 +83|253 +151|293 +427|63 +100|416 +428|63 +13|491 +197|53 +250|104 +324|328 +94|31 +132|221 +3|235 +463|76 +88|199 +113|104 +377|213 +\N|175 +2|438 +213|434 +44|358 +77|480 +423|128 +108|108 +303|160 +25|321 +321|466 +300|170 +475|359 +40|330 +65|10 +239|235 +108|389 +73|149 +379|123 +25|11 +56|132 +279|45 +\N|218 +422|498 +348|285 +320|329 +350|433 +479|362 +39|7 +\N|481 +342|122 +477|166 +163|444 +408|362 +214|128 +225|161 +428|\N +123|234 +174|457 +\N|250 +171|266 +499|294 +101|\N +248|88 +\N|302 +196|313 +398|204 +146|32 +460|\N +8|97 +255|213 +199|379 +250|36 +495|460 +88|273 +329|75 +179|198 +147|386 +216|155 +\N|200 +259|88 +155|3 +103|394 +46|\N +331|85 +155|144 +54|461 +93|216 +\N|413 +\N|335 +452|355 +449|398 +206|67 +225|411 +370|70 +91|107 +421|367 +306|36 +327|161 +375|20 +405|197 +482|308 +243|209 +36|290 +349|35 +309|425 +47|202 +399|249 +\N|5 +138|164 +456|464 +267|192 +398|73 +\N|418 +\N|474 +162|363 +300|38 +\N|37 +230|20 +469|187 +140|319 +\N|198 +230|456 +446|464 +\N|394 +426|399 +192|296 +386|345 +158|65 +436|263 +256|492 +224|410 +239|43 +187|57 +457|448 +13|288 +49|366 +234|435 +455|20 +306|34 +60|7 +210|485 +\N|399 +101|356 +96|410 +\N|455 +249|396 +448|186 +130|310 +168|489 +222|480 +104|146 +349|431 +379|336 +233|409 +53|415 +181|236 +120|239 +437|460 +117|352 +399|316 +224|159 +408|310 +463|140 +251|427 +239|213 +255|\N +380|\N +325|268 +\N|411 +204|\N +230|373 +64|484 +226|371 +404|464 +53|81 +188|479 +216|254 +17|172 +329|233 +75|377 +114|233 +124|\N +411|244 +219|173 +254|168 +96|\N +325|84 +321|109 +74|14 +311|168 +81|221 +489|466 +194|406 +297|234 +291|498 +442|408 +94|238 +183|193 +189|3 +352|434 +93|240 +475|\N +88|15 +407|\N +318|199 +216|9 +48|462 +450|167 +159|31 +100|313 +246|100 +\N|312 +290|457 +419|465 +478|348 +128|205 +130|238 +123|114 +351|391 +342|195 +67|148 +224|180 +106|432 +\N|29 +279|342 +93|94 +302|347 +67|213 +207|326 +197|90 +120|257 +163|\N +208|489 +386|287 +362|142 +394|455 +383|361 +50|54 +87|452 +72|340 +60|458 +219|353 +280|331 +222|405 +16|283 +\N|496 +256|475 +170|397 +188|386 +164|225 +178|238 +440|141 +424|184 +173|393 +339|205 +498|\N +229|281 +302|417 +42|223 +72|183 +155|256 +297|390 +241|107 +195|208 +128|411 +400|396 +319|69 +180|479 +429|99 +269|96 +460|228 +261|342 +454|247 +365|\N +496|437 +362|323 +485|433 +469|139 +443|320 +300|204 +219|447 +322|359 +183|320 +251|113 +31|388 +364|45 +171|68 +436|175 +72|385 +147|258 +6|389 +316|87 +39|381 +143|155 +387|17 +342|229 +48|134 +164|35 +460|442 +493|267 +395|400 +100|244 +292|5 +400|296 +144|326 +493|111 +391|500 +44|50 +230|361 +168|56 +210|\N +156|354 +457|470 +137|405 +238|119 +71|198 +54|29 +498|348 +29|201 +104|477 +378|121 +382|370 +498|160 +256|393 +165|22 +66|20 +57|385 +314|90 +150|63 +16|195 +445|220 +340|213 +145|29 +404|88 +94|235 +253|11 +199|464 +\N|318 +167|75 +127|49 +186|136 +274|198 +494|465 +96|347 +191|\N +431|421 +\N|113 +260|\N +116|259 +220|216 +113|455 +453|146 +2|444 +302|34 +\N|53 +302|37 +318|434 +73|485 +444|60 +287|301 +117|92 +167|323 +476|393 +21|229 +307|140 +130|440 +88|220 +361|47 +297|147 +307|290 +67|251 +8|311 +248|137 +79|463 +188|231 +445|62 +430|223 +\N|78 +485|365 +9|149 +304|302 +266|166 +\N|235 +346|8 +309|202 +475|420 +493|237 +211|\N +479|250 +425|\N +95|57 +3|242 +420|277 +\N|28 +132|194 +447|411 +480|210 +1|239 +378|101 +338|92 +491|250 +408|91 +31|153 +422|308 +283|364 +489|243 +167|493 +37|81 +491|285 diff --git a/regression-test/suites/nereids_p0/hint/fix_leading.groovy b/regression-test/suites/nereids_p0/hint/fix_leading.groovy index 4681fa16e06..ae17b17c53a 100644 --- a/regression-test/suites/nereids_p0/hint/fix_leading.groovy +++ b/regression-test/suites/nereids_p0/hint/fix_leading.groovy @@ -79,6 +79,24 @@ suite("fix_leading") { time 10000 } + streamLoad { + table "t5" + db "fix_leading" + set 'column_separator', '|' + set 'format', 'csv' + file 't5.csv' + time 10000 + } + + streamLoad { + table "t6" + db "fix_leading" + set 'column_separator', '|' + set 'format', 'csv' + file 't6.csv' + time 10000 + } + // 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;""" @@ -222,4 +240,31 @@ suite("fix_leading") { 5;""" contains("Used: leading({ tbl2 tbl3 } tbl1 )") } + + // check cte as input in alias leading query + explain { + sql """shape plan WITH tbl1 AS ( + SELECT + tbl1.c1 AS c111, + tbl2.c2 as c222 + FROM + t1 AS tbl1 + RIGHT JOIN t2 AS tbl2 ON tbl1.c1 = tbl2.c2 + ) + SELECT + tbl3.c3, + tbl2.c2 + FROM + ( + SELECT + /*+ leading( tbl2 tbl1 ) */ + tbl1.c111 AS c1, + tbl2.c2 AS c2 + FROM + t2 AS tbl2 + JOIN tbl1 ON tbl2.c2 = tbl1.c111 + ) AS tbl2 + RIGHT JOIN t3 AS tbl3 ON tbl2.c2 = tbl3.c3;""" + contains("Used: leading(tbl2 tbl1 )") + } } --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
