[fix](nereids) fix subquery unnest can not found aggregate slot #51086

924060929 · 2025-05-20T10:05:13Z

What problem does this PR solve?

when exists multiple subquery in a query block

select case
                when t1.k1=1 then (select count(*) from t2 where t1.k2=t2.k2) -- count(*)#7
                when t1.k1=2 then (select count(*) from t3 where t1.k2=t3.k2) -- count(*)#12
                else 0 end as kk
        from t1
        order by kk

the aggregate function output maybe bind failed and then throw exception

org.apache.doris.nereids.exceptions.AnalysisException: Input slot(s) not in child's output: count(*)#7

because we only replace the last aggregate function slot(only replace the #12, but not replace the #7 ):

this pr fix subquery unnest can not found aggregate slot, introduced by #39471

Check List (For Author)

Test
- Regression test
- Unit Test
- Manual test (add detailed scripts or steps below)
- No need to test or manual test. Explain why:
  - This is a refactor/code format and no logic has been changed.
  - Previous test can cover this change.
  - No code files have been changed.
  - Other reason
Behavior changed:
- No.
- Yes.
Does this need documentation?
- No.
- Yes.

Check List (For Reviewer who merge this PR)

Confirm the release note
Confirm test cases
Confirm document
Add branch pick label

hello-stephen · 2025-05-20T10:05:24Z

Thank you for your contribution to Apache Doris.
Don't know what should be done next? See How to process your PR.

Please clearly describe your PR:

What problem was fixed (it's best to include specific error reporting information). How it was fixed.
Which behaviors were modified. What was the previous behavior, what is it now, why was it modified, and what possible impacts might there be.
What features were added. Why was this function added?
Which code was refactored and why was this part of the code refactored?
Which functions were optimized and what is the difference before and after the optimization?

924060929 · 2025-05-20T10:05:28Z

run buildall

fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/SubqueryToApply.java

doris-robot · 2025-05-20T10:33:10Z

TPC-H: Total hot run time: 33889 ms

machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpch-tools
Tpch sf100 test result on commit a6b4186c3666ee8ac1cd8493367cea7bc2df6693, data reload: false

------ Round 1 ----------------------------------
q1	26321	5052	5009	5009
q2	2059	281	193	193
q3	10395	1236	698	698
q4	10224	999	515	515
q5	7498	2309	2333	2309
q6	177	164	131	131
q7	927	756	612	612
q8	9309	1297	1070	1070
q9	6823	5101	5118	5101
q10	6931	2401	1921	1921
q11	482	302	288	288
q12	354	358	233	233
q13	17913	3748	3132	3132
q14	233	232	215	215
q15	532	479	490	479
q16	423	439	378	378
q17	604	848	366	366
q18	7535	7141	7158	7141
q19	2172	958	555	555
q20	315	332	213	213
q21	3596	3209	2364	2364
q22	1050	988	966	966
Total cold run time: 115873 ms
Total hot run time: 33889 ms

----- Round 2, with runtime_filter_mode=off -----
q1	5173	5083	5057	5057
q2	238	313	226	226
q3	2120	2678	2282	2282
q4	1385	1772	1423	1423
q5	4461	4348	4368	4348
q6	216	164	126	126
q7	2024	1913	1769	1769
q8	2591	2562	2518	2518
q9	7242	7219	6945	6945
q10	3066	3178	2823	2823
q11	560	503	493	493
q12	663	762	663	663
q13	3543	3919	3320	3320
q14	315	299	267	267
q15	519	474	454	454
q16	428	507	439	439
q17	1164	1493	1419	1419
q18	7610	7587	7375	7375
q19	803	797	920	797
q20	1965	1960	1828	1828
q21	4813	4489	4566	4489
q22	1083	1043	996	996
Total cold run time: 51982 ms
Total hot run time: 50057 ms

doris-robot · 2025-05-20T10:45:26Z

TPC-DS: Total hot run time: 193380 ms

machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpcds-tools
TPC-DS sf100 test result on commit a6b4186c3666ee8ac1cd8493367cea7bc2df6693, data reload: false

query1	1393	1095	1033	1033
query2	6079	1832	1809	1809
query3	11017	4598	4437	4437
query4	54791	24720	23469	23469
query5	5231	523	457	457
query6	370	212	209	209
query7	4970	520	302	302
query8	329	261	244	244
query9	5922	2650	2629	2629
query10	441	325	282	282
query11	15443	15065	14988	14988
query12	164	111	106	106
query13	1082	530	408	408
query14	10200	6334	6450	6334
query15	207	201	176	176
query16	7093	660	461	461
query17	1103	744	603	603
query18	1533	412	336	336
query19	248	205	180	180
query20	122	119	126	119
query21	207	124	105	105
query22	4430	4522	4394	4394
query23	34749	33615	33717	33615
query24	6530	2456	2436	2436
query25	461	482	421	421
query26	689	272	149	149
query27	2204	511	342	342
query28	2969	2145	2105	2105
query29	585	586	446	446
query30	276	235	217	217
query31	857	852	800	800
query32	77	60	66	60
query33	463	383	307	307
query34	800	871	550	550
query35	805	852	739	739
query36	957	1004	931	931
query37	118	101	77	77
query38	4345	4186	4319	4186
query39	1500	1445	1480	1445
query40	217	120	111	111
query41	55	51	51	51
query42	132	118	118	118
query43	499	514	475	475
query44	1346	842	832	832
query45	177	181	164	164
query46	869	1050	651	651
query47	1881	1901	1775	1775
query48	412	449	317	317
query49	682	539	431	431
query50	687	706	416	416
query51	4307	4375	4360	4360
query52	115	108	104	104
query53	230	269	190	190
query54	588	594	517	517
query55	84	85	86	85
query56	303	318	293	293
query57	1217	1223	1142	1142
query58	283	267	265	265
query59	2709	2686	2651	2651
query60	344	347	318	318
query61	123	115	124	115
query62	739	746	719	719
query63	227	192	197	192
query64	1454	1000	665	665
query65	4302	4227	4254	4227
query66	748	408	301	301
query67	16038	15932	15491	15491
query68	7223	906	531	531
query69	541	319	275	275
query70	1180	1151	1057	1057
query71	503	320	286	286
query72	6023	4867	4840	4840
query73	1356	677	362	362
query74	8885	8897	8902	8897
query75	3899	3212	2676	2676
query76	4220	1220	740	740
query77	712	376	283	283
query78	10211	10195	9359	9359
query79	2197	816	588	588
query80	672	514	429	429
query81	490	248	223	223
query82	434	122	98	98
query83	339	251	242	242
query84	297	110	90	90
query85	784	357	297	297
query86	391	308	260	260
query87	4439	4450	4360	4360
query88	3808	2357	2332	2332
query89	406	322	272	272
query90	1838	211	213	211
query91	149	143	112	112
query92	74	60	63	60
query93	1903	968	570	570
query94	668	396	304	304
query95	377	287	291	287
query96	492	597	284	284
query97	2748	2818	2696	2696
query98	230	217	204	204
query99	1464	1387	1272	1272
Total cold run time: 300481 ms
Total hot run time: 193380 ms

doris-robot · 2025-05-20T10:50:38Z

ClickBench: Total hot run time: 28.91 s

machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/clickbench-tools
ClickBench test result on commit a6b4186c3666ee8ac1cd8493367cea7bc2df6693, data reload: false

query1	0.04	0.04	0.03
query2	0.12	0.11	0.11
query3	0.27	0.19	0.19
query4	1.59	0.20	0.11
query5	0.43	0.43	0.45
query6	1.17	0.67	0.64
query7	0.02	0.01	0.02
query8	0.04	0.03	0.03
query9	0.58	0.52	0.52
query10	0.56	0.57	0.57
query11	0.16	0.11	0.11
query12	0.15	0.12	0.11
query13	0.62	0.59	0.60
query14	0.78	0.82	0.80
query15	0.88	0.86	0.89
query16	0.38	0.38	0.37
query17	1.02	1.05	1.00
query18	0.22	0.21	0.21
query19	1.97	1.84	1.84
query20	0.01	0.01	0.01
query21	15.40	0.88	0.56
query22	0.79	1.06	0.63
query23	15.04	1.40	0.65
query24	6.83	1.84	0.81
query25	0.52	0.29	0.09
query26	0.57	0.17	0.13
query27	0.06	0.05	0.04
query28	9.75	0.89	0.45
query29	12.63	3.94	3.27
query30	0.25	0.10	0.07
query31	2.83	0.60	0.38
query32	3.23	0.56	0.47
query33	3.03	3.01	3.09
query34	15.76	5.08	4.50
query35	4.53	4.56	4.50
query36	0.66	0.51	0.50
query37	0.08	0.06	0.06
query38	0.05	0.04	0.03
query39	0.03	0.02	0.03
query40	0.18	0.13	0.13
query41	0.08	0.02	0.02
query42	0.03	0.02	0.02
query43	0.04	0.04	0.03
Total cold run time: 103.38 s
Total hot run time: 28.91 s

924060929 · 2025-05-21T03:56:39Z

run buildall

github-actions · 2025-05-21T04:10:05Z

PR approved by at least one committer and no changes requested.

github-actions · 2025-05-21T04:10:07Z

PR approved by anyone and no changes requested.

doris-robot · 2025-05-21T05:03:09Z

TPC-H: Total hot run time: 34242 ms

machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpch-tools
Tpch sf100 test result on commit d0843f96c548167be449178016a0b3e9316e7645, data reload: false

------ Round 1 ----------------------------------
q1	26654	5078	5061	5061
q2	2080	295	185	185
q3	10529	1288	720	720
q4	10233	1009	557	557
q5	7703	2413	2364	2364
q6	186	167	136	136
q7	927	745	634	634
q8	9317	1314	1126	1126
q9	6877	5209	5129	5129
q10	6829	2335	1901	1901
q11	474	299	284	284
q12	362	360	221	221
q13	17771	3666	3091	3091
q14	235	225	213	213
q15	536	492	491	491
q16	436	428	377	377
q17	628	877	370	370
q18	7685	7307	7179	7179
q19	1419	959	564	564
q20	338	335	227	227
q21	4015	3212	2464	2464
q22	1029	1028	948	948
Total cold run time: 116263 ms
Total hot run time: 34242 ms

----- Round 2, with runtime_filter_mode=off -----
q1	5183	5184	5135	5135
q2	229	315	232	232
q3	2167	2684	2299	2299
q4	1357	1809	1464	1464
q5	4551	4493	4388	4388
q6	210	170	129	129
q7	1990	1860	1743	1743
q8	2594	2506	2518	2506
q9	7286	7277	7158	7158
q10	3018	3208	2774	2774
q11	588	512	492	492
q12	684	776	633	633
q13	3473	3897	3221	3221
q14	292	306	267	267
q15	520	476	474	474
q16	449	501	446	446
q17	1164	1590	1357	1357
q18	7915	7560	7474	7474
q19	806	870	1117	870
q20	1973	2063	1868	1868
q21	4930	4288	4242	4242
q22	1060	1035	1002	1002
Total cold run time: 52439 ms
Total hot run time: 50174 ms

doris-robot · 2025-05-21T05:14:46Z

TPC-DS: Total hot run time: 186838 ms

machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpcds-tools
TPC-DS sf100 test result on commit d0843f96c548167be449178016a0b3e9316e7645, data reload: false

query1	990	488	494	488
query2	6568	1903	1881	1881
query3	6761	239	227	227
query4	26813	23383	23206	23206
query5	4364	607	446	446
query6	304	204	187	187
query7	4614	490	292	292
query8	283	244	240	240
query9	8582	2650	2646	2646
query10	470	322	272	272
query11	15369	15332	14934	14934
query12	159	109	105	105
query13	1641	516	409	409
query14	8768	6258	6123	6123
query15	196	201	175	175
query16	7144	642	468	468
query17	987	747	582	582
query18	1979	416	316	316
query19	197	196	167	167
query20	122	118	119	118
query21	213	130	109	109
query22	4361	4153	4103	4103
query23	34068	33080	33136	33080
query24	8422	2408	2412	2408
query25	571	493	420	420
query26	1241	284	161	161
query27	2710	514	348	348
query28	4376	2139	2108	2108
query29	765	563	446	446
query30	286	215	186	186
query31	935	831	751	751
query32	73	64	65	64
query33	575	353	326	326
query34	812	873	535	535
query35	770	822	747	747
query36	958	987	868	868
query37	110	102	154	102
query38	4129	4091	4098	4091
query39	1487	1406	1436	1406
query40	212	126	110	110
query41	55	56	56	56
query42	123	114	109	109
query43	532	514	483	483
query44	1307	827	825	825
query45	179	187	172	172
query46	852	1041	639	639
query47	1758	1780	1717	1717
query48	391	420	322	322
query49	777	531	443	443
query50	666	692	440	440
query51	4113	4139	4100	4100
query52	116	108	104	104
query53	224	264	188	188
query54	584	587	505	505
query55	92	86	89	86
query56	334	318	310	310
query57	1146	1142	1082	1082
query58	271	256	255	255
query59	2657	2714	2586	2586
query60	345	323	322	322
query61	127	127	133	127
query62	797	766	680	680
query63	230	192	192	192
query64	4416	1018	673	673
query65	4364	4249	4293	4249
query66	1145	411	319	319
query67	15979	15789	15574	15574
query68	8048	900	520	520
query69	477	314	282	282
query70	1159	1131	1119	1119
query71	478	321	294	294
query72	5811	4806	4839	4806
query73	740	649	353	353
query74	8916	9090	9006	9006
query75	3816	3204	2695	2695
query76	3641	1198	747	747
query77	798	378	281	281
query78	10251	10052	9394	9394
query79	3033	795	570	570
query80	647	549	441	441
query81	458	266	222	222
query82	445	128	99	99
query83	288	254	240	240
query84	300	119	89	89
query85	781	348	314	314
query86	341	296	359	296
query87	4527	4509	4318	4318
query88	3310	2314	2315	2314
query89	431	319	288	288
query90	1955	209	204	204
query91	145	144	116	116
query92	73	60	58	58
query93	2558	925	576	576
query94	680	407	288	288
query95	373	293	294	293
query96	501	584	288	288
query97	2783	2764	2632	2632
query98	246	214	215	214
query99	1443	1385	1272	1272
Total cold run time: 276418 ms
Total hot run time: 186838 ms

doris-robot · 2025-05-21T05:20:06Z

ClickBench: Total hot run time: 29.2 s

machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/clickbench-tools
ClickBench test result on commit d0843f96c548167be449178016a0b3e9316e7645, data reload: false

query1	0.04	0.04	0.03
query2	0.12	0.10	0.10
query3	0.26	0.20	0.21
query4	1.59	0.21	0.11
query5	0.44	0.42	0.42
query6	1.16	0.66	0.66
query7	0.02	0.02	0.01
query8	0.04	0.04	0.04
query9	0.59	0.52	0.51
query10	0.58	0.59	0.57
query11	0.15	0.11	0.11
query12	0.15	0.11	0.12
query13	0.62	0.61	0.60
query14	0.80	0.80	0.83
query15	0.88	0.86	0.87
query16	0.37	0.38	0.37
query17	1.02	1.08	1.05
query18	0.23	0.21	0.21
query19	1.88	1.78	1.82
query20	0.02	0.01	0.01
query21	15.40	0.90	0.54
query22	0.76	1.28	0.68
query23	14.81	1.35	0.61
query24	6.89	1.59	0.91
query25	0.49	0.18	0.12
query26	0.58	0.17	0.15
query27	0.05	0.05	0.05
query28	9.68	0.89	0.47
query29	12.62	3.98	3.33
query30	0.25	0.09	0.07
query31	2.84	0.60	0.38
query32	3.24	0.55	0.46
query33	3.04	3.14	3.09
query34	15.82	5.03	4.47
query35	4.55	4.52	4.50
query36	0.65	0.51	0.49
query37	0.09	0.06	0.06
query38	0.06	0.04	0.04
query39	0.03	0.02	0.03
query40	0.18	0.14	0.13
query41	0.08	0.03	0.03
query42	0.04	0.02	0.02
query43	0.04	0.04	0.03
Total cold run time: 103.15 s
Total hot run time: 29.2 s

when exists multiple subquery in a query block ```sql select case when t1.k1=1 then (select count(*) from t2 where t1.k2=t2.k2) -- count(*)#7 when t1.k1=2 then (select count(*) from t3 where t1.k2=t3.k2) -- count(*)#12 else 0 end as kk from t1 order by kk ``` the aggregate function output maybe bind failed and then throw exception ``` org.apache.doris.nereids.exceptions.AnalysisException: Input slot(s) not in child's output: count(*)#7 ``` because we only replace the last aggregate function slot(only replace the `#12`, but not replace the `#7` ): this pr fix subquery unnest can not found aggregate slot, introduced by #39471

…e slot #51086 (#51137) Cherry-picked from #51086 Co-authored-by: 924060929 <lanhuajian@selectdb.com>

…he#51086) when exists multiple subquery in a query block ```sql select case when t1.k1=1 then (select count(*) from t2 where t1.k2=t2.k2) -- count(*)apache#7 when t1.k1=2 then (select count(*) from t3 where t1.k2=t3.k2) -- count(*)apache#12 else 0 end as kk from t1 order by kk ``` the aggregate function output maybe bind failed and then throw exception ``` org.apache.doris.nereids.exceptions.AnalysisException: Input slot(s) not in child's output: count(*)apache#7 ``` because we only replace the last aggregate function slot(only replace the `apache#12`, but not replace the `apache#7` ): this pr fix subquery unnest can not found aggregate slot, introduced by apache#39471

…e slot apache#51086 (apache#51137) Cherry-picked from apache#51086 Co-authored-by: 924060929 <lanhuajian@selectdb.com>

924060929 added usercase Important user case type label dev/3.0.x labels May 20, 2025

924060929 changed the title ~~[fix](nereids) fix subquery unnested can not found slot~~ [fix](nereids) fix subquery unnested can not found aggregate slot May 20, 2025

924060929 changed the title ~~[fix](nereids) fix subquery unnested can not found aggregate slot~~ [fix](nereids) fix subquery unnest can not found aggregate slot May 20, 2025

morrySnow reviewed May 20, 2025

View reviewed changes

fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/SubqueryToApply.java Outdated Show resolved Hide resolved

fix

d0843f9

924060929 force-pushed the fix_subquery_not_slot branch from a6b4186 to d0843f9 Compare May 21, 2025 03:55

morrySnow approved these changes May 21, 2025

View reviewed changes

github-actions bot added the approved Indicates a PR has been approved by one committer. label May 21, 2025

github-actions bot added the reviewed label May 21, 2025

starocean999 approved these changes May 21, 2025

View reviewed changes

924060929 merged commit b289297 into apache:master May 22, 2025
28 of 30 checks passed

924060929 deleted the fix_subquery_not_slot branch May 22, 2025 03:30

github-actions bot mentioned this pull request May 22, 2025

branch-3.0: [fix](nereids) fix subquery unnest can not found aggregate slot #51086 #51137

Merged

dataroaring pushed a commit that referenced this pull request May 22, 2025

branch-3.0: [fix](nereids) fix subquery unnest can not found aggregat…

566cc51

…e slot #51086 (#51137) Cherry-picked from #51086 Co-authored-by: 924060929 <lanhuajian@selectdb.com>

dataroaring added dev/3.0.6-merged and removed dev/3.0.x labels May 22, 2025

[fix](nereids) fix subquery unnest can not found aggregate slot #51086

[fix](nereids) fix subquery unnest can not found aggregate slot #51086

Uh oh!

Conversation

924060929 commented May 20, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

What problem does this PR solve?

Check List (For Author)

Check List (For Reviewer who merge this PR)

Uh oh!

hello-stephen commented May 20, 2025

Uh oh!

924060929 commented May 20, 2025

Uh oh!

Uh oh!

doris-robot commented May 20, 2025

Uh oh!

doris-robot commented May 20, 2025

Uh oh!

doris-robot commented May 20, 2025

Uh oh!

924060929 commented May 21, 2025

Uh oh!

github-actions bot commented May 21, 2025

Uh oh!

github-actions bot commented May 21, 2025

Uh oh!

doris-robot commented May 21, 2025

Uh oh!

doris-robot commented May 21, 2025

Uh oh!

doris-robot commented May 21, 2025

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

6 participants

924060929 commented May 20, 2025 •

edited

Loading