db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From abr...@apache.org
Subject svn commit: r617548 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/IndexToBaseRowNode.java testing/org/apache/derbyTesting/functionTests/tests/lang/InListMultiProbeTest.java testing/org/apache/derbyTesting/junit/JDBC.java
Date Fri, 01 Feb 2008 16:26:34 GMT
Author: abrown
Date: Fri Feb  1 08:26:32 2008
New Revision: 617548

URL: http://svn.apache.org/viewvc?rev=617548&view=rev
Log:
DERBY-3279: Add logic for execution-time sorting of IN list values
into DESCENDING order when required.

This is a follow-up patch to add an implementation of the
adjustForSortElimination(RequiredRowOrdering) to IndexToBaseRowNode,
which was missing from the previous commit (svn # 616126).  This
commit also adds more test cases to InListMultiProbeTest.java.

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/IndexToBaseRowNode.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InListMultiProbeTest.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/JDBC.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/IndexToBaseRowNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/IndexToBaseRowNode.java?rev=617548&r1=617547&r2=617548&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/IndexToBaseRowNode.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/IndexToBaseRowNode.java
Fri Feb  1 08:26:32 2008
@@ -26,6 +26,7 @@
 import org.apache.derby.iapi.sql.compile.AccessPath;
 import org.apache.derby.iapi.sql.compile.CostEstimate;
 import org.apache.derby.iapi.sql.compile.Optimizable;
+import org.apache.derby.iapi.sql.compile.RequiredRowOrdering;
 
 import org.apache.derby.iapi.sql.dictionary.ConglomerateDescriptor;
 
@@ -359,6 +360,23 @@
 		 * FBT being under a PRN, etc.
 		 */
 		source.disableBulkFetch();
+	}
+
+	/**
+	 * @see ResultSetNode#adjustForSortElimination
+	 */
+	void adjustForSortElimination(RequiredRowOrdering rowOrdering)
+		throws StandardException
+	{
+		/* rowOrdering is not important to this specific node, so
+		 * just call the no-arg version of the method.
+		 */
+		adjustForSortElimination();
+
+		/* Now pass the rowOrdering down to source, which may
+		 * need to do additional work. DERBY-3279.
+		 */
+		source.adjustForSortElimination(rowOrdering);
 	}
 
 	/** 

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=617548&r1=617547&r2=617548&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
Fri Feb  1 08:26:32 2008
@@ -28,6 +28,7 @@
 import java.sql.SQLException;
 
 import java.util.ArrayList;
+import java.util.BitSet;
 import java.util.Collections;
 import java.util.Comparator;
 import java.util.HashSet;
@@ -617,41 +618,62 @@
          * to the same column and thus do the correct sorting.
          */
 
+        BitSet colsToCheck = new BitSet(6);
+
+        colsToCheck.set(3);
+        colsToCheck.set(4);
+        colsToCheck.set(5);
+
         String [][] expRS3 =
             new String [][] {
-                {"AAAAA", "EDAM", "999.8888", "54321", "EDAM", "8.5646"},
-                {"54321", "EDAM", "8.5646", "54321", "EDAM", "8.5646"},
-                {"00000", "EDAM", "2.1111", "54321", "EDAM", "8.5646"},
-                {"AAAAA", "EDAM", "999.8888", "00000", "EDAM", "2.1111"},
-                {"54321", "EDAM", "8.5646", "00000", "EDAM", "2.1111"},
-                {"00000", "EDAM", "2.1111", "00000", "EDAM", "2.1111"}
+                {"54321", "EDAM", "8.5646"},
+                {"54321", "EDAM", "8.5646"},
+                {"54321", "EDAM", "8.5646"},
+                {"00000", "EDAM", "2.1111"},
+                {"00000", "EDAM", "2.1111"},
+                {"00000", "EDAM", "2.1111"}
             };
 
