Skip to content

Conversation

@nextdreamblue
Copy link
Contributor

@nextdreamblue nextdreamblue commented Mar 12, 2024

Proposed changes

after #26886,
run this sql

SELECT
        caseId
    FROM
        (
            SELECT
                caseId,
                count(judgementDateId)
            FROM
                (
                    SELECT
                        abs(caseId) AS caseId,
                        id as judgementDateId
                    FROM
                        dr_user_test_t2
                ) AGG_RESULT
            GROUP BY
                caseId
        ) TOTAL
        order by 1;

will get

ERROR 1105 (HY000): errCode = 2, detailMessage = (172.17.0.1)[INTERNAL_ERROR]couldn't resolve slot descriptor 1, desc: tuples:
Tuple(id=5 slots=[Slot(id=10 type=DOUBLE col=-1, colname=, nullable=1), Slot(id=11 type=VARCHAR col=-1, colname=id, nullable=1)] has_varlen_slots=1)
Tuple(id=4 slots=[Slot(id=8 type=DOUBLE col=-1, colname=, nullable=1)] has_varlen_slots=0)
Tuple(id=2 slots=[Slot(id=4 type=DOUBLE col=-1, colname=caseId, nullable=1)] has_varlen_slots=0)
Tuple(id=0 slots=[Slot(id=0 type=VARCHAR col=-1, colname=caseId, nu

Further comments

If this is a relatively large or complex change, kick off the discussion at dev@doris.apache.org by explaining why you chose the solution you did and what alternatives you considered, etc...

…rialized

Signed-off-by: nextdreamblue <zxw520blue1@163.com>
@doris-robot
Copy link

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

@nextdreamblue
Copy link
Contributor Author

run buildall

@doris-robot
Copy link

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

------ Round 1 ----------------------------------
q1	17622	4150	4098	4098
q2	2020	155	143	143
q3	10576	926	875	875
q4	4654	710	743	710
q5	7565	2802	2781	2781
q6	194	124	128	124
q7	1170	822	796	796
q8	9485	2091	1983	1983
q9	7237	6452	6454	6452
q10	8138	2461	2463	2461
q11	418	224	215	215
q12	746	301	291	291
q13	17964	3021	3021	3021
q14	278	250	250	250
q15	509	451	449	449
q16	483	395	398	395
q17	940	635	548	548
q18	6633	5909	5808	5808
q19	1573	1439	1473	1439
q20	550	286	280	280
q21	7377	3271	3151	3151
q22	808	321	297	297
Total cold run time: 106940 ms
Total hot run time: 36567 ms

----- Round 2, with runtime_filter_mode=off -----
q1	4148	4077	4049	4049
q2	323	226	218	218
q3	2971	2826	2857	2826
q4	1782	1548	1555	1548
q5	5171	5200	5199	5199
q6	192	115	116	115
q7	2238	1861	1875	1861
q8	3131	3282	3283	3282
q9	8499	8542	8496	8496
q10	6142	3703	3637	3637
q11	531	442	448	442
q12	722	570	535	535
q13	11284	2857	2889	2857
q14	270	247	251	247
q15	478	470	445	445
q16	452	402	408	402
q17	1720	1494	1461	1461
q18	7684	7295	7116	7116
q19	6144	1492	1555	1492
q20	1906	1726	1702	1702
q21	5003	4709	4791	4709
q22	536	468	454	454
Total cold run time: 71327 ms
Total hot run time: 53093 ms

@doris-robot
Copy link

TPC-DS: Total hot run time: 186803 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 688fc27e1d6237758c59dd65116a25f9c3a37d14, data reload: false

query1	924	357	347	347
query2	7665	2307	2097	2097
query3	6815	223	224	223
query4	31804	21059	20924	20924
query5	4867	401	408	401
query6	893	177	176	176
query7	5988	299	286	286
query8	231	167	170	167
query9	10460	2301	2299	2299
query10	457	247	270	247
query11	17075	14052	14255	14052
query12	152	91	87	87
query13	1629	415	455	415
query14	11594	10696	10224	10224
query15	334	195	186	186
query16	8137	265	261	261
query17	2113	564	535	535
query18	2108	283	275	275
query19	321	157	154	154
query20	86	82	82	82
query21	197	131	126	126
query22	4610	4467	4424	4424
query23	32261	31104	31030	31030
query24	10618	2810	2801	2801
query25	602	403	360	360
query26	1211	147	151	147
query27	2420	362	343	343
query28	7414	1915	1863	1863
query29	860	602	627	602
query30	312	146	155	146
query31	960	702	715	702
query32	94	54	50	50
query33	755	242	245	242
query34	1035	462	470	462
query35	819	586	587	586
query36	1015	886	856	856
query37	122	74	73	73
query38	3293	3135	3130	3130
query39	1405	1390	1378	1378
query40	193	115	104	104
query41	47	44	43	43
query42	99	93	92	92
query43	474	453	443	443
query44	1237	689	674	674
query45	265	242	253	242
query46	1074	690	671	671
query47	1652	1593	1591	1591
query48	445	352	359	352
query49	1095	326	344	326
query50	767	368	381	368
query51	6733	6586	6487	6487
query52	111	87	95	87
query53	338	271	281	271
query54	308	227	242	227
query55	87	83	92	83
query56	236	228	241	228
query57	1086	1025	1030	1025
query58	227	210	199	199
query59	2790	2515	2579	2515
query60	261	236	247	236
query61	111	107	106	106
query62	596	418	396	396
query63	301	278	289	278
query64	5596	3880	3906	3880
query65	3028	3014	2993	2993
query66	866	329	333	329
query67	14773	14386	14471	14386
query68	8988	542	539	539
query69	632	375	383	375
query70	1231	1171	1115	1115
query71	557	275	283	275
query72	6945	2765	2540	2540
query73	725	326	314	314
query74	8494	6585	6554	6554
query75	10082	8074	8141	8074
query76	5512	910	1014	910
query77	875	252	252	252
query78	10389	9593	9589	9589
query79	12804	535	526	526
query80	1407	369	374	369
query81	514	207	220	207
query82	822	198	199	198
query83	199	142	136	136
query84	286	81	75	75
query85	1508	313	307	307
query86	418	297	282	282
query87	3395	3196	3223	3196
query88	5025	2384	2361	2361
query89	520	360	363	360
query90	2050	174	173	173
query91	158	127	129	127
query92	54	46	45	45
query93	7038	513	497	497
query94	1257	173	175	173
query95	419	331	327	327
query96	601	271	271	271
query97	4430	4302	4288	4288
query98	225	211	206	206
query99	1116	751	753	751
Total cold run time: 327128 ms
Total hot run time: 186803 ms

@doris-robot
Copy link

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

query1	0.03	0.03	0.03
query2	0.07	0.03	0.02
query3	0.22	0.06	0.06
query4	1.64	0.10	0.10
query5	0.53	0.51	0.53
query6	1.19	0.61	0.63
query7	0.02	0.02	0.02
query8	0.04	0.03	0.02
query9	0.55	0.50	0.48
query10	0.53	0.54	0.53
query11	0.12	0.09	0.09
query12	0.12	0.09	0.09
query13	0.57	0.57	0.56
query14	0.75	0.76	0.76
query15	0.79	0.77	0.78
query16	0.37	0.38	0.39
query17	0.96	0.93	1.02
query18	0.18	0.26	0.22
query19	1.82	1.72	1.74
query20	0.02	0.01	0.01
query21	15.40	0.54	0.55
query22	2.33	1.89	1.75
query23	17.04	0.80	0.74
query24	3.95	1.64	1.46
query25	0.20	0.06	0.10
query26	0.68	0.13	0.12
query27	0.03	0.04	0.04
query28	10.07	0.85	0.87
query29	12.49	3.23	3.16
query30	0.71	0.65	0.63
query31	2.81	0.34	0.33
query32	3.34	0.44	0.44
query33	2.78	2.88	2.86
query34	16.10	4.33	4.35
query35	4.41	4.32	4.45
query36	1.08	1.00	1.00
query37	0.07	0.05	0.05
query38	0.04	0.03	0.03
query39	0.03	0.01	0.01
query40	0.16	0.13	0.13
query41	0.08	0.02	0.01
query42	0.03	0.02	0.02
query43	0.03	0.02	0.02
Total cold run time: 104.38 s
Total hot run time: 30.81 s

@doris-robot
Copy link

Load test result on machine: 'aliyun_ecs.c7a.8xlarge_32C64G'

Load test result on commit 688fc27e1d6237758c59dd65116a25f9c3a37d14 with default session variables
Stream load json:         19 seconds loaded 2358488459 Bytes, about 118 MB/s
Stream load orc:          59 seconds loaded 1101869774 Bytes, about 17 MB/s
Stream load parquet:      31 seconds loaded 861443392 Bytes, about 26 MB/s
Insert into select:       16.7 seconds inserted 10000000 Rows, about 598K ops/s

Signed-off-by: nextdreamblue <zxw520blue1@163.com>
@nextdreamblue
Copy link
Contributor Author

run buildall

@doris-robot
Copy link

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

------ Round 1 ----------------------------------
q1	17721	4164	4111	4111
q2	2023	147	155	147
q3	10579	912	899	899
q4	5276	693	689	689
q5	7611	2770	2823	2770
q6	182	123	125	123
q7	1204	846	819	819
q8	9478	2046	1979	1979
q9	7375	6498	6481	6481
q10	8151	2540	2544	2540
q11	416	213	219	213
q12	765	303	288	288
q13	17940	3010	3006	3006
q14	275	258	248	248
q15	499	452	460	452
q16	473	392	408	392
q17	955	566	580	566
q18	6752	5983	5808	5808
q19	1575	1336	1439	1336
q20	533	311	277	277
q21	7356	3212	3242	3212
q22	800	294	337	294
Total cold run time: 107939 ms
Total hot run time: 36650 ms

----- Round 2, with runtime_filter_mode=off -----
q1	4133	4098	4116	4098
q2	327	224	232	224
q3	3001	2831	2849	2831
q4	1832	1540	1539	1539
q5	5209	5269	5261	5261
q6	199	116	118	116
q7	2292	1878	1820	1820
q8	3174	3290	3282	3282
q9	8599	8564	8562	8562
q10	6130	3654	3679	3654
q11	539	440	429	429
q12	743	577	551	551
q13	16935	2886	2852	2852
q14	270	246	269	246
q15	478	446	439	439
q16	453	398	410	398
q17	1717	1478	1464	1464
q18	7635	7209	7286	7209
q19	1603	1550	1513	1513
q20	1902	1702	1692	1692
q21	4914	4643	4591	4591
q22	551	453	457	453
Total cold run time: 72636 ms
Total hot run time: 53224 ms

@doris-robot
Copy link

TPC-DS: Total hot run time: 187409 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 c325499b1b02c504cf6414c4b1d94da1b0eaf894, data reload: false

query1	918	363	342	342
query2	7520	1997	1965	1965
query3	6699	212	213	212
query4	31178	20603	20614	20603
query5	4399	476	406	406
query6	277	179	174	174
query7	4623	320	290	290
query8	227	177	168	168
query9	9965	2322	2321	2321
query10	450	242	247	242
query11	14617	14170	14136	14136
query12	130	91	83	83
query13	1648	411	432	411
query14	11238	10209	10588	10209
query15	252	207	194	194
query16	7842	263	252	252
query17	1962	556	523	523
query18	1973	276	279	276
query19	214	160	142	142
query20	87	83	82	82
query21	198	131	121	121
query22	4837	4541	4599	4541
query23	31660	31063	31013	31013
query24	11068	2827	2832	2827
query25	629	356	407	356
query26	1575	152	158	152
query27	3139	361	356	356
query28	8241	1922	1876	1876
query29	1002	640	624	624
query30	306	148	150	148
query31	950	690	729	690
query32	91	59	53	53
query33	767	261	252	252
query34	1063	473	474	473
query35	846	628	603	603
query36	983	884	863	863
query37	122	75	76	75
query38	3336	3191	3204	3191
query39	1421	1411	1380	1380
query40	233	112	111	111
query41	51	47	48	47
query42	96	97	96	96
query43	490	447	461	447
query44	1261	711	719	711
query45	273	227	256	227
query46	1089	706	693	693
query47	1676	1607	1601	1601
query48	455	360	363	360
query49	1165	332	347	332
query50	754	375	374	374
query51	6757	6626	6686	6626
query52	105	91	95	91
query53	341	267	268	267
query54	326	249	248	248
query55	85	81	80	80
query56	245	234	233	233
query57	1084	1005	1028	1005
query58	236	199	208	199
query59	2774	2644	2634	2634
query60	263	256	251	251
query61	113	112	110	110
query62	589	392	404	392
query63	304	325	276	276
query64	6935	3819	3936	3819
query65	3095	3029	3053	3029
query66	1402	331	324	324
query67	15119	14840	14582	14582
query68	9231	551	535	535
query69	666	386	380	380
query70	1399	1207	1201	1201
query71	521	278	269	269
query72	6585	2649	2487	2487
query73	1622	328	328	328
query74	7677	6585	6602	6585
query75	9723	7996	8004	7996
query76	5615	949	977	949
query77	634	258	263	258
query78	10444	9794	9723	9723
query79	11625	523	515	515
query80	2241	392	379	379
query81	485	206	207	206
query82	333	203	197	197
query83	244	141	147	141
query84	280	73	80	73
query85	1240	324	319	319
query86	351	277	308	277
query87	3543	3306	3269	3269
query88	5283	2351	2365	2351
query89	463	357	352	352
query90	2058	174	171	171
query91	157	129	131	129
query92	57	47	46	46
query93	6539	515	499	499
query94	1375	173	174	173
query95	415	325	320	320
query96	606	269	270	269
query97	4429	4258	4291	4258
query98	224	209	204	204
query99	973	760	762	760
Total cold run time: 322807 ms
Total hot run time: 187409 ms

@doris-robot
Copy link

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

query1	0.04	0.03	0.03
query2	0.05	0.02	0.02
query3	0.23	0.06	0.06
query4	1.68	0.10	0.09
query5	0.48	0.52	0.51
query6	1.21	0.62	0.62
query7	0.02	0.01	0.01
query8	0.04	0.03	0.03
query9	0.54	0.50	0.50
query10	0.54	0.55	0.55
query11	0.11	0.09	0.09
query12	0.11	0.09	0.09
query13	0.57	0.57	0.56
query14	0.74	0.74	0.76
query15	0.82	0.78	0.79
query16	0.38	0.40	0.39
query17	0.98	0.95	1.00
query18	0.18	0.28	0.22
query19	1.73	1.66	1.68
query20	0.01	0.00	0.01
query21	15.41	0.56	0.56
query22	2.69	2.39	2.06
query23	17.18	0.76	0.78
query24	3.54	2.67	0.86
query25	0.10	0.12	0.16
query26	0.75	0.12	0.14
query27	0.03	0.03	0.03
query28	10.17	0.93	0.85
query29	12.53	3.19	3.19
query30	0.68	0.52	0.59
query31	2.80	0.36	0.34
query32	3.44	0.44	0.45
query33	2.79	2.80	2.82
query34	16.00	4.34	4.31
query35	4.31	4.30	4.37
query36	1.08	1.00	0.99
query37	0.07	0.05	0.06
query38	0.04	0.03	0.03
query39	0.02	0.02	0.02
query40	0.17	0.13	0.14
query41	0.08	0.02	0.02
query42	0.03	0.02	0.02
query43	0.03	0.02	0.02
Total cold run time: 104.4 s
Total hot run time: 30.44 s

@doris-robot
Copy link

Load test result on machine: 'aliyun_ecs.c7a.8xlarge_32C64G'

Load test result on commit c325499b1b02c504cf6414c4b1d94da1b0eaf894 with default session variables
Stream load json:         18 seconds loaded 2358488459 Bytes, about 124 MB/s
Stream load orc:          59 seconds loaded 1101869774 Bytes, about 17 MB/s
Stream load parquet:      31 seconds loaded 861443392 Bytes, about 26 MB/s
Insert into select:       17.9 seconds inserted 10000000 Rows, about 558K ops/s

@github-actions
Copy link
Contributor

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

@github-actions github-actions bot added approved Indicates a PR has been approved by one committer. reviewed labels Mar 12, 2024
@github-actions
Copy link
Contributor

PR approved by anyone and no changes requested.

@morrySnow morrySnow merged commit 6c66273 into apache:master Mar 12, 2024
nextdreamblue added a commit to nextdreamblue/doris that referenced this pull request Mar 12, 2024
…rialized (apache#32092)

introduced by apache#26886

run this sql:
SELECT
        caseId
    FROM
        (
            SELECT
                caseId,
                count(judgementDateId)
            FROM
                (
                    SELECT
                        abs(caseId) AS caseId,
                        id as judgementDateId
                    FROM
                        dr_user_test_t2
                ) AGG_RESULT
            GROUP BY
                caseId
        ) TOTAL
        order by 1;


will get:

ERROR 1105 (HY000): errCode = 2, detailMessage = (172.17.0.1)[INTERNAL_ERROR]couldn't resolve slot descriptor 1, desc: tuples:
Tuple(id=5 slots=[Slot(id=10 type=DOUBLE col=-1, colname=, nullable=1), Slot(id=11 type=VARCHAR col=-1, colname=id, nullable=1)] has_varlen_slots=1)
Tuple(id=4 slots=[Slot(id=8 type=DOUBLE col=-1, colname=, nullable=1)] has_varlen_slots=0)
Tuple(id=2 slots=[Slot(id=4 type=DOUBLE col=-1, colname=caseId, nullable=1)] has_varlen_slots=0)
Tuple(id=0 slots=[Slot(id=0 type=VARCHAR col=-1, colname=caseId, nu
yiguolei pushed a commit that referenced this pull request Mar 12, 2024
…rialized (#32092)

introduced by #26886

run this sql:
SELECT
        caseId
    FROM
        (
            SELECT
                caseId,
                count(judgementDateId)
            FROM
                (
                    SELECT
                        abs(caseId) AS caseId,
                        id as judgementDateId
                    FROM
                        dr_user_test_t2
                ) AGG_RESULT
            GROUP BY
                caseId
        ) TOTAL
        order by 1;


will get:

ERROR 1105 (HY000): errCode = 2, detailMessage = (172.17.0.1)[INTERNAL_ERROR]couldn't resolve slot descriptor 1, desc: tuples:
Tuple(id=5 slots=[Slot(id=10 type=DOUBLE col=-1, colname=, nullable=1), Slot(id=11 type=VARCHAR col=-1, colname=id, nullable=1)] has_varlen_slots=1)
Tuple(id=4 slots=[Slot(id=8 type=DOUBLE col=-1, colname=, nullable=1)] has_varlen_slots=0)
Tuple(id=2 slots=[Slot(id=4 type=DOUBLE col=-1, colname=caseId, nullable=1)] has_varlen_slots=0)
Tuple(id=0 slots=[Slot(id=0 type=VARCHAR col=-1, colname=caseId, nu
morrySnow pushed a commit that referenced this pull request Mar 12, 2024
…rialized (#32092) (#32133)

cherry-pick from master #27096

introduced by #26886

run this sql:
SELECT
        caseId
    FROM
        (
            SELECT
                caseId,
                count(judgementDateId)
            FROM
                (
                    SELECT
                        abs(caseId) AS caseId,
                        id as judgementDateId
                    FROM
                        dr_user_test_t2
                ) AGG_RESULT
            GROUP BY
                caseId
        ) TOTAL
        order by 1;


will get:

ERROR 1105 (HY000): errCode = 2, detailMessage = (172.17.0.1)[INTERNAL_ERROR]couldn't resolve slot descriptor 1, desc: tuples:
Tuple(id=5 slots=[Slot(id=10 type=DOUBLE col=-1, colname=, nullable=1), Slot(id=11 type=VARCHAR col=-1, colname=id, nullable=1)] has_varlen_slots=1)
Tuple(id=4 slots=[Slot(id=8 type=DOUBLE col=-1, colname=, nullable=1)] has_varlen_slots=0)
Tuple(id=2 slots=[Slot(id=4 type=DOUBLE col=-1, colname=caseId, nullable=1)] has_varlen_slots=0)
Tuple(id=0 slots=[Slot(id=0 type=VARCHAR col=-1, colname=caseId, nu
mongo360 pushed a commit to mongo360/doris that referenced this pull request Aug 16, 2024
…rialized (apache#32092) (apache#32133)

cherry-pick from master apache#27096

introduced by apache#26886

run this sql:
SELECT
        caseId
    FROM
        (
            SELECT
                caseId,
                count(judgementDateId)
            FROM
                (
                    SELECT
                        abs(caseId) AS caseId,
                        id as judgementDateId
                    FROM
                        dr_user_test_t2
                ) AGG_RESULT
            GROUP BY
                caseId
        ) TOTAL
        order by 1;

will get:

ERROR 1105 (HY000): errCode = 2, detailMessage = (172.17.0.1)[INTERNAL_ERROR]couldn't resolve slot descriptor 1, desc: tuples:
Tuple(id=5 slots=[Slot(id=10 type=DOUBLE col=-1, colname=, nullable=1), Slot(id=11 type=VARCHAR col=-1, colname=id, nullable=1)] has_varlen_slots=1)
Tuple(id=4 slots=[Slot(id=8 type=DOUBLE col=-1, colname=, nullable=1)] has_varlen_slots=0)
Tuple(id=2 slots=[Slot(id=4 type=DOUBLE col=-1, colname=caseId, nullable=1)] has_varlen_slots=0)
Tuple(id=0 slots=[Slot(id=0 type=VARCHAR col=-1, colname=caseId, nu
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

approved Indicates a PR has been approved by one committer. dev/2.0.7-merged reviewed

Projects

None yet

Development

Successfully merging this pull request may close these issues.

4 participants