Return-Path: X-Original-To: apmail-db-derby-commits-archive@www.apache.org Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 8CD1710B18 for ; Mon, 7 Oct 2013 10:46:51 +0000 (UTC) Received: (qmail 24097 invoked by uid 500); 7 Oct 2013 10:46:50 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 24088 invoked by uid 500); 7 Oct 2013 10:46:50 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 24076 invoked by uid 99); 7 Oct 2013 10:46:48 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 07 Oct 2013 10:46:48 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=5.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO eris.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 07 Oct 2013 10:46:47 +0000 Received: from eris.apache.org (localhost [127.0.0.1]) by eris.apache.org (Postfix) with ESMTP id 180AE238899C; Mon, 7 Oct 2013 10:46:27 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r1529815 - in /db/derby/code/branches/10.8: ./ java/client/org/apache/derby/client/net/ java/engine/org/apache/derby/impl/sql/compile/ java/testing/org/apache/derbyTesting/functionTests/tests/lang/ Date: Mon, 07 Oct 2013 10:46:26 -0000 To: derby-commits@db.apache.org From: kahatlen@apache.org X-Mailer: svnmailer-1.0.9 Message-Id: <20131007104627.180AE238899C@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: kahatlen Date: Mon Oct 7 10:46:26 2013 New Revision: 1529815 URL: http://svn.apache.org/r1529815 Log: DERBY-6363: Incorrect evaluation of logical expressions in CASE Merged revision 1529099 from trunk. Modified: db/derby/code/branches/10.8/ (props changed) db/derby/code/branches/10.8/java/client/org/apache/derby/client/net/NetCursor.java (props changed) db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/OrNode.java db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/BooleanValuesTest.java Propchange: db/derby/code/branches/10.8/ ------------------------------------------------------------------------------ Merged /db/derby/code/trunk:r1529099 Propchange: db/derby/code/branches/10.8/java/client/org/apache/derby/client/net/NetCursor.java ------------------------------------------------------------------------------ Merged /db/derby/code/trunk/java/client/org/apache/derby/client/net/NetCursor.java:r1529099 Modified: db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/OrNode.java URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/OrNode.java?rev=1529815&r1=1529814&r2=1529815&view=diff ============================================================================== --- db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/OrNode.java (original) +++ db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/OrNode.java Mon Oct 7 10:46:26 2013 @@ -116,6 +116,25 @@ public class OrNode extends BinaryLogica * or: * x = ColumnReference * where all ColumnReferences are from the same table. + * + * We only convert the OR chain to an IN list if it has been + * normalized to conjunctive normal form (CNF) first. That is, the + * shape of the chain must be something like this: + * + * OR + * / \ + * = OR + * / \ + * = OR + * / \ + * = FALSE + * + * Predicates in WHERE, HAVING and ON clauses will have been + * normalized by the time we get here. Boolean expressions other + * places in the query are not necessarily normalized, but they + * won't benefit from IN list conversion anyway, since they cannot + * be used as qualifiers in a multi-probe scan, so simply skip the + * conversion in those cases. */ if (firstOr) { @@ -123,8 +142,11 @@ public class OrNode extends BinaryLogica ColumnReference cr = null; int columnNumber = -1; int tableNumber = -1; + ValueNode vn; - for (ValueNode vn = this; vn instanceof OrNode; vn = ((OrNode) vn).getRightOperand()) + for (vn = this; + vn instanceof OrNode; + vn = ((OrNode) vn).getRightOperand()) { OrNode on = (OrNode) vn; ValueNode left = on.getLeftOperand(); @@ -211,6 +233,12 @@ public class OrNode extends BinaryLogica } } + // DERBY-6363: An OR chain on conjunctive normal form should be + // terminated by a false BooleanConstantNode. If it is terminated + // by some other kind of node, it is not on CNF, and it should + // not be converted to an IN list. + convert = convert && vn.isBooleanFalse(); + /* So, can we convert the OR chain? */ if (convert) { @@ -218,7 +246,9 @@ public class OrNode extends BinaryLogica C_NodeTypes.VALUE_NODE_LIST, getContextManager()); // Build the IN list - for (ValueNode vn = this; vn instanceof OrNode; vn = ((OrNode) vn).getRightOperand()) + for (vn = this; + vn instanceof OrNode; + vn = ((OrNode) vn).getRightOperand()) { OrNode on = (OrNode) vn; BinaryRelationalOperatorNode bron = @@ -461,7 +491,7 @@ public class OrNode extends BinaryLogica } else { - isValid = leftOperand.verifyChangeToCNF(); + isValid = isValid && leftOperand.verifyChangeToCNF(); } } Modified: db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/BooleanValuesTest.java URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/BooleanValuesTest.java?rev=1529815&r1=1529814&r2=1529815&view=diff ============================================================================== --- db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/BooleanValuesTest.java (original) +++ db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/BooleanValuesTest.java Mon Oct 7 10:46:26 2013 @@ -1979,6 +1979,50 @@ public class BooleanValuesTest extends "values case when 1 or 2 then 1 else 0 end"); } + /** + * Some BOOLEAN expressions used to be transformed to non-equivalent + * IN lists. Verify that they now return the correct results. + * Regression test case for DERBY-6363. + */ + public void test_6363() throws SQLException { + Statement s = createStatement(); + s.execute("create table d6363(a int, b char)"); + s.execute("insert into d6363 values (1, 'a'), (2, 'b'), (3, 'a'), " + + "(4, 'b'), (5, 'a'), (6, 'b')"); + + JDBC.assertFullResultSet(s.executeQuery( + "select a, ((b = 'a' or b = 'b') and a < 4), " + + "((b = 'a' or b = 'c' or b = 'b') and a < 4), " + + "((b = 'a' or (b = 'c' or b = 'b')) and a < 4), " + + "((b = 'a' or b in ('c', 'b')) and a < 4), " + + "(a < 4 and (b = 'a' or b = 'b')) " + + "from d6363 order by a"), + new String[][] { + { "1", "true", "true", "true", "true", "true" }, + { "2", "true", "true", "true", "true", "true" }, + { "3", "true", "true", "true", "true", "true" }, + { "4", "false", "false", "false", "false", "false" }, + { "5", "false", "false", "false", "false", "false" }, + { "6", "false", "false", "false", "false", "false" }, + }); + + JDBC.assertFullResultSet(s.executeQuery( + "select a, b, " + + "case when ((b = 'a' or b = 'b') and a < 4) " + + "then 'x' else '-' end, " + + "case when (a < 4 and (b = 'a' or b = 'b')) " + + "then 'y' else '-' end " + + "from d6363 order by a"), + new String[][] { + { "1", "a", "x", "y" }, + { "2", "b", "x", "y" }, + { "3", "a", "x", "y" }, + { "4", "b", "-", "-" }, + { "5", "a", "-", "-" }, + { "6", "b", "-", "-" }, + }); + } + /////////////////////////////////////////////////////////////////////////////////// // // SQL ROUTINES