-        JDBC.assertFullResultSet(st.executeQuery(
+        /* We can't use assertFullResultSet because the query
+         * only enforces an ordering on the columns from "C2",
+         * which means that the rows in "C1" w.r.t. a given
+         * row in C2 might be in any order.  We don't want to
+         * use assertUnorderedResultSet() because there _is_
+         * a required ordering of the rows--it's just a required
+         * ordering on a _subset_ of the columns in the result
+         * set.  So we use assertPartialResultSet() to check
+         * that the rows are correctly sorted w.r.t. the ORDER
+         * BY columns, but we don't bother checking the other
+         * (non-ORDER BY) columns.
+         */
+         
+        JDBC.assertPartialResultSet(st.executeQuery(
             "SELECT * FROM CHEESE C1, CHEESE C2 " +
             "WHERE C1.CHEESE_NAME = C2.CHEESE_NAME AND " +
             "(C2.CHEESE_CODE='00000' OR C2.CHEESE_CODE='54321') " +
             "AND C1.CHEESE_NAME='EDAM' ORDER BY 4 DESC, 5 DESC, 6 DESC"),
-            expRS3);
+            expRS3,
+            colsToCheck);
 
         // Same as previous query but with ASC in the ORDER BY.
 
         String [][] expRS4 =
             new String [][] {
-                {"00000", "EDAM", "2.1111", "00000", "EDAM", "2.1111"},
-                {"54321", "EDAM", "8.5646", "00000", "EDAM", "2.1111"},
-                {"AAAAA", "EDAM", "999.8888", "00000", "EDAM", "2.1111"},
-                {"00000", "EDAM", "2.1111", "54321", "EDAM", "8.5646"},
-                {"54321", "EDAM", "8.5646", "54321", "EDAM", "8.5646"},
-                {"AAAAA", "EDAM", "999.8888", "54321", "EDAM", "8.5646"}
+                {"00000", "EDAM", "2.1111"},
+                {"00000", "EDAM", "2.1111"},
+                {"00000", "EDAM", "2.1111"},
+                {"54321", "EDAM", "8.5646"},
+                {"54321", "EDAM", "8.5646"},
+                {"54321", "EDAM", "8.5646"}
             };
 
-        JDBC.assertFullResultSet(st.executeQuery(
+        JDBC.assertPartialResultSet(st.executeQuery(
             "SELECT * FROM CHEESE C1, CHEESE C2 " +
             "WHERE C1.CHEESE_NAME = C2.CHEESE_NAME AND " +
             "(C2.CHEESE_CODE='00000' OR C2.CHEESE_CODE='54321') " +
             "AND C1.CHEESE_NAME='EDAM' ORDER BY 4 ASC, 5 DESC, 6 DESC"),
-            expRS4);
+            expRS4,
+            colsToCheck);
 
         /* Repeat the tests with parameter markers instead of literals,
          * and explicit IN lists instead of an OR clause that would
@@ -696,7 +718,7 @@
 
         ps.setString(1, "00000");
         ps.setString(2, "54321");
-        JDBC.assertFullResultSet(ps.executeQuery(), expRS3);
+        JDBC.assertPartialResultSet(ps.executeQuery(), expRS3, colsToCheck);
 
         // Same as previous query but with ASC in the ORDER BY.
 
@@ -707,7 +729,150 @@
 
         ps.setString(1, "00000");
         ps.setString(2, "54321");
-        JDBC.assertFullResultSet(ps.executeQuery(), expRS4);
+        JDBC.assertPartialResultSet(ps.executeQuery(), expRS4, colsToCheck);
+
+        /* Now do the same tests yet again, but remove CHEESE_COST from
+         * the index (and from the ORDER BY).  Since the index now
+         * has a subset of the columns in the base table, we'll
+         * generate an IndexToBaseRowNode above the base table.
+         * We want to make sure that the correct sorting information
+         * is passed from the IndexToBaseRowNode down to the base
+         * table in that case...
+         */
+
+        st.execute("drop index cheese_index");
+        st.execute("create index cheese_index on CHEESE " +
+            "(CHEESE_CODE DESC, CHEESE_NAME DESC)");
+
+        /* ORDER BY is DESC, so we'll eliminate the ORDER BY sort for
+         * this query.  Results should still come back in descending
+         * order, though.
+         */
+        JDBC.assertFullResultSet(st.executeQuery(
+            "SELECT * FROM CHEESE " +
+            "WHERE (CHEESE_CODE='54321' OR CHEESE_CODE='00000' " +
+            "OR CHEESE_CODE='AAAAA') AND CHEESE_NAME='EDAM' " +
+            "ORDER BY CHEESE_CODE DESC, CHEESE_NAME DESC"),
+            expRS1);
+
+        /* ORDER BY is ASC so we will not eliminate the sort; make
+         * sure the rows are still correctly ordered.
+         */
+        JDBC.assertFullResultSet(st.executeQuery(
+            "SELECT * FROM CHEESE " +
+            "WHERE (CHEESE_CODE='54321' OR CHEESE_CODE='00000' " +
+            "OR CHEESE_CODE='AAAAA') AND CHEESE_NAME='EDAM' " +
+            "ORDER BY CHEESE_CODE ASC, CHEESE_NAME DESC"),
+            expRS2);
+
+        /* Simple join where the ORDER BY is based on position in
+         * the RCL and the probe predicate is w.r.t. to the second
+         * table in the FROM list.
+         */
+        JDBC.assertPartialResultSet(st.executeQuery(
+            "SELECT * FROM CHEESE C1, CHEESE C2 " +
+            "WHERE C1.CHEESE_NAME = C2.CHEESE_NAME AND " +
+            "(C2.CHEESE_CODE='00000' OR C2.CHEESE_CODE='54321') " +
+            "AND C1.CHEESE_NAME='EDAM' ORDER BY 4 DESC, 5 DESC"),
+            expRS3,
+            colsToCheck);
+
+        // Same as previous query but with ASC in the ORDER BY.
+        JDBC.assertPartialResultSet(st.executeQuery(
+            "SELECT * FROM CHEESE C1, CHEESE C2 " +
+            "WHERE C1.CHEESE_NAME = C2.CHEESE_NAME AND " +
+            "(C2.CHEESE_CODE='00000' OR C2.CHEESE_CODE='54321') " +
+            "AND C1.CHEESE_NAME='EDAM' ORDER BY 4 ASC, 5 DESC"),
+            expRS4,
+            colsToCheck);
+
+        /* Run a few queries with multiple IN lists in it (the OR
+         * clauses here will be transformed to IN lists during
+         * preprocessing). In this case we should only do multi-
+         * probing for the IN list that's on CHEESE_CODE; we
+         * shouldn't do it for CHEESE_NAME because CHEESE_NAME
+         * is not the first column in the index and thus is not
+         * eligible for IN list multi-probing.
+         */
+
+        JDBC.assertFullResultSet(st.executeQuery(
+            "SELECT * FROM CHEESE WHERE " +
+            "(CHEESE_CODE='00000' OR CHEESE_CODE='54321') " +
+            "AND (CHEESE_NAME='EDAM' OR CHEESE_NAME='ADAM') " +
+            "ORDER BY CHEESE_CODE DESC, CHEESE_NAME DESC"),
+            new String [][] {
+                {"54321","EDAM","8.5646"},
+                {"00000","EDAM","2.1111"}
+            });
+
+        JDBC.assertFullResultSet(st.executeQuery(
+            "SELECT * FROM CHEESE WHERE " +
+            "(CHEESE_CODE='00000' OR CHEESE_CODE='54321') " +
+            "AND (CHEESE_NAME='EDAM' OR CHEESE_NAME='ADAM') " +
+            "ORDER BY CHEESE_CODE ASC, CHEESE_NAME DESC"),
+            new String [][] {
+                {"00000","EDAM","2.1111"},
+                {"54321","EDAM","8.5646"}
+            });
+
+        /* Multiple IN lists on the same column get AND-ed
+         * together.  Only one of them can be used for multi-
+         * probing, the other has to be treated as a non-probing
+         * IN list (because we only multi-probe with start/stop
+         * predicates, and there can only be one start/stop
+         * predicate per column).
+         */
+
+        JDBC.assertFullResultSet(st.executeQuery(
+            "SELECT * FROM CHEESE WHERE " +
+            "(CHEESE_CODE='00000' OR CHEESE_CODE='54321') " +
+            "AND (CHEESE_CODE='AAAAA' OR CHEESE_CODE='00000') " +
+            "ORDER BY CHEESE_CODE DESC, CHEESE_NAME DESC"),
+            new String [][] {
+                {"00000","GOUDA","1.1234"},
+                {"00000","EDAM","2.1111"}
+            });
+
+        JDBC.assertFullResultSet(st.executeQuery(
+            "SELECT * FROM CHEESE WHERE " +
+            "(CHEESE_CODE='00000' OR CHEESE_CODE='54321') " +
+            "AND (CHEESE_CODE='AAAAA' OR CHEESE_CODE='00000') " +
+            "ORDER BY CHEESE_CODE ASC, CHEESE_NAME ASC"),
+            new String [][] {
+                {"00000","EDAM","2.1111"},
+                {"00000","GOUDA","1.1234"}
+            });
+
+        /* Multiple IN lists on the same column get OR-ed
+         * together.  They will be combined into a single
+         * IN list for which we will then do multi-probing.
+         */
+
+        JDBC.assertFullResultSet(st.executeQuery(
+            "SELECT * FROM CHEESE WHERE " +
+            "(CHEESE_CODE='00000' OR CHEESE_CODE='54321') " +
+            "OR (CHEESE_CODE='AAAAA' OR CHEESE_CODE='00000') " +
+            "ORDER BY CHEESE_CODE DESC, CHEESE_NAME DESC"),
+            new String [][] {
+                {"AAAAA","EDAM","999.8888"},
+                {"54321","MUENSTER","77.9545"},
+                {"54321","EDAM","8.5646"},
+                {"00000","GOUDA","1.1234"},
+                {"00000","EDAM","2.1111"}
+            });
+
+        JDBC.assertFullResultSet(st.executeQuery(
+            "SELECT * FROM CHEESE WHERE " +
+            "(CHEESE_CODE='00000' OR CHEESE_CODE='54321') " +
+            "OR (CHEESE_CODE='AAAAA' OR CHEESE_CODE='00000') " +
+            "ORDER BY CHEESE_CODE ASC, CHEESE_NAME ASC"),
+            new String [][] {
+                {"00000","EDAM","2.1111"},
+                {"00000","GOUDA","1.1234"},
+                {"54321","EDAM","8.5646"},
+                {"54321","MUENSTER","77.9545"},
+                {"AAAAA","EDAM","999.8888"}
+            });
 
         ps.close();
         st.execute("drop table cheese");

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/JDBC.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/JDBC.java?rev=617548&r1=617547&r2=617548&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/JDBC.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/JDBC.java Fri Feb  1 08:26:32
2008
@@ -23,6 +23,7 @@
 import java.sql.*;
 import java.util.ArrayList;
 import java.util.Arrays;
+import java.util.BitSet;
 import java.util.Iterator;
 import java.util.ListIterator;
 
@@ -885,6 +886,69 @@
     }
 
     /**
+     * Similar to assertFullResultSet(...) above, except that this
+     * method takes a BitSet and checks the received expectedRows
+     * against the columns referenced by the BitSet.  So the assumption
+     * here is that expectedRows will only have as many columns as
+     * there are "true" bits in the received BitSet.
+     *
+     * This method is useful when we expect there to be a specific
+     * ordering on some column OC in the result set, but do not care
+     * about the ordering of the non-OC columns when OC is the
+     * same across rows.  Ex.  If we have the following results with
+     * an expected ordering on column J:
+     *
+     *   I    J
+     *   -    -
+     *   a    1
+     *   b    1
+     *   c    2
+     *   c    2
+     *
+     * Then this method allows us to verify that J is sorted as
+     * "1, 1, 2, 2" without having to worry about whether or not
+     * (a,1) comes before (b,1).  The caller would simply pass in
+     * a BitSet whose content was {1} and an expectedRows array
+     * of {{"1"},{"1"},{"2"},{"2"}}.
+     *
+     * For now this method always does comparisons with
+     * "asTrimmedStrings" set to true, and always closes
+     * the result set.
+     */
+    public static void assertPartialResultSet(ResultSet rs,
+        Object [][] expectedRows, BitSet colsToCheck)
+        throws SQLException
+    {
+        int rows;
+
+        // Assert that we have the right number of columns. If we expect an
+        // empty result set, the expected column count is unknown, so don't
+        // check.
+        if (expectedRows.length > 0) {
+            Assert.assertEquals("Unexpected column count:",
+                expectedRows[0].length, colsToCheck.cardinality());
+        }
+
+        for (rows = 0; rs.next(); rows++)
+        {
+            /* If we have more actual rows than expected rows, don't
+             * try to assert the row.  Instead just keep iterating
+             * to see exactly how many rows the actual result set has.
+             */
+            if (rows < expectedRows.length)
+            {
+                assertRowInResultSet(rs, rows + 1,
+                    expectedRows[rows], true, colsToCheck);
+            }
+        }
+
+        rs.close();
+
+        // And finally, assert the row count.
+        Assert.assertEquals("Unexpected row count:", expectedRows.length, rows);
+    }
+
+    /**
      * Assert that every column in the current row of the received
      * result set matches the corresponding column in the received
      * array.  This means that the order of the columns in the result
@@ -922,9 +986,39 @@
     private static void assertRowInResultSet(ResultSet rs, int rowNum,
         Object [] expectedRow, boolean asTrimmedStrings) throws SQLException
     {
+        assertRowInResultSet(
+            rs, rowNum, expectedRow, asTrimmedStrings, (BitSet)null);
+    }
+
+    /**
+     * See assertRowInResultSet(...) above.
+     *
+     * @param BitSet colsToCheck If non-null then for every bit b
+     *   that is set in colsToCheck, we'll compare the (b+1)-th column
+     *   of the received result set's current row to the i-th column
+     *   of expectedRow, where 0 <= i < # bits set in colsToCheck.
+     *   So if colsToCheck is { 0, 3 } then expectedRow should have
+     *   two objects and we'll check that:
+     *
+     *     expectedRow[0].equals(rs.getXXX(1));
+     *     expectedRow[1].equals(rs.getXXX(4));
+     *
+     *   If colsToCheck is null then the (i+1)-th column in the
+     *   result set is compared to the i-th column in expectedRow,
+     *   where 0 <= i < expectedRow.length.
+     */
+    private static void assertRowInResultSet(ResultSet rs,
+        int rowNum, Object [] expectedRow, boolean asTrimmedStrings,
+        BitSet colsToCheck) throws SQLException
+    {
+        int cPos = 0;
         ResultSetMetaData rsmd = rs.getMetaData();
         for (int i = 0; i < expectedRow.length; i++)
         {
+            cPos = (colsToCheck == null)
+                ? (i+1)
+                : colsToCheck.nextSetBit(cPos) + 1;
+
             Object obj;
             if (asTrimmedStrings)
             {
@@ -942,18 +1036,18 @@
                  * column is intended to be a mock boolean column.
                  */
                 if ((expectedRow[i] != null)
-                    && (rsmd.getColumnType(i+1) == Types.SMALLINT))
+                    && (rsmd.getColumnType(cPos) == Types.SMALLINT))
                 {
                     String s = expectedRow[i].toString();
                     if (s.equals("true") || s.equals("false"))
-                        obj = (rs.getShort(i+1) == 0) ? "false" : "true";
+                        obj = (rs.getShort(cPos) == 0) ? "false" : "true";
                     else
-                        obj = rs.getString(i+1);
+                        obj = rs.getString(cPos);
                         
                 }
                 else
                 {
-                    obj = rs.getString(i+1);
+                    obj = rs.getString(cPos);
 
                 }
                 
@@ -963,7 +1057,7 @@
 
             }
             else
-                obj = rs.getObject(i+1);
+                obj = rs.getObject(cPos);
 
             boolean ok = (rs.wasNull() && (expectedRow[i] == null))
                 || (!rs.wasNull()
@@ -981,13 +1075,13 @@
                     found = bytesToString((byte[] )obj);
                 }
                 Assert.fail("Column value mismatch @ column '" +
-                    rsmd.getColumnName(i+1) + "', row " + rowNum +
+                    rsmd.getColumnName(cPos) + "', row " + rowNum +
                     ":\n    Expected: >" + expected +
                     "<\n    Found:    >" + found + "<");
             }
             
             if (rs.wasNull())
-                assertResultColumnNullable(rsmd, i+1);
+                assertResultColumnNullable(rsmd, cPos);
 
         }
     }



Mime
View raw message