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 mi...@apache.org on 2014/10/10 19:51:35 UTC
svn commit: r1630946 - in /db/derby/code/branches/10.10: ./
java/engine/org/apache/derby/impl/sql/compile/
java/testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: mikem
Date: Fri Oct 10 17:51:35 2014
New Revision: 1630946
URL: http://svn.apache.org/r1630946
Log:
DERBY-6563: NOT elimination for CASE expressions is broken
backporting change #1592945 from trunk to 10.10 branch. Had to resolve
conflicts and change code in tests to compile/run in pre-jdk15 environments.
Modified:
db/derby/code/branches/10.10/ (props changed)
db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java
db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CaseExpressionTest.java
db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NullIfTest.java
Propchange: db/derby/code/branches/10.10/
------------------------------------------------------------------------------
Merged /db/derby/code/trunk:r1592945
Modified: db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java?rev=1630946&r1=1630945&r2=1630946&view=diff
==============================================================================
--- db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java (original)
+++ db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/ConditionalNode.java Fri Oct 10 17:51:35 2014
@@ -672,19 +672,19 @@ public class ConditionalNode extends Val
ValueNode eliminateNots(boolean underNotNode)
throws StandardException
{
- ValueNode thenExpression;
- ValueNode elseExpression;
-
- if (! underNotNode)
- {
- return this;
- }
-
- /* Simply swap the then and else expressions */
- thenExpression = (ValueNode) thenElseList.elementAt(0);
- elseExpression = (ValueNode) thenElseList.elementAt(1);
- thenElseList.setElementAt(elseExpression, 0);
- thenElseList.setElementAt(thenExpression, 1);
+ // NOT CASE WHEN a THEN b ELSE c END is equivalent to
+ // CASE WHEN a THEN NOT b ELSE NOT c END, so just push the
+ // NOT node down to the THEN and ELSE expressions.
+ for (int i = 0; i < thenElseList.size(); i++) {
+ thenElseList.setElementAt(
+ ((ValueNode) thenElseList.elementAt(i)).eliminateNots(underNotNode),
+ i);
+ }
+
+ // Eliminate NOTs in the WHEN expression too. The NOT node above us
+ // should not be pushed into the WHEN expression, though, as that
+ // would alter the meaning of the CASE expression.
+ testCondition = testCondition.eliminateNots(false);
return this;
}
Modified: db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CaseExpressionTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CaseExpressionTest.java?rev=1630946&r1=1630945&r2=1630946&view=diff
==============================================================================
--- db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CaseExpressionTest.java (original)
+++ db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CaseExpressionTest.java Fri Oct 10 17:51:35 2014
@@ -21,11 +21,13 @@
package org.apache.derbyTesting.functionTests.tests.lang;
-import java.sql.Connection;
+import java.sql.PreparedStatement;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
+import java.util.ArrayList;
+import java.util.Arrays;
import junit.framework.Test;
import junit.framework.TestSuite;
@@ -378,5 +380,108 @@ public class CaseExpressionTest extends
JDBC.assertSingleValueResultSet(rs, "6");
}
-
+
+ /**
+ * Verify that NOT elimination produces the correct results.
+ * DERBY-6563.
+ */
+ public void testNotElimination() throws SQLException {
+ setAutoCommit(false);
+
+ Statement s = createStatement();
+ s.execute("create table d6563(b1 boolean, b2 boolean, b3 boolean)");
+
+ // Fill the table with all possible combinations of true/false/null.
+ Boolean[] universe = { Boolean.TRUE, Boolean.FALSE, null };
+ PreparedStatement insert = prepareStatement(
+ "insert into d6563 values (?, ?, ?)");
+
+ for (int i = 0; i < universe.length; i++)
+ {
+ Boolean v1 = universe[i];
+
+ insert.setObject(1, v1);
+
+ for (int i2 = 0; i2 < universe.length; i2++)
+ {
+ Boolean v2 = universe[i2];
+
+ insert.setObject(2, v2);
+
+ for (int i3 = 0; i3 < universe.length; i3++)
+ {
+ Boolean v3 = universe[i3];
+
+ insert.setObject(3, v3);
+ insert.executeUpdate();
+ }
+ }
+ }
+
+ // Truth table for
+ // B1, B2, B3, WHEN B1 THEN B2 ELSE B3, NOT (WHEN B1 THEN B2 ELSE B3).
+ Object[][] expectedRows = {
+ { Boolean.FALSE, Boolean.FALSE, Boolean.FALSE, Boolean.FALSE, Boolean.TRUE },
+ { Boolean.FALSE, Boolean.FALSE, Boolean.TRUE, Boolean.TRUE, Boolean.FALSE },
+ { Boolean.FALSE, Boolean.FALSE, null, null, null },
+ { Boolean.FALSE, Boolean.TRUE, Boolean.FALSE, Boolean.FALSE, Boolean.TRUE },
+ { Boolean.FALSE, Boolean.TRUE, Boolean.TRUE, Boolean.TRUE, Boolean.FALSE },
+ { Boolean.FALSE, Boolean.TRUE, null, null, null },
+ { Boolean.FALSE, null, Boolean.FALSE, Boolean.FALSE, Boolean.TRUE },
+ { Boolean.FALSE, null, Boolean.TRUE, Boolean.TRUE, Boolean.FALSE },
+ { Boolean.FALSE, null, null, null, null },
+ { Boolean.TRUE, Boolean.FALSE, Boolean.FALSE, Boolean.FALSE, Boolean.TRUE },
+ { Boolean.TRUE, Boolean.FALSE, Boolean.TRUE, Boolean.FALSE, Boolean.TRUE },
+ { Boolean.TRUE, Boolean.FALSE, null, Boolean.FALSE, Boolean.TRUE },
+ { Boolean.TRUE, Boolean.TRUE, Boolean.FALSE, Boolean.TRUE, Boolean.FALSE },
+ { Boolean.TRUE, Boolean.TRUE, Boolean.TRUE, Boolean.TRUE, Boolean.FALSE },
+ { Boolean.TRUE, Boolean.TRUE, null, Boolean.TRUE, Boolean.FALSE },
+ { Boolean.TRUE, null, Boolean.FALSE, null, null },
+ { Boolean.TRUE, null, Boolean.TRUE, null, null },
+ { Boolean.TRUE, null, null, null, null },
+ { null, Boolean.FALSE, Boolean.FALSE, Boolean.FALSE, Boolean.TRUE },
+ { null, Boolean.FALSE, Boolean.TRUE, Boolean.TRUE, Boolean.FALSE },
+ { null, Boolean.FALSE, null, null, null },
+ { null, Boolean.TRUE, Boolean.FALSE, Boolean.FALSE, Boolean.TRUE },
+ { null, Boolean.TRUE, Boolean.TRUE, Boolean.TRUE, Boolean.FALSE },
+ { null, Boolean.TRUE, null, null, null },
+ { null, null, Boolean.FALSE, Boolean.FALSE, Boolean.TRUE },
+ { null, null, Boolean.TRUE, Boolean.TRUE, Boolean.FALSE },
+ { null, null, null, null, null },
+ };
+
+ // Verify the truth table. Since NOT elimination is not performed on
+ // expressions in the SELECT list, this passed even before the fix.
+ JDBC.assertFullResultSet(
+ s.executeQuery(
+ "select b1, b2, b3, case when b1 then b2 else b3 end, "
+ + "not case when b1 then b2 else b3 end "
+ + "from d6563 order by b1, b2, b3"),
+ expectedRows, false);
+
+ // Now take only those rows where the NOT CASE expression evaluated
+ // to TRUE, and strip off the expression columns at the end.
+ ArrayList rows = new ArrayList();
+
+
+ for (int i = 0; i < expectedRows.length; i++)
+ {
+ Object[] row = expectedRows[i];
+
+ if (row[4] == Boolean.TRUE) {
+ rows.add(Arrays.copyOf(row, 3));
+ }
+ }
+
+ // Assert that those are the only rows returned if the NOT CASE
+ // expression is used as a predicate. This query used to return a
+ // different set of rows before the fix.
+ expectedRows = (Object[][]) rows.toArray(new Object[rows.size()][]);
+ JDBC.assertFullResultSet(
+ s.executeQuery("select * from d6563 where "
+ + "not case when b1 then b2 else b3 end "
+ + "order by b1, b2, b3"),
+ expectedRows, false);
+ }
+
}
Modified: db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NullIfTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NullIfTest.java?rev=1630946&r1=1630945&r2=1630946&view=diff
==============================================================================
--- db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NullIfTest.java (original)
+++ db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/NullIfTest.java Fri Oct 10 17:51:35 2014
@@ -305,6 +305,51 @@ public class NullIfTest extends BaseJDBC
}
/**
+ * Verify that NOT elimination produces the correct results.
+ * DERBY-6563.
+ */
+ public void testNotElimination() throws SQLException {
+ setAutoCommit(false);
+
+ Statement s = createStatement();
+ s.execute("create table d6563(b1 boolean, b2 boolean)");
+ s.execute("insert into d6563 values (true, true), (true, false), "
+ + "(true, null), (false, true), (false, false), "
+ + "(false, null), (null, null), (null, true), (null, false)");
+
+ // Truth table for B1, B2, NULLIF(B1, B2), NOT NULLIF(B1, B2).
+ Object[][] expectedRows = {
+ { Boolean.FALSE, Boolean.FALSE, null, null },
+ { Boolean.FALSE, Boolean.TRUE, Boolean.FALSE, Boolean.TRUE },
+ { Boolean.FALSE, null, Boolean.FALSE, Boolean.TRUE },
+ { Boolean.TRUE, Boolean.FALSE, Boolean.TRUE, Boolean.FALSE },
+ { Boolean.TRUE, Boolean.TRUE, null, null },
+ { Boolean.TRUE, null, Boolean.TRUE, Boolean.FALSE },
+ { null, Boolean.FALSE, null, null },
+ { null, Boolean.TRUE, null, null },
+ { null, null, null, null },
+ };
+
+ // Verify the truth table. Since NOT elimination is not performed on
+ // expressions in the SELECT list, this passed even before the fix.
+ JDBC.assertFullResultSet(
+ s.executeQuery(
+ "select b1 , b2, nullif(b1, b2), not nullif(b1, b2) "
+ + "from d6563 order by b1, b2"),
+ expectedRows, false);
+
+ // Only two rows - (false, true) and (false, null) - made
+ // NOT NULLIF(B1, B2) evaluate to TRUE. Verify that it also evaluates
+ // to TRUE for those two rows, and only those two rows, when it is
+ // used as a predicate. The query used to return (true, true).
+ JDBC.assertFullResultSet(
+ s.executeQuery(
+ "select * from d6563 where not nullif(b1, b2) order by b1, b2"),
+ new Object[][] { { Boolean.FALSE, Boolean.TRUE }, { Boolean.FALSE, null } },
+ false);
+ }
+
+ /**
* Runs the test fixtures in embedded and client.
*
* @return test suite