xiong duan created CALCITE-6481:
-----------------------------------
Summary: Optimize 'VALUES...UNION ALL...VALUES' to a single
'VALUES' when IN-list that the values include NULL is converted to Values
Key: CALCITE-6481
URL: https://issues.apache.org/jira/browse/CALCITE-6481
Project: Calcite
Issue Type: Improvement
Reporter: xiong duan
The SQL:
{code:java}
with
t1(a,y) as (select * from (values (1, 2), (3,
null),(7369,null),(7499,30),(null, 20),(null, 5)) as t1)
select *
from t1
where (t1.a,t1.y) in ((1, 2), (3, null),(7369,null),(7499,30),(null, 20),(null,
5));
EnumerableHashJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[semi])
EnumerableValues(tuples=[[{ 1, 2 }, { 3, null }, { 7369, null }, { 7499, 30
}, { null, 20 }, { null, 5 }]])
EnumerableUnion(all=[true])
EnumerableValues(tuples=[[{ 3, null }]])
EnumerableValues(tuples=[[{ 7369, null }]])
EnumerableValues(tuples=[[{ null, 20 }]])
EnumerableValues(tuples=[[{ null, 5 }]])
EnumerableValues(tuples=[[{ 1, 2 }, { 7499, 30 }]])
!plan
with
t1(a,y) as (select * from (values (1, 2), (3, 5),(7369,6),(7499,30),(2, 20),(3,
5)) as t1)
select *
from t1
where (t1.a,t1.y) in ((1, 2), (3, 3),(7369,3),(7499,30),(1, 20),(3, 5));
EnumerableHashJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[semi])
EnumerableValues(tuples=[[{ 1, 2 }, { 3, 5 }, { 7369, 6 }, { 7499, 30 }, { 2,
20 }, { 3, 5 }]])
EnumerableValues(tuples=[[{ 1, 2 }, { 3, 3 }, { 7369, 3 }, { 7499, 30 }, { 1,
20 }, { 3, 5 }]])
!plan {code}
If the IN-list includes NULL, Calcite will convert VALUES to UNION ALL.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
