db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kahat...@apache.org
Subject svn commit: r1574505 - in /db/derby/code/branches/10.10: ./ java/engine/org/apache/derby/impl/sql/compile/ java/testing/org/apache/derbyTesting/functionTests/master/ java/testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Wed, 05 Mar 2014 14:30:16 GMT
Author: kahatlen
Date: Wed Mar  5 14:30:15 2014
New Revision: 1574505

URL: http://svn.apache.org/r1574505
Log:
DERBY-6408: EXISTS returns NULL instead of FALSE
DERBY-6409: Wrong result from quantified comparison

Backported revisions 1573548, 1573935, 1574464 from trunk.

Modified:
    db/derby/code/branches/10.10/   (props changed)
    db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/RowResultSetNode.java
    db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/SubqueryList.java
    db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java
    db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/ValueNode.java
    db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/master/schemas.out
    db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ExistsWithSubqueriesTest.java
    db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java

Propchange: db/derby/code/branches/10.10/
------------------------------------------------------------------------------
  Merged /db/derby/code/trunk:r1573548,1573935,1574464

Modified: db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/RowResultSetNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/RowResultSetNode.java?rev=1574505&r1=1574504&r2=1574505&view=diff
==============================================================================
--- db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/RowResultSetNode.java
(original)
+++ db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/RowResultSetNode.java
Wed Mar  5 14:30:15 2014
@@ -446,22 +446,13 @@ public class RowResultSetNode extends Fr
 									FromList fromList)
 								throws StandardException
 	{
-
-		if (subquerys.size() > 0)
-		{
-			subquerys.preprocess(
-								numTables,
-								(FromList) getNodeFactory().getNode(
-									C_NodeTypes.FROM_LIST,
-									getNodeFactory().doJoinOrderOptimization(),
-									getContextManager()),
-								(SubqueryList) getNodeFactory().getNode(
-													C_NodeTypes.SUBQUERY_LIST,
-													getContextManager()),
-								(PredicateList) getNodeFactory().getNode(
+        getResultColumns().preprocess(
+                numTables,
+                fromList,
+                subquerys,
+                (PredicateList) getNodeFactory().getNode(
 													C_NodeTypes.PREDICATE_LIST,
 													getContextManager()));
-		}
 
 		/* Allocate a dummy referenced table map */ 
 		referencedTableMap = new JBitSet(numTables);

Modified: db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/SubqueryList.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/SubqueryList.java?rev=1574505&r1=1574504&r2=1574505&view=diff
==============================================================================
--- db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/SubqueryList.java
(original)
+++ db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/SubqueryList.java
Wed Mar  5 14:30:15 2014
@@ -48,35 +48,6 @@ public class SubqueryList extends QueryT
 	}
 
 	/**
-	 * Preprocess a SubqueryList.  For now, we just preprocess each SubqueryNode
-	 * in the list.
-	 *
-	 * @param	numTables			Number of tables in the DML Statement
-	 * @param	outerFromList		FromList from outer query block
-	 * @param	outerSubqueryList	SubqueryList from outer query block
-	 * @param	outerPredicateList	PredicateList from outer query block
-	 *
-	 * @exception StandardException		Thrown on error
-	 */
-	public void preprocess(int numTables,
-							FromList outerFromList,
-							SubqueryList outerSubqueryList,
-							PredicateList outerPredicateList) 
-				throws StandardException
-	{
-		SubqueryNode	subqueryNode;
-
-		int size = size();
-		for (int index = 0; index < size; index++)
-		{
-			subqueryNode = (SubqueryNode) elementAt(index);
-			subqueryNode.preprocess(numTables, outerFromList,
-									outerSubqueryList,
-									outerPredicateList);
-		}
-	}
-
-	/**
 	 * Optimize the subqueries in this list.  
 	 *
 	 * @param dataDictionary	The data dictionary to use for optimization

Modified: db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java?rev=1574505&r1=1574504&r2=1574505&view=diff
==============================================================================
--- db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java
(original)
+++ db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java
Wed Mar  5 14:30:15 2014
@@ -889,12 +889,13 @@ public class SubqueryNode extends ValueN
 			topNode = pushNewPredicate(numTables);
 			pushedNewPredicate = true;
 		}
-		/* Since NOT EXISTS subquery is not flattened, now is good time to create
-		 * an IS NULL node on top.  Other cases are taken care of in pushNewPredicate.
+        /* EXISTS and NOT EXISTS subqueries that haven't been flattened, need
+         * an IS [NOT] NULL node on top so that they return a BOOLEAN. Other
+         * cases are taken care of in pushNewPredicate.
 		 */
-		else if (subqueryType == NOT_EXISTS_SUBQUERY)
+        else if (isEXISTS() || isNOT_EXISTS())
 		{
-			topNode = genIsNullTree();
+            topNode = genIsNullTree(isEXISTS());
 			subqueryType = EXISTS_SUBQUERY;
 		}
 

Modified: db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/ValueNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/ValueNode.java?rev=1574505&r1=1574504&r2=1574505&view=diff
==============================================================================
--- db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/ValueNode.java
(original)
+++ db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/ValueNode.java
Wed Mar  5 14:30:15 2014
@@ -561,20 +561,22 @@ public abstract class ValueNode extends 
 	}
 
 	/**
-	 * Transform this into this is null.  Useful for NOT elimination.
+     * Transform this into this IS NULL or IS NOT NULL.
 	 *
+     * @param notNull if true, transform this into IS NOT NULL;
+     *                otherwise, transform this into IS NULL
 	 * @return		The modified expression
 	 *
 	 * @exception StandardException		Thrown on error
 	 */
-	public ValueNode genIsNullTree()
+    ValueNode genIsNullTree(boolean notNull)
 			throws StandardException
 	{
 		IsNullNode isNullNode;
 
 		isNullNode = (IsNullNode)
 							getNodeFactory().getNode(
-													C_NodeTypes.IS_NULL_NODE,
+													notNull ? C_NodeTypes.IS_NOT_NULL_NODE : C_NodeTypes.IS_NULL_NODE,
 													this,
 													getContextManager());
 		isNullNode.setType(new DataTypeDescriptor(

Modified: db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/master/schemas.out
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/master/schemas.out?rev=1574505&r1=1574504&r2=1574505&view=diff
==============================================================================
--- db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/master/schemas.out
(original)
+++ db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/master/schemas.out
Wed Mar  5 14:30:15 2014
@@ -774,9 +774,9 @@ from test.s
 order by a;
 A    
 -----
+false
+false
 true 
-NULL 
-NULL 
 ij> -- negative tests
 
 -- multiple matches at parent level

Modified: db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ExistsWithSubqueriesTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ExistsWithSubqueriesTest.java?rev=1574505&r1=1574504&r2=1574505&view=diff
==============================================================================
--- db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ExistsWithSubqueriesTest.java
(original)
+++ db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ExistsWithSubqueriesTest.java
Wed Mar  5 14:30:15 2014
@@ -540,4 +540,106 @@ public class ExistsWithSubqueriesTest ex
         s.close();
     }
 
+    /**
+     * Some EXISTS subqueries (and IN subqueries transformed to EXISTS)
+     * returned NULL instead of TRUE or FALSE before DERBY-6408. This test
+     * case verifies the fix.
+     */
+    public void testDerby6408() throws SQLException {
+        setAutoCommit(false);
+        Statement s = createStatement();
+
+        // This statement used to return only NULLs.
+        JDBC.assertFullResultSet(
+                s.executeQuery("values (exists(select * from empty), "
+                        + "not exists (select * from empty), "
+                        + "not (exists (select * from empty)), "
+                        + "not (not exists (select * from empty)))"),
+                new String[][] {{"false", "true", "true", "false" }});
+
+        // This similar statement worked even before the fix.
+        JDBC.assertFullResultSet(
+                s.executeQuery("values (exists(select * from onerow), "
+                        + "not exists (select * from onerow), "
+                        + "not (exists (select * from onerow)), "
+                        + "not (not exists (select * from onerow)))"),
+                new String[][] {{"true", "false", "false", "true" }});
+
+        // Now put the same expressions in the SELECT list. Used to return
+        // only NULLs.
+        JDBC.assertFullResultSet(
+                s.executeQuery("select exists(select * from empty), "
+                        + "not exists (select * from empty), "
+                        + "not (exists (select * from empty)), "
+                        + "not (not exists (select * from empty)) from onerow"),
+                new String[][] {{"false", "true", "true", "false" }});
+
+        // Check the returned value when used in a WHERE predicate. All of
+        // these queries returned one row, but they should return no rows
+        // because EXISTS shouldn't return null.
+        JDBC.assertEmpty(s.executeQuery("select * from onerow "
+                + "where (exists (select * from empty)) is null"));
+        JDBC.assertEmpty(s.executeQuery("select * from onerow "
+                + "where (not exists (select * from empty)) is null"));
+        JDBC.assertEmpty(s.executeQuery("select * from onerow "
+                + "where (not (not exists (select * from empty))) is null"));
+
+        // The results were correct even before the fix if the subquery
+        // wasn't empty. Verify that they still are.
+        JDBC.assertEmpty(s.executeQuery("select * from onerow "
+                + "where (exists (select * from onerow)) is null"));
+        JDBC.assertEmpty(s.executeQuery("select * from onerow "
+                + "where (not exists (select * from onerow)) is null"));
+        JDBC.assertEmpty(s.executeQuery("select * from onerow "
+                + "where (not (not exists (select * from onerow))) is null"));
+
+        // Similar problems were seen in IN subqueries that were rewritten
+        // to EXISTS subqueries internally. For example, this query used
+        // to return NULL.
+        JDBC.assertSingleValueResultSet(
+                s.executeQuery("values 1 in (select j from onerow)"), "false");
+
+        // If it should evaluate to TRUE, it worked even before the fix.
+        JDBC.assertSingleValueResultSet(
+                s.executeQuery("values 2 in (select j from onerow)"), "true");
+
+        // DERBY-6409: Quantified comparisons can also be rewritten to EXISTS,
+        // and these two queries returned wrong results before the fix.
+        JDBC.assertSingleValueResultSet(
+                s.executeQuery("values 1 > all (select 2 from tworows)"), "false");
+        JDBC.assertSingleValueResultSet(
+                s.executeQuery("values 1 < all (select 2 from tworows)"), "true");
+
+        // Verify that EXISTS works in INSERT and UPDATE.
+        s.execute("create table d6408(id int generated by default as identity,"
+                + " b boolean not null)");
+
+        // This used to fail with
+        // ERROR 23502: Column 'B'  cannot accept a NULL value.
+        s.execute("insert into d6408(b) values exists (select * from empty), "
+                + "not exists (select * from empty), "
+                + "exists (select * from onerow), "
+                + "not exists (select * from onerow)");
+
+        JDBC.assertFullResultSet(
+                s.executeQuery("select b from d6408 order by id"),
+                new String[][] {{"false"}, {"true"}, {"true"}, {"false"}});
+
+        // These used to fail with
+        // ERROR 23502: Column 'B'  cannot accept a NULL value.
+        s.execute("update d6408 set b = exists (select * from empty)");
+        JDBC.assertSingleValueResultSet(
+                s.executeQuery("select distinct b from d6408"), "false");
+        s.execute("update d6408 set b = not exists (select * from empty)");
+        JDBC.assertSingleValueResultSet(
+                s.executeQuery("select distinct b from d6408"), "true");
+
+        // These passed even before the fix.
+        s.execute("update d6408 set b = exists (select * from onerow)");
+        JDBC.assertSingleValueResultSet(
+                s.executeQuery("select distinct b from d6408"), "true");
+        s.execute("update d6408 set b = not exists (select * from onerow)");
+        JDBC.assertSingleValueResultSet(
+                s.executeQuery("select distinct b from d6408"), "false");
+    }
 }

Modified: db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java?rev=1574505&r1=1574504&r2=1574505&view=diff
==============================================================================
--- db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java
(original)
+++ db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java
Wed Mar  5 14:30:15 2014
@@ -10186,9 +10186,8 @@ public final class GrantRevokeDDLTest ex
             { "values (select count(*) from user1.t4191)", new String[][] {{"0"}} },
             { "values (select count(1) from user1.t4191)", new String[][] {{"0"}} },
             { "values ((select 1 from user1.t4191))",      new String[][] {{null}} },
-            // DERBY-6408: Next two queries should have returned FALSE.
-            { "values exists(select 1 from user1.t4191)",  new String[][] {{null}} },
-            { "values exists(select * from user1.t4191)",  new String[][] {{null}} },
+            { "values exists(select 1 from user1.t4191)",  new String[][] {{"false"}} },
+            { "values exists(select * from user1.t4191)",  new String[][] {{"false"}} },
             { "select count(*) from (select 1 from user1.t4191) s", new String[][] {{"0"}}
},
             { "insert into user1.t4191_table3 select 1, 2 from user1.t4191", new Integer(0)
},
             { "update user1.t4191_table3 set c31 = 1 where exists (select * from user1.t4191)",
new Integer(0) },



Mime
View raw message