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. ij> select i, v from obt by i; ERROR 42X01: Syntax error: Encountered "by" at line 1, column 22. ij> -- . show order, by are reserved keywords select order from obt; ERROR 42X01: Syntax error: Encountered "order" at line 2, column 8. ij> select by from obt; ERROR 42X01: Syntax error: Encountered "by" at line 1, column 8. 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> -- . order by only allowed on cursor spec, not subquerys (error) select v from obt where i in (select i from obt2 order by i); ERROR 42X01: Syntax error: Encountered "order" at line 2, column 50. ij> select v from obt where i = (select i from obt2 order by i); ERROR 42X01: Syntax error: Encountered "order" at line 1, column 49. ij> select v from (select i,v from obt2 order by i); ERROR 42X01: Syntax error: Encountered "order" at line 1, column 37. 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. 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> 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. select distinct name from person order by age*2; NAME ---------- John Mary John 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>