[
http://issues.apache.org/jira/browse/DERBY-1967?page=comments#action_12442949 ]
Yip Ng commented on DERBY-1967:
-------------------------------
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.
> UNION (ALL) contraint violation problem
> ---------------------------------------
>
> Key: DERBY-1967
> URL: http://issues.apache.org/jira/browse/DERBY-1967
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6, 10.1.3.1
> 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.
> Reporter: Radu Radutiu
> Assigned To: Yip Ng
>
> 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.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
