[jira] [Created] (IGNITE-20649) SQL: class cast exception in EXCEPT SELECT with different column types

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

Andrey Khitrin created IGNITE-20649:
---------------------------------------

             Summary: SQL: class cast exception in EXCEPT SELECT with different column types
                 Key: IGNITE-20649
                 URL: https://issues.apache.org/jira/browse/IGNITE-20649
             Project: Ignite
          Issue Type: Bug
          Components: sql
    Affects Versions: 3.0.0-beta2
            Reporter: Andrey Khitrin


Steps to reproduce:

1. Create two tables, one with `INT` column and one with `NUMERIC`. Add some sample data into both.

{code:sql}
-- first table
drop table if exists tmp_table_age_name_wage;
create table tmp_table_age_name_wage (key_field INT PRIMARY KEY,AGE INT,field1 VARCHAR,field2 INT);
insert into tmp_table_age_name_wage (key_field,AGE,field1,field2) values (1, 42,'John',100000);
insert into tmp_table_age_name_wage (key_field,AGE,field1,field2) values (2, 43,'Jack',50000);
-- second table
drop table if exists tmp_table_age_name_wage_2;
create table tmp_table_age_name_wage_2 (key_field INT PRIMARY KEY,AGE DECIMAL,field1 VARCHAR,field2 DECIMAL);
insert into tmp_table_age_name_wage_2 (key_field,AGE,field1,field2) values (1, 42,'John',100000);
{code}

2. `UNION SELECT` for columns with different types works fine:

{code:sql}
sql-cli> SELECT AGE,field1,field2 FROM tmp_table_age_name_wage UNION SELECT AGE,field1,field2 FROM tmp_table_age_name_wage_2;
╔═════╤════════╤════════╗
║ AGE │ FIELD1 │ FIELD2 ║
╠═════╪════════╪════════╣
║ 42  │ John   │ 100000 ║
╟─────┼────────┼────────╢
║ 42  │ John   │ 100000 ║
╟─────┼────────┼────────╢
║ 43  │ Jack   │ 50000  ║
╚═════╧════════╧════════╝
{code}

3. Nevertheless, `EXCEPT SELECT` leads to class casting exception:

{code:sql}
sql-cli> SELECT AGE,field1,field2 FROM tmp_table_age_name_wage EXCEPT SELECT AGE,field1,field2 FROM tmp_table_age_name_wage_2;
SQL query execution error
class java.math.BigDecimal cannot be cast to class java.lang.Integer (java.math.BigDecimal and java.lang.Integer are in module java.base of loader 'bootstrap')
{code}

The query above worked in AI3 some time ago (e.g., in 3.0.0-beta1), but not now. Looks like a degradation.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)