db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From abr...@apache.org
Subject svn commit: r574730 - in /db/derby/code/branches/10.3/java: engine/org/apache/derby/impl/sql/compile/Predicate.java testing/org/apache/derbyTesting/functionTests/tests/lang/InListMultiProbeTest.java
Date Tue, 11 Sep 2007 23:38:57 GMT
Author: abrown
Date: Tue Sep 11 16:38:56 2007
New Revision: 574730

URL: http://svn.apache.org/viewvc?rev=574730&view=rev
Log:
DERBY-3061: Fix wrong results regression caused by incorrect sorting of
"probe predicates" prior to code generation.  Port from trunk to 10.3.

Modified:
    db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/Predicate.java
    db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InListMultiProbeTest.java

Modified: db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/Predicate.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/Predicate.java?rev=574730&r1=574729&r2=574730&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/Predicate.java
(original)
+++ db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/Predicate.java
Tue Sep 11 16:38:56 2007
@@ -306,7 +306,16 @@
 		boolean thisIsEquals = false, otherIsEquals = false;
 		boolean thisIsNotEquals = true, otherIsNotEquals = true;
 
-		if (this.isRelationalOpPredicate()) // this is not "in"
+		/* The call to "isRelationalOpPredicate()" will return false
+		 * for a "probe predicate" because a probe predicate is really
+		 * a disguised IN list. But when it comes to sorting, the probe
+		 * predicate (which is of the form "<col> = ?") should be treated
+		 * as an equality--i.e. it should have precedence over any non-
+		 * equals predicate, per the comment at the start of this
+		 * method.  So that's what we're checking here.
+		 */
+		if (this.isRelationalOpPredicate() || // this is not "in" or
+			this.isInListProbePredicate())    // this is a probe predicate
 		{
 			int thisOperator = ((RelationalOperator)andNode.getLeftOperand()).getOperator();
 			thisIsEquals = (thisOperator == RelationalOperator.EQUALS_RELOP ||
@@ -314,7 +323,9 @@
 			thisIsNotEquals = (thisOperator == RelationalOperator.NOT_EQUALS_RELOP ||
 								   thisOperator == RelationalOperator.IS_NOT_NULL_RELOP);
 		}
-		if (otherPred.isRelationalOpPredicate()) // other is not "in"
+
+		if (otherPred.isRelationalOpPredicate() || // other is not "in" or
+			otherPred.isInListProbePredicate())    // other is a probe predicate
 		{
 			int	otherOperator = ((RelationalOperator)(otherPred.getAndNode().getLeftOperand())).getOperator();
 			otherIsEquals = (otherOperator == RelationalOperator.EQUALS_RELOP ||

Modified: db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InListMultiProbeTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InListMultiProbeTest.java?rev=574730&r1=574729&r2=574730&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InListMultiProbeTest.java
(original)
+++ db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InListMultiProbeTest.java
Tue Sep 11 16:38:56 2007
@@ -264,7 +264,7 @@
      * to do index multi-probing *and* there are multiple start/stop preds.
      * That is to say, there are predicates other than the probe predicate
      * that can be used as start and/or stop keys on the index, as well.
-     * DERBY-2470.
+     * DERBY-2470, DERBY-3061.
      */
     public void testMultipleStartStopPreds() throws Exception
     {
@@ -408,10 +408,80 @@
                 {"91","91   ","212398    "}
             }, st);
 
+        st.execute("drop table ct");
+
+        /* DERBY-3061: Slightly different scenario in which the
+         * less-than predicate was being chosen as the stop key
+         * while the probe predicate was being chosen as the start
+         * key.  That was leading to incorrect results.
+         */
+
+        st.execute("create table mytable (id int primary key)");
+        st.execute("insert into mytable (id) values " +
+            "0, 1, 2, 3, 4, 5, 6, 7, 8, 9");
+
+        st.execute("insert into mytable select id + 10 from mytable");
+        st.execute("insert into mytable select id + 20 from mytable");
+        st.execute("insert into mytable select id + 40 from mytable");
+        st.execute("insert into mytable select id + 100 from mytable");
+
+        // Sanity check: single less than predicate. Expect 80 rows.
+        JDBC.assertDrainResults(st.executeQuery(
+            "select mytable.id from mytable where mytable.id < 100"),
+            80);
+
+        // Sanity check: single IN predicate.
+        JDBC.assertUnorderedResultSet(st.executeQuery(
+            "select mytable.id from mytable where " +
+            "mytable.id in ( 2, 15, 19, 20, 21, 48, 49 )"),
+            new String [][] {
+                {"2"}, {"15"}, {"19"}, {"20"}, {"21"}, {"48"}, {"49"}
+            });
+
+        /* Now both predicates combined; check to make sure we're
+         * getting the correct results.
+         */
+        JDBC.assertUnorderedResultSet(st.executeQuery(
+            "select mytable.id from mytable where mytable.id < 100 " +
+            "and mytable.id in ( 2, 15, 19, 20, 21, 48, 49 )"),
+            new String [][] {
+                {"2"}, {"15"}, {"19"}, {"20"}, {"21"}, {"48"}, {"49"}
+            });
+
+        /* Same as previous query, but put the probe predicate first; this
+         * can affect sorting so we need to make sure things work in this
+         * case as well.
+         */
+        JDBC.assertUnorderedResultSet(st.executeQuery(
+            "select mytable.id from mytable where " +
+            "mytable.id in ( 2, 15, 19, 20, 21, 48, 49 ) " +
+            "and mytable.id < 100"),
+            new String [][] {
+                {"2"}, {"15"}, {"19"}, {"20"}, {"21"}, {"48"}, {"49"}
+            });
+
+        /* Similar to previous query but make the "other" predicate an
+         * equality predicate, as well.  In this case we end up choosing
+         * the "other" predicate for start/stop key instead of the probe
+         * predicate.  Make sure that we still get the correct results
+         * in that case...
+         */
+
+        JDBC.assertEmpty(st.executeQuery(
+            "select mytable.id from mytable where " +
+            "mytable.id in ( 2, 15, 19, 20, 21, 48, 49 ) " +
+            "and mytable.id = 100"));
+
+        JDBC.assertUnorderedResultSet(st.executeQuery(
+            "select mytable.id from mytable where " +
+            "mytable.id in ( 2, 15, 19, 20, 21, 48, 49 ) " +
+            "and mytable.id = 21"),
+            new String [][] {{"21"}});
+
         // Cleanup.
 
         st.execute(RUNTIME_STATS_OFF_QUERY);
-        st.execute("drop table ct");
+        st.execute("drop table mytable");
 
         ps.close();
         st.close();



Mime
View raw message