|
If you were logged in you would be able to see more operations.
|
|
|
|
File Attachments:
|
|
|
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.
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.
|
|
Issue Links:
|
Reference
|
|
|
|
This issue is related to:
|
|
|
|
|
|
|
|
| Bug behavior facts: |
Regression
|
| Resolution Date: |
18/Oct/06 02:21 PM
|
|
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.
|
|
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.
|
Show » |
| Repository |
Revision |
Date |
User |
Message |
| ASF |
#465122 |
Wed Oct 18 01:43:14 UTC 2006 |
bpendleton |
DERBY-1967: UNION (ALL) constraint violation problem
This patch was contributed by Yip Ng (yipng168@gmail.com)
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.
|
| Files Changed |
MODIFY
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/union.out
MODIFY
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/union.sql
MODIFY
/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java
|
|
Bug
Major
UNION (ALL) contraint violation problem
