kate created CALCITE-6520:
-----------------------------
Summary: IN operator returns incorrect result
Key: CALCITE-6520
URL: https://issues.apache.org/jira/browse/CALCITE-6520
Project: Calcite
Issue Type: Bug
Reporter: kate
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|https://issues.apache.org/jira/browse/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)
