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 2012/12/21 13:11:19 UTC
svn commit: r1424889 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/compile/
testing/org/apache/derbyTesting/functionTests/tests/lang/
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());