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 ab...@apache.org on 2007/09/11 18:12:00 UTC
svn commit: r574635 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/compile/Predicate.java
testing/org/apache/derbyTesting/functionTests/tests/lang/InListMultiProbeTest.java
Author: abrown
Date: Tue Sep 11 09:11:59 2007
New Revision: 574635
URL: http://svn.apache.org/viewvc?rev=574635&view=rev
Log:
DERBY-3061: Fix wrong results regression caused by incorrect sorting of
"probe predicates" prior to code generation.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/Predicate.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InListMultiProbeTest.java
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/Predicate.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/Predicate.java?rev=574635&r1=574634&r2=574635&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/Predicate.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/Predicate.java Tue Sep 11 09:11:59 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/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InListMultiProbeTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InListMultiProbeTest.java?rev=574635&r1=574634&r2=574635&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InListMultiProbeTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InListMultiProbeTest.java Tue Sep 11 09:11:59 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();