UNION (ALL) contraint violation problem

• Key: DERBY-1967
• Type: Bug
• Status: Closed
• Resolution: Fixed
• Priority: Major
• Assignee: Yip Ng
• Reporter: Radu Radutiu
• Component/s: SQL
• Affects Version/s: 10.1.3.1, 10.2.1.6
• Fix Version/s: 10.2.2.0, 10.3.1.4

Environment

derby v10.1.3.1 and v10.2.1.6 on linux (FC5), jdk 1.5.0_06-b05
and jdk 1.6.0-rc-b99.

Description

The following simple test case gives an error:

create table a (f1 varchar(10));
create table b (f2 varchar(10));
insert into b values('test');
select nullif('x','x') as f0, f1 from a
   union all
   select nullif('x','x') as f0, nullif('x','x') as f1 from b;

ERROR 23502: Column 'F0' cannot accept a NULL value.
SQLState(23502) vendor code(30000)

However the following works ok:
drop table a;
drop table b;
create table a (f1 int);
create table b (f2 int);
insert into b values(1);
select nullif('x','x') as f0, f1 from a
   union all
   select nullif('x','x') as f0, nullif(1,1) as f1 from b;

The test case is a simplification of a query generated by Hibernate
with the table per class inheritance strategy. Both queries work ok on
MSSQL and PostgreSQL. On Derby only the second query works, the first
one giving a contraint violation.