You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by bp...@apache.org on 2006/10/18 03:43:16 UTC
svn commit: r465122 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/compile/ConditionalNode.java
testing/org/apache/derbyTesting/functionTests/master/union.out
testing/org/apache/derbyTesting/functionTests/tests/lang/union.sql
Author: bpendleton
Date: Tue Oct 17 18:43:14 2006
New Revision: 465122
URL: http://svn.apache.org/viewvc?view=rev&rev=465122
Log:
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.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/union.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/union.sql
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java?view=diff&rev=465122&r1=465121&r2=465122
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java Tue Oct 17 18:43:14 2006
@@ -153,10 +153,20 @@
//At bind phase, we should bind it to the type of V1 since now we know the
//type of V1
BinaryComparisonOperatorNode bcon = (BinaryComparisonOperatorNode)testCondition;
+
+ /*
+ * NULLIF(V1,V2) is equivalent to:
+ *
+ * CASE WHEN V1=V2 THEN NULL ELSE V1 END
+ *
+ * The untyped NULL should have a data type descriptor
+ * that allows its value to be nullable.
+ */
QueryTreeNode cast = getNodeFactory().getNode(
C_NodeTypes.CAST_NODE,
thenElseList.elementAt(0),
- bcon.getLeftOperand().getTypeServices(),
+ new DataTypeDescriptor(
+ bcon.getLeftOperand().getTypeServices(), true),
getContextManager());
thenElseList.setElementAt(cast,0);
}
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/union.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/union.out?view=diff&rev=465122&r1=465121&r2=465122
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/union.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/union.out Tue Oct 17 18:43:14 2006
@@ -911,4 +911,40 @@
0 rows inserted/updated/deleted
ij> drop table insert_test2;
0 rows inserted/updated/deleted
+ij> -- DERBY-1967
+-- NULLIF with UNION throws SQLSTATE 23502.
+create table a (f1 varchar(10));
+0 rows inserted/updated/deleted
+ij> create table b (f2 varchar(10));
+0 rows inserted/updated/deleted
+ij> insert into b values('test');
+1 row inserted/updated/deleted
+ij> -- this used to throw 23502
+select nullif('x','x') as f0, f1 from a
+ union all
+ select nullif('x','x') as f0, nullif('x','x') as f1 from b;
+F0 |F1
+---------------
+NULL|NULL
+ij> drop table a;
+0 rows inserted/updated/deleted
+ij> drop table b;
+0 rows inserted/updated/deleted
+ij> create table a (f1 int);
+0 rows inserted/updated/deleted
+ij> create table b (f2 int);
+0 rows inserted/updated/deleted
+ij> insert into b values(1);
+1 row inserted/updated/deleted
+ij> -- ok
+select nullif('x','x') as f0, f1 from a
+ union all
+ select nullif('x','x') as f0, nullif(1,1) as f1 from b;
+F0 |F1
+----------------
+NULL|NULL
+ij> drop table a;
+0 rows inserted/updated/deleted
+ij> drop table b;
+0 rows inserted/updated/deleted
ij>
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/union.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/union.sql?view=diff&rev=465122&r1=465121&r2=465122
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/union.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/union.sql Tue Oct 17 18:43:14 2006
@@ -343,3 +343,25 @@
drop table t2;
drop table insert_test;
drop table insert_test2;
+
+-- DERBY-1967
+-- NULLIF with UNION throws SQLSTATE 23502.
+
+create table a (f1 varchar(10));
+create table b (f2 varchar(10));
+insert into b values('test');
+-- this used to throw 23502
+select nullif('x','x') as f0, f1 from a
+ union all
+ select nullif('x','x') as f0, nullif('x','x') as f1 from b;
+drop table a;
+drop table b;
+create table a (f1 int);
+create table b (f2 int);
+insert into b values(1);
+-- ok
+select nullif('x','x') as f0, f1 from a
+ union all
+ select nullif('x','x') as f0, nullif(1,1) as f1 from b;
+drop table a;
+drop table b;