Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Subject: svn commit: r1424889 - in /db/derby/code/trunk/java:
 engine/org/apache/derby/impl/sql/compile/
 testing/org/apache/derbyTesting/functionTests/tests/lang/
Date: Fri, 21 Dec 2012 12:11:19 -0000
To: derby-commits@db.apache.org
From: kahatlen@apache.org
Message-Id: <20121221121120.44CFF23888CD@eris.apache.org>

Author: kahatlen
Date: Fri Dec 21 12:11:19 2012
New Revision: 1424889

URL: http://svn.apache.org/viewvc?rev=1424889&view=rev
Log:
DERBY-6017: IN lists with mixed types may return wrong results

Fix the case where all the constant values in an IN list get reduced
to a single constant after conversion to the dominant type. In such
cases, the remaining constant should be converted to the dominant type.

Added:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InPredicateTest.java   (with props)
Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/InListOperatorNode.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/InListOperatorNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/InListOperatorNode.java?rev=1424889&r1=1424888&r2=1424889&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/InListOperatorNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/InListOperatorNode.java Fri Dec 21 12:11:19 2012
@@ -279,6 +279,27 @@ public final class InListOperatorNode ex
 
 				if (judgeODV.equals(minODV, maxODV).equals(true))
 				{
+                    int judgePrecedence = judgeODV.typePrecedence();
+                    int leftPrecedence = leftOperand.getTypeServices()
+                            .getTypeId().typePrecedence();
+                    if (leftPrecedence != judgePrecedence &&
+                            minODV.typePrecedence() != judgePrecedence) {
+                        // DERBY-6017: If neither the minimum value nor the
+                        // left operand is of the dominant type, cast the
+                        // minimum value to the dominant type. Otherwise, the
+                        // equals operation will be performed using a different
+                        // type, which may not have the same ordering as the
+                        // type used to sort the list, and it could produce
+                        // unexpected results.
+                        CastNode cn = (CastNode) getNodeFactory().getNode(
+                                C_NodeTypes.CAST_NODE,
+                                minValue,
+                                targetType,
+                                getContextManager());
+                        cn.bindCastNodeOnly();
+                        minValue = cn;
+                    }
+
 					BinaryComparisonOperatorNode equal = 
 						(BinaryComparisonOperatorNode)getNodeFactory().getNode(
 							C_NodeTypes.BINARY_EQUALS_OPERATOR_NODE,

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InPredicateTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InPredicateTest.java?rev=1424889&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InPredicateTest.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InPredicateTest.java Fri Dec 21 12:11:19 2012
@@ -0,0 +1,121 @@
+/*
+
+Derby - Class org.apache.derbyTesting.functionTests.tests.lang.InPredicateTest
+
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements.  See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License.  You may obtain a copy of the License at
+
+   http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+
+*/
+
+package org.apache.derbyTesting.functionTests.tests.lang;
+
+import java.sql.PreparedStatement;
+import java.sql.SQLException;
+import java.sql.Statement;
+import junit.framework.Test;
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.TestConfiguration;
+
+/**
+ * Test cases for IN predicates.
+ */
+public class InPredicateTest extends BaseJDBCTestCase {
+    public InPredicateTest(String name) {
+        super(name);
+    }
+
+    public static Test suite() {
+        // This is a test for language features, so running in one
+        // configuration should be enough.
+        return new CleanDatabaseTestSetup(
+                TestConfiguration.embeddedSuite(InPredicateTest.class));
+    }
+
+    /**
+     * <p>
+     * Test case for DERBY-6017. InListOperatorNode optimizes the case
+     * where all values in the IN list are constant and represent the same
+     * value, but the optimization could get confused if the IN list had
+     * constants of different types.
+     * </p>
+     *
+     * <p>
+     * For example, a predicate such as {@code x IN (9223372036854775806,
+     * 9223372036854775807, 9.223372036854776E18)} would be optimized to
+     * {@code x = 9223372036854775806}, which is not an equivalent expression.
+     * </p>
+     *
+     * <p>
+     * It is correct to reduce the IN list to a single comparison in this
+     * case, since all the values in the IN list should be converted to the
+     * dominant type. The dominant type in the list is DOUBLE, and all three
+     * values are equal when they are converted to DOUBLE (because DOUBLE can
+     * only approximate the integers that are close to Long.MAX_VALUE).
+     * However, the simplified expression needs to use the value as a DOUBLE,
+     * otherwise it cannot be used as a substitution for all the values in
+     * the IN list.
+     * </p>
+     *
+     * <p>
+     * DERBY-6017 solves it by optimizing the above predicate to
+     * {@code x = CAST(9223372036854775806 AS DOUBLE)}.
+     * </p>
+     */
+    public void testDuplicateConstantsMixedTypes() throws SQLException {
+        setAutoCommit(false);
+
+        Statement s = createStatement();
+        s.executeUpdate("create table t1(b bigint)");
+
+        String[][] allRows = {
+            { Long.toString(Long.MAX_VALUE - 2) },
+            { Long.toString(Long.MAX_VALUE - 1) },
+            { Long.toString(Long.MAX_VALUE)     },
+        };
+
+        // Fill the table with BIGINT values so close to Long.MAX_VALUE that
+        // they all degenerate to a single value when converted to DOUBLE.
+        PreparedStatement insert = prepareStatement("insert into t1 values ?");
+        for (int i = 0; i < allRows.length; i++) {
+            insert.setString(1, allRows[i][0]);
+            insert.executeUpdate();
+        }
+
+        // Expect this query to return all the rows in the table. It used
+        // to return only the first row.
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                "select * from t1 where b in " +
+                "(9223372036854775805, 9223372036854775806," +
+                " 9223372036854775807, 9.223372036854776E18)"),
+                allRows);
+
+        // SQL:2003, 8.4 <in predicate> says IN (x,y,z) is equivalent to
+        // IN (VALUES x,y,z), and also that x IN (...) is equivalent to
+        // x = ANY (...). Verify the correctness of the above result by
+        // comparing to the following equivalent queries.
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                "select * from t1 where b in " +
+                "(values 9223372036854775805, 9223372036854775806," +
+                " 9223372036854775807, 9.223372036854776E18)"),
+                allRows);
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                "select * from t1 where b = any " +
+                "(values 9223372036854775805, 9223372036854775806," +
+                " 9223372036854775807, 9.223372036854776E18)"),
+                allRows);
+    }
+}

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InPredicateTest.java
------------------------------------------------------------------------------
    svn:eol-style = native

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java?rev=1424889&r1=1424888&r2=1424889&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java Fri Dec 21 12:11:19 2012
@@ -106,6 +106,7 @@ public class _Suite extends BaseTestCase
         suite.addTest(ScrollCursors2Test.suite());
         suite.addTest(NullIfTest.suite());
         suite.addTest(InListMultiProbeTest.suite());
+        suite.addTest(InPredicateTest.suite());
         suite.addTest(SecurityPolicyReloadingTest.suite());
         suite.addTest(CurrentOfTest.suite());
         suite.addTest(UnaryArithmeticParameterTest.suite());