Skip to content

[fix](virtual slot) adjust virtual column expression nullable - #55694

Merged
englefly merged 2 commits into
apache:masterfrom
yujun777:fix-PushDownVirtualColumnsIntoOlapScan
Sep 5, 2025
Merged

[fix](virtual slot) adjust virtual column expression nullable#55694
englefly merged 2 commits into
apache:masterfrom
yujun777:fix-PushDownVirtualColumnsIntoOlapScan

Conversation

@yujun777

@yujun777 yujun777 commented Sep 5, 2025 •
edited
Loading

Copy link
Copy Markdown
Contributor

What problem does this PR solve?

need adjust nullable for expression of the virtual slot

bug introduced by: #52701

example as follow:

SQL:

        SELECT t1.*, t2.*
        FROM
            tbl_adjust_virtual_slot_nullable_1 AS t1
        LEFT JOIN tbl_adjust_virtual_slot_nullable_2 AS t2
        ON  t1.c_int = t2.c_int
        WHERE
            NOT (
                    day(t2.c_date) IN (1, 3)
                AND
                    day(t2.c_date) IN (2, 3, 3)
                );

throw exception:

java.sql.SQLException: errCode = 2, detailMessage = (127.0.0.1)[INTERNAL_ERROR]Could not find function dayofmonth, arg c_date return Nullable(TINYINT)

Release note

None

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

@Thearas

Thearas commented Sep 5, 2025

Copy link
Copy Markdown
Contributor

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?

@yujun777

yujun777 commented Sep 5, 2025

Copy link
Copy Markdown
Contributor Author

run buildall

@yujun777 yujun777 changed the title [fix](nereids) adjust virtual column expression nullable [fix](virtual slot) adjust virtual column expression nullable Sep 5, 2025
@doris-robot

doris-robot commented Sep 5, 2025

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

------ Round 1 ----------------------------------
q1	17656	5164	5085	5085
q2	2058	319	204	204
q3	10229	1257	693	693
q4	10228	1063	529	529
q5	7527	2477	2301	2301
q6	198	171	136	136
q7	932	739	658	658
q8	9351	1329	1118	1118
q9	6921	5108	5211	5108
q10	6944	2399	1963	1963
q11	504	321	286	286
q12	357	361	221	221
q13	17809	3643	3056	3056
q14	236	245	224	224
q15	579	511	504	504
q16	455	446	395	395
q17	602	858	361	361
q18	7476	7092	6888	6888
q19	1387	959	571	571
q20	354	349	234	234
q21	3865	3146	2336	2336
q22	1047	1069	982	982
Total cold run time: 106715 ms
Total hot run time: 33853 ms

----- Round 2, with runtime_filter_mode=off -----
q1	5196	5142	5136	5136
q2	255	339	236	236
q3	2224	2706	2330	2330
q4	1341	1737	1311	1311
q5	4247	4377	4543	4377
q6	216	213	134	134
q7	1968	2038	1861	1861
q8	2676	2610	2562	2562
q9	7306	7401	7337	7337
q10	3072	3446	2888	2888
q11	584	508	524	508
q12	673	790	673	673
q13	3714	3995	3388	3388
q14	305	313	282	282
q15	618	501	481	481
q16	449	489	439	439
q17	1267	1579	1420	1420
q18	8016	7552	7795	7552
q19	805	795	819	795
q20	1883	1953	1841	1841
q21	4705	4305	4208	4208
q22	1076	1033	990	990
Total cold run time: 52596 ms
Total hot run time: 50749 ms

@doris-robot

doris-robot commented Sep 5, 2025

Copy link
Copy Markdown
TPC-DS: Total hot run time: 186861 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 9a5cc58941ef8b70e3d7eeea8f791af665872945, data reload: false

