db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kahat...@apache.org
Subject svn commit: r1573548 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Mon, 03 Mar 2014 12:50:08 GMT
Author: kahatlen
Date: Mon Mar  3 12:50:08 2014
New Revision: 1573548

URL: http://svn.apache.org/r1573548
Log:
DERBY-6408: EXISTS returns NULL instead of FALSE

Make sure EXISTS subqueries are wrapped in an IsNullNode to convert
NULL to a BOOLEAN. Before, only NOT EXISTS subqueries were wrapped.

Make RowResultSetNode.preprocess() invoke preprocess() on the result
columns instead of directly on the subquery nodes. Calling it directly
on the subquery nodes prevented the result columns from seeing the
added IsNullNode.

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

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowResultSetNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowResultSetNode.java?rev=1573548&r1=1573547&r2=1573548&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowResultSetNode.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowResultSetNode.java
Mon Mar  3 12:50:08 2014
@@ -420,17 +420,11 @@ class RowResultSetNode extends FromTable
 									FromList fromList)
 								throws StandardException
 	{
-
-		if (subquerys.size() > 0)
-		{
-			subquerys.preprocess(
+        getResultColumns().preprocess(
                 numTables,
-                new FromList(
-                    getOptimizerFactory().doJoinOrderOptimization(),
-                    getContextManager()),
-                new SubqueryList(getContextManager()),
+                fromList,
+                subquerys,
                 new PredicateList(getContextManager()));
-		}
 
 		/* Allocate a dummy referenced table map */ 
 		setReferencedTableMap( new JBitSet(numTables) );

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryList.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryList.java?rev=1573548&r1=1573547&r2=1573548&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryList.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryList.java Mon
Mar  3 12:50:08 2014
@@ -50,31 +50,6 @@ class SubqueryList extends QueryTreeNode
 	}
 
 	/**
-	 * 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
-	 */
-    void preprocess(int numTables,
-							FromList outerFromList,
-							SubqueryList outerSubqueryList,
-							PredicateList outerPredicateList) 
-				throws StandardException
-	{
-        for (SubqueryNode sqn : this)
-		{
-            sqn.preprocess(numTables, outerFromList,
-									outerSubqueryList,
-									outerPredicateList);
-		}
-	}
-
-	/**
 	 * Optimize the subqueries in this list.  
 	 *
 	 * @param dataDictionary	The data dictionary to use for optimization

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java?rev=1573548&r1=1573547&r2=1573548&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java Mon
Mar  3 12:50:08 2014
@@ -885,12 +885,13 @@ class SubqueryNode extends ValueNode
 			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/trunk/java/engine/org/apache/derby/impl/sql/compile/ValueNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ValueNode.java?rev=1573548&r1=1573547&r2=1573548&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ValueNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ValueNode.java Mon Mar
 3 12:50:08 2014
@@ -483,18 +483,20 @@ 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
 	 */
-    ValueNode genIsNullTree()
+    ValueNode genIsNullTree(boolean notNull)
 			throws StandardException
 	{
 		IsNullNode isNullNode;
 
-       isNullNode = new IsNullNode(this, false, getContextManager());
+        isNullNode = new IsNullNode(this, notNull, getContextManager());
 		isNullNode.setType(new DataTypeDescriptor(
 									TypeId.BOOLEAN_ID,
 									false)

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/schemas.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/schemas.out?rev=1573548&r1=1573547&r2=1573548&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/schemas.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/schemas.out
Mon Mar  3 12:50:08 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/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ExistsWithSubqueriesTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ExistsWithSubqueriesTest.java?rev=1573548&r1=1573547&r2=1573548&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ExistsWithSubqueriesTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ExistsWithSubqueriesTest.java
Mon Mar  3 12:50:08 2014
@@ -540,4 +540,99 @@ 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 an 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");
+
+        // 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/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java?rev=1573548&r1=1573547&r2=1573548&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java
Mon Mar  3 12:50:08 2014
@@ -10198,9 +10198,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