[ https://issues.apache.org/jira/browse/CALCITE-4897?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Jiajun Xie reassigned CALCITE-4897: ----------------------------------- Assignee: Jiajun Xie > Set operation in DML, implicit type conversion is not complete > -------------------------------------------------------------- > > Key: CALCITE-4897 > URL: https://issues.apache.org/jira/browse/CALCITE-4897 > Project: Calcite > Issue Type: Bug > Components: core > Affects Versions: 1.28.0 > Reporter: Jiajun Xie > Assignee: Jiajun Xie > Priority: Major > Labels: pull-request-available > Time Spent: 1h 40m > Remaining Estimate: 0h > > *case1* > Here is the sql: > {code:java} > insert into t3 > select 'a', 1.0, 1 union > select 'b', 2, 2 union > select 'c', 3.0, CAST(3 AS SMALLINT) union > select 'd', 4.0, 4 union > select 'e', 5.0, 5 {code} > but the validated sql is : *(The same problem occurs in other set > operations in DML)* > {code:java} > INSERT INTO `CATALOG`.`SALES`.`T3` > SELECT 'a', CAST(1.0 AS INTEGER), CAST(1 AS SMALLINT) UNION > SELECT 'b', 2, CAST(2 AS SMALLINT) UNION > SELECT 'c', 3.0, CAST(3 AS SMALLINT) UNION > SELECT 'd', 4.0, 4 UNION -- should be cast > SELECT 'e', 5.0, 5 -- should be cast {code} > This is why CALCITE-4458 changed '&&' to '||', the right validated sql is: > {code:java} > INSERT INTO `CATALOG`.`SALES`.`T3` > SELECT 'a', CAST(1.0 AS INTEGER), CAST(1 AS SMALLINT) > UNION > SELECT 'b', 2, CAST(2 AS SMALLINT) > UNION > SELECT 'c', CAST(3.0 AS INTEGER), CAST(3 AS SMALLINT) > UNION > SELECT 'd', CAST(4.0 AS INTEGER), CAST(4 AS SMALLINT) > UNION > SELECT 'e', CAST(5.0 AS INTEGER), CAST(5 AS SMALLINT){code} > > *case2* > Here is the sql > {code:java} > insert into t3 values > ('a', 1.0, 1), > ('b', 2, 2), > ('c', 3.0, CAST(3 AS SMALLINT)), > ('d', 4.0, 4), > ('e', 5.0, 5){code} > the validated sql is : > {code:java} > INSERT INTO `CATALOG`.`SALES`.`T3` > VALUES ROW('a', CAST(1.0 AS INTEGER), CAST(1 AS SMALLINT)), > ROW('b', 2, CAST(2 AS SMALLINT)),-- Encountered an integer and ended early > ROW('c', 3.0, CAST(3 AS SMALLINT)),-- Encountered an samllint and ended early > ROW('d', 4.0, 4), > ROW('e', 5.0, 5) {code} > the right validated sql is: > {code:java} > INSERT INTO `CATALOG`.`SALES`.`T3` > VALUES ROW('a', CAST(1.0 AS INTEGER), CAST(1 AS SMALLINT)), > ROW('b', 2, CAST(2 AS SMALLINT)), > ROW('c', CAST(3.0 AS INTEGER), CAST(3 AS SMALLINT)), > ROW('d', CAST(4.0 AS INTEGER), CAST(4 AS SMALLINT)), > ROW('e', CAST(5.0 AS INTEGER), CAST(5 AS SMALLINT)){code} > -- This message was sent by Atlassian Jira (v8.20.1#820001)