[jira] [Commented] (CALCITE-5156) Support implicit number type cast for IN Sub-query

["Julian Hyde (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-5156?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17714777#comment-17714777 ] 

Julian Hyde commented on CALCITE-5156:
--------------------------------------

[~nobigo], I reviewed, and had similar concerns to [~Chunwei Lei]. There are undesirable plan changes such as
{noformat}
@@ -1174,7 +1173,7 @@ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[null:BOOLEAN
     EnumerableLimit(fetch=[1])
       EnumerableSort(sort0=[$0], dir0=[DESC])
         EnumerableAggregate(group=[{0}], c=[COUNT()])
-          EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], cs=[$t3])
+          EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):INTEGER], expr#4=[IS NOT NULL($t3)], cs=[$t4])
             EnumerableTableScan(table=[[scott, DEPT]])
 !plan
 {noformat}
and
{noformat}
@@ -2421,7 +2451,12 @@ LogicalProject(EMPNO=[$0])
     LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO0=[CAST($0):BIGINT NOT NULL])
       LogicalTableScan(table=[[CATALOG, SALES, EMP]])
     LogicalAggregate(group=[{0}])
-      LogicalValues(tuples=[[{ 130 }, { 131 }, { 132 }, { 133 }, { 134 }]])
+      LogicalUnion(all=[true])
+        LogicalValues(tuples=[[{ 130 }]])
+        LogicalValues(tuples=[[{ 131 }]])
+        LogicalValues(tuples=[[{ 132 }]])
+        LogicalValues(tuples=[[{ 133 }]])
+        LogicalValues(tuples=[[{ 134 }]])
 ]]>
     </Resource>
   </TestCase>
{noformat}

If these can be resolved, I would like to merge the PR.

> Support implicit number type cast for IN Sub-query
> --------------------------------------------------
>
>                 Key: CALCITE-5156
>                 URL: https://issues.apache.org/jira/browse/CALCITE-5156
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 1.30.0
>            Reporter: xiong duan
>            Assignee: xiong duan
>            Priority: Major
>              Labels: pull-request-available
>          Time Spent: 0.5h
>  Remaining Estimate: 0h
>
> The SQL:
> {code:java}
> select * from dept where deptno + 20 in (select deptno from dept);{code}
> Calcite returns the wrong answer.
> but the SQL
>  
> {code:java}
> select * from dept where deptno + 20 in (select cast(deptno as integer) from dept);{code}
> Calcite returns the correct answer.
> So when we generate the RelNode, we can add the type cast.
> Before the type cast:
> {noformat}
> LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2])
>   LogicalFilter(condition=[IN(+($0, 20), {
> LogicalProject(DEPTNO=[$0])
>   LogicalTableScan(table=[[scott, DEPT]])
> })])
>     LogicalTableScan(table=[[scott, DEPT]]){noformat}
> After the type cast:
> {noformat}
> LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2])
>   LogicalFilter(condition=[IN(+($0, 20), {
> LogicalProject(EXPR$0=[CAST($0):INTEGER NOT NULL])
>   LogicalTableScan(table=[[scott, DEPT]])
> })])
>     LogicalTableScan(table=[[scott, DEPT]]){noformat}
> Same SQL includes:
> {code:java}
> select *
> from dept
> where deptno in (select sal-780 from emp){code}



--
This message was sent by Atlassian Jira
(v8.20.10#820010)