db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From abr...@apache.org
Subject svn commit: r547066 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Thu, 14 Jun 2007 00:10:39 GMT
Author: abrown
Date: Wed Jun 13 17:10:38 2007
New Revision: 547066

URL: http://svn.apache.org/viewvc?view=rev&rev=547066
Log:
DERBY-2805: Fix FromVTI to not throw an ASSERT when sort elimination occurs.
In particular this patch does the following:

  1 - Renames the "markOrderingDependent()" method and related variables to
      reflect their use, which is to indicate that the optimizer has eliminated
      a sort and thus that the underlying result sets *may* need to make
      adjustments to compensate for the dropped sort.  At the moment the only
      result set node which needs to make such an adjustment is
      IndexRowToBaseRowNode.

  2 - Updates comments where appropriate to more explicitly describe the
      intended use of the "adjustForSortElimination()" method (which is
      what "markOrderingDependent()" was renamed to).

  3 - Adds a void implementation of "adjustForSortElimination()" to the
      FromVTI class since that class doesn't need to make any adjustments.
      This void method is what solves the failure reported in DERBY-2805.

  4 - Adds appropriate test cases to a new fixture in the existing
      lang/SysDiagVTIMappingTest JUnit test.

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromVTI.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/IndexToBaseRowNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowResultSetNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SingleChildResultSetNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableOperatorNode.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SysDiagVTIMappingTest.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java?view=diff&rev=547066&r1=547065&r2=547066
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java Wed
Jun 13 17:10:38 2007
@@ -4149,11 +4149,9 @@
 
 
 	/**
-	 * Notify the underlying result set tree that the result is
-	 * ordering dependent.  (For example, no bulk fetch on an index
-	 * if under an IndexRowToBaseRow.)
+	 * @see ResultSetNode#adjustForSortElimination
 	 */
-	void markOrderingDependent()
+	void adjustForSortElimination()
 	{
 		/* NOTE: IRTBR will use a different method to tell us that
 		 * it cannot do a bulk fetch as the ordering issues are

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromVTI.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromVTI.java?view=diff&rev=547066&r1=547065&r2=547066
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromVTI.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromVTI.java Wed Jun
13 17:10:38 2007
@@ -311,6 +311,27 @@
 	}
 
 	/**
+	 * @see ResultSetNode#adjustForSortElimination()
+	 */
+	public void adjustForSortElimination()
+	{
+		/* It's possible that we have an ORDER BY on the columns for this
+		 * VTI but that the sort was eliminated during preprocessing (see
+		 * esp. SelectNode.preprocess()).  Take as an example the following
+		 * query:
+		 *
+		 *   select distinct * from
+		 *      table(syscs_diag.space_table('T1')) X order by 3
+		 *
+		 * For this query we will merge the ORDER BY and the DISTINCT and
+		 * thereby eliminate the sort for the ORDER BY.  As a result we
+		 * will end up here--but we don't need to do anything special to
+		 * return VTI rows in the correct order, so this method is a no-op.
+		 * DERBY-2805.
+		 */
+	}
+
+	/**
 	 * @see Optimizable#modifyAccessPath
 	 *
 	 * @exception StandardException		Thrown on error

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?view=diff&rev=547066&r1=547065&r2=547066
==============================================================================
--- 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
Wed Jun 13 17:10:38 2007
@@ -349,11 +349,9 @@
 	}
 
 	/**
-	 * Notify the underlying result set tree that the result is
-	 * ordering dependent.  (For example, no bulk fetch on an index
-	 * if under an IndexRowToBaseRow.)
+	 * @see ResultSetNode#adjustForSortElimination
 	 */
-	void markOrderingDependent()
+	void adjustForSortElimination()
 	{
 		/* NOTE: We use a different method to tell a FBT that
 		 * it cannot do a bulk fetch as the ordering issues are

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java?view=diff&rev=547066&r1=547065&r2=547066
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java Wed
Jun 13 17:10:38 2007
@@ -1870,16 +1870,38 @@
 	}
 
 	/**
-	 * Notify the underlying result set tree that the result is
-	 * ordering dependent.  (For example, no bulk fetch on an index
-	 * if under an IndexRowToBaseRow.)
+	 * Notify the underlying result set tree that the optimizer has chosen
+	 * to "eliminate" a sort.  Sort elimination can happen as part of
+	 * preprocessing (see esp. SelectNode.preprocess(...)) or it can happen
+	 * if the optimizer chooses an access path that inherently returns the
+	 * rows in the correct order (also known as a "sort avoidance" plan).
+	 * In either case we drop the sort and rely on the underlying result set
+	 * tree to return its rows in the correct order.
+	 *
+	 * For most types of ResultSetNodes we automatically get the rows in the
+	 * correct order if the sort was eliminated. One exception to this rule,
+	 * though, is the case of an IndexRowToBaseRowNode, for which we have
+	 * to disable bulk fetching on the underlying base table.  Otherwise
+	 * the index scan could return rows out of order if the base table is
+	 * updated while the scan is "in progress" (i.e. while the result set
+	 * is open).
+	 *
+	 * In order to account for this (and potentially other, similar issues
+	 * in the future) this method exists to notify the result set node that
+	 * it is expected to return rows in the correct order.  The result set
+	 * can then take necessary action to satsify this requirement--such as
+	 * disabling bulk fetch in the case of IndexRowToBaseRowNode.
+	 *
+	 * All of that said, any ResultSetNodes for which we could potentially
+	 * eliminate sorts should override this method accordingly.  So we don't
+	 * ever expect to get here.
 	 */
-	void markOrderingDependent()
+	void adjustForSortElimination()
 	{
 		if (SanityManager.DEBUG)
 		{
 			SanityManager.THROWASSERT(
-				"markOrderingDependent() not expected to be called for " +
+				"adjustForSortElimination() not expected to be called for " +
 				getClass().getName());
 		}
 	}

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowResultSetNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowResultSetNode.java?view=diff&rev=547066&r1=547065&r2=547066
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowResultSetNode.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/RowResultSetNode.java
Wed Jun 13 17:10:38 2007
@@ -724,11 +724,9 @@
 	}
 
 	/**
-	 * Notify the underlying result set tree that the result is
-	 * ordering dependent.  (For example, no bulk fetch on an index
-	 * if under an IndexRowToBaseRow.)
+	 * @see ResultSetNode#adjustForSortElimination
 	 */
-	void markOrderingDependent()
+	void adjustForSortElimination()
 	{
 	}
 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java?view=diff&rev=547066&r1=547065&r2=547066
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java Wed
Jun 13 17:10:38 2007
@@ -1206,7 +1206,7 @@
 	public ResultSetNode genProjectRestrict(int origFromListSize)
 				throws StandardException
 	{
-		boolean				orderingDependent = false;
+		boolean				eliminateSort = false;
 		PredicateList		restrictionList;
 		ResultColumnList	prRCList;
 		ResultSetNode		prnRSN;
@@ -1255,8 +1255,8 @@
 			groupByList = null;
 			prnRSN  = gbn.getParent();
 
-			// Remember if the result is dependent on the ordering
-			orderingDependent = orderingDependent || gbn.getIsInSortedOrder();
+			// Remember whether or not we can eliminate the sort.
+			eliminateSort = eliminateSort || gbn.getIsInSortedOrder();
 		}
 
 		// if it is distinct, that must also be taken care of.
@@ -1322,8 +1322,8 @@
 											getContextManager());
 				prnRSN.costEstimate = costEstimate.cloneMe();
 
