Details
-
Improvement
-
Status: Closed
-
Major
-
Resolution: Fixed
-
None
-
None
Description
The SQL:
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
If the IN-list includes NULL, Calcite will convert VALUES to UNION ALL.
Attachments
Issue Links
- links to