[
https://issues.apache.org/jira/browse/CALCITE-6520?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
kate updated CALCITE-6520:
--------------------------
Description:
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}
was:
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}
> 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)
