[
https://issues.apache.org/jira/browse/CALCITE-4897?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
xiejiajun updated CALCITE-4897:
-------------------------------
Description:
*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}
was:
*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}
> 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)
