[
https://issues.apache.org/jira/browse/CALCITE-6520?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17871809#comment-17871809
]
kate commented on CALCITE-6520:
-------------------------------
[~mbudiu] Hi, Thank you for your attention to this issue.
My guess is that you can't reproduce this problem because of the parameter
withInSubQueryThreshold, it will convertInToOr() in SqlToRelConverter, and the
default value is 20
Without modifying the code, the problem can be reproduced like this:
{code:java}
SELECT ENAME, ENAME in ('Adam', 'Alice', 'Bob',
'Case1', 'Case2', 'Case3', 'Case4', 'Case5', 'Case6', 'Case7', 'Case8', 'Case9',
'Case10', 'Case11', 'Case12', 'Case13', 'Case14', 'Case15', 'Case16', 'Case17')
{code}
Only Alice = true, Interestingly, when you remove an item, Adam, Alice and Bob
all become true. Does this verify that this is a bug?
{code:java}
SELECT ENAME, ENAME in ('Adam', 'Alice', 'Bob',
'Case1', 'Case2', 'Case3', 'Case4', 'Case5', 'Case6', 'Case7', 'Case8', 'Case9',
'Case10', 'Case11', 'Case12', 'Case13', 'Case14', 'Case15', 'Case16') {code}
> IN operator returns incorrect result
> ------------------------------------
>
> Key: CALCITE-6520
> URL: https://issues.apache.org/jira/browse/CALCITE-6520
> Project: Calcite
> Issue Type: Bug
> Reporter: kate
> Priority: Minor
>
> I am trying to query the following SQL in calcite (I changed the
> withInSubQueryThreshold parameter in SqlToRelConvete.Config to 2) :
> {code:java}
> SELECT ENAME, ENAME in ('Adam', 'Alice', 'Eve') {code}
> Result:
> {code:java}
> ENAME | EXPR$1
> -------+--------
> Adam | false
> Alice | true
> Bob | false
> Eric | false
> Eve | false
> Grace | false
> Jane | false
> Susan | false
> Wilma | false
> (9 rows){code}
> plan:
> {code:java}
> EnumerableCalc(expr#0..5=[{inputs}], expr#6=[IS NOT NULL($t5)],
> expr#7=[0:BIGINT], expr#8=[<>($t1, $t7)], expr#9=[AND($t6, $t8)],
> expr#10=[<($t2, $t1)], expr#11=[null:BOOLEAN], expr#12=[IS NULL($t5)],
> expr#13=[AND($t10, $t11, $t8, $t12)], expr#14=[OR($t9, $t13)],
> expr#15=[CAST($t14):BOOLEAN NOT NULL], ENAME=[$t0], EXPR$1=[$t15])
> EnumerableMergeJoin(condition=[=($3, $4)], joinType=[left])
> EnumerableSort(sort0=[$3], dir0=[ASC])
> EnumerableCalc(expr#0..3=[{inputs}], ENAME=[$t0], $f0=[$t2], $f1=[$t3],
> ENAME0=[$t0])
> EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
> EnumerableValues(tuples=[[{ 'Jane', 10 }, { 'Bob', 10 }, { 'Eric',
> 20 }, { 'Susan', 30 }, { 'Alice', 30 }, { 'Adam', 50 }, { 'Eve', 50 }, {
> 'Grace', 60 }, { 'Wilma', null }]])
> EnumerableCalc(expr#0=[{inputs}], $f0=[$t0], $f00=[$t0])
> EnumerableAggregate(group=[{}], agg#0=[COUNT()])
> EnumerableValues(tuples=[[{ 'Adam ' }, { 'Alice' }, { 'Eve '
> }]])
> EnumerableCalc(expr#0=[{inputs}], expr#1=[true], proj#0..1=[{exprs}])
> EnumerableValues(tuples=[[{ 'Adam ' }, { 'Alice' }, { 'Eve ' }]])
> {code}
> I've seen a lot of similar discussions in past issues such as CALCITE-4590,
> However, even if I make the following changes to the SQL (adding spaces), I
> still don't get the expected results unless I use
> shouldConvertRaggedUnionTypesToVarying.I'm not sure this is the design that
> calcite intended.
> {code:java}
> SELECT ENAME, ENAME in ('Adam ', 'Alice', 'Eve ')
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
