db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From abr...@apache.org
Subject svn commit: r628823 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/OrderByColumn.java testing/org/apache/derbyTesting/functionTests/master/orderby.out testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql
Date Mon, 18 Feb 2008 17:19:16 GMT
Author: abrown
Date: Mon Feb 18 09:19:06 2008
New Revision: 628823

URL: http://svn.apache.org/viewvc?rev=628823&view=rev
Log:
DERBY-3303: Fix OrderByColumn.java to account for "pulled" GROUP BY columns
when a) checking to see if the user has specified a valid ORDER BY column,
and b) trying to resolve "pulled" ORDER BY columns to their underlying values.
Also, add some relevant test cases to the existing lang/orderby.sql test.

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

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java?rev=628823&r1=628822&r2=628823&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java Mon
Feb 18 09:19:06 2008
@@ -196,8 +196,14 @@
 			ResultColumnList targetCols = target.getResultColumns();
 			columnPosition = ((Integer)expression.getConstantValueAsObject()).intValue();
 			resultCol = targetCols.getOrderByColumn(columnPosition);
-			
-			if (resultCol == null) {
+
+			/* Column is out of range if either a) resultCol is null, OR
+			 * b) resultCol points to a column that is not visible to the
+			 * user (i.e. it was generated internally).
+			 */
+			if ((resultCol == null) ||
+				(resultCol.getColumnPosition() > targetCols.visibleSize()))
+			{
 				throw StandardException.newException(SQLState.LANG_COLUMN_OUT_OF_RANGE, 
 								     String.valueOf(columnPosition));
 			}
@@ -219,10 +225,51 @@
 		resultCol.verifyOrderable();
 	}
 
+    /**
+     * Assuming this OrderByColumn was "pulled" into the received target's
+     * ResultColumnList (because it wasn't there to begin with), use
+     * this.addedColumnOffset to figure out which of the target's result
+     * columns is the one corresponding to "this".
+     *
+     * The desired position is w.r.t. the original, user-specified result
+     * column list--which is what "visibleSize()" gives us.  I.e. To get
+     * this OrderByColumn's position in target's RCL, first subtract out
+     * all columns which were "pulled" into the RCL for GROUP BY or ORDER
+     * BY, then add "this.addedColumnOffset". As an example, if the query
+     * was:
+     *
+     *   select sum(j) as s from t1 group by i, k order by k, sum(k)
+     *
+     * then we will internally add columns "K" and "SUM(K)" to the RCL for
+     * ORDER BY, *AND* we will add a generated column "I" to the RCL for
+     * GROUP BY.  Thus we end up with four result columns:
+     *
+     *          (1)        (2)  (3)   (4)
+     *  select sum(j) as s, K, SUM(K), I from t1 ...
+     *
+     * So when we get here and we want to find out which column "this"
+     * corresponds to, we begin by taking the total number of VISIBLE
+     * columns, which is 1 (i.e. 4 total columns minus 1 GROUP BY column
+     * minus 2 ORDER BY columns).  Then we add this.addedColumnOffset in
+     * order to find the target column position.  Since addedColumnOffset
+     * is 0-based, an addedColumnOffset value of "0" means we want the
+     * the first ORDER BY column added to target's RCL, "1" means we want
+     * the second ORDER BY column added, etc.  So if we assume that
+     * this.addedColumnOffset is "1" in this example then we add that
+     * to the RCL's "visible size". And finally, we add 1 more to account
+     * for fact that addedColumnOffset is 0-based while column positions
+     * are 1-based. This gives:
+     *
+     *  position = 1 + 1 + 1 = 3
+     *
+     * which points to SUM(K) in the RCL.  Thus an addedColumnOffset
+     * value of "1" resolves to column SUM(K) in target's RCL; similarly,
+     * an addedColumnOffset value of "0" resolves to "K". DERBY-3303.
+     */
     private void resolveAddedColumn(ResultSetNode target)
     {
         ResultColumnList targetCols = target.getResultColumns();
-        columnPosition = targetCols.size() - targetCols.getOrderBySelect() + addedColumnOffset
+ 1;
+        columnPosition = targetCols.visibleSize() + addedColumnOffset + 1;
         resultCol = targetCols.getResultColumn( columnPosition);
     }
 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out?rev=628823&r1=628822&r2=628823&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out
