[
https://issues.apache.org/jira/browse/CALCITE-4897?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17446927#comment-17446927
]
Julian Hyde commented on CALCITE-4897:
--------------------------------------
I think the third column should have type INTEGER. It is least restrictive of
the types {SMALLINT, INTEGER}. What am I missing?
> 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
> Priority: Major
> Labels: pull-request-available
> Time Spent: 10m
> 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 :
> {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)
