[jira] [Commented] (CALCITE-4683) In-list to join causes field datatypes not matched
[ https://issues.apache.org/jira/browse/CALCITE-4683?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17491943#comment-17491943 ] xiong duan commented on CALCITE-4683: - [~yanjing.wang] 1) How about we change the issue summary to( capitalize the keyword) "IN-list converted to JOIN throws type mismatch exception"? 2) Please reformat the description log by 'Preformatted'. > 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 > Fix For: 1.30.0 > > Time Spent: 50m > 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 a join, the original leave rel will be > replaced by a new rel, but the new rel hasn't been set as a leave 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) -- This message was sent by Atlassian Jira (v8.20.1#820001)
[jira] [Commented] (CALCITE-4683) In-list to join causes field datatypes not matched
[ https://issues.apache.org/jira/browse/CALCITE-4683?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17490667#comment-17490667 ] xiong duan commented on CALCITE-4683: - [~yanjing.wang] I will review it next week. And welcome everyone to review. > 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 > Fix For: 1.30.0 > > Time Spent: 10m > 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 a join, the original leave rel will be > replaced by a new rel, but the new rel hasn't been set as a leave rel, This > will cause mismatched field type. > 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)
[jira] [Commented] (CALCITE-4683) In-list to join causes field datatypes not matched
[ https://issues.apache.org/jira/browse/CALCITE-4683?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17490645#comment-17490645 ] yanjing.wang commented on CALCITE-4683: --- Hi [~nobigo] , Could you have time to review this pr? > 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 > Fix For: 1.30.0 > > Time Spent: 10m > 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 a join, the original leave rel will be > replaced by a new rel, but the new rel hasn't been set as a leave rel, This > will cause mismatched field type. > 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)
[jira] [Commented] (CALCITE-4683) In-list to join causes field datatypes not matched
[ https://issues.apache.org/jira/browse/CALCITE-4683?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17482419#comment-17482419 ] yanjing.wang commented on CALCITE-4683: --- Thanks duan, I have simplified the sql case and updated the pr, If you're available, Welcome review. > 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 > Fix For: 1.30.0 > > Time Spent: 10m > 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 a join, the original leave rel will be > replaced by a new rel, but the new rel hasn't been set as a leave rel, This > will cause mismatched field type. > 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)
[jira] [Commented] (CALCITE-4683) In-list to join causes field datatypes not matched
[ 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)