svn commit: r829022 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java testing/org/apache/derbyTesting/functionTests/master/union.out testing/org/apache/derbyTesting/functionTests/tests/lang/union.sql

[ka...@apache.org]

Author: kahatlen
Date: Fri Oct 23 12:23:06 2009
New Revision: 829022

URL: http://svn.apache.org/viewvc?rev=829022&view=rev
Log:
DERBY-4411: Scalar subquery erroneously rejected for not returning exactly one row

Make sure a ProjectRestrictNode is not considered a no-op if it only
contains constant restrictions (like 1=0).

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.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/ProjectRestrictNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java?rev=829022&r1=829021&r2=829022&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ProjectRestrictNode.java Fri Oct 23 12:23:06 2009
@@ -1586,7 +1586,7 @@
 		** This ProjectRestrictNode is not a No-Op if it does any
 		** restriction.
 		*/
-		if ( (restriction != null) ||
+		if ( (restriction != null) || (constantRestriction != null) ||
 			 (restrictionList != null && restrictionList.size() > 0) )
 		{
 			return false;

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?rev=829022&r1=829021&r2=829022&view=diff
==============================================================================
--- 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 Fri Oct 23 12:23:06 2009
@@ -1251,4 +1251,18 @@
 4          |0          
 ij> drop table d4391;
 0 rows inserted/updated/deleted
+ij> -- Regression test for DERBY-4411. The predicate 1=0 used to be lost when the
+-- SELECT statement was compiled, and the statement would fail with a message
+-- saying that a scalar sub-query should return exactly one row.
+create table d4411(a int primary key, b int);
+0 rows inserted/updated/deleted
+ij> insert into d4411 values (0, 4), (1, 3), (2, 2), (3, 1), (4, 0);
+5 rows inserted/updated/deleted
+ij> select * from d4411 where a < (values 2 union select b from d4411 where 1=0);
+A          |B          
+-----------------------
+0          |4          
+1          |3          
+ij> drop table d4411;
+0 rows inserted/updated/deleted
 ij> 
\ No newline at end of file

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?rev=829022&r1=829021&r2=829022&view=diff
==============================================================================
--- 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 Fri Oct 23 12:23:06 2009
@@ -502,3 +502,11 @@
 select * from d4391 where a < (select a+b from d4391 union select a from d4391);
 select * from d4391 where a < (select sum(a) from d4391 union select sum(b) from d4391);
 drop table d4391;
+
+-- Regression test for DERBY-4411. The predicate 1=0 used to be lost when the
+-- SELECT statement was compiled, and the statement would fail with a message
+-- saying that a scalar sub-query should return exactly one row.
+create table d4411(a int primary key, b int);
+insert into d4411 values (0, 4), (1, 3), (2, 2), (3, 1), (4, 0);
+select * from d4411 where a < (values 2 union select b from d4411 where 1=0);
+drop table d4411;