Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Subject: svn commit: r882958 - in /db/derby/code/branches/10.3: ./
 java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
 java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java
Date: Sat, 21 Nov 2009 18:28:39 -0000
To: derby-commits@db.apache.org
From: dag@apache.org
Message-Id: <20091121182839.8A835238888E@eris.apache.org>

Author: dag
Date: Sat Nov 21 18:28:38 2009
New Revision: 882958

URL: http://svn.apache.org/viewvc?rev=882958&view=rev
Log:
DERBY-4450 GROUP BY in an IN-subquery inside HAVING clause whose select list is subset of group by columns, gives NPE

Patch DERBY-4450b + Knut's simplification of the autocommit call in GroupByTest#testDerby4450.

This solves the problem seen in this issue, which was a regression
from DERBY-681. The crux of the problem is that a PRN is added in the
result set tree without adjusting a higher level reference so that
wrong code is generated. The solution here is to reuse the result
column list in the inserted PRN, so that reference from above will
point correctly even after the PRN insertion (more details in JIRA). 

Backported from trunk as

svn merge -c 882732 https://svn.apache.org/repos/asf/db/derby/code/trunk

plus a manual fixup.

Modified:
    db/derby/code/branches/10.3/   (props changed)
    db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
    db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java

Propchange: db/derby/code/branches/10.3/
------------------------------------------------------------------------------
--- svn:mergeinfo (original)
+++ svn:mergeinfo Sat Nov 21 18:28:38 2009
@@ -1 +1 @@
-/db/derby/code/trunk:552046,788436,793588,794303,796316,796372,798347,798742,800523,803548,816536
+/db/derby/code/trunk:552046,788436,793588,794303,796316,796372,798347,798742,800523,803548,816536,882732

Modified: db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java?rev=882958&r1=882957&r2=882958&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java (original)
+++ db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/SelectNode.java Sat Nov 21 18:28:38 2009
@@ -1424,14 +1424,24 @@
 			// select c1 from t group by c1, c2
 			// we would have added c2 to the projection list which will have to be 
 			// projected out.
-			
-			ResultColumnList newSelectList = prnRSN.getResultColumns().copyListAndObjects(); 
-			newSelectList.removeGeneratedGroupingColumns();
-			newSelectList.genVirtualColumnNodes(prnRSN, prnRSN.getResultColumns());
+			//
+
+			// Keep the same RCL on top, since there may be
+			// references to its result columns above us, e.g. in this query:
+			//
+			// select sum(j),i from t group by i having i
+			//             in (select i from t group by i,j )
+			//
+			ResultColumnList topList = prnRSN.getResultColumns();
+			ResultColumnList newSelectList = topList.copyListAndObjects();
+			prnRSN.setResultColumns(newSelectList);
+
+			topList.removeGeneratedGroupingColumns();
+			topList.genVirtualColumnNodes(prnRSN, newSelectList);
 			prnRSN = (ResultSetNode) getNodeFactory().getNode(
 						C_NodeTypes.PROJECT_RESTRICT_NODE,
 						prnRSN,
-						newSelectList,
+						topList,
 						null,
 						null,
 						null,

Modified: db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java?rev=882958&r1=882957&r2=882958&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java (original)
+++ db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GroupByTest.java Sat Nov 21 18:28:38 2009
@@ -611,5 +611,31 @@
     }
 
 
+    /**
+     * GROUP BY in an IN-subquery inside HAVING clause whose select list is
+     * subset of group by columns.
+     *
+     * @throws SQLException
+     */
+    public void testDerby4450() throws SQLException {
+        setAutoCommit(false);
+        Statement s = createStatement();
+        ResultSet rs;
+
+        s.executeUpdate(
+            "create table tt(i int not null," +
+            "               j int, k int)");
+        s.executeUpdate(
+            "insert into tt values " +
+            "    (1,10,1), (1,40,1),(3,45,1),(4,46,1),(5,90,1)");
+
+        rs = s.executeQuery(
+            "select sum(j) from tt group by i having i " +
+            "                     in (select i from tt group by i,j )");
+
+        JDBC.assertFullResultSet(rs, new String[][] {
+                {"50"},{"45"},{"46"},{"90"}});
+        rollback();
+    }
 }