Description
Steps to reproduce:
1. Create two tables, one with `INT` column and one with `NUMERIC`. Add some sample data into both.
-- 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);
2. `UNION SELECT` for columns with different types works fine:
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 ║ ╚═════╧════════╧════════╝
3. Nevertheless, `EXCEPT SELECT` leads to class casting exception:
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')
The query above worked in AI3 some time ago (e.g., in 3.0.0-beta1), but not now. Looks like a degradation.
Attachments
Issue Links
- is related to
-
IGNITE-21827 Sql. Union returns incorrect result for DECIMAL and INT columns
-
- Resolved
-
-
-
- Resolved
-
- links to
