db-derby-commits mailing list archives

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



Mime
View raw message