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

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

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

-- rollback should release the prepared statements
rollback;

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

-- save the data we've created
commit;

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

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

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

-- rollback should release the prepared statements
rollback ;

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

-- save the data we've created
commit ;

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

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

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

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

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

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

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

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

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

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

-- rollback should release the prepared statements
rollback ;

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

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

-- rollback should release the prepared statements
rollback ;

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

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

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

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

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

-- rollback should release the prepared statements
rollback ;

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

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

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

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

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

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

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

-- . order by on qualified column name
create table t1(c1 int);
create table t2(c1 int);
create table t3(c3 int);
insert into t1 values 2, 1;
insert into t2 values 4, 3;
insert into t3 values 6, 5;
select t1.c1, t2.c1 from t1, t2 order by t1.c1;
select t1.c1, t2.c1 from t1, t2 order by t2.c1;
select t1.c1, t2.c1 from t1, t1 t2 order by t2.c1;
select t1.c1, t2.c1 from t1, t1 t2 order by t1.c1;
-- bug 5716 - qualified column name not allowed in order by when union/union all is used - following 4 test cases for that
select c1 from t1 union select c3 as c1 from t3 order by t1.c1;
select * from obt union all select * from obt2 order by obt.v;
select * from obt union all select * from obt2 order by obt2.v;
select * from obt union all select * from obt2 order by abc.v;
select * from t1 inner join t2 on 1=1 order by t1.c1;
select * from t1 inner join t2 on 1=1 order by t2.c1;
select c1 from t1 order by app.t1.c1;
select c1 from app.t1 order by app.t1.c1;
select c1 from app.t1 order by t1.c1;
select c1 from app.t1 order by c1;
select c1 from app.t1 c order by c1;
select c1 from app.t1 c order by c.c1;
select c1 from t1 order by c1;

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

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

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

-- rollback should release the prepared statements
rollback ;

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

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

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

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

-- rollback should release the prepared statements
rollback ;

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

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

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

-- rollback should release the prepared statements
rollback ;

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

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

-- . order by with long values (varchars)
create table v (v varchar(1200));
insert into v values 'itsastart';
insert into v values 'hereandt';
update v set v = v || v || v;
update v set v = v || v || v;
update v set v = v || v;
update v set v = v || v;
update v set v = v || v;
update v set v = v || v;
update v set v = v || v;
select v from v order by v desc;
rollback ;
drop table v;

-- . order by on all data types
create table missed (s smallint, r real, d date, t time, ts timestamp, c char(10), l bigint);
insert into missed values (1,1.2e4, '1992-01-01','23:01:01', '1993-02-04 12:02:00.001', 'theend', 2222222222222);
insert into missed values (1,1.2e4, '1992-01-01', '23:01:01', '1993-02-04 12:02:00.001', 'theend', 3333333333333);
insert into missed values (2,1.0e4, '1992-01-01', '20:01:01', '1997-02-04 12:02:00.001', 'theend', 4444444444444);
insert into missed values (2,1.0e4, '1992-01-01', '20:01:01', '1997-02-04 12:02:00.001', null,     2222222222222);
select s from missed order by s;
select r from missed order by r;
select d,c from missed order by c,d;
select ts,t from missed order by ts desc, t;
select l from missed order by l;
select l from missed order by l desc;
rollback ;

-- . order by on char column
create table ut (u char(10));
insert into ut values (null);
insert into ut values (cast ('hello' as char(10)));
insert into ut values ('world');
insert into ut values ('hello');
insert into ut values ('world  ');
-- rollback should release the prepared statements
rollback ;

-- . order by and explicit for update (no, some cols)
get cursor c1 as 'select i from obt order by i for update of v';

-- . order by and explicit read only (ok)
get cursor c1 as 'select i from obt order by i for read only';
next c1;
close c1;

-- . order by is implicitly read only
get cursor c1 as 'select i from obt order by i';
next c1;
-- error
update obt set v='newval' where current of c1;
close c1;
-- no rows
select v from obt where v='newval';

-- rollback should release the prepared statements
rollback ;

-- . order by only allowed on cursor spec, not subquerys (error) 
select v from obt where i in (select i from obt2 order by i);

select v from obt where i = (select i from obt2 order by i);

select v from (select i,v from obt2 order by i);

-- rollback should release the prepared statements
rollback ;

-- 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));
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)));
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)));
select * from tab1 order by 1;
rollback;

-- bug 2769 (correlation columns, group by and order by)
create table bug2769(c1 int, c2 int);
insert into bug2769 values (1, 1), (1, 2), (3, 2), (3, 3);
select a.c1, sum(a.c1) from bug2769 a group by a.c1 order by a.c1;
select bug2769.c1 as x, sum(bug2769.c1) as y from bug2769 group by bug2769.c1 order by bug2769.c1;
select bug2769.c1 as x, sum(bug2769.c1) as y from bug2769 group by bug2769.c1 order by x;
select c1 as x, c2 as y from bug2769 group by bug2769.c1, bug2769.c2 order by c1 + c2;
select c1 as x, c2 as y from bug2769 group by bug2769.c1, bug2769.c2 order by -(c1 + c2);
rollback;

-- reset autocommit
autocommit on;

-- cleanup
drop table obt;
drop table obt2;

