[jira] [Closed] (CALCITE-4683) IN-list converted to JOIN throws type mismatch exception

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

     [ https://issues.apache.org/jira/browse/CALCITE-4683?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Liya Fan closed CALCITE-4683.
-----------------------------

Resolved in release 1.30.0 (2022-03-20)

> IN-list converted to JOIN throws type mismatch exception
> --------------------------------------------------------
>
>                 Key: CALCITE-4683
>                 URL: https://issues.apache.org/jira/browse/CALCITE-4683
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 1.26.0, 1.27.0
>         Environment: jdk8
>  
>            Reporter: yanjing.wang
>            Assignee: yanjing.wang
>            Priority: Major
>              Labels: pull-request-available
>             Fix For: 1.30.0
>
>          Time Spent: 2.5h
>  Remaining Estimate: 0h
>
> The sql query is 
> {code:java}
> SELECT * FROM (
> SELECT '20210101' AS dt, deptno
> FROM emp
> GROUP BY deptno
> ) t
> WHERE cast(deptno as varchar) in ('1')
> {code}
> When Calcite converts the IN list to JOIN, the original leaf rel will be replaced by a new rel, but the new rel hasn't been set as a leaf rel, This will cause mismatched field type.
> Finally, the query results
> {noformat}
> java.lang.AssertionError: Conversion to relational algebra failed to preserve datatypes:
> validated type:
> RecordType(CHAR(8) NOT NULL dt, INTEGER deptno) NOT NULL
> converted type:
> RecordType(INTEGER dt, INTEGER deptno) NOT NULL
> rel:
> LogicalProject(dt=[$0], deptno=[$1])
>   LogicalJoin(condition=[=($2, $3)], joinType=[inner])
>     LogicalProject(dt=['20210101'], deptno=[$0], deptno0=[CAST($0):VARCHAR])
>       LogicalAggregate(group=[\{0}])
>         LogicalProject(deptno=[$1])
>           HiveTableScan(table=[[default, emp]])
>     LogicalAggregate(group=[\{0}])
>       LogicalValues(tuples=[[\{ '1' }]])    at org.apache.calcite.sql2rel.SqlToRelConverter.checkConvertedType(SqlToRelConverter.java:467)
>     at org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:582){noformat}



--
This message was sent by Atlassian Jira
(v8.20.1#820001)