query1	1060	481	406	406
query2	6580	1732	1746	1732
query3	6752	227	223	223
query4	26386	23049	23098	23049
query5	4417	657	530	530
query6	341	257	238	238
query7	4659	518	312	312
query8	304	256	279	256
query9	8664	2898	2979	2898
query10	547	361	321	321
query11	15919	15069	14797	14797
query12	188	122	117	117
query13	1705	576	436	436
query14	9724	5968	5898	5898
query15	216	197	178	178
query16	7690	700	506	506
query17	1241	784	671	671
query18	2078	455	351	351
query19	279	199	166	166
query20	141	122	123	122
query21	219	133	115	115
query22	4272	4257	4116	4116
query23	33749	32992	32968	32968
query24	8134	2396	2382	2382
query25	557	519	451	451
query26	733	284	171	171
query27	2602	516	356	356
query28	4268	2252	2223	2223
query29	693	630	494	494
query30	295	228	195	195
query31	905	816	738	738
query32	93	82	83	82
query33	592	411	375	375
query34	822	853	534	534
query35	809	823	751	751
query36	983	1012	905	905
query37	124	108	90	90
query38	4112	4078	4067	4067
query39	1532	1455	1434	1434
query40	231	142	138	138
query41	65	62	60	60
query42	128	118	114	114
query43	524	508	468	468
query44	1396	859	860	859
query45	192	176	170	170
query46	880	1029	656	656
query47	1816	1818	1723	1723
query48	381	435	321	321
query49	721	514	433	433
query50	666	693	399	399
query51	4189	4191	4231	4191
query52	122	116	110	110
query53	249	260	214	214
query54	638	612	542	542
query55	94	90	100	90
query56	332	357	334	334
query57	1169	1219	1144	1144
query58	294	289	277	277
query59	2588	2605	2656	2605
query60	359	351	346	346
query61	169	168	163	163
query62	825	708	656	656
query63	235	196	198	196
query64	3180	1177	844	844
query65	4289	4192	4223	4192
query66	909	442	355	355
query67	15460	15304	15087	15087
query68	8701	945	588	588
query69	499	342	294	294
query70	1350	1125	1143	1125
query71	573	345	332	332
query72	5949	5067	5070	5067
query73	744	614	366	366
query74	8954	8824	8747	8747
query75	3988	3149	2606	2606
query76	3549	1151	753	753
query77	811	429	343	343
query78	9527	9626	8899	8899
query79	3062	908	597	597
query80	718	647	531	531
query81	477	265	231	231
query82	454	143	119	119
query83	301	268	254	254
query84	305	114	99	99
query85	889	476	436	436
query86	346	329	300	300
query87	4370	4284	4164	4164
query88	2852	2224	2212	2212
query89	427	333	297	297
query90	1950	233	233	233
query91	167	172	136	136
query92	92	79	80	79
query93	1594	994	659	659
query94	699	424	329	329
query95	422	347	331	331
query96	491	589	283	283
query97	2717	2700	2558	2558
query98	245	231	216	216
query99	1473	1437	1336	1336
Total cold run time: 275182 ms
Total hot run time: 186861 ms

@doris-robot

doris-robot commented Sep 5, 2025

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

query1	0.06	0.04	0.05
query2	0.10	0.05	0.05
query3	0.26	0.08	0.08
query4	1.61	0.12	0.12
query5	0.45	0.43	0.43
query6	1.19	0.65	0.64
query7	0.04	0.03	0.03
query8	0.05	0.04	0.04
query9	0.61	0.54	0.53
query10	0.59	0.58	0.58
query11	0.17	0.11	0.11
query12	0.16	0.12	0.12
query13	0.63	0.62	0.61
query14	0.79	0.82	0.85
query15	0.88	0.88	0.86
query16	0.38	0.42	0.40
query17	1.06	1.04	1.06
query18	0.25	0.20	0.20
query19	1.92	1.86	1.84
query20	0.02	0.01	0.02
query21	15.40	0.94	0.58
query22	0.80	1.23	0.71
query23	14.83	1.38	0.67
query24	7.08	1.64	0.53
query25	0.49	0.26	0.07
query26	0.56	0.16	0.14
query27	0.05	0.05	0.05
query28	8.99	0.92	0.42
query29	12.54	3.87	3.25
query30	3.09	3.03	2.98
query31	2.83	0.59	0.38
query32	3.24	0.55	0.49
query33	3.08	3.03	3.14
query34	15.76	5.46	4.87
query35	4.90	4.93	4.91
query36	0.70	0.50	0.50
query37	0.11	0.07	0.08
query38	0.07	0.05	0.04
query39	0.04	0.02	0.02
query40	0.18	0.15	0.15
query41	0.08	0.03	0.03
query42	0.04	0.03	0.03
query43	0.05	0.03	0.04
Total cold run time: 106.13 s
Total hot run time: 32.5 s

@yujun777

yujun777 commented Sep 5, 2025

Copy link
Copy Markdown
Contributor Author

run buildall

@doris-robot

doris-robot commented Sep 5, 2025

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

