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 ab...@apache.org on 2008/02/18 18:19:16 UTC
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
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;