db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bpendle...@apache.org
Subject svn commit: r648492 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/execute/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Wed, 16 Apr 2008 03:40:33 GMT
Author: bpendleton
Date: Tue Apr 15 20:40:30 2008
New Revision: 648492

URL: http://svn.apache.org/viewvc?rev=648492&view=rev
Log:
DERBY-3603: 'IN' clause ignores valid results.

Patch contributed by A B (qozinx at gmail dot com)

Some queries using multi-valued IN clauses were not returning the right
results. An example of a query which was processed incorrectly is:

    select count(*) FROM spike.accounts account, spike.admin_units admin_unit,
        spike.bookings booking
    WHERE booking.child_id = 2 AND
        admin_unit.admin_unit_id IN (1,21) AND
        booking.booking_date_time_out >= 20080331000000 AND
        booking.booking_date_time_in <= 20080406235900 AND
        account.account_id = booking.account_id AND
        admin_unit.admin_unit_id = account.admin_unit_id; 

The issue involves the behavior of MultiProbeTableScanResultSet when it
goes to re-open the scan; under certain circumstances, it was failing to
reset the probing state, and so was performing the probing incorrectly,
using only partial portions of the IN list values. For example, in the
above query, there were certain rows which were only tested against the
value "admin_unit_id = 21"; the "admin_unit_id = 1" case was skipped.

MultiProbeTableScanResultSet.reopenCore() was using a heuristic test to
distinguish between the two cases of:

   * A - The first is for join processing. In this case we have
   * a(nother) row from some outer table and we want to reopen this
   * scan to look for rows matching the new outer row.
   *
   * B - The second is for multi-probing. Here we want to reopen
   * the scan on this table to look for rows matching the next value
   * in the probe list.

The patch modifies this code so that the caller passes in a boolean flag
to specify which case is occurring, which avoids the problem thinking that
it was in case "B" when in fact it was actually in case "A".
  

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/MultiProbeTableScanResultSet.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out
    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/execute/MultiProbeTableScanResultSet.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/MultiProbeTableScanResultSet.java?rev=648492&r1=648491&r2=648492&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/MultiProbeTableScanResultSet.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/MultiProbeTableScanResultSet.java
Tue Apr 15 20:40:30 2008
@@ -242,34 +242,50 @@
      */
     public void reopenCore() throws StandardException
     {
-        /* There are two scenarios for which we reopen this kind of scan:
-         *
-         *   A - The first is for join processing.  In this case we have
-         * a(nother) row from some outer table and we want to reopen this
-         * scan to look for rows matching the new outer row.
-         *
-         *   B - The second is for multi-probing.  Here we want to reopen
-         * the scan on this table to look for rows matching the next value
-         * in the probe list.
-         *
-         * If we are reopening the scan for scenario A (join processing)
-         * then we need to reset our position within the probe list. 
-         * If we are reopening the scan for scenario B then we do *not*
-         * want to reset our position within the probe list because that
-         * position tells us where to find the next probe value.
-         *
-         * The way we tell the difference between the two scenarios is
-         * by looking at our current position in the probe list (i.e. the
-         * value of probeValIndex): if our current position is beyond the
-         * length of the probe list then we know that we are reopening the
-         * scan for scenario A.  Or put another away, we should never get
-         * here for scenario B if probeValIndex is greater than or equal
-         * to the length of the probe list.  The reason is that the call
-         * to reopenCore() for scenario B will only ever happen when
-         * moreInListVals() returns true--and in that case we know that
-         * probeValIndex will be less than the length of the probeValues.
-         */
-        if (probeValIndex >= probeValues.length)
+        reopenCore(false);
+    }
+
+    /**
+     * There are two scenarios for which we reopen this kind of scan:
+     *
+     *   A - The first is for join processing.  In this case we have
+     * a(nother) row from some outer table and we want to reopen this
+     * scan to look for rows matching the new outer row.
+     *
+     *   B - The second is for multi-probing.  Here we want to reopen
+     * the scan on this table to look for rows matching the next value
+     * in the probe list.
+     *
+     * If we are reopening the scan for scenario A (join processing)
+     * then we need to reset our position within the probe list. 
+     * If we are reopening the scan for scenario B then we do *not*
+     * want to reset our position within the probe list because that
+     * position tells us where to find the next probe value.
+     *
+     * That said, this method does the work of reopenCore() using
+     * the received boolean to determine which of the two scenarios
+     * we are in.  Note that if our current position (i.e. the value
+     * of probeValIndex) is beyond the length of the probe list then
+     * we know that we are reopening the scan for scenario A.  Or put
+     * another away, we should never get here for scenario B if
+     * probeValIndex is greater than or equal to the length of the
+     * probe list.  The reason is that the call to reopenCore() for
+     * scenario B will only ever happen when moreInListVals() returns
+     * true--and in that case we know that probeValIndex will be less
+     * than the length of the probeValues.  But the opposite is not
+     * true: i.e. it is *not* safe to say that a probeValIndex which
+     * is less than the length of probe list is always for scenario
+     * B.  That's not true because it's possible that the join to
+     * which this scan belongs is a "oneRowRightSide" join, meaning
+     * that this, the "right" side scan, will be "interrupted" after
+     * we return a single row for the current outer row.  If we then
+     * come back with a new outer row we need to reset our position--
+     * even though probeValIndex will be less than probeValues.length
+     * in that case.  DERBY-3603.
+     */
+    private void reopenCore(boolean forNextProbe) throws StandardException
+    {
+        if (!forNextProbe)
             probeValIndex = 0;
 
         super.reopenCore();
@@ -346,7 +362,7 @@
              * figure out what the next probe value should be (and thus
              * where to position the scan).
              */
-            reopenCore();
+            reopenCore(true);
             result = super.getNextRowCore();
         }
 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out?rev=648492&r1=648491&r2=648492&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out