------ Round 1 ----------------------------------
q1	17628	5312	5074	5074
q2	1998	354	209	209
q3	10244	1321	733	733
q4	10219	1015	524	524
q5	7545	2389	2385	2385
q6	185	170	145	145
q7	936	765	617	617
q8	9341	1387	1089	1089
q9	6978	5042	5149	5042
q10	6898	2391	2001	2001
q11	485	303	306	303
q12	361	359	228	228
q13	17802	3605	2990	2990
q14	243	243	232	232
q15	564	536	486	486
q16	435	424	376	376
q17	599	853	356	356
q18	7567	7206	7125	7125
q19	1093	953	560	560
q20	336	340	221	221
q21	3806	2515	2320	2320
q22	1065	1047	988	988
Total cold run time: 106328 ms
Total hot run time: 34004 ms

----- Round 2, with runtime_filter_mode=off -----
q1	5151	5165	5086	5086
q2	250	331	228	228
q3	2176	2706	2289	2289
q4	1348	1779	1335	1335
q5	4211	4207	4621	4207
q6	225	178	132	132
q7	2099	2032	1833	1833
q8	2652	2677	2643	2643
q9	7403	7527	7312	7312
q10	3126	3294	2934	2934
q11	565	542	512	512
q12	731	791	667	667
q13	3446	3927	3251	3251
q14	304	302	311	302
q15	528	483	478	478
q16	476	529	472	472
q17	1183	1597	1435	1435
q18	7950	7729	7662	7662
q19	821	812	881	812
q20	2042	2097	1971	1971
q21	5062	4615	4456	4456
q22	1100	1019	1009	1009
Total cold run time: 52849 ms
Total hot run time: 51026 ms

@doris-robot

doris-robot commented Sep 5, 2025

Copy link
Copy Markdown
TPC-DS: Total hot run time: 186300 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 a105942d2db5a0dbab4d669eef6d2f7b4d65bd05, data reload: false

query1	1064	427	438	427
query2	6554	1740	1744	1740
query3	6747	238	227	227
query4	26377	23750	23227	23227
query5	5062	652	548	548
query6	343	266	228	228
query7	4679	524	298	298
query8	299	251	238	238
query9	8633	2884	2895	2884
query10	541	366	299	299
query11	15918	14985	14709	14709
query12	189	120	123	120
query13	1683	564	419	419
query14	9570	5794	5724	5724
query15	209	193	175	175
query16	7656	661	495	495
query17	1178	741	637	637
query18	2047	451	342	342
query19	216	204	179	179
query20	133	130	121	121
query21	252	131	116	116
query22	4126	4175	4007	4007
query23	33948	33037	33157	33037
query24	8096	2387	2397	2387
query25	608	535	442	442
query26	1238	277	168	168
query27	2732	513	363	363
query28	4375	2277	2235	2235
query29	774	617	488	488
query30	302	219	194	194
query31	902	805	740	740
query32	92	82	79	79
query33	570	388	363	363
query34	810	846	521	521
query35	812	825	751	751
query36	994	1053	911	911
query37	131	113	92	92
query38	4029	4104	3979	3979
query39	1528	1462	1419	1419
query40	233	139	138	138
query41	72	71	65	65
query42	131	120	119	119
query43	511	523	473	473
query44	1348	871	856	856
query45	186	183	173	173
query46	879	1022	655	655
query47	1790	1802	1732	1732
query48	396	439	337	337
query49	773	521	468	468
query50	653	690	409	409
query51	4145	4181	4046	4046
query52	124	123	111	111
query53	255	273	219	219
query54	642	617	569	569
query55	97	97	95	95
query56	363	357	338	338
query57	1183	1248	1130	1130
query58	288	280	287	280
query59	2631	2739	2606	2606
query60	362	353	346	346
query61	164	157	183	157
query62	804	708	684	684
query63	229	193	197	193
query64	4498	1145	848	848
query65	4304	4173	4216	4173
query66	1113	440	369	369
query67	15778	15438	15055	15055
query68	8971	926	585	585
query69	499	341	302	302
query70	1221	1168	1174	1168
query71	565	351	316	316
query72	5583	4994	5132	4994
query73	768	657	366	366
query74	8970	9187	8731	8731
query75	4261	3093	2629	2629
query76	3749	1146	724	724
query77	846	417	328	328
query78	9614	10032	8819	8819
query79	1309	865	602	602
query80	701	576	543	543
query81	506	264	228	228
query82	223	149	111	111
query83	290	273	252	252
query84	308	119	100	100
query85	857	463	413	413
query86	376	309	303	303
query87	4341	4204	4221	4204
query88	2805	2219	2212	2212
query89	398	353	296	296
query90	2052	222	231	222
query91	159	169	130	130
query92	89	76	71	71
query93	1126	991	661	661
query94	696	425	325	325
query95	409	334	319	319
query96	483	601	282	282
query97	2708	2735	2558	2558
query98	248	226	218	218
query99	1466	1419	1315	1315
Total cold run time: 275966 ms
Total hot run time: 186300 ms

