[ https://issues.apache.org/jira/browse/CALCITE-4683?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17482397#comment-17482397 ]
xiong duan commented on CALCITE-4683: ------------------------------------- Hi [~yanjing.wang]. Please find a more easy SQL to describe the issue, If it exists. The Before Plan is not complete? > In-list to join causes field datatypes not matched > -------------------------------------------------- > > 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 > Time Spent: 10m > Remaining Estimate: 0h > > The sql query is > {code:java} > SELECT * FROM (SELECT '20210101' AS dt, deptno, max(cast(deptno2 as > varchar(200))) as m FROM (SELECT emp.deptno as deptno, dept.deptno as deptno2 > FROM emp > JOIN dept on emp.deptno = dept.deptno) tmp GROUP BY deptno) WHERE cast(deptno > as > varchar) in ('1', '3', '5') > {code} > When Calcite converts the in list to a join, the original relational algebra > root will be replaced by a new project, for example > When we set InSubQueryThreshold value to 2, then the relational algebra tree > will be converted from > {code:java} > LogicalProject(DT=['20210101'], DEPTNO=[$0], M=[$1]) > LogicalAggregate(group=[{0}], M=[MAX($1)]) > LogicalProject(DEPTNO=[$7], $f1=[CAST($9):VARCHAR(200) NOT NULL]) > LogicalJoin(condition=[=($7, $9)], joinType=[inner]) > LogicalTableScan(table=[[CATALOG, SALES, EMP]]) > LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) > {code} > to > {code:java} > LogicalJoin(condition=[=($3, $4)], joinType=[inner]) > LogicalProject(DT=['20210101'], DEPTNO=[$0], M=[$1], > DEPTNO0=[CAST($0):VARCHAR NOT NULL]) > LogicalAggregate(group=[{0}], M=[MAX($1)]) > LogicalProject(DEPTNO=[$7], $f1=[CAST($9):VARCHAR(200) NOT NULL]) > LogicalJoin(condition=[=($7, $9)], joinType=[inner]) > LogicalTableScan(table=[[CATALOG, SALES, EMP]]) > LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) > LogicalAggregate(group=[{0}]) > LogicalValues(tuples=[[{ '1' }, { '3' }, { '5' }]]) > {code} > We can see that LogicalProject(DT=['20210101'], DEPTNO=[$0], M=[$1]) with > leaves being true has been replaced by LogicalProject(DT=['20210101'], > DEPTNO=[$0], M=[$1], DEPTNO0=[CAST($0):VARCHAR NOT NULL]) with leaves being > false. > Finally, the query results java.lang.AssertionError: Conversion to relational > algebra failed to preserve datatypes. -- This message was sent by Atlassian Jira (v8.20.1#820001)