Mon Feb 18 09:19:06 2008
@@ -1903,4 +1903,150 @@
 abc 
 ij> drop table d2352;
 0 rows inserted/updated/deleted
+ij> -- DERBY-3303: Failures in MergeSort when GROUP BY is used with
+-- an ORDER BY on an expression (as opposed to an ORDER BY on
+-- a column reference).
+
+create table d3303 (i int, j int, k int);
+0 rows inserted/updated/deleted
+ij> insert into d3303 values (1, 1, 2), (1, 3, 3), (2, 3, 1), (2, 2, 4);
+4 rows inserted/updated/deleted
+ij> select * from d3303;
+I          |J          |K          
+-----------------------------------
+1          |1          |2          
+1          |3          |3          
+2          |3          |1          
+2          |2          |4          
+ij> -- All of these should execute without error.  Note the variance
+-- in expressions and sort order for the ORDER BY clause.
+
+select sum(j) as s from d3303 group by i order by 1;
+S          
+-----------
+4          
+5          
+ij> select sum(j) as s from d3303 group by i order by s;
+S          
+-----------
+4          
+5          
+ij> select sum(j) as s from d3303 group by i order by s desc;
+S          
+-----------
+5          
+4          
+ij> select sum(j) as s from d3303 group by i order by abs(1), s;
+S          
+-----------
+4          
+5          
+ij> select sum(j) as s from d3303 group by i order by sum(k), s desc;
+S          
+-----------
+5          
+4          
+ij> select sum(j) as s from d3303 group by k order by abs(k) desc;
+S          
+-----------
+2          
+3          
+1          
+3          
+ij> select sum(j) as s from d3303 group by k order by abs(k) asc;
+S          
+-----------
+3          
+1          
+3          
+2          
+ij> select sum(j) as s from d3303 group by i order by abs(i);
+S          
+-----------
+4          
+5          
+ij> select sum(j) as s from d3303 group by i order by abs(i) desc;
+S          
+-----------
+5          
+4          
+ij> -- Sanity check that a DISTINCT with a GROUP BY is ok, too.
+select distinct sum(j) as s from d3303 group by i;
+S          
+-----------
+4          
+5          
+ij> -- Slightly more complex queries, more in line with the query
+-- that was reported in DERBY-3303.  Try out various ORDER
+-- BY clauses to make sure they are actually being enforced.
+
+select max(i) as m1, max(j) as m2, sum(k) - max(j) as mdiff
+  from d3303 group by j order by abs(sum(k) - max(j)) asc;
+M1         |M2         |MDIFF      
+-----------------------------------
+2          |3          |1          
+1          |1          |1          
+2          |2          |2          
+ij> select max(i) as m1, max(j) as m2, sum(k) - max(j) as mdiff
+  from d3303 group by j order by abs(sum(k) - max(j)) desc;
+M1         |M2         |MDIFF      
+-----------------------------------
+2          |2          |2          
+2          |3          |1          
+1          |1          |1          
+ij> select max(i) as m1, max(j) as m2, sum(k) - max(j) as mdiff
+  from d3303 group by j order by abs(sum(k) - max(j)) desc, m2 asc;
+M1         |M2         |MDIFF      
+-----------------------------------
+2          |2          |2          
+1          |1          |1          
+2          |3          |1          
+ij> select max(i) as m1, max(j) as m2, sum(k) - max(j) as mdiff
+  from d3303 group by j order by abs(sum(k) - max(j)) desc, m2 desc;
+M1         |M2         |MDIFF      
+-----------------------------------
+2          |2          |2          
+2          |3          |1          
+1          |1          |1          
+ij> -- Queries that include a "*" in the SELECT list and have
+-- expressions in the ORDER BY.
+
+select d3303.i as old_i, sum(d3303.k), d3303.*
+  from d3303 group by k, i, j order by j;
+OLD_I      |2          |I          |J          |K          
+-----------------------------------------------------------
+1          |2          |1          |1          |2          
+2          |4          |2          |2          |4          
+1          |3          |1          |3          |3          
+2          |1          |2          |3          |1          
+ij> select d3303.i as old_i, sum(d3303.k), d3303.*
+  from d3303 group by k, i, j order by 4;
+OLD_I      |2          |I          |J          |K          
+-----------------------------------------------------------
+1          |2          |1          |1          |2          
+2          |4          |2          |2          |4          
+1          |3          |1          |3          |3          
+2          |1          |2          |3          |1          
+ij> select d3303.i as old_i, sum(d3303.k), d3303.*
+  from d3303 group by k, i, j order by k+2;
+OLD_I      |2          |I          |J          |K          
+-----------------------------------------------------------
+2          |1          |2          |3          |1          
+1          |2          |1          |1          |2          
+1          |3          |1          |3          |3          
+2          |4          |2          |2          |4          
+ij> -- These should all fail with error 42X77 (as opposed to an
+-- ASSERT or an IndexOutOfBoundsException or an execution time
+-- NPE).
+
+select k as s from d3303 order by 2;
+ERROR 42X77: Column position '2' is out of range for the query expression.
+ij> select sum(k) as s from d3303 group by i order by 2;
+ERROR 42X77: Column position '2' is out of range for the query expression.
+ij> select k from d3303 group by i,k order by 2;
+ERROR 42X77: Column position '2' is out of range for the query expression.
+ij> select k as s from d3303 group by i,k order by 2;
+ERROR 42X77: Column position '2' is out of range for the query expression.
+ij> drop table d3303;
+0 rows inserted/updated/deleted
 ij> 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql?rev=628823&r1=628822&r2=628823&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql
