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 da...@apache.org on 2010/06/19 17:02:25 UTC
svn commit: r956234 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/compile/
testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: dag
Date: Sat Jun 19 15:02:25 2010
New Revision: 956234
URL: http://svn.apache.org/viewvc?rev=956234&view=rev
Log:
DERBY-4698 Simple query with HAVING clause crashes with NullPointerException
Patch derby-4698-2. The case of column references in HAVING clauses
being wrong after JOIN flattening was initially solved by
DERBY-3880. That solution was partial in that it can sometimes happen
too late.
This patch changes the fix-up of column references in a having clause
after join flattening to the same point in time as that of other
column references that need fix-up after the flattening (rcl, column
references in join predicates and group by clauses). Thus the fixup is
moved from the modifyaccesspath phase to the preprocess phase.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AggregateNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromList.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromTable.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AggregateNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AggregateNode.java?rev=956234&r1=956233&r2=956234&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AggregateNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AggregateNode.java Sat Jun 19 15:02:25 2010
@@ -548,26 +548,6 @@ public class AggregateNode extends Unary
operand;
- /* The operand for this aggregate node was initialized at bind
- * time. Between then and now it's possible that certain changes
- * have been made to the query tree which affect this operand. In
- * particular, if the operand was pointing to a result column in
- * a JoinNode and then that JoinNode was flattened during pre-
- * processing, all of the references to that JoinNode--including
- * this aggregate's operand--need to be updated to reflect the
- * fact that the Join Node no longer exists. So check to see if
- * the operand is a column reference, and if so, make a call to
- * remap it to its underlying expression. If nothing has happened
- * then this will be a no-op; but if something has changed to void
- * out the result column to which the operand points, the result
- * column will be marked "redundant" and the following call should
- * remap as appropriate. DERBY-3880.
- */
- if (operand instanceof ColumnReference)
- {
- ((ColumnReference)operand).remapColumnReferencesToExpressions();
- }
-
return (ResultColumn) getNodeFactory().getNode(
C_NodeTypes.RESULT_COLUMN,
"##aggregate expression",
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromList.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromList.java?rev=956234&r1=956233&r2=956234&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromList.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromList.java Sat Jun 19 15:02:25 2010
@@ -708,13 +708,15 @@ public class FromList extends QueryTreeN
* @param predicateList The PredicateList from the outer query
* @param sql The SubqueryList from the outer query
* @param gbl The group by list, if any
+ * @param havingClause The HAVING clause, if any
*
* @exception StandardException Thrown on error
*/
public void flattenFromTables(ResultColumnList rcl,
PredicateList predicateList,
SubqueryList sql,
- GroupByList gbl)
+ GroupByList gbl,
+ ValueNode havingClause)
throws StandardException
{
boolean flattened = true;
@@ -757,7 +759,8 @@ public class FromList extends QueryTreeN
rcl,
predicateList,
sql,
- gbl);
+ gbl,
+ havingClause);
if (SanityManager.DEBUG)
{
SanityManager.ASSERT(flatteningFL == null ||
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java?rev=956234&r1=956233&r2=956234&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromSubquery.java Sat Jun 19 15:02:25 2010
@@ -483,6 +483,7 @@ public class FromSubquery extends FromTa
* @param outerPList PredicateList to append wherePredicates to.
* @param sql The SubqueryList from the outer query
* @param gbl The group by list, if any
+ * @param havingClause The HAVING clause, if any
*
* @return FromList The fromList from the underlying SelectNode.
*
@@ -491,7 +492,8 @@ public class FromSubquery extends FromTa
public FromList flatten(ResultColumnList rcl,
PredicateList outerPList,
SubqueryList sql,
- GroupByList gbl)
+ GroupByList gbl,
+ ValueNode havingClause)
throws StandardException
{
@@ -541,6 +543,10 @@ public class FromSubquery extends FromTa
gbl.remapColumnReferencesToExpressions();
}
+ if (havingClause != null) {
+ havingClause.remapColumnReferencesToExpressions();
+ }
+
return fromList;
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromTable.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromTable.java?rev=956234&r1=956233&r2=956234&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromTable.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromTable.java Sat Jun 19 15:02:25 2010
@@ -1420,6 +1420,7 @@ abstract class FromTable extends ResultS
* @param outerPList PredicateList to append wherePredicates to.
* @param sql The SubqueryList from the outer query
* @param gbl The group by list, if any
+ * @param havingClause The HAVING clause, if any
*
* @return FromList The fromList from the underlying SelectNode.
*
@@ -1428,7 +1429,8 @@ abstract class FromTable extends ResultS
public FromList flatten(ResultColumnList rcl,
PredicateList outerPList,
SubqueryList sql,
- GroupByList gbl)
+ GroupByList gbl,
+ ValueNode havingClause)
throws StandardException
{
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java?rev=956234&r1=956233&r2=956234&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/JoinNode.java Sat Jun 19 15:02:25 2010
@@ -1414,6 +1414,7 @@ public class JoinNode extends TableOpera
* @param outerPList PredicateList to append wherePredicates to.
* @param sql The SubqueryList from the outer query
* @param gbl The group by list, if any
+ * @param havingClause The HAVING clause, if any
*
* @return FromList The fromList from the underlying SelectNode.
*
@@ -1422,7 +1423,8 @@ public class JoinNode extends TableOpera
public FromList flatten(ResultColumnList rcl,
PredicateList outerPList,
SubqueryList sql,
- GroupByList gbl)
+ GroupByList gbl,
+ ValueNode havingClause)
throws StandardException
{
@@ -1466,6 +1468,11 @@ public class JoinNode extends TableOpera
gbl.remapColumnReferencesToExpressions();
}
+ if (havingClause != null) {
+ havingClause.remapColumnReferencesToExpressions();
+ }
+
+
if (joinPredicates.size() > 0)
{
outerPList.destructiveAppend(joinPredicates);
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?rev=956234&r1=956233&r2=956234&view=diff
==============================================================================
--- 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 Sat Jun 19 15:02:25 2010
@@ -1087,7 +1087,8 @@ public class SelectNode extends ResultSe
fromList.flattenFromTables(resultColumns,
wherePredicates,
whereSubquerys,
- groupByList);
+ groupByList,
+ havingClause);
if (wherePredicates != null && wherePredicates.size() > 0 && fromList.size() > 0)
{
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java?rev=956234&r1=956233&r2=956234&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java Sat Jun 19 15:02:25 2010
@@ -2270,4 +2270,85 @@ public class GroupByTest extends BaseJDB
{"50"},{"45"},{"46"},{"90"}});
rollback();
}
+
+
+ /**
+ * HAVING with an aggregate function in presence of join flattening
+ * DERBY-4698. See also DERBY-3880.
+ * @throws SQLException
+ */
+ public void testDerby4698() throws SQLException {
+ setAutoCommit(false);
+ Statement s = createStatement();
+ ResultSet rs;
+
+ s.executeUpdate(
+ "create table usr ( user_id bigint primary key," +
+ " deleted char(1) not null)");
+ s.executeUpdate(
+ "create table user_account ( user_account_id bigint primary key," +
+ " user_id bigint not null," +
+ " account_id bigint not null)");
+
+ s.executeUpdate(
+ "CREATE TABLE ACCOUNT (ACCOUNT_ID BIGINT PRIMARY KEY," +
+ " ACCOUNT_TYPE VARCHAR(10) NOT NULL," +
+ " BALANCE DOUBLE NOT NULL)");
+
+ s.executeUpdate(
+ "insert into usr values " +
+ " (3003, 'Y'), (3004, 'N'), (1001, 'N'), (1002, 'Y')," +
+ " (1003, 'N'), (1004, 'N'), (1005, 'N'), (1006, 'N')," +
+ " (1007, 'N'), (1008, 'N'), (2002, 'N')");
+
+ s.executeUpdate(
+ "insert into user_account values " +
+ " (4004, 3003, 9009), (4005, 3004, 9010), (5005, 1001, 10010)," +
+ " (5006, 3004, 10011), (5007, 2002, 10012), (5008, 1003, 10013)," +
+ " (5009, 1004, 10014), (5010, 1005, 10015), (5011, 1006, 10016)," +
+ " (5012, 1007, 10017), (5013, 1008, 10018), (6006, 1001, 11011)," +
+ " (6007, 3004, 11012), (6008, 2002, 11013), (6009, 1003, 11014)," +
+ " (6010, 1004, 11015), (6011, 1005, 11016), (6012, 1006, 11017)," +
+ " (6013, 1007, 11018), (6014, 1008, 11019), (1001, 1001, 1002)," +
+ " (1002, 1002, 1003), (1003, 1003, 1004), (1004, 1004, 1005)," +
+ " (1005, 1005, 1006), (1006, 1006, 1007), (1007, 1007, 1008)," +
+ " (1008, 1008, 1009), (1009, 1004, 1010), (2002, 1004, 6006)," +
+ " (3003, 2002, 7007)");
+
+ s.executeUpdate(
+ "insert into account values " +
+ " (9009, 'USER', 12.5), (9010, 'USER', 12.5)," +
+ " (10010, 'USER-01', 0.0), (10011, 'USER-01', 0.0)," +
+ " (10012, 'USER-01', 0.0), (10013, 'USER-01', 0.0)," +
+ " (10014, 'USER-01', 99.0), (10015, 'USER-01', 0.0)," +
+ " (10016, 'USER-01', 0.0), (10017, 'USER-01', 0.0)," +
+ " (10018, 'USER-01', 0.0), (11011, 'USER-02', 0.0)," +
+ " (11012, 'USER-02', 0.0), (11013, 'USER-02', 0.0)," +
+ " (11014, 'USER-02', 0.0), (11015, 'USER-02', 0.0)," +
+ " (11016, 'USER-02', 0.0), (11017, 'USER-02', 0.0)," +
+ " (11018, 'USER-02', 0.0), (11019, 'USER-02', 0.0)," +
+ " (1002, 'USER', 10.0), (1003, 'USER', 80.31)," +
+ " (1004, 'USER', 10.0), (1005, 'USER', 161.7)," +
+ " (1006, 'USER', 10.0), (1007, 'USER', 10.0)," +
+ " (1008, 'USER', 10.0), (1009, 'USER', 10.0)," +
+ " (7007, 'USER', 11.0)");
+
+ rs = s.executeQuery(
+ "SELECT user0_.user_id AS col_0_0_," +
+ " SUM(account2_.balance) AS col_1_0_ " +
+ " FROM usr user0_ " +
+ " INNER JOIN user_account accountlin1_ " +
+ " ON user0_.user_id = accountlin1_.user_id " +
+ " INNER JOIN account account2_ " +
+ " ON accountlin1_.account_id = account2_.account_id " +
+ "WHERE user0_.deleted = 'N' " +
+ " AND ( account2_.account_type IN ( 'USER-01', 'USER' ) )" +
+ "GROUP BY user0_.user_id " +
+ "HAVING SUM(account2_.balance) >= 100.0 ");
+
+ JDBC.assertFullResultSet(rs, new String[][] {
+ {"1004", "260.7"} });
+
+ rollback();
+ }
}