create table t (a int, b int, c int);
insert into t values (1, 2, null), (2, 3, null), (3, 0, null), (1, 3, null);

select * from t order by a;
select * from t order by a, a;
select * from t order by a, a, a;
select * from t order by a, b;

select a, b, c from t order by a, a;
select a, b, c from t order by a, b;

select a, c from t order by b; 
select a, c from t order by b, b; 
select a, b, c from t order by b; 

select a from t order by b, c;
select a, c from t order by b, c;
select a, c from t order by b, c, b, c;
select a, b, c from t order by b, c;
select b, c from t order by app.t.a;


--Test addtive expression in order clause

create table test_word(value varchar(32));
insert into test_word(value) values('anaconda');
insert into test_word(value) values('America');
insert into test_word(value) values('camel');
insert into test_word(value) values('Canada');

select * from test_word order by value;
select * from test_word order by upper(value);

drop table test_word;

create table test_number(value integer);
insert into test_number(value) values(-1);
insert into test_number(value) values(0);
insert into test_number(value) values(1);
insert into test_number(value) values(2);
insert into test_number(value) values(3);
insert into test_number(value) values(100);
insert into test_number(value) values(1000);
select * from test_number order by value;
select * from test_number order by value + 1;
select * from test_number order by value - 1;
select * from test_number order by value * 1;
select * from test_number order by value / 1;
select * from test_number order by 1 + value;
select * from test_number order by 1 - value;
select * from test_number order by 1 * value;
select * from test_number where value <> 0 order by 6000 / value;
select * from test_number order by -1 + value;
select * from test_number order by -1 - value;
select * from test_number order by - 1 * value;
select * from test_number where value <> 0 order by - 6000 / value;
select * from test_number order by abs(value);
select * from test_number order by value desc;
select * from test_number order by value + 1 desc;
select * from test_number order by value - 1 desc;
select * from test_number order by value * 1 desc;
select * from test_number order by value / 1 desc;
select * from test_number order by 1 + value desc;
select * from test_number order by 1 - value desc;
select * from test_number order by 1 * value desc;
select * from test_number where value <> 0 order by 6000 / value desc;
select * from test_number order by -1 + value desc;
select * from test_number order by -1 - value desc;
select * from test_number order by - 1 * value desc;
select * from test_number where value <> 0 order by - 6000 / value desc;
select * from test_number order by abs(value) desc;
drop table test_number;
create table test_number2(value1 integer,value2 integer);
insert into test_number2(value1,value2) values(-2,2);
insert into test_number2(value1,value2) values(-1,2);
insert into test_number2(value1,value2) values(0,1);
insert into test_number2(value1,value2) values(0,2);
insert into test_number2(value1,value2) values(1,1);
insert into test_number2(value1,value2) values(2,1);
select * from test_number2 order by abs(value1),mod(value2,2);
drop table test_number2;
-- error case
select * from t order by d;
select t.* from t order by d;
select t.* from t order by t.d;
select s.* from t s order by s.d;

select *, d from t order by d;
select t.*, d from t order by d;
select t.*, d from t order by t.d;
select t.*, d from t order by app.t.d;
select s.*, d from t s order by s.d;
select t.*, t.d from t order by t.d;
select s.*, s.d from t s order by s.d;


select a, b, c from t order by d;
select a from t order by d;
select t.a from t order by t.d;
select s.a from t s order by s.d;

drop table t;

-- test fof using table correlation names 
select * from (values (2),(1)) as t(x) order by t.x;

create table ta(id int);
create table tb(id int,c1 int,c2 int);
insert into ta(id)  values(1);
insert into ta(id)  values(2);
insert into ta(id)  values(3);
insert into ta(id)  values(4);
insert into ta(id)  values(5);
insert into tb(id,c1,c2) values(1,5,3);
insert into tb(id,c1,c2) values(2,4,3);
insert into tb(id,c1,c2) values(3,4,2);
insert into tb(id,c1,c2) values(4,4,1);
insert into tb(id,c1,c2) values(5,4,2);
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;

drop table ta;
drop table tb;

-- 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);
insert into derby147 values (1, 2, 3, 4);
insert into derby147 values (6, 6, 6, 6);
select t.* from derby147 t;
select t.a,t.b,t.* from derby147 t order by b;
select t.a,t.b,t.b,t.c from derby147 t;
select t.a,t.b,t.b,t.c from derby147 t order by t.b;
-- 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;

create table derby147_a (a int, b int, c int, d int);
insert into derby147_a values (1,2,3,4), (40, 30, 20, 10), (1,50,3,50);
create table derby147_b (a int, b int);
insert into derby147_b values (4, 4), (10, 10), (2, 50);
-- 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;
select t1.a,t2.a from derby147_a t1, derby147_b t2 where t1.d=t2.b order by t2.a;
select a,a,b,c,d,a from derby147_a order by a;
select c+d as a, t1.a, t1.b+t1.c as a from derby147_a t1 order by 3, 2 desc;
-- 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;
select a, c+d as a from derby147_a;
-- The columns named "a" are NOT equivalent.
select a, c+d as a from derby147_a order by a;
select c+d as a, t1.a, t1.b+t1.c as b_plus_c from derby147_a t1 order by c+d;
-- 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;
select * from derby147_a order by c+2 desc, b asc, a desc;
-- 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;
-- 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;