-				// Remember if the result is dependent on the ordering
-				orderingDependent = orderingDependent || inSortedOrder;
+				// Remember whether or not we can eliminate the sort.
+				eliminateSort = eliminateSort || inSortedOrder;
 			}
 		}
 
@@ -1395,18 +1395,18 @@
 
 		if (!(orderByList != null && orderByList.getSortNeeded()) && orderByQuery)
 		{
-			// Remember if the result is dependent on the ordering
-			orderingDependent = true;
+			// Remember whether or not we can eliminate the sort.
+			eliminateSort = true;
 		}
 
-		/* If the result is ordering dependent, then we must
-		 * tell the underlying tree.  At minimum, this means no
-		 * group fetch on an index under an IndexRowToBaseRow
-		 * since that could lead to incorrect results.  (Bug 2347.)
+		/* If we were able to eliminate the sort during optimization then
+		 * we must tell the underlying tree.  At minimum, this means no
+		 * group fetch on an index under an IndexRowToBaseRow since that
+		 * that could lead to incorrect results.  (Bug 2347.)
 		 */
-		if (orderingDependent)
+		if (eliminateSort)
 		{
-			prnRSN.markOrderingDependent();
+			prnRSN.adjustForSortElimination();
 		}
 
 		/* Set the cost of this node in the generated node */

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SingleChildResultSetNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SingleChildResultSetNode.java?view=diff&rev=547066&r1=547065&r2=547066
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SingleChildResultSetNode.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SingleChildResultSetNode.java
Wed Jun 13 17:10:38 2007
@@ -569,13 +569,11 @@
 	}
 
 	/**
-	 * Notify the underlying result set tree that the result is
-	 * ordering dependent.  (For example, no bulk fetch on an index
-	 * if under an IndexRowToBaseRow.)
+	 * @see ResultSetNode#adjustForSortElimination
 	 */
