db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bpendle...@apache.org
Subject 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
Date Wed, 18 Oct 2006 14:20:45 GMT
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;



Mime
View raw message