ij> -- -- Licensed to the Apache Software Foundation (ASF) under one or more -- contributor license agreements. See the NOTICE file distributed with -- this work for additional information regarding copyright ownership. -- The ASF licenses this file to You under the Apache License, Version 2.0 -- (the "License"); you may not use this file except in compliance with -- the License. You may obtain a copy of the License at -- -- http://www.apache.org/licenses/LICENSE-2.0 -- -- Unless required by applicable law or agreed to in writing, software -- distributed under the License is distributed on an "AS IS" BASIS, -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -- See the License for the specific language governing permissions and -- limitations under the License. -- -- order by tests -- in V52, we allow "select a from t order by b" where the ORDERBY column doesn't necessarily appear in the SELECT list. autocommit off; ij> -- . order by on values values (1,0,1),(1,0,0),(0,0,1),(0,1,0) order by 1,2,3; 1 |2 |3 ----------------------------------- 0 |0 |1 0 |1 |0 1 |0 |0 1 |0 |1 ij> values (1,0,1),(1,0,0),(0,0,1),(0,1,0) order by 1,3; 1 |2 |3 ----------------------------------- 0 |1 |0 0 |0 |1 1 |0 |0 1 |0 |1 ij> values (1,0,1),(1,0,0),(0,0,1),(0,1,0) order by 2,1; 1 |2 |3 ----------------------------------- 0 |0 |1 1 |0 |0 1 |0 |1 0 |1 |0 ij> values (1,0,1),(1,0,0),(0,0,1),(0,1,0) order by 2; 1 |2 |3 ----------------------------------- 0 |0 |1 1 |0 |0 1 |0 |1 0 |1 |0 ij> -- . order by on position < 1, > range (error) values (1,0,1),(1,0,0),(0,0,1),(0,1,0) order by 0; ERROR 42X77: Column position '0' is out of range for the query expression. ij> values (1,0,1),(1,0,0),(0,0,1),(0,1,0) order by 4; ERROR 42X77: Column position '4' is out of range for the query expression. ij> -- . order by doesn't see generated names values (1,0,1),(1,0,0),(0,0,1),(0,1,0); 1 |2 |3 ----------------------------------- 1 |0 |1 1 |0 |0 0 |0 |1 0 |1 |0 ij> values (1,0,1),(1,0,0),(0,0,1),(0,1,0) order by "SQLCol1"; ERROR 42X78: Column 'SQLCol1' is not in the result of the query expression. ij> values (1,0,1),(1,0,0),(0,0,1),(0,1,0) order by "SQLCol2"; ERROR 42X78: Column 'SQLCol2' is not in the result of the query expression. ij> values (1,0,1),(1,0,0),(0,0,1),(0,1,0) order by 1,1,2,3; 1 |2 |3 ----------------------------------- 0 |0 |1 0 |1 |0 1 |0 |0 1 |0 |1 ij> -- rollback should release the prepared statements rollback; ij> -- . order by on select -- . order by with duplicate rows in source set schema app; 0 rows inserted/updated/deleted ij> create table obt (i int, v varchar(40)); 0 rows inserted/updated/deleted ij> insert into obt (i) values (null); 1 row inserted/updated/deleted ij> insert into obt values (1, 'hello'); 1 row inserted/updated/deleted ij> insert into obt values (2, 'planet'); 1 row inserted/updated/deleted ij> insert into obt values (1, 'world'); 1 row inserted/updated/deleted ij> insert into obt values (3, 'hello'); 1 row inserted/updated/deleted ij> -- save the data we've created commit; ij> select * from obt order by i; I |V ---------------------------------------------------- 1 |world 1 |hello 2 |planet 3 |hello NULL |NULL ij> select * from obt order by v; I |V ---------------------------------------------------- 3 |hello 1 |hello 2 |planet 1 |world NULL |NULL ij> -- . order by all select columns select * from obt order by i,v; I |V ---------------------------------------------------- 1 |hello 1 |world 2 |planet 3 |hello NULL |NULL ij> select * from obt order by v,i; I |V ---------------------------------------------------- 1 |hello 3 |hello 2 |planet 1 |world NULL |NULL ij> -- . order by asc/desc mix select * from obt order by v desc, i asc; I |V ---------------------------------------------------- NULL |NULL 1 |world 2 |planet 1 |hello 3 |hello ij> -- reverse prior order select * from obt order by i asc, v desc; I |V ---------------------------------------------------- 1 |world 1 |hello 2 |planet 3 |hello NULL |NULL ij> -- . order by with duplicates but different asc/desc attributes (ok) select * from obt order by i asc, i desc; I |V ---------------------------------------------------- 1 |world 1 |hello 2 |planet 3 |hello NULL |NULL ij> select * from obt order by i, v, i; I |V ---------------------------------------------------- 1 |hello 1 |world 2 |planet 3 |hello NULL |NULL ij> select v from obt order by i, v, i; V ---------------------------------------- hello world planet hello NULL ij> select v from obt order by i desc, v, i; V ---------------------------------------- NULL hello planet hello world ij> -- . order by on position < 1, > range (error) select * from obt order by 1, 0; ERROR 42X77: Column position '0' is out of range for the query expression. ij> select * from obt order by 1,2,3,4,5,6,7,8,9; ERROR 42X77: Column position '3' is out of range for the query expression. ij> select * from obt order by 32767; ERROR 42X77: Column position '32767' is out of range for the query expression. ij> -- rollback should release the prepared statements rollback ; ij> -- . order by on union all create table obt2 (i2 int, v varchar(40)); 0 rows inserted/updated/deleted ij> insert into obt2 values (3, 'hello'), (4, 'planet'), (1, 'shoe'), (3, 'planet'); 4 rows inserted/updated/deleted ij> -- save the data we've created commit ; ij> select * from obt union all select * from obt2 order by v; 1 |V ---------------------------------------------------- 3 |hello 3 |hello 1 |hello 3 |planet 4 |planet 2 |planet 1 |shoe 1 |world NULL |NULL ij> select * from obt union all select * from obt order by i; I |V ---------------------------------------------------- 1 |world 1 |hello 1 |world 1 |hello 2 |planet 2 |planet 3 |hello 3 |hello NULL |NULL NULL |NULL ij> select * from obt union all select * from obt order by i, i; I |V ---------------------------------------------------- 1 |world 1 |hello 1 |world 1 |hello 2 |planet 2 |planet 3 |hello 3 |hello NULL |NULL NULL |NULL ij> -- . order by on union with differing column names on sources. Error select * from obt union all select * from obt2 order by i; ERROR 42X78: Column 'I' is not in the result of the query expression. ij> select * from obt union all values (1,'hello') order by i; ERROR 42X78: Column 'I' is not in the result of the query expression. ij> values (1,'hello') union all select * from obt order by i; ERROR 42X78: Column 'I' is not in the result of the query expression. ij> -- . order by can not see generated names, though OK by position values (1,'hello') union all select * from obt; 1 |2 ---------------------------------------------------- 1 |hello NULL |NULL 1 |hello 2 |planet 1 |world 3 |hello ij> values (1,'hello') union all select * from obt order by "SQLCol1"; ERROR 42X78: Column 'SQLCol1' is not in the result of the query expression. ij> values (1,'hello') union all select * from obt order by 1; 1 |2 ---------------------------------------------------- 1 |world 1 |hello 1 |hello 2 |planet 3 |hello NULL |NULL ij> values (1,'hello') union all select * from obt order by 1, 1; 1 |2 ---------------------------------------------------- 1 |world 1 |hello 1 |hello 2 |planet 3 |hello NULL |NULL ij> -- rollback should release the prepared statements rollback ; ij> select i from obt union all values (1) order by 1; 1 ----------- 1 1 1 2 3 NULL ij> -- sees noname on both sides although second side is named values (1) union all select i from obt order by i; ERROR 42X78: Column 'I' is not in the result of the query expression. ij> -- rollback should release the prepared statements rollback ; ij> -- i2's name is hidden by obt, fails select * from obt union all select * from obt2 order by i2; ERROR 42X78: Column 'I2' is not in the result of the query expression. ij> -- . order by position/name mix select * from obt order by 1,i; I |V ---------------------------------------------------- 1 |world 1 |hello 2 |planet 3 |hello NULL |NULL ij> select * from obt order by 1,v; I |V ---------------------------------------------------- 1 |hello 1 |world 2 |planet 3 |hello NULL |NULL ij> -- . order by with duplicate positions select * from obt order by 1,2,1; I |V ---------------------------------------------------- 1 |hello 1 |world 2 |planet 3 |hello NULL |NULL ij> -- . order by with duplicate names select * from obt order by v,i,v; I |V ---------------------------------------------------- 1 |hello 3 |hello 2 |planet 1 |world NULL |NULL ij> -- . order by name gets select name, not underlying name select i as i2, v from obt order by i2; I2 |V ---------------------------------------------------- 1 |world 1 |hello 2 |planet 3 |hello NULL |NULL ij> -- error, i is not seen by order by select i as i2, v from obt order by i; I2 |V ---------------------------------------------------- 1 |world 1 |hello 2 |planet 3 |hello NULL |NULL ij> -- rollback should release the prepared statements rollback ; ij> -- . order without by (error) select i, v from obt order i; ERROR 42X01: Syntax error: Encountered "i" at line 2, column 28. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. ij> select i, v from obt by i; ERROR 42X01: Syntax error: Encountered "by" at line 1, column 22. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. ij> -- . show order, by are reserved keywords select order from obt; ERROR 42X01: Syntax error: Encountered "order" at line 2, column 8. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. ij> select by from obt; ERROR 42X01: Syntax error: Encountered "by" at line 1, column 8. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. ij> -- . order by on column not in query (error) select i from obt order by c; ERROR 42X04: Column 'C' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'C' is not a column in the target table. ij> -- . order by on column not in select, in table (error) select i from obt order by v; I ----------- 3 1 2 1 NULL ij> -- . order by on expression (allowed) select i from obt order by i+1; I ----------- 1 1 2 3 NULL ij> -- . order by on qualified column name, incorrect correlation name (not allowed) select i from obt t order by obt.i; ERROR 42X04: Column 'OBT.I' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'OBT.I' is not a column in the target table. ij> -- . order by on qualified column name, incorrect column name (not allowed) select i from obt t order by obt.notexists; ERROR 42X04: Column 'OBT.NOTEXISTS' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'OBT.NOTEXISTS' is not a column in the target table. ij> -- . order by on qualified column name create table t1(c1 int); 0 rows inserted/updated/deleted ij> create table t2(c1 int); 0 rows inserted/updated/deleted ij> create table t3(c3 int); 0 rows inserted/updated/deleted ij> insert into t1 values 2, 1; 2 rows inserted/updated/deleted ij> insert into t2 values 4, 3; 2 rows inserted/updated/deleted ij> insert into t3 values 6, 5; 2 rows inserted/updated/deleted ij> select t1.c1, t2.c1 from t1, t2 order by t1.c1; C1 |C1 ----------------------- 1 |3 1 |4 2 |3 2 |4 ij> select t1.c1, t2.c1 from t1, t2 order by t2.c1; C1 |C1 ----------------------- 1 |3 2 |3 1 |4 2 |4 ij> select t1.c1, t2.c1 from t1, t1 t2 order by t2.c1; C1 |C1 ----------------------- 1 |1 2 |1 1 |2 2 |2 ij> select t1.c1, t2.c1 from t1, t1 t2 order by t1.c1; C1 |C1 ----------------------- 1 |1 1 |2 2 |1 2 |2 ij> -- bug 5716 - qualified column name not allowed in order by when union/union all is used - following 4 test cases for that select c1 from t1 union select c3 as c1 from t3 order by t1.c1; ERROR 42877: A qualified column name 'T1.C1' is not allowed in the ORDER BY clause. ij> select * from obt union all select * from obt2 order by obt.v; ERROR 42877: A qualified column name 'OBT.V' is not allowed in the ORDER BY clause. ij> select * from obt union all select * from obt2 order by obt2.v; ERROR 42877: A qualified column name 'OBT2.V' is not allowed in the ORDER BY clause. ij> select * from obt union all select * from obt2 order by abc.v; ERROR 42877: A qualified column name 'ABC.V' is not allowed in the ORDER BY clause. ij> select * from t1 inner join t2 on 1=1 order by t1.c1; C1 |C1 ----------------------- 1 |3 1 |4 2 |3 2 |4 ij> select * from t1 inner join t2 on 1=1 order by t2.c1; C1 |C1 ----------------------- 1 |3 2 |3 1 |4 2 |4 ij> select c1 from t1 order by app.t1.c1; C1 ----------- 1 2 ij> select c1 from app.t1 order by app.t1.c1; C1 ----------- 1 2 ij> select c1 from app.t1 order by t1.c1; C1 ----------- 1 2 ij> select c1 from app.t1 order by c1; C1 ----------- 1 2 ij> select c1 from app.t1 c order by c1; C1 ----------- 1 2 ij> select c1 from app.t1 c order by c.c1; C1 ----------- 1 2 ij> select c1 from t1 order by c1; C1 ----------- 1 2 ij> -- negative -- shouldn't find exposed name select c1 from t1 union select c3 from t3 order by t3.c3; ERROR 42877: A qualified column name 'T3.C3' is not allowed in the ORDER BY clause. ij> select c1 from t1 union select c3 from t3 order by asdf.c3; ERROR 42877: A qualified column name 'ASDF.C3' is not allowed in the ORDER BY clause. ij> select c1 from t1 order by sys.t1.c1; ERROR 42X04: Column 'SYS.T1.C1' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'SYS.T1.C1' is not a column in the target table. ij> select c1 from app.t1 order by sys.t1.c1; ERROR 42X04: Column 'SYS.T1.C1' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'SYS.T1.C1' is not a column in the target table. ij> select c1 from t1 c order by app.c.c1; ERROR 42X10: 'APP.C' is not an exposed table name in the scope in which it appears. ij> select c1 from app.t1 c order by app.t1.c1; ERROR 42X04: Column 'APP.T1.C1' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'APP.T1.C1' is not a column in the target table. ij> -- a is not a column in t1 select 1 as a from t1 order by t1.a; ERROR 42X04: Column 'T1.A' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'T1.A' is not a column in the target table. ij> -- t3.c1 does not exist select * from t1, t3 order by t3.c1; ERROR 42X04: Column 'T3.C1' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'T3.C1' is not a column in the target table. ij> -- rollback should release the prepared statements rollback ; ij> -- . order by on join select obt.i, obt2.i2+1, obt2.v from obt, obt2 order by 2, 3; I |2 |V ---------------------------------------------------------------- 3 |2 |shoe 1 |2 |shoe 2 |2 |shoe 1 |2 |shoe NULL |2 |shoe 3 |4 |hello 1 |4 |hello 2 |4 |hello 1 |4 |hello NULL |4 |hello 3 |4 |planet 1 |4 |planet 2 |4 |planet 1 |4 |planet NULL |4 |planet 3 |5 |planet 1 |5 |planet 2 |5 |planet 1 |5 |planet NULL |5 |planet ij> select obt.i, obt2.i2+1, obt2.v from obt2, obt where obt.i=obt2.i2 order by 2, 3; I |2 |V ---------------------------------------------------------------- 1 |2 |shoe 1 |2 |shoe 3 |4 |hello 3 |4 |planet ij> -- . order by with spaces at end of values values 'hello ', 'hello ', 'hello ', 'hello' order by 1; 1 --------- hello hello hello hello ij> -- . order by on select items that are expressions select i+1, v, {fn length(v)} from obt order by 2, 1 desc, 3; 1 |V |3 ---------------------------------------------------------------- 4 |hello |5 2 |hello |5 3 |planet |6 2 |world |5 NULL |NULL |NULL ij> -- rollback should release the prepared statements rollback ; ij> -- . redundant order by on distinct, ?non-redundant (different ordering) select distinct i from obt order by i; I ----------- 1 2 3 NULL ij> select distinct i,v from obt order by v; I |V ---------------------------------------------------- 1 |hello 3 |hello 2 |planet 1 |world NULL |NULL ij> select distinct i,v from obt order by v desc, i desc, v desc; I |V ---------------------------------------------------- NULL |NULL 1 |world 2 |planet 3 |hello 1 |hello ij> -- . redundant order by on distinct, redundant (subset/prefix) select distinct i,v from obt order by i; I |V ---------------------------------------------------- 1 |hello 1 |world 2 |planet 3 |hello NULL |NULL ij> -- . redundant order by on index scan (later) -- rollback should release the prepared statements rollback ; ij> -- . order by with empty source, nulls in source, etc. delete from obt; 5 rows inserted/updated/deleted ij> select * from obt order by 1; I |V ---------------------------------------------------- ij> select * from obt order by v; I |V ---------------------------------------------------- ij> rollback ; ij> -- . order by with close values (doubles) create table d (d double precision); 0 rows inserted/updated/deleted ij> insert into d values 1e-300,2e-300; 2 rows inserted/updated/deleted ij> select d,d/1e5 as dd from d order by dd,d; D |DD --------------------------------------------- 1.0E-300 |1.0E-305 2.0E-300 |2.0E-305 ij> rollback ; ij> -- . order by with long values (varchars) create table v (v varchar(1200)); 0 rows inserted/updated/deleted ij> insert into v values 'itsastart'; 1 row inserted/updated/deleted ij> insert into v values 'hereandt'; 1 row inserted/updated/deleted ij> update v set v = v || v || v; 2 rows inserted/updated/deleted ij> update v set v = v || v || v; 2 rows inserted/updated/deleted ij> update v set v = v || v; 2 rows inserted/updated/deleted ij> update v set v = v || v; 2 rows inserted/updated/deleted ij> update v set v = v || v; 2 rows inserted/updated/deleted ij> update v set v = v || v; ERROR 22001: A truncation error was encountered trying to shrink VARCHAR 'itsastartitsastartitsastartitsastartitsastartitsastartitsast&' to length 1200. ij> update v set v = v || v; ERROR 22001: A truncation error was encountered trying to shrink VARCHAR 'itsastartitsastartitsastartitsastartitsastartitsastartitsast&' to length 1200. ij> select v from v order by v desc; V -------------------------------------------------------------------------------------------------------------------------------- itsastartitsastartitsastartitsastartitsastartitsastartitsastartitsastartitsastartitsastartitsastartitsastartitsastartitsastarti& hereandthereandthereandthereandthereandthereandthereandthereandthereandthereandthereandthereandthereandthereandthereandthereand& ij> rollback ; ij> drop table v; ERROR 42Y55: 'DROP TABLE' cannot be performed on 'V' because it does not exist. ij> -- . order by on all data types create table missed (s smallint, r real, d date, t time, ts timestamp, c char(10), l bigint); 0 rows inserted/updated/deleted ij> insert into missed values (1,1.2e4, '1992-01-01','23:01:01', '1993-02-04 12:02:00.001', 'theend', 2222222222222); 1 row inserted/updated/deleted ij> insert into missed values (1,1.2e4, '1992-01-01', '23:01:01', '1993-02-04 12:02:00.001', 'theend', 3333333333333); 1 row inserted/updated/deleted ij> insert into missed values (2,1.0e4, '1992-01-01', '20:01:01', '1997-02-04 12:02:00.001', 'theend', 4444444444444); 1 row inserted/updated/deleted ij> insert into missed values (2,1.0e4, '1992-01-01', '20:01:01', '1997-02-04 12:02:00.001', null, 2222222222222); 1 row inserted/updated/deleted ij> select s from missed order by s; S ------ 1 1 2 2 ij> select r from missed order by r; R ------------- 10000.0 10000.0 12000.0 12000.0 ij> select d,c from missed order by c,d; D |C --------------------- 1992-01-01|theend 1992-01-01|theend 1992-01-01|theend 1992-01-01|NULL ij> select ts,t from missed order by ts desc, t; TS |T -------------------------------------- 1997-02-04 12:02:00.001 |20:01:01 1997-02-04 12:02:00.001 |20:01:01 1993-02-04 12:02:00.001 |23:01:01 1993-02-04 12:02:00.001 |23:01:01 ij> select l from missed order by l; L -------------------- 2222222222222 2222222222222 3333333333333 4444444444444 ij> select l from missed order by l desc; L -------------------- 4444444444444 3333333333333 2222222222222 2222222222222 ij> rollback ; ij> -- . order by on char column create table ut (u char(10)); 0 rows inserted/updated/deleted ij> insert into ut values (null); 1 row inserted/updated/deleted ij> insert into ut values (cast ('hello' as char(10))); 1 row inserted/updated/deleted ij> insert into ut values ('world'); 1 row inserted/updated/deleted ij> insert into ut values ('hello'); 1 row inserted/updated/deleted ij> insert into ut values ('world '); 1 row inserted/updated/deleted ij> -- rollback should release the prepared statements rollback ; ij> -- . order by and explicit for update (no, some cols) get cursor c1 as 'select i from obt order by i for update of v'; ERROR 42Y90: FOR UPDATE is not permitted in this type of statement. ij> -- . order by and explicit read only (ok) get cursor c1 as 'select i from obt order by i for read only'; ij> next c1; I ----------- 1 ij> close c1; ij> -- . order by is implicitly read only get cursor c1 as 'select i from obt order by i'; ij> next c1; I ----------- 1 ij> -- error update obt set v='newval' where current of c1; ERROR 42X23: Cursor C1 is not updatable. ij> close c1; ij> -- no rows select v from obt where v='newval'; V ---------------------------------------- ij> -- rollback should release the prepared statements rollback ; ij> select v from obt where i in (select i from obt2 order by i); V ---------------------------------------- hello planet world hello ij> select v from obt where i in (select i2 from obt2 order by i2); V ---------------------------------------- hello world hello ij> -- more than one row in subquery: error select v from obt where i = (select i from obt2 order by i); V ---------------------------------------- ERROR 21000: Scalar subquery is only allowed to return a single row. ij> select v from (select i2,v from obt2 order by i2)s; V ---------------------------------------- shoe planet hello planet ij> -- rollback should release the prepared statements rollback ; ij> -- order by allowed on datatypes, -- but not non-mapped user types -- bit maps to Byte[], so can't test for now create table tab1 ( i integer, tn integer, s integer, l integer, c char(10), v char(10), lvc char(10), d double precision, r real, dt date, t time, ts timestamp, dc decimal(2,1)); 0 rows inserted/updated/deleted ij> insert into tab1 values (1, cast(1 as int), cast(1 as smallint), cast(1 as bigint), '1', '1', '1', cast(1.1 as double precision), cast(1.1 as real), '1996-01-01', '11:11:11','1996-01-01 11:10:10.1', cast(1.1 as decimal(2,1))); 1 row inserted/updated/deleted ij> insert into tab1 values (2, cast(2 as int), cast(2 as smallint), cast(2 as bigint), '2', '2', '2', cast(2.2 as double precision), cast(2.2 as real), '1995-02-02', '12:12:12', '1996-02-02 12:10:10.1', cast(2.2 as decimal(2,1))); 1 row inserted/updated/deleted ij> select * from tab1 order by 1; I |TN |S |L |C |V |LVC |D |R |DT |T |TS |DC ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 |1 |1 |1 |1 |1 |1 |1.1 |1.1 |1996-01-01|11:11:11|1996-01-01 11:10:10.1 |1.1 2 |2 |2 |2 |2 |2 |2 |2.2 |2.2 |1995-02-02|12:12:12|1996-02-02 12:10:10.1 |2.2 ij> rollback; ij> -- bug 2769 (correlation columns, group by and order by) create table bug2769(c1 int, c2 int); 0 rows inserted/updated/deleted ij> insert into bug2769 values (1, 1), (1, 2), (3, 2), (3, 3); 4 rows inserted/updated/deleted ij> select a.c1, sum(a.c1) from bug2769 a group by a.c1 order by a.c1; C1 |2 ----------------------- 1 |2 3 |6 ij> select bug2769.c1 as x, sum(bug2769.c1) as y from bug2769 group by bug2769.c1 order by bug2769.c1; X |Y ----------------------- 1 |2 3 |6 ij> select bug2769.c1 as x, sum(bug2769.c1) as y from bug2769 group by bug2769.c1 order by x; X |Y ----------------------- 1 |2 3 |6 ij> select c1 as x, c2 as y from bug2769 group by bug2769.c1, bug2769.c2 order by c1 + c2; X |Y ----------------------- 1 |1 1 |2 3 |2 3 |3 ij> select c1 as x, c2 as y from bug2769 group by bug2769.c1, bug2769.c2 order by -(c1 + c2); X |Y ----------------------- 3 |3 3 |2 1 |2 1 |1 ij> rollback; ij> -- reset autocommit autocommit on; ij> -- cleanup drop table obt; 0 rows inserted/updated/deleted ij> drop table obt2; 0 rows inserted/updated/deleted ij> create table t (a int, b int, c int); 0 rows inserted/updated/deleted ij> insert into t values (1, 2, null), (2, 3, null), (3, 0, null), (1, 3, null); 4 rows inserted/updated/deleted ij> select * from t order by a; A |B |C ----------------------------------- 1 |3 |NULL 1 |2 |NULL 2 |3 |NULL 3 |0 |NULL ij> select * from t order by a, a; A |B |C ----------------------------------- 1 |3 |NULL 1 |2 |NULL 2 |3 |NULL 3 |0 |NULL ij> select * from t order by a, a, a; A |B |C ----------------------------------- 1 |3 |NULL 1 |2 |NULL 2 |3 |NULL 3 |0 |NULL ij> select * from t order by a, b; A |B |C ----------------------------------- 1 |2 |NULL 1 |3 |NULL 2 |3 |NULL 3 |0 |NULL ij> select a, b, c from t order by a, a; A |B |C ----------------------------------- 1 |3 |NULL 1 |2 |NULL 2 |3 |NULL 3 |0 |NULL ij> select a, b, c from t order by a, b; A |B |C ----------------------------------- 1 |2 |NULL 1 |3 |NULL 2 |3 |NULL 3 |0 |NULL ij> select a, c from t order by b; A |C ----------------------- 3 |NULL 1 |NULL 1 |NULL 2 |NULL ij> select a, c from t order by b, b; A |C ----------------------- 3 |NULL 1 |NULL 1 |NULL 2 |NULL ij> select a, b, c from t order by b; A |B |C ----------------------------------- 3 |0 |NULL 1 |2 |NULL 1 |3 |NULL 2 |3 |NULL ij> select a from t order by b, c; A ----------- 3 1 1 2 ij> select a, c from t order by b, c; A |C ----------------------- 3 |NULL 1 |NULL 1 |NULL 2 |NULL ij> select a, c from t order by b, c, b, c; A |C ----------------------- 3 |NULL 1 |NULL 1 |NULL 2 |NULL ij> select a, b, c from t order by b, c; A |B |C ----------------------------------- 3 |0 |NULL 1 |2 |NULL 1 |3 |NULL 2 |3 |NULL ij> select b, c from t order by app.t.a; B |C ----------------------- 3 |NULL 2 |NULL 3 |NULL 0 |NULL ij> --Test addtive expression in order clause create table test_word(value varchar(32)); 0 rows inserted/updated/deleted ij> insert into test_word(value) values('anaconda'); 1 row inserted/updated/deleted ij> insert into test_word(value) values('America'); 1 row inserted/updated/deleted ij> insert into test_word(value) values('camel'); 1 row inserted/updated/deleted ij> insert into test_word(value) values('Canada'); 1 row inserted/updated/deleted ij> select * from test_word order by value; VALUE -------------------------------- America Canada anaconda camel ij> select * from test_word order by upper(value); VALUE -------------------------------- America anaconda camel Canada ij> drop table test_word; 0 rows inserted/updated/deleted ij> create table test_number(value integer); 0 rows inserted/updated/deleted ij> insert into test_number(value) values(-1); 1 row inserted/updated/deleted ij> insert into test_number(value) values(0); 1 row inserted/updated/deleted ij> insert into test_number(value) values(1); 1 row inserted/updated/deleted ij> insert into test_number(value) values(2); 1 row inserted/updated/deleted ij> insert into test_number(value) values(3); 1 row inserted/updated/deleted ij> insert into test_number(value) values(100); 1 row inserted/updated/deleted ij> insert into test_number(value) values(1000); 1 row inserted/updated/deleted ij> select * from test_number order by value; VALUE ----------- -1 0 1 2 3 100 1000 ij> select * from test_number order by value + 1; VALUE ----------- -1 0 1 2 3 100 1000 ij> select * from test_number order by value - 1; VALUE ----------- -1 0 1 2 3 100 1000 ij> select * from test_number order by value * 1; VALUE ----------- -1 0 1 2 3 100 1000 ij> select * from test_number order by value / 1; VALUE ----------- -1 0 1 2 3 100 1000 ij> select * from test_number order by 1 + value; VALUE ----------- -1 0 1 2 3 100 1000 ij> select * from test_number order by 1 - value; VALUE ----------- 1000 100 3 2 1 0 -1 ij> select * from test_number order by 1 * value; VALUE ----------- -1 0 1 2 3 100 1000 ij> select * from test_number where value <> 0 order by 6000 / value; VALUE ----------- -1 1000 100 3 2 1 ij> select * from test_number order by -1 + value; VALUE ----------- -1 0 1 2 3 100 1000 ij> select * from test_number order by -1 - value; VALUE ----------- 1000 100 3 2 1 0 -1 ij> select * from test_number order by - 1 * value; VALUE ----------- 1000 100 3 2 1 0 -1 ij> select * from test_number where value <> 0 order by - 6000 / value; VALUE ----------- 1 2 3 100 1000 -1 ij> select * from test_number order by abs(value); VALUE ----------- 0 1 -1 2 3 100 1000 ij> select * from test_number order by value desc; VALUE ----------- 1000 100 3 2 1 0 -1 ij> select * from test_number order by value + 1 desc; VALUE ----------- 1000 100 3 2 1 0 -1 ij> select * from test_number order by value - 1 desc; VALUE ----------- 1000 100 3 2 1 0 -1 ij> select * from test_number order by value * 1 desc; VALUE ----------- 1000 100 3 2 1 0 -1 ij> select * from test_number order by value / 1 desc; VALUE ----------- 1000 100 3 2 1 0 -1 ij> select * from test_number order by 1 + value desc; VALUE ----------- 1000 100 3 2 1 0 -1 ij> select * from test_number order by 1 - value desc; VALUE ----------- -1 0 1 2 3 100 1000 ij> select * from test_number order by 1 * value desc; VALUE ----------- 1000 100 3 2 1 0 -1 ij> select * from test_number where value <> 0 order by 6000 / value desc; VALUE ----------- 1 2 3 100 1000 -1 ij> select * from test_number order by -1 + value desc; VALUE ----------- 1000 100 3 2 1 0 -1 ij> select * from test_number order by -1 - value desc; VALUE ----------- -1 0 1 2 3 100 1000 ij> select * from test_number order by - 1 * value desc; VALUE ----------- -1 0 1 2 3 100 1000 ij> select * from test_number where value <> 0 order by - 6000 / value desc; VALUE ----------- -1 1000 100 3 2 1 ij> select * from test_number order by abs(value) desc; VALUE ----------- 1000 100 3 2 1 -1 0 ij> drop table test_number; 0 rows inserted/updated/deleted ij> create table test_number2(value1 integer,value2 integer); 0 rows inserted/updated/deleted ij> insert into test_number2(value1,value2) values(-2,2); 1 row inserted/updated/deleted ij> insert into test_number2(value1,value2) values(-1,2); 1 row inserted/updated/deleted ij> insert into test_number2(value1,value2) values(0,1); 1 row inserted/updated/deleted ij> insert into test_number2(value1,value2) values(0,2); 1 row inserted/updated/deleted ij> insert into test_number2(value1,value2) values(1,1); 1 row inserted/updated/deleted ij> insert into test_number2(value1,value2) values(2,1); 1 row inserted/updated/deleted ij> select * from test_number2 order by abs(value1),mod(value2,2); VALUE1 |VALUE2 ----------------------- 0 |2 0 |1 -1 |2 1 |1 -2 |2 2 |1 ij> drop table test_number2; 0 rows inserted/updated/deleted ij> -- error case select * from t order by d; ERROR 42X04: Column 'D' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'D' is not a column in the target table. ij> select t.* from t order by d; ERROR 42X04: Column 'D' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'D' is not a column in the target table. ij> select t.* from t order by t.d; ERROR 42X04: Column 'T.D' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'T.D' is not a column in the target table. ij> select s.* from t s order by s.d; ERROR 42X04: Column 'S.D' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'S.D' is not a column in the target table. ij> select *, d from t order by d; ERROR 42X01: Syntax error: Encountered "," at line 1, column 9. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. ij> select t.*, d from t order by d; ERROR 42X04: Column 'D' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'D' is not a column in the target table. ij> select t.*, d from t order by t.d; ERROR 42X04: Column 'D' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'D' is not a column in the target table. ij> select t.*, d from t order by app.t.d; ERROR 42X04: Column 'D' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'D' is not a column in the target table. ij> select s.*, d from t s order by s.d; ERROR 42X04: Column 'D' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'D' is not a column in the target table. ij> select t.*, t.d from t order by t.d; ERROR 42X04: Column 'T.D' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'T.D' is not a column in the target table. ij> select s.*, s.d from t s order by s.d; ERROR 42X04: Column 'S.D' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'S.D' is not a column in the target table. ij> select a, b, c from t order by d; ERROR 42X04: Column 'D' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'D' is not a column in the target table. ij> select a from t order by d; ERROR 42X04: Column 'D' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'D' is not a column in the target table. ij> select t.a from t order by t.d; ERROR 42X04: Column 'T.D' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'T.D' is not a column in the target table. ij> select s.a from t s order by s.d; ERROR 42X04: Column 'S.D' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'S.D' is not a column in the target table. ij> drop table t; 0 rows inserted/updated/deleted ij> -- test fof using table correlation names select * from (values (2),(1)) as t(x) order by t.x; X ----------- 1 2 ij> create table ta(id int); 0 rows inserted/updated/deleted ij> create table tb(id int,c1 int,c2 int); 0 rows inserted/updated/deleted ij> insert into ta(id) values(1); 1 row inserted/updated/deleted ij> insert into ta(id) values(2); 1 row inserted/updated/deleted ij> insert into ta(id) values(3); 1 row inserted/updated/deleted ij> insert into ta(id) values(4); 1 row inserted/updated/deleted ij> insert into ta(id) values(5); 1 row inserted/updated/deleted ij> insert into tb(id,c1,c2) values(1,5,3); 1 row inserted/updated/deleted ij> insert into tb(id,c1,c2) values(2,4,3); 1 row inserted/updated/deleted ij> insert into tb(id,c1,c2) values(3,4,2); 1 row inserted/updated/deleted ij> insert into tb(id,c1,c2) values(4,4,1); 1 row inserted/updated/deleted ij> insert into tb(id,c1,c2) values(5,4,2); 1 row inserted/updated/deleted ij> select t1.id,t2.c1 from ta as t1 join tb as t2 on t1.id = t2.id order by t2.c1,t2.c2,t1.id; ID |C1 ----------------------- 4 |4 3 |4 5 |4 2 |4 1 |5 ij> drop table ta; 0 rows inserted/updated/deleted ij> drop table tb; 0 rows inserted/updated/deleted ij> -- some investigation of the handling of non-unique columns in the result set -- related to DERBY-147. The idea with this tests is that it should be -- acceptable to mention a column in the SELECT statement multiple times and -- then order by it, so long as the multiple columns truly are equivalent. -- There are a few cases where there truly is an ambiguity, and in those -- cases we reject the ORDER BY clause. create table derby147 (a int, b int, c int, d int); 0 rows inserted/updated/deleted ij> insert into derby147 values (1, 2, 3, 4); 1 row inserted/updated/deleted ij> insert into derby147 values (6, 6, 6, 6); 1 row inserted/updated/deleted ij> select t.* from derby147 t; A |B |C |D ----------------------------------------------- 1 |2 |3 |4 6 |6 |6 |6 ij> select t.a,t.b,t.* from derby147 t order by b; A |B |A |B |C |D ----------------------------------------------------------------------- 1 |2 |1 |2 |3 |4 6 |6 |6 |6 |6 |6 ij> select t.a,t.b,t.b,t.c from derby147 t; A |B |B |C ----------------------------------------------- 1 |2 |2 |3 6 |6 |6 |6 ij> select t.a,t.b,t.b,t.c from derby147 t order by t.b; A |B |B |C ----------------------------------------------- 1 |2 |2 |3 6 |6 |6 |6 ij> -- This one truly is ambiguous, because the two columns named "e" are -- NOT equivalent. So it should fail: select a+b as e, c+d as e from derby147 order by e; ERROR 42X79: Column name 'E' appears more than once in the result of the query expression. ij> create table derby147_a (a int, b int, c int, d int); 0 rows inserted/updated/deleted ij> insert into derby147_a values (1,2,3,4), (40, 30, 20, 10), (1,50,3,50); 3 rows inserted/updated/deleted ij> create table derby147_b (a int, b int); 0 rows inserted/updated/deleted ij> insert into derby147_b values (4, 4), (10, 10), (2, 50); 3 rows inserted/updated/deleted ij> -- The columns named "a" are NOT equivalent. select t1.a,t2.a from derby147_a t1, derby147_b t2 where t1.d=t2.b order by a; ERROR 42X79: Column name 'A' appears more than once in the result of the query expression. ij> select t1.a,t2.a from derby147_a t1, derby147_b t2 where t1.d=t2.b order by t2.a; A |A ----------------------- 1 |2 1 |4 40 |10 ij> select a,a,b,c,d,a from derby147_a order by a; A |A |B |C |D |A ----------------------------------------------------------------------- 1 |1 |50 |3 |50 |1 1 |1 |2 |3 |4 |1 40 |40 |30 |20 |10 |40 ij> select c+d as a, t1.a, t1.b+t1.c as a from derby147_a t1 order by 3, 2 desc; A |A |A ----------------------------------- 7 |1 |5 30 |40 |50 53 |1 |53 ij> -- The columns named "a" are NOT equivalent. select c+d as a, t1.a, t1.b+t1.c as a from derby147_a t1 order by a, a desc; ERROR 42X79: Column name 'A' appears more than once in the result of the query expression. ij> select a, c+d as a from derby147_a; A |A ----------------------- 1 |7 40 |30 1 |53 ij> -- The columns named "a" are NOT equivalent. select a, c+d as a from derby147_a order by a; ERROR 42X79: Column name 'A' appears more than once in the result of the query expression. ij> select c+d as a, t1.a, t1.b+t1.c as b_plus_c from derby147_a t1 order by c+d; A |A |B_PLUS_C ----------------------------------- 7 |1 |5 30 |40 |50 53 |1 |53 ij> -- The columns named "a" are NOT equivalent. select c+d as a, t1.a, t1.b+t1.c as a from derby147_a t1 order by d-4, a; ERROR 42X79: Column name 'A' appears more than once in the result of the query expression. ij> select * from derby147_a order by c+2 desc, b asc, a desc; A |B |C |D ----------------------------------------------- 40 |30 |20 |10 1 |2 |3 |4 1 |50 |3 |50 ij> -- If you introduce a coorelation name for a table, use the correlation -- name in the order by: select a, b from derby147_a t order by derby147_a.b; ERROR 42X04: Column 'DERBY147_A.B' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'DERBY147_A.B' is not a column in the target table. ij> -- pull expressions from the ORDER BY clause into the implicit area of -- the SELECT column list, and ensure they don't end up in the result. This -- statement causes a SanityManager assertion, filed as DERBY-1861 -- select * from derby147_b order by b, a+2; -- Verify that correlation names match the table names properly: select t.a, sum(t.a) from derby147_a t group by t.a order by t.a; A |2 ----------------------- 1 |2 40 |40 ij> -- Tests which verify the handling of expressions in the ORDER BY list -- related to DERBY-1861. The issue in DERBY-1861 has to do with how the -- compiler handles combinations of expressions and simple columns in the -- ORDER BY clause, so we try a number of such combinations create table derby1861 (a int, b int, c int, d int); 0 rows inserted/updated/deleted ij> insert into derby1861 values (1, 2, 3, 4); 1 row inserted/updated/deleted ij> select * from derby1861 order by a, b, c+2; A |B |C |D ----------------------------------------------- 1 |2 |3 |4 ij> select a, c from derby1861 order by a, b, c-4; A |C ----------------------- 1 |3 ij> select t.* from derby1861 t order by t.a, t.b, t.c+2; A |B |C |D ----------------------------------------------- 1 |2 |3 |4 ij> select a, b, a, c, d from derby1861 order by b, c-1, a; A |B |A |C |D ----------------------------------------------------------- 1 |2 |1 |3 |4 ij> select * from derby1861 order by a, c+2, a; A |B |C |D ----------------------------------------------- 1 |2 |3 |4 ij> select * from derby1861 order by c-1, c+1, a, b, c * 6; A |B |C |D ----------------------------------------------- 1 |2 |3 |4 ij> select t.*, t.c+2 from derby1861 t order by a, b, c+2; A |B |C |D |5 ----------------------------------------------------------- 1 |2 |3 |4 |5 ij> select * from derby1861 order by 3, 1; A |B |C |D ----------------------------------------------- 1 |2 |3 |4 ij> select * from derby1861 order by 2, a-2; A |B |C |D ----------------------------------------------- 1 |2 |3 |4 ij> -- Tests which verify the handling of expressions in the ORDER BY list -- related to DERBY-2459. The issue in DERBY-2459 has to do with handling -- of ORDER BY in the UNION case. The current Derby implementation has no -- support for expressions in the ORDER BY clause of a UNION SELECT. -- These test cases demonstrate some aspects of what works, and what doesn't. create table d2459_A1 ( id char(1) ,value int ,ref char(1)); 0 rows inserted/updated/deleted ij> create table d2459_A2 ( id char(1) ,value int ,ref char(1)); 0 rows inserted/updated/deleted ij> create table d2459_B1 ( id char(1) ,value int); 0 rows inserted/updated/deleted ij> create table d2459_B2 ( id char(1) ,value int); 0 rows inserted/updated/deleted ij> insert into d2459_A1 (id, value, ref) values ('b', 1, null); 1 row inserted/updated/deleted ij> insert into d2459_A1 (id, value, ref) values ('a', 12, 'e'); 1 row inserted/updated/deleted ij> insert into d2459_A2 (id, value, ref) values ('c', 3, 'g'); 1 row inserted/updated/deleted ij> insert into d2459_A2 (id, value, ref) values ('d', 8, null); 1 row inserted/updated/deleted ij> insert into d2459_B1 (id, value) values ('f', 2); 1 row inserted/updated/deleted ij> insert into d2459_B1 (id, value) values ('e', 4); 1 row inserted/updated/deleted ij> insert into d2459_B2 (id, value) values ('g', 5); 1 row inserted/updated/deleted ij> -- Should work, as the order by expression is against a select, not a union: select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END from d2459_A2 t1 left outer join d2459_B2 t2 ON t2.id = t1.ref order by CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END ; ID |2 ---------------- c |5 d |8 ij> -- Should work, it's a simple column reference to the first column in UNION: select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END from d2459_A1 t1 left outer join d2459_B1 t2 ON t2.id = t1.ref union all select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END from d2459_A2 t1 left outer join d2459_B2 t2 ON t2.id = t1.ref order by id; ID |2 ---------------- a |4 b |1 c |5 d |8 ij> -- Should work, it's a column reference by position number select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END from d2459_A1 t1 left outer join d2459_B1 t2 ON t2.id = t1.ref union all select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END from d2459_A2 t1 left outer join d2459_B2 t2 ON t2.id = t1.ref order by 2; ID |2 ---------------- b |1 a |4 c |5 d |8 ij> -- should fail, because qualified column references can't refer to UNIONs select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END from d2459_A1 t1 left outer join d2459_B1 t2 ON t2.id = t1.ref union all select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END from d2459_A2 t1 left outer join d2459_B2 t2 ON t2.id = t1.ref order by t1.id; ERROR 42877: A qualified column name 'T1.ID' is not allowed in the ORDER BY clause. ij> -- should fail, because the union's results can't be referenced this way select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END from d2459_A1 t1 left outer join d2459_B1 t2 ON t2.id = t1.ref union all select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END from d2459_A2 t1 left outer join d2459_B2 t2 ON t2.id = t1.ref order by CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END; ERROR 42878: The ORDER BY clause of a SELECT UNION statement only supports unqualified column references and column position numbers. Other expressions are not currently supported. ij> -- should fail, because this column is not in the result: select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END from d2459_A1 t1 left outer join d2459_B1 t2 ON t2.id = t1.ref union all select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END from d2459_A2 t1 left outer join d2459_B2 t2 ON t2.id = t1.ref order by value; ERROR 42X78: Column 'VALUE' is not in the result of the query expression. ij> -- ought to work, but currently fails, due to implementation restrictions: select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END from d2459_A1 t1 left outer join d2459_B1 t2 ON t2.id = t1.ref union all select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END from d2459_A2 t1 left outer join d2459_B2 t2 ON t2.id = t1.ref order by CASE WHEN id IS NOT NULL THEN id ELSE 2 END; ERROR 42878: The ORDER BY clause of a SELECT UNION statement only supports unqualified column references and column position numbers. Other expressions are not currently supported. ij> -- Also ought to work, but currently fails due to implementation restrictions: select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END from d2459_A1 t1 left outer join d2459_B1 t2 ON t2.id = t1.ref union all select t1.id, CASE WHEN t2.value IS NOT NULL THEN t2.value ELSE t1.value END from d2459_A2 t1 left outer join d2459_B2 t2 ON t2.id = t1.ref order by id || 'abc'; ERROR 42878: The ORDER BY clause of a SELECT UNION statement only supports unqualified column references and column position numbers. Other expressions are not currently supported. ij> -- A number of simpler test cases investigating how the result set of the -- UNION is constructed. If both children have identical result column names, -- then the UNION result set's columns have the same names. Otherwise the -- UNION result set's columns have generated names, and can only be -- referred to by column position. Note als othat the matching of columns -- for the result set of the UNION is done by column position, not by name select id from D2459_A1 union select ref from D2459_A2; 1 ---- a b g NULL ij> select id from D2459_A1 union select ref from D2459_A2 order by id; ERROR 42X78: Column 'ID' is not in the result of the query expression. ij> select id from D2459_A1 union select ref from D2459_A2 order by 1; 1 ---- a b g NULL ij> select id i from D2459_A1 union select ref i from D2459_A2 order by i; I ---- a b g NULL ij> select id i from D2459_A1 union select ref j from D2459_A2; 1 ---- a b g NULL ij> select id i from D2459_A1 union select ref j from D2459_A2 order by i; ERROR 42X78: Column 'I' is not in the result of the query expression. ij> select id i from D2459_A1 union select ref j from D2459_A2 order by 1; 1 ---- a b g NULL ij> select id from D2459_A1 union select id from D2459_A2 order by D2459_A1.id; ERROR 42877: A qualified column name 'D2459_A1.ID' is not allowed in the ORDER BY clause. ij> select id from D2459_A1 union select id from D2459_A2 order by id||'abc'; ERROR 42878: The ORDER BY clause of a SELECT UNION statement only supports unqualified column references and column position numbers. Other expressions are not currently supported. ij> select * from D2459_A1 union select id, value, ref from D2459_A2 order by value; ID |VALUE |REF --------------------- b |1 |NULL c |3 |g d |8 |NULL a |12 |e ij> select id, value, ref from D2459_A1 union select * from D2459_A2 order by 2; ID |VALUE |REF --------------------- b |1 |NULL c |3 |g d |8 |NULL a |12 |e ij> select id, id i from D2459_A1 union select id j, id from D2459_A2 order by id; ERROR 42X78: Column 'ID' is not in the result of the query expression. ij> select id, id i from D2459_A1 union select id j, id from D2459_A2 order by 2; 1 |2 --------- a |a b |b c |c d |d ij> select id, ref from D2459_A1 union select ref, id from D2459_A2; 1 |2 --------- a |e b |NULL g |c NULL|d ij> select id i, ref j from D2459_A1 union select ref i, id j from D2459_A2; I |J --------- a |e b |NULL g |c NULL|d ij> -- Some test cases for DERBY-2351. The issue in DERBY-2351 involves whether -- pulled-up ORDER BY columns appear in the result set or not, and how -- DISCTINCT interacts with that decision. The point is that DISTINCT should -- apply only to the columns specified by the user in the result column list, -- not to the extra columns pulled up into the result by the ORDER BY. This -- means that some queries should throw an error, but due to DERBY-2351 -- the queries instead display erroneous results. create table t1 (c1 int, c2 varchar(10)); 0 rows inserted/updated/deleted ij> create table t2 (t2c1 int); 0 rows inserted/updated/deleted ij> insert into t1 values (3, 'a'), (4, 'c'), (2, 'b'), (1, 'c'); 4 rows inserted/updated/deleted ij> insert into t2 values (4), (3); 2 rows inserted/updated/deleted ij> -- This query should return 4 distinct rows, ordered by column c1: select distinct c1, c2 from t1 order by c1; C1 |C2 ---------------------- 1 |c 2 |b 3 |a 4 |c ij> -- This statement is legitimate. Even though c1+1 is not distinct, c1 is: select distinct c1, c2 from t1 order by c1+1; C1 |C2 ---------------------- 1 |c 2 |b 3 |a 4 |c ij> -- DERBY-2351 causes this statement to return 4 rows, which it should -- instead show an error. Note that the rows returned are not distinct! select distinct c2 from t1 order by c1; ERROR 42879: The ORDER BY clause may not contain column 'C1', since the query specifies DISTINCT and that column does not appear in the query result. ij> -- This query should return 3 distinct rows, ordered by column c2 select distinct c2 from t1 order by c2; C2 ---------- a b c ij> -- This query should work because * will be expanded to include c2: select distinct * from t1 order by c2; C1 |C2 ---------------------- 3 |a 2 |b 1 |c 4 |c ij> -- After the * is expanded, the query contains c1, so this is legitimate: select distinct * from t1 order by c1+1; C1 |C2 ---------------------- 1 |c 2 |b 3 |a 4 |c ij> -- This query also should not work because the order by col is not in result: select distinct t1.* from t1, t2 where t1.c1=t2.t2c1 order by t2c1; ERROR 42879: The ORDER BY clause may not contain column 'T2C1', since the query specifies DISTINCT and that column does not appear in the query result. ij> -- But without the distinct it should be fine: select t1.* from t1, t2 where t1.c1=t2.t2c1 order by t2c1; C1 |C2 ---------------------- 3 |a 4 |c ij> drop table t1; 0 rows inserted/updated/deleted ij> -- some test cases for DERBY-4371: This issue addresses the situations where -- the order by clause contains an expression such as j*2, i/j etc. when the distict -- is specified in the select clause. The rule is, a query will be a valid one if, -- 1) the exact expression can be found in the select clause OR -- 2) all the ColumnReferences of the expression can be found in the select clause create table t1 (c1 int, c2 int); 0 rows inserted/updated/deleted ij> insert into t1 values (3, 1), (4, 2), (2, 1), (1, 3); 4 rows inserted/updated/deleted ij> -- Following query should be failed since neither C1*2 nor C1 is there -- in the select clause select distinct C2 from t1 order by C1*2; ERROR 42879: The ORDER BY clause may not contain column 'C1', since the query specifies DISTINCT and that column does not appear in the query result. ij> -- Following query should be failed since neither C1*2 nor C1 is there -- in the select clause select distinct C2,C1*3 from t1 order by C1*2; ERROR 42879: The ORDER BY clause may not contain column 'C1', since the query specifies DISTINCT and that column does not appear in the query result. ij> -- Following query should be failed since neither C1*3 nor C1 is there -- in the select clause select distinct C2 from t1 order by C2, C1*3; ERROR 42879: The ORDER BY clause may not contain column 'C1', since the query specifies DISTINCT and that column does not appear in the query result. ij> -- Following query should be failed since C1/C2 or (C1 & C2) is not there -- in the select clause select distinct C1*C2 from t1 order by C1/C2; ERROR 42879: The ORDER BY clause may not contain column 'C1', since the query specifies DISTINCT and that column does not appear in the query result. ij> -- Following query should be failed since neither -C2 nor C2 is there -- in the select clause select distinct C1 from t1 order by -C2; ERROR 42879: The ORDER BY clause may not contain column 'C2', since the query specifies DISTINCT and that column does not appear in the query result. ij> -- Following query should be failed since neither C1/C2 nor C2 is there -- in the select clause select distinct C1 from t1 order by C1/C2; ERROR 42879: The ORDER BY clause may not contain column 'C2', since the query specifies DISTINCT and that column does not appear in the query result. ij> -- This query is valid since C2 is there in the select clause though -- C2*2 itself not. select distinct C1,C2 from t1 order by C2*2; C1 |C2 ----------------------- 2 |1 3 |1 4 |2 1 |3 ij> -- This query is valid since C2*2 is there in the select clause. select distinct C1,C2*2 from t1 order by C2*2; C1 |2 ----------------------- 2 |2 3 |2 4 |4 1 |6 ij> -- This query is valid since both C1 & C2 is there in the select clause though -- neither C1*2 nor C2*3 is there. select distinct C1,C2 from t1 order by C1*2,C2*3; C1 |C2 ----------------------- 1 |3 2 |1 3 |1 4 |2 ij> -- This query is valid since the select all is there though -- neither C2*2 nor C1*3 is there. select distinct * from t1 order by C2*2, C1*3; C1 |C2 ----------------------- 2 |1 3 |1 4 |2 1 |3 ij> -- This query is valid since order by column not involve any columns select distinct C1 from t1 order by 1+1; C1 ----------- 1 2 3 4 ij> drop table t1; 0 rows inserted/updated/deleted ij> create table person (name varchar(10), age int); 0 rows inserted/updated/deleted ij> insert into person values ('John', 10); 1 row inserted/updated/deleted ij> insert into person values ('John', 30); 1 row inserted/updated/deleted ij> insert into person values ('Mary', 20); 1 row inserted/updated/deleted ij> -- DERBY-2351 causes this statement to display 3 rows, when it should -- instead show an error. Again, note that the rows returned are not distinct. SELECT DISTINCT name FROM person ORDER BY age; ERROR 42879: The ORDER BY clause may not contain column 'AGE', since the query specifies DISTINCT and that column does not appear in the query result. ij> -- This query should return two rows, ordered by name. SELECT DISTINCT name FROM person ORDER BY name; NAME ---------- John Mary ij> -- This query should return two rows, ordered by name descending: SELECT DISTINCT name FROM person ORDER BY name desc; NAME ---------- Mary John ij> -- Ordering by an expression involving name is legitimate: select distinct name from person order by upper(name); NAME ---------- John Mary ij> -- Ordering by an expression involving an unselected column is not. However, -- Derby does not currently enforce this restriction. Note that the answer -- that Derby returns is incorrect: Derby returns two rows with duplicate -- 'name' values. This is because Derby currently implicitly includes the -- 'age' column into the 'distinct' processing due to its presence in the -- ORDER BY clause. DERBY-2351 and DERBY-3373 discuss this situation in -- more detail. -- The above issue is corrected under DERBY-4371 select distinct name from person order by age*2; ERROR 42879: The ORDER BY clause may not contain column 'AGE', since the query specifies DISTINCT and that column does not appear in the query result. ij> -- Some test cases involving column aliasing: select distinct name as first_name from person order by name; FIRST_NAME ---------- John Mary ij> select distinct name as first_name from person order by first_name; FIRST_NAME ---------- John Mary ij> select distinct person.name from person order by name; NAME ---------- John Mary ij> select distinct name as first_name from person order by person.name; FIRST_NAME ---------- John Mary ij> select distinct name as age from person order by age; AGE ---------- John Mary ij> select distinct name as age from person order by person.age; ERROR 42879: The ORDER BY clause may not contain column 'AGE', since the query specifies DISTINCT and that column does not appear in the query result. ij> select distinct name, name from person order by name; NAME |NAME --------------------- John |John Mary |Mary ij> select distinct name, name as first_name from person order by name; NAME |FIRST_NAME --------------------- John |John Mary |Mary ij> select distinct name, name as first_name from person order by 2; NAME |FIRST_NAME --------------------- John |John Mary |Mary ij> -- Some test cases combining column aliasing with table aliasing: select distinct name nm from person p order by name; NM ---------- John Mary ij> select distinct name nm from person p order by nm; NM ---------- John Mary ij> select distinct name nm from person p order by p.name; NM ---------- John Mary ij> select distinct name nm from person p order by person.name; ERROR 42X04: Column 'PERSON.NAME' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'PERSON.NAME' is not a column in the target table. ij> select distinct name nm from person p order by person.nm; ERROR 42X04: Column 'PERSON.NM' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'PERSON.NM' is not a column in the target table. ij> select distinct name nm from person p order by p.nm; ERROR 42X04: Column 'P.NM' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'P.NM' is not a column in the target table. ij> create table pets (name varchar(10), age int); 0 rows inserted/updated/deleted ij> insert into pets values ('Rover', 3), ('Fido', 5), ('Buster', 1); 3 rows inserted/updated/deleted ij> select distinct name from person union select distinct name from pets order by name; NAME ---------- Buster Fido John Mary Rover ij> select distinct name from person, pets order by name; ERROR 42X03: Column name 'NAME' is in more than one table in the FROM list. ij> select distinct person.name as person_name, pets.name as pet_name from person,pets order by name; ERROR 42X79: Column name 'NAME' appears more than once in the result of the query expression. ij> select distinct person.name as person_name, pets.name from person,pets order by name; ERROR 42X79: Column name 'NAME' appears more than once in the result of the query expression. ij> select distinct person.name as person_name, pets.name from person,pets order by person.name; PERSON_NA&|NAME --------------------- John |Buster John |Fido John |Rover Mary |Buster Mary |Fido Mary |Rover ij> select distinct person.name as name, pets.name as pet_name from person,pets order by name; ERROR 42X79: Column name 'NAME' appears more than once in the result of the query expression. ij> select distinct person.name as name, pets.name as pet_name from person,pets order by pets.name; NAME |PET_NAME --------------------- John |Buster Mary |Buster John |Fido Mary |Fido John |Rover Mary |Rover ij> -- Include some of the error cases from above without the DISTINCT -- specification to investigate how that affects the behavior: select name as age from person order by person.age; AGE ---------- John Mary John ij> select name from person, pets order by name; ERROR 42X03: Column name 'NAME' is in more than one table in the FROM list. ij> select person.name as person_name, pets.name as pet_name from person,pets order by name; ERROR 42X79: Column name 'NAME' appears more than once in the result of the query expression. ij> select person.name as person_name, pets.name from person,pets order by person.name; PERSON_NA&|NAME --------------------- John |Buster John |Fido John |Rover John |Buster John |Fido John |Rover Mary |Buster Mary |Fido Mary |Rover ij> select person.name as person_name, pets.name from person,pets order by name; ERROR 42X79: Column name 'NAME' appears more than once in the result of the query expression. ij> select person.name as name, pets.name as pet_name from person,pets order by name; ERROR 42X79: Column name 'NAME' appears more than once in the result of the query expression. ij> drop table person; 0 rows inserted/updated/deleted ij> drop table pets; 0 rows inserted/updated/deleted ij> create table d2887_types( id int, c1_smallint smallint, c2_int integer, c3_bigint bigint, c4_real real, c5_float float, c6_numeric numeric(10,2), c7_char char(10), c8_date date, c9_time time, c10_timestamp timestamp, c11_varchar varchar(50) ); 0 rows inserted/updated/deleted ij> -- Tests to demonstrate proper operation of (DERBY-2887) insert into d2887_types values (1, 1, 1, 1, 1.0, 1.0, 1.0, 'one', '1991-01-01', '11:01:01', '1991-01-01 11:01:01', 'one'), (2, 2, 2, 2, 2.0, 2.0, 2.0, 'two', '1992-02-02', '12:02:02', '1992-02-02 12:02:02', 'two'), (3, 3, 3, 3, 3.0, 3.0, 3.0, 'three', '1993-03-03', '03:03:03', '1993-03-03 03:03:03', 'three'), (4, null, null, null, null, null, null, null, null, null, null, null); 4 rows inserted/updated/deleted ij> -- Demonstrate various combinations of NULLS FIRST, NULLS LAST, and default, -- with various combinations of ASC, DESC, and default, with various -- data types. These should all succeed, should all produce output with the -- non-null values in the proper order, and should all produce output with -- the null values ordered as specified. If null ordering was not specified, -- the default Derby behavior is nulls are last if asc, first if desc. select id, c1_smallint from d2887_types order by c1_smallint nulls first; ID |C1_SM& ------------------ 4 |NULL 1 |1 2 |2 3 |3 ij> select id, c2_int from d2887_types order by c2_int nulls last; ID |C2_INT ----------------------- 1 |1 2 |2 3 |3 4 |NULL ij> select id, c3_bigint from d2887_types order by c3_bigint asc; ID |C3_BIGINT -------------------------------- 1 |1 2 |2 3 |3 4 |NULL ij> select id, c4_real from d2887_types order by c4_real desc; ID |C4_REAL ------------------------- 4 |NULL 3 |3.0 2 |2.0 1 |1.0 ij> select id, c5_float from d2887_types order by c5_float asc nulls last; ID |C5_FLOAT ---------------------------------- 1 |1.0 2 |2.0 3 |3.0 4 |NULL ij> select id, c6_numeric from d2887_types order by c6_numeric desc nulls last; ID |C6_NUMERIC ------------------------ 3 |3.00 2 |2.00 1 |1.00 4 |NULL ij> select id, c7_char from d2887_types order by c7_char asc nulls first; ID |C7_CHAR ---------------------- 4 |NULL 1 |one 3 |three 2 |two ij> select id, c8_date from d2887_types order by c8_date desc nulls first; ID |C8_DATE ---------------------- 4 |NULL 3 |1993-03-03 2 |1992-02-02 1 |1991-01-01 ij> drop table d2887_types; 0 rows inserted/updated/deleted ij> -- DERBY-2352 involved a mismatch between the return type of the SUBSTR -- method and the expected type of the result column. During compilation, -- bind processing was computing that the SUBSTR would return a CHAR, but -- at execution time it actually returned a VARCHAR, resulting in a type -- mismatch detected by the sorter. Since the TRIM functions are very -- closely related to the SUBSTR function, we include a few tests of -- those functions in the test case. create table d2352 (c int); 0 rows inserted/updated/deleted ij> insert into d2352 values (1), (2), (3); 3 rows inserted/updated/deleted ij> select substr('abc', 1) from d2352 order by substr('abc', 1); 1 ---- abc abc abc ij> select substr('abc', 1) from d2352 group by substr('abc', 1); 1 ---- abc ij> select ltrim('abc') from d2352 order by ltrim('abc'); 1 ---- abc abc abc ij> select ltrim('abc') from d2352 group by ltrim('abc'); 1 ---- abc ij> select trim(trailing ' ' from 'abc') from d2352 order by trim(trailing ' ' from 'abc'); 1 ---- abc abc abc ij> select trim(trailing ' ' from 'abc') from d2352 group by trim(trailing ' ' from 'abc'); 1 ---- 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> -- See also Derby5005.java which should be merged with this test when -- it gets converted to JUnit. ; ij>