[
https://issues.apache.org/jira/browse/CALCITE-6481?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated CALCITE-6481:
------------------------------------
Labels: pull-request-available (was: )
> Optimize 'VALUES...UNION ALL...VALUES' to a single 'VALUES' the IN-list
> contains NULL and it is converted to VALUES
> -------------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-6481
> URL: https://issues.apache.org/jira/browse/CALCITE-6481
> Project: Calcite
> Issue Type: Improvement
> Reporter: xiong duan
> Assignee: xiong duan
> Priority: Major
> Labels: pull-request-available
>
> 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)
