Skip to content

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

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Merged
dataroaring merged 1 commit into from
May 22, 2025
Merged

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

dataroaring merged 1 commit into from
May 22, 2025

Conversation

@github-actions
Copy link
Contributor

@github-actions github-actions bot commented May 22, 2025

Cherry-picked from #51086

@924060929
[fix](nereids) fix subquery unnest can not found aggregate slot (#51086)
86cbc46 
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
@github-actions github-actions bot requested a review from dataroaring as a code owner May 22, 2025 03:31
@hello-stephen
Copy link
Contributor

hello-stephen commented May 22, 2025

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:

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

@dataroaring dataroaring reopened this May 22, 2025
@hello-stephen
Copy link
Contributor

hello-stephen commented May 22, 2025

run buildall

@doris-robot
Copy link

doris-robot commented May 22, 2025

TPC-H: Total hot run time: 39462 ms 
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpch-tools
Tpch sf100 test result on commit 86cbc469d0d458863d2de6ca180c81da6d7ae4ff, data reload: false

------ Round 1 ----------------------------------
q1	17593	6920	6547	6547
q2	2057	168	161	161
q3	10716	1060	1136	1060
q4	10536	729	754	729
q5	7731	2789	2766	2766
q6	211	134	132	132
q7	975	621	618	618
q8	9361	1898	2021	1898
q9	6573	6381	6406	6381
q10	6984	2297	2319	2297
q11	443	253	275	253
q12	393	212	210	210
q13	17795	2950	2999	2950
q14	225	199	211	199
q15	510	464	467	464
q16	473	395	385	385
q17	960	574	596	574
q18	7225	6605	6655	6605
q19	1401	1094	1123	1094
q20	481	198	200	198
q21	3966	2949	3126	2949
q22	1105	992	1014	992
Total cold run time: 107714 ms
Total hot run time: 39462 ms

----- Round 2, with runtime_filter_mode=off -----
q1	6525	6518	6504	6504
q2	327	232	236	232
q3	2871	2789	2879	2789
q4	2017	1760	1763	1760
q5	5801	5786	5685	5685
q6	208	129	127	127
q7	2161	1794	1784	1784
q8	3336	3553	3539	3539
q9	8958	8909	9024	8909
q10	3622	3592	3562	3562
q11	609	499	506	499
q12	820	633	642	633
q13	9596	3164	3103	3103
q14	312	271	289	271
q15	501	464	460	460
q16	488	439	433	433
q17	1813	1609	1613	1609
q18	8195	7832	7899	7832
q19	1687	1623	1463	1463
q20	2113	1880	1829	1829
q21	5452	5317	5328	5317
q22	1129	1026	1053	1026
Total cold run time: 68541 ms
Total hot run time: 59366 ms

@doris-robot
Copy link

doris-robot commented May 22, 2025

TPC-DS: Total hot run time: 196246 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 86cbc469d0d458863d2de6ca180c81da6d7ae4ff, data reload: false

query1	1282	905	895	895
query2	6313	1918	1885	1885
query3	10822	4274	4167	4167
query4	61386	28789	23703	23703
query5	5151	457	441	441
query6	399	176	189	176
query7	5454	316	304	304
query8	292	218	218	218
query9	8429	2607	2595	2595
query10	463	282	257	257
query11	17325	15182	15441	15182
query12	163	109	106	106
query13	1406	432	432	432
query14	10420	6936	7448	6936
query15	201	178	178	178
query16	7152	487	518	487
query17	1157	594	576	576
query18	1875	337	317	317
query19	220	168	169	168
query20	118	113	110	110
query21	205	104	104	104
query22	4759	4583	4767	4583
query23	34318	34130	33969	33969
query24	6183	2844	2905	2844
query25	498	402	398	398
query26	650	162	162	162
query27	1791	349	353	349
query28	4298	2168	2108	2108
query29	704	456	426	426
query30	253	160	158	158
query31	991	803	822	803
query32	73	55	55	55
query33	431	278	283	278
query34	922	516	513	513
query35	828	759	724	724
query36	1079	984	948	948
query37	113	65	74	65
query38	4066	3951	3948	3948
query39	1563	1471	1470	1470
query40	211	99	98	98
query41	49	51	47	47
query42	115	107	97	97
query43	535	493	501	493
query44	1159	806	803	803
query45	185	166	169	166
query46	1131	714	713	713
query47	2080	1955	1982	1955
query48	465	377	389	377
query49	740	400	413	400
query50	869	431	426	426
query51	7258	7206	7434	7206
query52	112	94	93	93
query53	267	183	184	183
query54	593	466	486	466
query55	82	79	80	79
query56	278	278	250	250
query57	1275	1149	1159	1149
query58	233	229	226	226
query59	3178	3128	3021	3021
query60	298	277	274	274
query61	140	133	139	133
query62	785	693	688	688
query63	216	185	186	185
query64	1490	697	636	636
query65	3229	3195	3243	3195
query66	708	295	293	293
query67	15816	15542	15405	15405
query68	4379	580	563	563
query69	435	264	277	264
query70	1197	1066	1051	1051
query71	360	250	252	250
query72	6309	3886	4020	3886
query73	753	347	356	347
query74	10330	8961	9211	8961
query75	3410	2638	2614	2614
query76	1963	1117	1150	1117
query77	523	279	269	269
query78	10620	9555	9510	9510
query79	2127	595	611	595
query80	1314	417	438	417
query81	518	223	221	221
query82	1210	95	88	88
query83	161	143	138	138
query84	282	86	80	80
query85	993	311	287	287
query86	388	308	301	301
query87	4394	4230	4190	4190
query88	3995	2390	2378	2378
query89	413	295	287	287
query90	1991	183	182	182
query91	186	151	152	151
query92	62	51	53	51
query93	2978	539	552	539
query94	763	271	303	271
query95	350	261	256	256
query96	630	280	278	278
query97	3307	3125	3158	3125
query98	206	205	205	205
query99	1592	1279	1291	1279
Total cold run time: 315389 ms
Total hot run time: 196246 ms

@doris-robot
Copy link

doris-robot commented May 22, 2025

ClickBench: Total hot run time: 30.26 s 
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/clickbench-tools
ClickBench test result on commit 86cbc469d0d458863d2de6ca180c81da6d7ae4ff, data reload: false

query1	0.03	0.04	0.03
query2	0.07	0.02	0.03
query3	0.24	0.07	0.06
query4	1.61	0.10	0.10
query5	0.53	0.52	0.51
query6	1.14	0.72	0.72
query7	0.02	0.01	0.02
query8	0.04	0.03	0.02
query9	0.56	0.50	0.50
query10	0.54	0.54	0.56
query11	0.13	0.10	0.12
query12	0.14	0.10	0.11
query13	0.61	0.60	0.60
query14	0.79	0.78	0.83
query15	0.84	0.82	0.81
query16	0.39	0.36	0.39
query17	0.99	1.06	1.05
query18	0.24	0.23	0.22
query19	1.92	1.87	1.80
query20	0.02	0.01	0.01
query21	15.39	0.60	0.59
query22	2.86	1.86	1.42
query23	16.96	0.80	0.79
query24	3.16	1.89	1.39
query25	0.37	0.10	0.16
query26	0.41	0.14	0.13
query27	0.06	0.04	0.06
query28	9.38	0.47	0.44
query29	12.60	3.30	3.29
query30	0.25	0.06	0.06
query31	2.87	0.39	0.39
query32	3.24	0.46	0.46
query33	2.99	3.04	3.01
query34	17.13	4.52	4.60
query35	4.50	4.52	4.51
query36	0.68	0.48	0.47
query37	0.09	0.06	0.06
query38	0.04	0.03	0.04
query39	0.04	0.02	0.02
query40	0.16	0.14	0.13
query41	0.08	0.03	0.02
query42	0.03	0.03	0.02
query43	0.04	0.03	0.03
Total cold run time: 104.18 s
Total hot run time: 30.26 s

dataroaring
dataroaring approved these changes May 22, 2025
View reviewed changes
Copy link
Contributor

@dataroaring dataroaring left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

LGTM

@dataroaring dataroaring merged commit 566cc51 into branch-3.0 May 22, 2025
22 of 24 checks passed
@github-actions github-actions bot deleted the auto-pick-51086-branch-3.0 branch May 22, 2025 06:21
Hastyshell pushed a commit to Hastyshell/doris that referenced this pull request Jul 21, 2025
@github-actions @924060929
branch-3.0: [fix](nereids) fix subquery unnest can not found aggregat…
f07c64d 
…e slot apache#51086 (apache#51137)

Cherry-picked from apache#51086

Co-authored-by: 924060929 <lanhuajian@selectdb.com>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

@dataroaring dataroaring dataroaring approved these changes

Assignees

No one assigned

Labels

None yet

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

5 participants

@hello-stephen @doris-robot @dataroaring @924060929