Mon Feb 18 09:19:06 2008
@@ -753,3 +753,65 @@
        group by trim(trailing ' ' from 'abc');
 drop table d2352;
 
+-- DERBY-3303: Failures in MergeSort when GROUP BY is used with
+-- an ORDER BY on an expression (as opposed to an ORDER BY on
+-- a column reference).
+
+create table d3303 (i int, j int, k int);
+insert into d3303 values (1, 1, 2), (1, 3, 3), (2, 3, 1), (2, 2, 4);
+select * from d3303;
+
+-- All of these should execute without error.  Note the variance
+-- in expressions and sort order for the ORDER BY clause.
+
+select sum(j) as s from d3303 group by i order by 1;
+select sum(j) as s from d3303 group by i order by s;
+select sum(j) as s from d3303 group by i order by s desc;
+select sum(j) as s from d3303 group by i order by abs(1), s;
+select sum(j) as s from d3303 group by i order by sum(k), s desc;
+select sum(j) as s from d3303 group by k order by abs(k) desc;
+select sum(j) as s from d3303 group by k order by abs(k) asc;
+select sum(j) as s from d3303 group by i order by abs(i);
+select sum(j) as s from d3303 group by i order by abs(i) desc;
+
+-- Sanity check that a DISTINCT with a GROUP BY is ok, too.
+select distinct sum(j) as s from d3303 group by i;
+
+-- Slightly more complex queries, more in line with the query
+-- that was reported in DERBY-3303.  Try out various ORDER
+-- BY clauses to make sure they are actually being enforced.
+
+select max(i) as m1, max(j) as m2, sum(k) - max(j) as mdiff
+  from d3303 group by j order by abs(sum(k) - max(j)) asc;
+
+select max(i) as m1, max(j) as m2, sum(k) - max(j) as mdiff
+  from d3303 group by j order by abs(sum(k) - max(j)) desc;
+
+select max(i) as m1, max(j) as m2, sum(k) - max(j) as mdiff
+  from d3303 group by j order by abs(sum(k) - max(j)) desc, m2 asc;
+
+select max(i) as m1, max(j) as m2, sum(k) - max(j) as mdiff
+  from d3303 group by j order by abs(sum(k) - max(j)) desc, m2 desc;
+
+-- Queries that include a "*" in the SELECT list and have
+-- expressions in the ORDER BY.
+
+select d3303.i as old_i, sum(d3303.k), d3303.*
+  from d3303 group by k, i, j order by j; 
+
+select d3303.i as old_i, sum(d3303.k), d3303.*
+  from d3303 group by k, i, j order by 4; 
+
+select d3303.i as old_i, sum(d3303.k), d3303.*
+  from d3303 group by k, i, j order by k+2; 
+
+-- These should all fail with error 42X77 (as opposed to an
+-- ASSERT or an IndexOutOfBoundsException or an execution time
+-- NPE).
+
+select k as s from d3303 order by 2;
+select sum(k) as s from d3303 group by i order by 2;
+select k from d3303 group by i,k order by 2;
+select k as s from d3303 group by i,k order by 2;
+
+drop table d3303;



Mime
View raw message