Tue Apr 15 20:40:30 2008
@@ -4301,8 +4301,8 @@
 			Right result set:
 				Project-Restrict ResultSet (6):
 				Number of opens = 6
-				Rows seen = 7
-				Rows filtered = 1
+				Rows seen = 6
+				Rows filtered = 0
 				restriction = true
 				projection = true
 					constructor time (milliseconds) = 0
@@ -4314,7 +4314,7 @@
 				Source result set:
 					Index Row to Base Row ResultSet for COLLS:
 					Number of opens = 6
-					Rows seen = 7
+					Rows seen = 6
 					Columns accessed from heap = {0}
 						constructor time (milliseconds) = 0
 						open time (milliseconds) = 0
@@ -4322,7 +4322,7 @@
 						close time (milliseconds) = 0
 						Index Scan ResultSet for COLLS using index NEW_INDEX3 at serializable isolation level
using share row locking chosen by the optimizer
 						Number of opens = 6
-						Rows seen = 7
+						Rows seen = 6
 						Rows filtered = 0
 						Fetch Size = 1
 							constructor time (milliseconds) = 0
@@ -4335,8 +4335,8 @@
 							Number of columns fetched=2
 							Number of deleted rows visited=0
 							Number of pages visited=6
-							Number of rows qualified=7
-							Number of rows visited=7
+							Number of rows qualified=6
+							Number of rows visited=6
 							Scan type=btree
 							Tree height=1
 							start position: 
@@ -4445,8 +4445,8 @@
 			Right result set:
 				Project-Restrict ResultSet (6):
 				Number of opens = 6
-				Rows seen = 7
-				Rows filtered = 1
+				Rows seen = 6
+				Rows filtered = 0
 				restriction = true
 				projection = true
 					constructor time (milliseconds) = 0
@@ -4458,7 +4458,7 @@
 				Source result set:
 					Index Row to Base Row ResultSet for COLLS:
 					Number of opens = 6
-					Rows seen = 7
+					Rows seen = 6
 					Columns accessed from heap = {0}
 						constructor time (milliseconds) = 0
 						open time (milliseconds) = 0
@@ -4466,7 +4466,7 @@
 						close time (milliseconds) = 0
 						Index Scan ResultSet for COLLS using index NEW_INDEX3 at serializable isolation level
using share row locking chosen by the optimizer
 						Number of opens = 6
-						Rows seen = 7
+						Rows seen = 6
 						Rows filtered = 0
 						Fetch Size = 1
 							constructor time (milliseconds) = 0
@@ -4479,8 +4479,8 @@
 							Number of columns fetched=2
 							Number of deleted rows visited=0
 							Number of pages visited=6
-							Number of rows qualified=7
-							Number of rows visited=7
+							Number of rows qualified=6
+							Number of rows visited=6
 							Scan type=btree
 							Tree height=1
 							start position: 
