You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by ka...@apache.org on 2014/03/05 15:30:16 UTC
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/
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) },