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

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.28.0
• Fix Version/s: 1.31.0
• Component/s: core
• Labels: pull-request-available
• External issue URL: https://issues.apache.org/jira/browse/CALCITE-4458
• Assignee: Jiajun Xie
• Reporter: Jiajun Xie
• Created: 20/Nov/21 08:58
• Updated: 03/Aug/22 16:21
• Resolved: 21/Jun/22 09:14

Description

case1

 Here is the sql:

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 

but the validated sql is :    (The same problem occurs in other set operations in DML)

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 

This is why CALCITE-4458 changed '&&' to '||', the right validated sql is:

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)

 

case2
Here is the sql

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)

 the validated sql is :

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) 

the right validated sql is:

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))

 

Issue Links

• Blocked: CALCITE-5141 Incomplete implicit type conversion for insert values
• links to: GitHub Pull Request #2615