db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bpendle...@apache.org
Subject svn commit: r648722 - in /db/derby/code/branches/10.4/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 15:26:32 GMT
Author: bpendleton
Date: Wed Apr 16 08:26:28 2008
New Revision: 648722

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

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

Merged by svn merge -r 648491:648492 ../trunk/

Simple merge with no conflicts; no additional changes were necessary.


Modified:
    db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/execute/MultiProbeTableScanResultSet.java
    db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out
    db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InListMultiProbeTest.java

Modified: db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/execute/MultiProbeTableScanResultSet.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/execute/MultiProbeTableScanResultSet.java?rev=648722&r1=648721&r2=648722&view=diff
==============================================================================
--- db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/execute/MultiProbeTableScanResultSet.java
(original)
+++ db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/execute/MultiProbeTableScanResultSet.java
Wed Apr 16 08:26:28 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/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out?rev=648722&r1=648721&r2=648722&view=diff
==============================================================================
--- db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out
(original)
+++ db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/master/subqueryFlattening.out
Wed Apr 16 08:26:28 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/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InListMultiProbeTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InListMultiProbeTest.java?rev=648722&r1=648721&r2=648722&view=diff
==============================================================================
--- db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InListMultiProbeTest.java
(original)
+++ db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InListMultiProbeTest.java
Wed Apr 16 08:26:28 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