db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From abr...@apache.org
Subject svn commit: r518687 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Thu, 15 Mar 2007 16:50:43 GMT
Author: abrown
Date: Thu Mar 15 09:50:41 2007
New Revision: 518687

URL: http://svn.apache.org/viewvc?view=rev&rev=518687
Log:
DERBY-681 (followup): Make the visibleSize() method of ResultColumnList less
restrictive by taking into account all generated columns, not just those added
in GroupByList#bindGroupByColumns.  Also, add an example query to subquery.sql
that shows why this change is needed.

Contributed by: Manish Khettry (manish_khettry@yahoo.com)

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subquery.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subquery.sql

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java?view=diff&rev=518687&r1=518686&r2=518687
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
Thu Mar 15 09:50:41 2007
@@ -3999,6 +3999,21 @@
 		initialListSize = size();
 	}
 
+	private int numGeneratedColumns() 
+	{
+		int numGenerated = 0;
+		int sz = size();
+		for (int i = sz - 1; i >= 0; i--) 
+		{
+			ResultColumn rc = (ResultColumn) elementAt(i);
+			if (rc.isGenerated()) 
+			{
+				numGenerated++;
+			}
+		}
+		return numGenerated;
+	}
+		
 	/**
 	 * @return the number of generated columns in this RCL.
 	 */
@@ -4039,6 +4054,6 @@
 	 */
 	public int visibleSize() 
 	{
-		return size() - orderBySelect - numGeneratedColumnsForGroupBy(); 
+		return size() - orderBySelect - numGeneratedColumns();
 	}
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java?view=diff&rev=518687&r1=518686&r2=518687
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/SubqueryNode.java Thu
Mar 15 09:50:41 2007
@@ -437,7 +437,7 @@
 		/* The parser does not enforce the fact that a subquery can only return
 		 * a single column, so we must check here.
 		 */
-		if (resultColumns.size() != 1)
+		if (resultColumns.visibleSize() != 1)
 		{
 			throw StandardException.newException(SQLState.LANG_NON_SINGLE_COLUMN_SUBQUERY);
 		}
@@ -697,8 +697,7 @@
 		{
 			SelectNode	select = (SelectNode) resultSet;
 			if ((select.getAggregateVector(IN_SELECT_LIST).size() == 0) &&
-			    (select.havingClause == null) &&
-				(! select.getGeneratedForGroupbyClause()))
+			    (select.havingClause == null))
 			{
 				ValueNode origLeftOperand = leftOperand;
 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subquery.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subquery.out?view=diff&rev=518687&r1=518686&r2=518687
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subquery.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/subquery.out
Thu Mar 15 09:50:41 2007
@@ -858,6 +858,27 @@
 2 dependencies found                                                                    
                   
 ij> -- reset autocommit
 autocommit on;
+ij> -- subquery with groupby and having clause
+select distinct vc, i from t as myt1
+      where s <= (select max(myt1.s) from t as myt2
+          where myt1.vc = myt2.vc and myt1.s <= myt2.s
+          group by s
+          having count(distinct s) <= 3);
+VC                            |I          
+------------------------------------------
+0                             |0          
+1                             |1          
+2                             |2          
+ij> -- subquery with having clause but no groupby
+select distinct vc, i from t as myt1
+      where s <= (select max(myt1.s) from t as myt2
+          where myt1.vc = myt2.vc and myt1.s <= myt2.s
+          having count(distinct s) <= 3);
+VC                            |I          
+------------------------------------------
+0                             |0          
+1                             |1          
+2                             |2          
 ij> -- drop the tables
 drop table li;
 0 rows inserted/updated/deleted

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subquery.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subquery.sql?view=diff&rev=518687&r1=518686&r2=518687
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subquery.sql
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/subquery.sql
Thu Mar 15 09:50:41 2007
@@ -416,6 +416,19 @@
 -- reset autocommit
 autocommit on;
 
+-- subquery with groupby and having clause
+select distinct vc, i from t as myt1
+      where s <= (select max(myt1.s) from t as myt2
+          where myt1.vc = myt2.vc and myt1.s <= myt2.s
+          group by s
+          having count(distinct s) <= 3); 
+
+-- subquery with having clause but no groupby
+select distinct vc, i from t as myt1
+      where s <= (select max(myt1.s) from t as myt2
+          where myt1.vc = myt2.vc and myt1.s <= myt2.s
+          having count(distinct s) <= 3); 
+
 -- drop the tables
 drop table li;
 drop table s;



Mime
View raw message