[CALCITE-4683] IN-list converted to JOIN throws type mismatch exception

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.26.0, 1.27.0
• Fix Version/s: 1.30.0
• Component/s: core
• Labels: pull-request-available

Environment

jdk8

 

• Assignee: yanjing.wang
• Reporter: yanjing.wang
• Due: 28/Jan/22
• Created: 07/Jul/21 09:11
• Updated: 19/Mar/22 09:21
• Resolved: 17/Feb/22 11:25

Description

The sql query is 

SELECT * FROM (
SELECT '20210101' AS dt, deptno
FROM emp
GROUP BY deptno
) t
WHERE cast(deptno as varchar) in ('1')

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

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)

Issue Links

• links to: GitHub Pull Request #2454