You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by bp...@apache.org on 2008/10/28 02:52:16 UTC
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/
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.
*/