[jira] [Created] (CALCITE-4897) In some cases, implicit type conversion is not complete

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

xiejiajun created CALCITE-4897:
----------------------------------

             Summary: In some cases, 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


*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 :

 

 
{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 `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
SELECT '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`
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}
 



--
This message was sent by Atlassian Jira
(v8.20.1#820001)