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

["xiong duan (Jira)" <ji...@apache.org>]

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

xiong duan commented on CALCITE-5156:
-------------------------------------

[~julianhyde] [~Chunwei Lei] Hi, Sorry for the late response.

For the first question:

For example SQL:
{code:java}
with t (a, b) as (select * from (values (60, 'b')))
select * from t where a in (select deptno from "scott".dept); {code}
When we extract data from the table "scott".dept. According to the VALUES, We know the COLUMN 'a' IS NOT NULL, That's why we add the extra cast condition to the filter rule.

For the last question:

I have checked this again and I will fix it. Thanks for the review.

> 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)