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 On Tuesday, January 7, 2014, Ilya Belopolsky wrote: > 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]<http://localhost:9011/query.do?jsessionid=a3d32787983c6e37996a90a63c637001#> > 22018/22018 > (Help)<http://h2database.com/javadoc/org/h2/constant/ErrorCode.html#c22018> > > > -- > 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 [email protected] <javascript:_e({}, > 'cvml', 'h2-database%[email protected]');>. > To post to this group, send email to > [email protected]<javascript:_e({}, 'cvml', > '[email protected]');> > . > Visit this group at http://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/groups/opt_out. > -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
