Bug or feature?: data conversion error on union with different column type categories

[Ilya Belopolsky]

When I try to run a union on a column with an INT and a VARCHAR, I get a "data conversion error"

The statement does work in MySQL as MySQL converts int to VARCHAR and returns both results as VARCHAR.

If I am try a union with an INT and a FLOAT, the query is successful and both results are converted into FLOAT.

I am curious as to whether this inconsistency with MySQL is by design or not?  Thanks.

Here is a quick example:

drop table test;

create table test(id int, name varchar);

insert into test values(1, 'a');

(select id from test) 

union (select name from test);

Here is the error

Data conversion error converting "a"; SQL statement:
(select id from test) 
union (select name from test) [22018-173] 22018/22018 (Help)

 

[Thomas Mueller]

Hi,

This is by design, the data types from the first query are used. In PostgreSQL, the statement fails as well with the message "UNION types integer and character varying cannot be matched 42804/0". Apache Derby says "Types 'INTEGER' and 'VARCHAR' are not UNION compatible. 42X61/30000" and HSQLDB says "incompatible data types in combination 42562/-5562".

I suggest to use "cast(id as varchar(255))". This works for PostgreSQL, HSQLDB and H2. It doesn't for Apache Derby, which is strange (maybe a bug there).

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.