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)
