orderby.out

derby database source code.good for you.

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', 'xxxxxxFILTERED-TIMESTAMPxxxxx', 'theend', 2222222222222);
1 row inserted/updated/deleted
ij> insert into missed values (1,1.2e4, '1992-01-01', '23:01:01', 'xxxxxxFILTERED-TIMESTAMPxxxxx', 'theend', 3333333333333);
1 row inserted/updated/deleted
ij> insert into missed values (2,1.0e4, '1992-01-01', '20:01:01', 'xxxxxxFILTERED-TIMESTAMPxxxxx', 'theend', 4444444444444);
1 row inserted/updated/deleted
ij> insert into missed values (2,1.0e4, '1992-01-01', '20:01:01', 'xxxxxxFILTERED-TIMESTAMPxxxxx', 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       
-----------------------------------
xxxxxxFILTERED-TIMESTAMPxxxxx|20:01:01
xxxxxxFILTERED-TIMESTAMPxxxxx|20:01:01
xxxxxxFILTERED-TIMESTAMPxxxxx|23:01:01
xxxxxxFILTERED-TIMESTAMPxxxxx|23:01:01
ij> select l from missed order by l;
L