@doris-robot

doris-robot commented Sep 5, 2025

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

query1	0.05	0.05	0.04
query2	0.10	0.05	0.05
query3	0.26	0.08	0.08
query4	1.61	0.12	0.12
query5	0.45	0.43	0.43
query6	1.16	0.64	0.66
query7	0.04	0.02	0.02
query8	0.06	0.04	0.05
query9	0.60	0.54	0.53
query10	0.58	0.58	0.58
query11	0.17	0.12	0.11
query12	0.16	0.12	0.12
query13	0.63	0.63	0.63
query14	0.80	0.85	0.85
query15	0.89	0.85	0.87
query16	0.38	0.41	0.40
query17	1.03	1.08	1.03
query18	0.22	0.23	0.20
query19	1.94	1.82	1.85
query20	0.02	0.02	0.01
query21	15.41	0.95	0.58
query22	0.77	1.21	0.80
query23	14.81	1.41	0.60
query24	6.66	1.15	1.45
query25	0.50	0.25	0.09
query26	0.57	0.16	0.14
query27	0.06	0.06	0.06
query28	9.61	0.92	0.43
query29	12.55	3.89	3.24
query30	3.10	3.07	3.02
query31	2.83	0.59	0.39
query32	3.23	0.54	0.48
query33	3.13	3.09	3.11
query34	16.09	5.47	4.85
query35	4.94	4.93	4.98
query36	0.70	0.52	0.51
query37	0.11	0.08	0.08
query38	0.06	0.05	0.04
query39	0.04	0.03	0.03
query40	0.18	0.15	0.13
query41	0.08	0.04	0.03
query42	0.04	0.03	0.03
query43	0.04	0.04	0.04
Total cold run time: 106.66 s
Total hot run time: 33.29 s

@RoanHeNaN

RoanHeNaN commented Sep 5, 2025

Copy link
Copy Markdown
Contributor

Why would the virtual column affect this SQL? It doesn’t seem like there’s any common subexpression.

@yujun777

yujun777 commented Sep 5, 2025

Copy link
Copy Markdown
Contributor Author

Why would the virtual column affect this SQL? It doesn’t seem like there’s any common subexpression.

@zhiqiang-hhhh day(t2.c_date) exists twice in filter

@hello-stephen

hello-stephen commented Sep 5, 2025

Copy link
Copy Markdown
Contributor

FE Regression Coverage Report

Increment line coverage 100.00% (14/14) 🎉
Increment coverage report
Complete coverage report

@github-actions

github-actions Bot commented Sep 5, 2025

Copy link
Copy Markdown
Contributor

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

@github-actions github-actions Bot added the approved Indicates a PR has been approved by one committer. label Sep 5, 2025
@github-actions

github-actions Bot commented Sep 5, 2025

Copy link
Copy Markdown
Contributor

PR approved by anyone and no changes requested.

@englefly
englefly merged commit a5a1d28 into apache:master Sep 5, 2025
wenzhenghu pushed a commit to wenzhenghu/doris that referenced this pull request Sep 8, 2025
@yujun777 @wenzhenghu
[fix](virtual slot) adjust virtual column expression nullable (apache…
493d0b4 
…#55694)

### What problem does this PR solve?

need adjust nullable for expression of the virtual slot

bug introduced by:  apache#52701 

example as follow:

SQL: 

```sql
        SELECT t1.*, t2.*
        FROM
            tbl_adjust_virtual_slot_nullable_1 AS t1
        LEFT JOIN tbl_adjust_virtual_slot_nullable_2 AS t2
        ON  t1.c_int = t2.c_int
        WHERE
            NOT (
                    day(t2.c_date) IN (1, 3)
                AND
                    day(t2.c_date) IN (2, 3, 3)
                );
```

throw exception:

```
java.sql.SQLException: errCode = 2, detailMessage = (127.0.0.1)[INTERNAL_ERROR]Could not find function dayofmonth, arg c_date return Nullable(TINYINT)
```
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

@englefly englefly englefly approved these changes
@morrySnow morrySnow morrySnow approved these changes

Assignees

No one assigned

Labels

approved Indicates a PR has been approved by one committer. reviewed

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

7 participants

@yujun777 @Thearas @doris-robot @RoanHeNaN @hello-stephen @englefly @morrySnow