[
https://issues.apache.org/jira/browse/CALCITE-6481?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17866621#comment-17866621
]
Julian Hyde commented on CALCITE-6481:
--------------------------------------
This might be because null needs an implicit cast to be of the right type, and
some piece of logic doesn’t think that an expression can be converted to a
multi-row Values.
In any case, can you check that RelBuilder does the right thing here. And once
RelBuilder does the right thing, SqlToRelConverter should use it for Values if
it’s not already.
> 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
> Assignee: xiong duan
> Priority: Major
>
> 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)
