db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bpendle...@apache.org
Subject svn commit: r708413 - in /db/derby/code/branches/10.4/java: engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/tests/lang/ testing/org/apache/derbyTesting/junit/
Date Tue, 28 Oct 2008 01:52:16 GMT
Author: bpendleton
Date: Mon Oct 27 18:52:16 2008
New Revision: 708413

URL: http://svn.apache.org/viewvc?rev=708413&view=rev
Log:
DERBY-3904: NPE on left join with aggregate

Merged from the trunk by svn merge -r 708001:708002 ../trunk/

Conflict resolved in GroupByTest.java by taking just the changes
for DERBY-3904.

Modified:
    db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/GroupByNode.java
    db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java
    db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/junit/RuntimeStatisticsParser.java

Modified: db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/GroupByNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/GroupByNode.java?rev=708413&r1=708412&r2=708413&view=diff
==============================================================================
--- db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/GroupByNode.java
(original)
+++ db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/GroupByNode.java
Mon Oct 27 18:52:16 2008
@@ -1186,10 +1186,13 @@
 							int colNum = crs[0].getColumnNumber();
 							
 							/* Check if we have an access path, this will be
-							 * null in a join case (See Beetle 4423)
+							 * null in a join case (See Beetle 4423,DERBY-3904)
 							 */
 							AccessPath accessPath= getTrulyTheBestAccessPath();
-							if (accessPath == null)
+							if (accessPath == null ||
+								accessPath.getConglomerateDescriptor()==null||
+								accessPath.getConglomerateDescriptor().
+								           getIndexDescriptor() == null)
 								return;
 							IndexDescriptor id = accessPath.
 												getConglomerateDescriptor().

Modified: db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java?rev=708413&r1=708412&r2=708413&view=diff
==============================================================================
--- db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java
(original)
+++ db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java
Mon Oct 27 18:52:16 2008
@@ -39,6 +39,8 @@
 import org.apache.derbyTesting.junit.BaseJDBCTestCase;
 import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
 import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.RuntimeStatisticsParser;
+import org.apache.derbyTesting.junit.SQLUtilities;
 
 /**
  * Many of these test cases were converted from the old groupBy.sql
@@ -98,6 +100,16 @@
         st.executeUpdate("insert into d3613 values (1,2,1,2), (1,2,3,4), " +
                 "(1,3,5,6), (2,2,2,2)");
 
+		st.execute("CREATE TABLE d3904_T1( " +
+				"D1 DATE NOT NULL PRIMARY KEY, N1 VARCHAR( 10 ))");
+		st.execute("CREATE TABLE d3904_T2( " +
+				"D2 DATE NOT NULL PRIMARY KEY, N2 VARCHAR( 10 ))");
+		st.execute("INSERT INTO d3904_T1 VALUES "+
+				"( DATE( '2008-10-01' ), 'something' ), "+
+				"( DATE( '2008-10-02' ), 'something' )" );
+		st.execute("INSERT INTO d3904_T2 VALUES" +
+				"( DATE( '2008-10-01' ), 'something' )" ); 
+
         // create an all types tables
         
         st.executeUpdate(
@@ -1886,5 +1898,53 @@
         }
         return rows;
     }
+
+    /**
+      * DERBY-3904: Min/Max optimization needs to be aware of joins.
+      */
+    public void testDerby3904MinMaxOptimization() throws SQLException
+    {
+        Statement s = createStatement();
+
+        JDBC.assertFullResultSet(
+                s.executeQuery("SELECT d3904_T1.D1 " +
+					"FROM d3904_T1 LEFT JOIN d3904_T2 " +
+				    "ON d3904_T1.D1 = d3904_T2.D2 " +
+					"WHERE d3904_T2.D2 IS NULL"), 
+            new String[][] {  {"2008-10-02"} } );
+        JDBC.assertFullResultSet(
+                s.executeQuery("SELECT MAX( d3904_T1.D1 ) as D " +
+					"FROM d3904_T1 WHERE d3904_T1.D1 NOT IN " +
+					"( SELECT d3904_T2.D2 FROM d3904_T2 )"), 
+            new String[][] {  {"2008-10-02"} } );
+		//
+		// In DERBY-3904, this next query fails with a null pointer
+		// exception because GroupByNode doesn't realize that there
+		// is a join involved here
+		//
+        JDBC.assertFullResultSet(
+                s.executeQuery("SELECT MAX( d3904_T1.D1 ) AS D " +
+					"FROM d3904_T1 LEFT JOIN d3904_T2 " +
+					"ON d3904_T1.D1 = d3904_T2.D2 " +
+					"WHERE d3904_T2.D2 IS NULL"),
+            new String[][] {  {"2008-10-02"} } );
+
+		// Verify that the min/max optimization still works for the
+		// simple query SELECT MAX(D1) FROM T1:
+		s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
+        JDBC.assertFullResultSet(
+                s.executeQuery("SELECT MAX(D1) FROM D3904_T1"),
+            new String[][] {  {"2008-10-02"} } );
+		RuntimeStatisticsParser rtsp =
+			SQLUtilities.getRuntimeStatisticsParser(s);
+		assertTrue(rtsp.usedLastKeyIndexScan());
+		assertFalse(rtsp.usedIndexRowToBaseRow());
+
+		// A form of the Beetle 4423 query:
+        JDBC.assertFullResultSet(
+                s.executeQuery("SELECT MAX(D1) " +
+					"FROM d3904_T1, D3904_T2 WHERE d3904_T1.D1='2008-10-02'"),
+            new String[][] {  {"2008-10-02"} } );
+	}
 }
 

Modified: db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/junit/RuntimeStatisticsParser.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/junit/RuntimeStatisticsParser.java?rev=708413&r1=708412&r2=708413&view=diff
==============================================================================
--- db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/junit/RuntimeStatisticsParser.java
(original)
+++ db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/junit/RuntimeStatisticsParser.java
Mon Oct 27 18:52:16 2008
@@ -31,6 +31,7 @@
     private boolean tableScan = false;
     private final boolean indexScan;
     private final boolean indexRowToBaseRow;
+	private final boolean lastKeyIndexScan;
     private String statistics = "";
     private boolean scrollInsensitive = false;
     private final HashSet qualifiers;
@@ -65,6 +66,7 @@
         indexScan = (rts.indexOf("Index Scan ResultSet") >= 0);
         indexRowToBaseRow =
             (rts.indexOf("Index Row to Base Row ResultSet") >= 0);
+        lastKeyIndexScan = (rts.indexOf("Last Key Index Scan ResultSet") >= 0);
         
         if (rts.indexOf("Eliminate duplicates = true") > 0) {
         	eliminatedDuplicates = true;
@@ -169,6 +171,15 @@
     }
 
     /**
+     * Return whether or not a last key index scan result set was used
+	 * in the query. A last key index scan is a special optimization for
+	 * MIN and MAX queries against an indexed column (SELECT MAX(ID) FROM T).
+     */
+    public boolean usedLastKeyIndexScan() {
+        return lastKeyIndexScan;
+    }
+
+    /**
      * Return whether or not an index row to base row result set was used in
      * the query.
      */



Mime
View raw message