[jira] [Commented] (CALCITE-5930) When sqlToRel is converted from in to join, condition '=' on both sides will have inconsistent data types.

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

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

LakeShen commented on CALCITE-5930:
-----------------------------------

Hi [~leepb] ,in the validation phase, = operator could do implicit conversions,more details could see:

[https://github.com/apache/calcite/blob/51da34bdf91e602f32f965b3490bace026d51ef4/core/src/main/java/org/apache/calcite/sql/type/ComparableOperandTypeChecker.java#L67C3-L70C60]

It looks like the in subquery is not implicitly converted,could you use PG to test the result of a in sub query and see what it looks like.

> When sqlToRel is converted from in to join, condition '=' on both sides will have inconsistent data types.
> ----------------------------------------------------------------------------------------------------------
>
>                 Key: CALCITE-5930
>                 URL: https://issues.apache.org/jira/browse/CALCITE-5930
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: libopeng
>            Priority: Major
>
> Wrong calculation result
> {code:java}
> select *
> from dept
> where deptno in (
>   select count(*)
>   from emp
>   where comm is null);
> +--------+------------+----------+
> | DEPTNO |   DNAME    |   LOC    |
> +--------+------------+----------+
> +--------+------------+----------+ {code}
> Correct calculation results
> {code:java}
> select *
> from dept
> where cast(deptno as bigint) in (
>   select count(*)
>   from emp
>   where comm is null); 
> +--------+------------+----------+
> | DEPTNO | DNAME      | LOC      |
> +--------+------------+----------+
> |     10 | ACCOUNTING | NEW YORK |
> +--------+------------+----------+{code}
> The above two cases can be reproduced in agg.iq.
> Or look at this submission [https://github.com/apache/calcite/commit/6cef259f29884fbda3f165d579232285998d1b46].
>  
>  
>  
>  
>  



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