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 16:20:45 UTC
svn commit: r465256 - in /db/derby/code/branches/10.2/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: Wed Oct 18 07:20:44 2006
New Revision: 465256
URL: http://svn.apache.org/viewvc?view=rev&rev=465256
Log:
DERBY-1967: UNION (ALL) constraint violation problem
This patch was contributed by Yip Ng (yipng168@gmail.com)
Merged from the trunk by svn merge -r 465121:465122 ../trunk
Modified:
db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java
db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/union.out
db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/union.sql
Modified: db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java?view=diff&rev=465256&r1=465255&r2=465256
==============================================================================
--- db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java (original)
+++ db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java Wed Oct 18 07:20:44 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/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/union.out
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/union.out?view=diff&rev=465256&r1=465255&r2=465256
==============================================================================
--- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/union.out (original)
+++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/union.out Wed Oct 18 07:20:44 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/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/union.sql
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/union.sql?view=diff&rev=465256&r1=465255&r2=465256
==============================================================================
--- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/union.sql (original)
+++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/union.sql Wed Oct 18 07:20:44 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;