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.

Comments

[A B]

I did some investigating around this and it appears that this query works in 10.1.2.1 but fails in 10.1.3 and later. I was eventually able to track it down to the changes for DERBY-7. Before those changes were committed the query ran without error; after that commit this query now fails. For what that's worth...

[Yip Ng]

Thanks Army, I just found the problem and running my patch against derbyall currently. Some explanation of the problem:

In the bind phase of ConditionalNode (NULLIF), the CAST node is generated on top of the untyped NULL and it gets the data type descriptor(DTD) of the left operand. However, the CAST node should have DTD where its value can be nullable.

BinaryComparisonOperatorNode bcon = (BinaryComparisonOperatorNode)testCondition;

QueryTreeNode cast = getNodeFactory().getNode(
C_NodeTypes.CAST_NODE,
thenElseList.elementAt(0),
bcon.getLeftOperand().getTypeServices(), <=== not nullable!
getContextManager());

The second query:

select nullif('x','x') as f0, f1 from a
   union all
   select nullif('x','x') as f0, nullif(1,1) as f1 from b;

works because it didn't generate a NormalizedResultSet on top of the PRN on the right hand side of the union since the datatype and length matches. So it didn't hit the path where it does additional checking at execution time.

For the first query:

select nullif('x','x') as f0, f1 from a
   union all
   select nullif('x','x') as f0, nullif('x','x') as f1 from b;

The union result column's length does not match with the right hand side result column, so it generated a NormalizedResultSet on top of the RHS of the union. When the system retrieves the row from NormalizedResultSet at execution time, the normalize method is called on the DTD and checks if the source is NULL and whether its DTD is not nullable. In this case, the SQLSTATE 23502 is thrown.

[Yip Ng]

Attaching patch derby1967-trunk-diff01.txt for review. derbyall still running. Will post result when it completes.

[Yip Ng]

derbyall passes.

From what Army described in previous comment, this looks like a regression.

Thanks for the quick turn-around on this, Yip.

Your description of the problem sounds correct to me and the changes themselves match what you say. They also line up with DERBY-7 as the code in question was added as part of that issue.

I confirmed that the patch applies cleanly to trunk and that the new test case fails without your changes and passes with it. The code comments also indicate why the change was necessary, which is great.

It's a pretty small change, it makes sense to me, and derbayll passed. So I vote +1 to commit...

[Bryan Pendleton]

Thanks Yip for the patch and thanks Army for the review. The patch looks good to me, too, and
my build and tests were successful, so I committed it to the trunk as subversion
revision 465122.

Should this patch be merged back to previous branch(es)?

I cleared patch available since the patch is now committed to the trunk, but didn't resolve
the issue in case we want to merge it to a release branch first.

[Yip Ng]

Thanks Army and Bryan for reviewing the patch. I also have backported this to 10.2 line and ran derbyall without any problems.

[Yip Ng]

Attaching derby1967-10.2-diff01.txt for 10.2 codeline. derbyall pass.

[Bryan Pendleton]

Thanks Yip! I committed the merged fix to the 10.2 branch as revision 465256.

[Andrew McIntyre]

This issue has been resolved for over a year with no further movement. Closing.