@@ -6139,8 +6139,8 @@
 			Right result set:
 				Project-Restrict ResultSet (6):
 				Number of opens = 7
-				Rows seen = 19
-				Rows filtered = 15
+				Rows seen = 21
+				Rows filtered = 17
 				restriction = true
 				projection = true
 					constructor time (milliseconds) = 0
@@ -6152,15 +6152,15 @@
 				Source result set:
 					Index Row to Base Row ResultSet for COLLS:
 					Number of opens = 7
-					Rows seen = 19
+					Rows seen = 21
 					Columns accessed from heap = {0}
 						constructor time (milliseconds) = 0
 						open time (milliseconds) = 0
 						next time (milliseconds) = 0
 						close time (milliseconds) = 0
 						Index Scan ResultSet for COLLS using index NEW_INDEX3 at serializable isolation level
using share row locking chosen by the optimizer
-						Number of opens = 10
-						Rows seen = 19
+						Number of opens = 11
+						Rows seen = 21
 						Rows filtered = 0
 						Fetch Size = 1
 							constructor time (milliseconds) = 0
@@ -6172,9 +6172,9 @@
 							Bit set of columns fetched=All
 							Number of columns fetched=2
 							Number of deleted rows visited=0
-							Number of pages visited=10
-							Number of rows qualified=19
-							Number of rows visited=25
+							Number of pages visited=11
+							Number of rows qualified=21
+							Number of rows visited=28
 							Scan type=btree
 							Tree height=1
 							start position: 

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=648492&r1=648491&r2=648492&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 Apr 15 20:40:30 2008
@@ -208,9 +208,23 @@
                     " ADD CONSTRAINT " + DATA_TABLE + "_PK " +
                     "PRIMARY KEY (ID)";
                 s.executeUpdate(ddl);
+                
+                for (int i = 0; i < DERBY_3603_Objects.length; i++)
+                    s.executeUpdate(DERBY_3603_Objects[i]);
             }
         };
     }
+    private static String []DERBY_3603_Objects = {
+        "create table d3603_a (a_id integer, c_id integer)",
+        "create table d3603_c (c_id integer not null, primary key(c_id)," +
+            " d_id integer, t_o bigint, t_i bigint)",
+        "insert into d3603_a (a_id, c_id) values (1, 1)",
+        "insert into d3603_a (a_id, c_id) values (2, 2)",
+        "insert into d3603_a (a_id, c_id) values (3, 1)",
+        "insert into d3603_c (c_id, d_id, t_o, t_i) values (1, 1, 1, 1)",
+        "insert into d3603_c (c_id, d_id, t_o, t_i) values (2, 2, 1, 1)",
+        "insert into d3603_c (c_id, d_id, t_o, t_i) values (21, 1, 1, 1)",
+    };
 
     /**
      * Executes three different types of queries ("strategies") repeatedly
@@ -877,6 +891,37 @@
         ps.close();
         st.execute("drop table cheese");
         st.close();
+    }
+
+    public void testDerby3603()
+        throws SQLException
+    {
+        Statement s = createStatement();
+
+        JDBC.assertFullResultSet(s.executeQuery(
+                    "select count(*) from d3603_a, d3603_c " +
+                    "   where d3603_a.a_id <> 2 and d3603_c.c_id in (1, 21)"+
+                    "         and d3603_a.c_id = d3603_c.c_id"),
+                new String[][] {
+                    {"2"}
+                });
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                    "select d3603_a.a_id from d3603_a, d3603_c " +
+                    "   where d3603_a.a_id <> 2 and d3603_c.c_id in (1, 21)"+
+                    "         and d3603_a.c_id = d3603_c.c_id"),
+                new String[][] {
+                    {"1"},
+                    {"3"}
+                });
+        JDBC.assertUnorderedResultSet(s.executeQuery(
+                    "select d3603_a.a_id,d3603_c.d_id " +
+                    "       from d3603_a, d3603_c " +
+                    "   where d3603_a.a_id <> 2 and d3603_c.c_id in (1, 21)" +
+                    "         and d3603_a.c_id = d3603_c.c_id"),
+                new String[][] {
+                    {"1","1"},
+                    {"3","1"}
+                    });
     }
 
     /**



Mime
View raw message