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 <null ordering> (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.

-- DERBY-6009: forbid expression when using ORDER BY with VALUES
values 1 order by 1+0;
ERROR 4287B: In this context, the ORDER BY clause may only specify a column number.
ij> values 1,2 order by 1+0;
ERROR 4287B: In this context, the ORDER BY clause may only specify a column number.
ij> (values 1 order by 1+0) union (values 1,2 order by 1);
ERROR 4287B: In this context, the ORDER BY clause may only specify a column number.
ij> -- OK:
(values 1 order by 1) union (values 1,2 order by 1);
1          
-----------
1          
2          
ij> values (1,-1),(3,-3),(2,-2) order by 1;
1          |2          
-----------------------
1          |-1         
2          |-2         
3          |-3         
ij> values (1,-1),(3,-3),(2,-2) order by 2;
1          |2          
-----------------------
3          |-3         
2          |-2         
1          |-1         
ij> -- DERBY-6027: ORDER BY a cast expression gives NPE 
values 1 order by int(1);
ERROR 4287B: In this context, the ORDER BY clause may only specify a column number.
ij> -- expect error
values 1 order by 1;
1          
-----------
1          
ij> -- ok, order by column #

-- check that int(1) doesn't do sorting with SELECT, it's just a constant expression
create table d6027(i int);
0 rows inserted/updated/deleted
ij> insert into d6027 values (2,1,3);
ERROR 42802: The number of values assigned is not the same as the number of specified or implied columns.
ij> select i from d6027 order by 1;
I          
-----------
ij> -- OK, sort
select i from d6027 order by int(1);
I          
-----------
ij> -- OK, don't sort

drop table d6027;
0 rows inserted/updated/deleted
ij>