-	void markOrderingDependent()
+	void adjustForSortElimination()
 	{
-		childResult.markOrderingDependent();
+		childResult.adjustForSortElimination();
 	}
 
 	/**

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableOperatorNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableOperatorNode.java?view=diff&rev=547066&r1=547065&r2=547066
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableOperatorNode.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableOperatorNode.java
Wed Jun 13 17:10:38 2007
@@ -929,14 +929,12 @@
 	}
 
 	/**
-	 * Notify the underlying result set tree that the result is
-	 * ordering dependent.  (For example, no bulk fetch on an index
-	 * if under an IndexRowToBaseRow.)
+	 * @see ResultSetNode#adjustForSortElimination
 	 */
-	void markOrderingDependent()
+	void adjustForSortElimination()
 	{
-		leftResultSet.markOrderingDependent();
-		rightResultSet.markOrderingDependent();
+		leftResultSet.adjustForSortElimination();
+		rightResultSet.adjustForSortElimination();
 	}
 
 	/**

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SysDiagVTIMappingTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SysDiagVTIMappingTest.java?view=diff&rev=547066&r1=547065&r2=547066
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SysDiagVTIMappingTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SysDiagVTIMappingTest.java
Wed Jun 13 17:10:38 2007
@@ -564,6 +564,91 @@
         st.close();
     }
 
+    /**
+     * Test that diagnostic VTIs will work correctly when an ORDER BY
+     * clause appears and/or sort elimination occurs.  DERBY-2805.
+     */
+    public void testOrderBy() throws SQLException
+    {
+        Statement st = createStatement();
+        st.executeUpdate("set schema APP");
+
+        // Create a single testing table for this fixture only.
+
+        st.execute("create table ob_t1 (i int, c char(250))");
+        st.execute("create index i_ix on ob_t1 (i)");
+        st.execute("create index c_ix on ob_t1 (c desc)");
+
+        /* Several queries to make sure ORDER BY actually takes effect.
+         * First execute with just the ORDER BY, then execute with the
+         * ORDER BY *and* a DISTINCT. The latter leads to sort elimination
+         * but should still run without error and return the same results
+         * (prior to the fix for DERBY-2805 the sort elimination would
+         * lead to an ASSERT failure with sane builds).
+         */
+
+        String [][] expRS = new String [][]
+        {
+            {"C_IX", "1", "0"},
+            {"I_IX", "1", "0"},
+            {"OB_T1", "0", "0"},
+        };
+
+        ResultSet rs = st.executeQuery(
+            "select CONGLOMERATENAME, ISINDEX, NUMFREEPAGES from " +
+            "table(syscs_diag.space_table('OB_T1')) X order by 1");
+
+        JDBC.assertFullResultSet(rs, expRS);
+
+        rs = st.executeQuery(
+            "select distinct CONGLOMERATENAME, ISINDEX, NUMFREEPAGES from " +
+            "table(syscs_diag.space_table('OB_T1')) X order by 1");
+
+        JDBC.assertFullResultSet(rs, expRS);
+
+        expRS = new String [][]
+        {
+            {"OB_T1", "0", "0"},
+            {"C_IX", "1", "0"},
+            {"I_IX", "1", "0"},
+        };
+
+        rs = st.executeQuery(
+            "select CONGLOMERATENAME, ISINDEX, NUMFREEPAGES from " +
+            "table(syscs_diag.space_table('OB_T1')) X order by 2, 1");
+
+        JDBC.assertFullResultSet(rs, expRS);
+
+        rs = st.executeQuery(
+            "select distinct CONGLOMERATENAME, ISINDEX, NUMFREEPAGES from " +
+            "table(syscs_diag.space_table('OB_T1')) X order by 2, 1");
+
+        JDBC.assertFullResultSet(rs, expRS);
+        expRS = new String [][]
+        {
+            {"OB_T1", "0", "0"},
+            {"I_IX", "1", "0"},
+            {"C_IX", "1", "0"},
+        };
+
+        rs = st.executeQuery(
+            "select CONGLOMERATENAME, ISINDEX, NUMFREEPAGES from " +
+            "table(syscs_diag.space_table('OB_T1')) X order by 2, 1 desc");
+
+        JDBC.assertFullResultSet(rs, expRS);
+
+        rs = st.executeQuery(
+            "select distinct CONGLOMERATENAME, ISINDEX, NUMFREEPAGES from " +
+            "table(syscs_diag.space_table('OB_T1')) X order by 2, 1 desc");
+
+        JDBC.assertFullResultSet(rs, expRS);
+
+        // Cleanup.
+
+        st.execute("drop table ob_t1");
+        st.close();
+    }
+
     /* All statements in this method should fail because a VTI table-
      * mapping that takes arguments can only be used as part of the TABLE 
      * constructor.  Any other uses of, or attempts to modify, such a



Mime
View raw message