[jira] [Commented] (CALCITE-4897) Set operation in DML, implicit type conversion is not complete

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

    [ https://issues.apache.org/jira/browse/CALCITE-4897?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17450779#comment-17450779 ] 

xiejiajun commented on CALCITE-4897:
------------------------------------

It occur with set set operation and DML, I updated the description.

> 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: xiejiajun
>            Priority: Major
>              Labels: pull-request-available
>          Time Spent: 1h 20m
>  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)