[DERBY-1967] UNION (ALL) contraint violation problem

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.1.3.1, 10.2.1.6
• Fix Version/s: 10.2.2.0, 10.3.1.4
• Component/s: SQL
• 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.
• Bug behavior facts: Regression
• Assignee: Yip Ng
• Reporter: Radu Radutiu
• Created: 17/Oct/06 11:02
• Updated: 30/Jun/09 15:55
• Resolved: 18/Oct/06 14:21

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.

Issue Links

• is related to: DERBY-7 Bug in NULLIF Function