[
https://issues.apache.org/jira/browse/CALCITE-6520?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17871811#comment-17871811
]
suibianwanwan edited comment on CALCITE-6520 at 11/12/24 2:36 AM:
------------------------------------------------------------------
[~mbudiu] You can try running the following sql in sql/sub-query.iq
{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')
from "scott".emp {code}
was (Author: JIRAUSER301334):
[~mbudiu] You can try running the following sql in sql/sub-query.iq
{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}
> IN operator returns incorrect result
> ------------------------------------
>
> Key: CALCITE-6520
> URL: https://issues.apache.org/jira/browse/CALCITE-6520
> Project: Calcite
> Issue Type: Bug
> Reporter: suibianwanwan
> 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)
