orderby.sql

derby database source code.good for you.

-- 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;

-- . order by on values
values (1,0,1),(1,0,0),(0,0,1),(0,1,0) order by 1,2,3;
values (1,0,1),(1,0,0),(0,0,1),(0,1,0) order by 1,3;
values (1,0,1),(1,0,0),(0,0,1),(0,1,0) order by 2,1;
values (1,0,1),(1,0,0),(0,0,1),(0,1,0) order by 2;
-- . order by on position < 1, > range (error)
values (1,0,1),(1,0,0),(0,0,1),(0,1,0) order by 0;
values (1,0,1),(1,0,0),(0,0,1),(0,1,0) order by 4;
-- . order by doesn't see generated names
values (1,0,1),(1,0,0),(0,0,1),(0,1,0);
values (1,0,1),(1,0,0),(0,0,1),(0,1,0) order by "SQLCol1";
values (1,0,1),(1,0,0),(0,0,1),(0,1,0) order by "SQLCol2";

values (1,0,1),(1,0,0),(0,0,1),(0,1,0) order by 1,1,2,3;

-- rollback should release the prepared statements
rollback;

-- . order by on select
-- . order by with duplicate rows in source
set schema app;
create table obt (i int, v varchar(40));
insert into obt (i) values (null);
insert into obt values (1, 'hello');
insert into obt values (2, 'planet');
insert into obt values (1, 'world');
insert into obt values (3, 'hello');

-- save the data we've created
commit;

select * from obt order by i;
select * from obt order by v;
-- . order by all select columns
select * from obt order by i,v;
select * from obt order by v,i;
-- . order by asc/desc mix
select * from obt order by v desc, i asc;
-- reverse prior order
select * from obt order by i asc, v desc;
-- . order by with duplicates but different asc/desc attributes (ok)
select * from obt order by i asc, i desc;

select * from obt order by i, v, i;
select v from obt order by i, v, i;
select v from obt order by i desc, v, i;

-- . order by on position < 1, > range (error)
select * from obt order by 1, 0;
select * from obt order by 1,2,3,4,5,6,7,8,9;
select * from obt order by 32767;

-- rollback should release the prepared statements
rollback ;

-- . order by on union all
create table obt2 (i2 int, v varchar(40));
insert into obt2 values (3, 'hello'), (4, 'planet'), (1, 'shoe'), (3, 'planet');

-- save the data we've created
commit ;

select * from obt union all select * from obt2 order by v;

select * from obt union all select * from obt order by i;

select * from obt union all select * from obt order by i, i;

-- . order by on union with differing column names on sources. Error
select * from obt union all select * from obt2 order by i;

select * from obt union all values (1,'hello') order by i;

values (1,'hello') union all select * from obt order by i;

-- . order by can not see generated names, though OK by position
values (1,'hello') union all select * from obt; 

values (1,'hello') union all select * from obt order by "SQLCol1"; 

values (1,'hello') union all select * from obt order by 1;

values (1,'hello') union all select * from obt order by 1, 1;

-- rollback should release the prepared statements
rollback ;

select i from obt union all values (1) order by 1;

-- sees noname on both sides although second side is named
values (1) union all select i from obt order by i;

-- rollback should release the prepared statements
rollback ;

-- i2's name is hidden by obt, fails
select * from obt union all select * from obt2 order by i2;

-- . order by position/name mix
select * from obt order by 1,i;
select * from obt order by 1,v;

-- . order by with duplicate positions
select * from obt order by 1,2,1;

-- . order by with duplicate names
select * from obt order by v,i,v;

-- . order by name gets select name, not underlying name
select i as i2, v from obt order by i2;
-- error, i is not seen by order by
select i as i2, v from obt order by i;

-- rollback should release the prepared statements
rollback ;

-- . order without by (error)
select i, v from obt order i;
select i, v from obt by i;

-- . show order, by are reserved keywords
select order from obt;
select by from obt;

-- . order by on column not in query (error)
select i from obt order by c;

-- . order by on column not in select, in table (error)
select i from obt order by v;

-- . order by on expression (allowed)
select i from obt order by i+1;

-- . order by on qualified column name, incorrect correlation name (not allowed)
select i from obt t order by obt.i;

-- . order by on qualified column name, incorrect column name (not allowed)
select i from obt t order by obt.notexists;

-- . order by on qualified column name
create table t1(c1 int);
create table t2(c1 int);
create table t3(c3 int);
insert into t1 values 2, 1;
insert into t2 values 4, 3;
insert into t3 values 6, 5;
select t1.c1, t2.c1 from t1, t2 order by t1.c1;
select t1.c1, t2.c1 from t1, t2 order by t2.c1;
select t1.c1, t2.c1 from t1, t1 t2 order by t2.c1;
select t1.c1, t2.c1 from t1, t1 t2 order by t1.c1;
-- 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;
select * from obt union all select * from obt2 order by obt.v;
select * from obt union all select * from obt2 order by obt2.v;
select * from obt union all select * from obt2 order by abc.v;
select * from t1 inner join t2 on 1=1 order by t1.c1;
select * from t1 inner join t2 on 1=1 order by t2.c1;
select c1 from t1 order by app.t1.c1;
select c1 from app.t1 order by app.t1.c1;
select c1 from app.t1 order by t1.c1;
select c1 from app.t1 order by c1;
select c1 from app.t1 c order by c1;
select c1 from app.t1 c order by c.c1;
select c1 from t1 order by c1;

-- negative
-- shouldn't find exposed name
select c1 from t1 union select c3 from t3 order by t3.c3;
select c1 from t1 union select c3 from t3 order by asdf.c3;
select c1 from t1 order by sys.t1.c1;
select c1 from app.t1 order by sys.t1.c1;
select c1 from t1 c order by app.c.c1;
select c1 from app.t1 c order by app.t1.c1;

-- a is not a column in t1
select 1 as a from t1 order by t1.a;

-- t3.c1 does not exist
select * from t1, t3 order by t3.c1;

-- rollback should release the prepared statements
rollback ;

-- . order by on join
select obt.i, obt2.i2+1, obt2.v from obt, obt2 order by 2, 3;

select obt.i, obt2.i2+1, obt2.v from obt2, obt where obt.i=obt2.i2 order by 2, 3;

-- . order by with spaces at end of values
values 'hello ', 'hello    ', 'hello  ', 'hello' order by 1;

-- . order by on select items that are expressions
select i+1, v, {fn length(v)} from obt order by 2, 1 desc, 3;

-- rollback should release the prepared statements
rollback ;

-- . redundant order by on distinct, ?non-redundant (different ordering)
select distinct i from obt order by i;
select distinct i,v from obt order by v;
select distinct i,v from obt order by v desc, i desc, v desc;

-- . redundant order by on distinct, redundant (subset/prefix)
select distinct i,v from obt order by i;

-- . redundant order by on index scan (later)

-- rollback should release the prepared statements
rollback ;

-- . order by with empty source, nulls in source, etc.
delete from obt;
select * from obt order by 1;
select * from obt order by v;
rollback ;

-- . order by with close values (doubles)
create table d (d double precision);
insert into d values 1e-300,2e-300;
select d,d/1e5 as dd from d order by dd,d;
rollback ;

-- . order by with long values (varchars)
create table v (v varchar(1200));