Recently I spend quite some time on a hsqldb issue I encountered in hsqldb 1.8.0.9. Let's take the following ddl: create table A( ID NUMERIC(10) ); create table B( ID NUMERIC(10), NAME VARCHAR(8) ); create VIEW my_view as select ID, null as NAME from A UNION ALL select ID, NAME from B ; Ane the following insert: insert into B (ID, NAME) VALUES(1, 'foo'); Now the following select will lead to this (wrong) result select * from my_view; ID NAME ----- ----- 1 null After some debugging I found a workaround: The fix is to add a cast to the union: create VIEW my_view as select ID, cast (null as VARCHAR) as NAME from A UNION ALL select ID, NAME from B ; Now the select delivers the correct results select * from my_view; ID NAME ----- ----- 1 foo

Responses[3]

Posted by Kai Grabfelder on May 19, 2008 7:05:00 PM CEST #