Author: abrown
Date: Wed Feb 20 09:01:05 2008
New Revision: 629535
URL: http://svn.apache.org/viewvc?rev=629535&view=rev
Log:
DERBY3303: 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.
Port to 10.3 using "d3303_10_3_merge.patch" as attached to the Jira. No
additional changes were necessary.
Modified:
db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java
db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out
db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql
Modified: db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java?rev=629535&r1=629534&r2=629535&view=diff
==============================================================================
 db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java
(original)
+++ db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java
Wed Feb 20 09:01:05 2008
@@ 178,8 +178,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));
}
@@ 201,10 +207,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, userspecified result
+ * column listwhich 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 0based, 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 0based while column positions
+ * are 1based. 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". DERBY3303.
+ */
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/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out?rev=629535&r1=629534&r2=629535&view=diff
==============================================================================
 db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out
(original)
+++ db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out
Wed Feb 20 09:01:05 2008
@@ 1807,4 +1807,150 @@
abc
ij> drop table d2352;
0 rows inserted/updated/deleted
+ij>  DERBY3303: 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 DERBY3303. 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/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql?rev=629535&r1=629534&r2=629535&view=diff
==============================================================================
 db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql
(original)
+++ db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql
Wed Feb 20 09:01:05 2008
@@ 703,3 +703,66 @@
select trim(trailing ' ' from 'abc') from d2352
group by trim(trailing ' ' from 'abc');
drop table d2352;
+
+ DERBY3303: 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 DERBY3303. 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;
