[jira] [Resolved] (CALCITE-4897) Implicit type conversion is not complete for set operation in DML

["Ruben Q L (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-4897?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Ruben Q L resolved CALCITE-4897.
--------------------------------
    Resolution: Fixed

Fixed via [https://github.com/apache/calcite/commit/9a102e77dd2f7b566b0a4a8cb0d504c6c572e1e1]

Thanks [~jiajunbernoulli] for the patch!

> Implicit type conversion is not complete for set operation in DML
> -----------------------------------------------------------------
>
>                 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
>             Fix For: 1.31.0
>
>          Time Spent: 2h 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.7#820007)