| -- |
| -- 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 ; |
| |
| select v from obt where i in (select i from obt2 order by i); |
| select v from obt where i in (select i2 from obt2 order by i2); |
| |
| -- more than one row in subquery: error |
| select v from obt where i = (select i from obt2 order by i); |
| |
| select v from (select i2,v from obt2 order by i2)s; |
| |
| -- 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; |
| |
| -- 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); |
| insert into derby1861 values (1, 2, 3, 4); |
| select * from derby1861 order by a, b, c+2; |
| select a, c from derby1861 order by a, b, c-4; |
| select t.* from derby1861 t order by t.a, t.b, t.c+2; |
| select a, b, a, c, d from derby1861 order by b, c-1, a; |
| select * from derby1861 order by a, c+2, a; |
| select * from derby1861 order by c-1, c+1, a, b, c * 6; |
| select t.*, t.c+2 from derby1861 t order by a, b, c+2; |
| select * from derby1861 order by 3, 1; |
| select * from derby1861 order by 2, a-2; |
| |
| -- 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)); |
| create table d2459_A2 ( id char(1) ,value int ,ref char(1)); |
| create table d2459_B1 ( id char(1) ,value int); |
| create table d2459_B2 ( id char(1) ,value int); |
| insert into d2459_A1 (id, value, ref) values ('b', 1, null); |
| insert into d2459_A1 (id, value, ref) values ('a', 12, 'e'); |
| insert into d2459_A2 (id, value, ref) values ('c', 3, 'g'); |
| insert into d2459_A2 (id, value, ref) values ('d', 8, null); |
| insert into d2459_B1 (id, value) values ('f', 2); |
| insert into d2459_B1 (id, value) values ('e', 4); |
| insert into d2459_B2 (id, value) values ('g', 5); |
| |
| -- 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 ; |
| |
| -- 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; |
| |
| -- 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; |
| |
| -- 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; |
| |
| -- 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; |
| |
| -- 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; |
| |
| -- 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; |
| |
| -- 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'; |
| |
| -- 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; |
| select id from D2459_A1 union select ref from D2459_A2 order by id; |
| select id from D2459_A1 union select ref from D2459_A2 order by 1; |
| select id i from D2459_A1 union select ref i from D2459_A2 order by i; |
| select id i from D2459_A1 union select ref j from D2459_A2; |
| select id i from D2459_A1 union select ref j from D2459_A2 order by i; |
| select id i from D2459_A1 union select ref j from D2459_A2 order by 1; |
| select id from D2459_A1 union select id from D2459_A2 order by D2459_A1.id; |
| select id from D2459_A1 union select id from D2459_A2 order by id||'abc'; |
| select * from D2459_A1 union select id, value, ref from D2459_A2 order by value; |
| select id, value, ref from D2459_A1 union select * from D2459_A2 order by 2; |
| select id, id i from D2459_A1 union select id j, id from D2459_A2 order by id; |
| select id, id i from D2459_A1 union select id j, id from D2459_A2 order by 2; |
| select id, ref from D2459_A1 union select ref, id from D2459_A2; |
| select id i, ref j from D2459_A1 union select ref i, id j from D2459_A2; |
| |
| -- 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)); |
| create table t2 (t2c1 int); |
| insert into t1 values (3, 'a'), (4, 'c'), (2, 'b'), (1, 'c'); |
| insert into t2 values (4), (3); |
| -- This query should return 4 distinct rows, ordered by column c1: |
| select distinct c1, c2 from t1 order by c1; |
| -- This statement is legitimate. Even though c1+1 is not distinct, c1 is: |
| select distinct c1, c2 from t1 order by c1+1; |
| -- 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; |
| -- This query should return 3 distinct rows, ordered by column c2 |
| select distinct c2 from t1 order by c2; |
| -- This query should work because * will be expanded to include c2: |
| select distinct * from t1 order by c2; |
| -- After the * is expanded, the query contains c1, so this is legitimate: |
| select distinct * from t1 order by c1+1; |
| -- 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; |
| -- But without the distinct it should be fine: |
| select t1.* from t1, t2 where t1.c1=t2.t2c1 order by t2c1; |
| drop table t1; |
| |
| -- 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); |
| insert into t1 values (3, 1), (4, 2), (2, 1), (1, 3); |
| -- 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; |
| -- 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; |
| -- 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; |
| -- 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; |
| -- Following query should be failed since neither -C2 nor C2 is there |
| -- in the select clause |
| select distinct C1 from t1 order by -C2; |
| -- 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; |
| -- 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; |
| -- This query is valid since C2*2 is there in the select clause. |
| select distinct C1,C2*2 from t1 order by C2*2; |
| -- 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; |
| -- 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; |
| -- This query is valid since order by column not involve any columns |
| select distinct C1 from t1 order by 1+1; |
| drop table t1; |
| |
| create table person (name varchar(10), age int); |
| insert into person values ('John', 10); |
| insert into person values ('John', 30); |
| insert into person values ('Mary', 20); |
| -- 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; |
| -- This query should return two rows, ordered by name. |
| SELECT DISTINCT name FROM person ORDER BY name; |
| -- This query should return two rows, ordered by name descending: |
| SELECT DISTINCT name FROM person ORDER BY name desc; |
| -- Ordering by an expression involving name is legitimate: |
| select distinct name from person order by upper(name); |
| -- 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; |
| -- Some test cases involving column aliasing: |
| select distinct name as first_name from person order by name; |
| select distinct name as first_name from person order by first_name; |
| select distinct person.name from person order by name; |
| select distinct name as first_name from person order by person.name; |
| select distinct name as age from person order by age; |
| select distinct name as age from person order by person.age; |
| select distinct name, name from person order by name; |
| select distinct name, name as first_name from person order by name; |
| select distinct name, name as first_name from person order by 2; |
| -- Some test cases combining column aliasing with table aliasing: |
| select distinct name nm from person p order by name; |
| select distinct name nm from person p order by nm; |
| select distinct name nm from person p order by p.name; |
| select distinct name nm from person p order by person.name; |
| select distinct name nm from person p order by person.nm; |
| select distinct name nm from person p order by p.nm; |
| create table pets (name varchar(10), age int); |
| insert into pets values ('Rover', 3), ('Fido', 5), ('Buster', 1); |
| select distinct name from person union select distinct name from pets order by name; |
| select distinct name from person, pets order by name; |
| select distinct person.name as person_name, pets.name as pet_name from person,pets order by name; |
| select distinct person.name as person_name, pets.name from person,pets order by name; |
| select distinct person.name as person_name, pets.name from person,pets order by person.name; |
| select distinct person.name as name, pets.name as pet_name from person,pets order by name; |
| select distinct person.name as name, pets.name as pet_name from person,pets order by pets.name; |
| -- 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; |
| select name from person, pets order by name; |
| select person.name as person_name, pets.name as pet_name from person,pets order by name; |
| select person.name as person_name, pets.name from person,pets order by person.name; |
| select person.name as person_name, pets.name from person,pets order by name; |
| select person.name as name, pets.name as pet_name from person,pets order by name; |
| drop table person; |
| drop table pets; |
| |
| |
| 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) |
| ); |
| |
| -- 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); |
| |
| |
| -- 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; |
| select id, c2_int from d2887_types order by c2_int nulls last; |
| select id, c3_bigint from d2887_types order by c3_bigint asc; |
| select id, c4_real from d2887_types order by c4_real desc; |
| select id, c5_float from d2887_types order by c5_float asc nulls last; |
| select id, c6_numeric from d2887_types order by c6_numeric desc nulls last; |
| select id, c7_char from d2887_types order by c7_char asc nulls first; |
| select id, c8_date from d2887_types order by c8_date desc nulls first; |
| |
| drop table d2887_types; |
| |
| -- 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); |
| insert into d2352 values (1), (2), (3); |
| select substr('abc', 1) from d2352 order by substr('abc', 1); |
| select substr('abc', 1) from d2352 group by substr('abc', 1); |
| select ltrim('abc') from d2352 order by ltrim('abc'); |
| select ltrim('abc') from d2352 group by ltrim('abc'); |
| select trim(trailing ' ' from 'abc') from d2352 |
| order by trim(trailing ' ' from 'abc'); |
| select trim(trailing ' ' from 'abc') from d2352 |
| group by trim(trailing ' ' from 'abc'); |
| drop table d2352; |
| |
| -- 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); |
| insert into d3303 values (1, 1, 2), (1, 3, 3), (2, 3, 1), (2, 2, 4); |
| select * from d3303; |
| |
| -- 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; |
| select sum(j) as s from d3303 group by i order by s; |
| select sum(j) as s from d3303 group by i order by s desc; |
| select sum(j) as s from d3303 group by i order by abs(1), s; |
| select sum(j) as s from d3303 group by i order by sum(k), s desc; |
| select sum(j) as s from d3303 group by k order by abs(k) desc; |
| select sum(j) as s from d3303 group by k order by abs(k) asc; |
| select sum(j) as s from d3303 group by i order by abs(i); |
| select sum(j) as s from d3303 group by i order by abs(i) desc; |
| |
| -- Sanity check that a DISTINCT with a GROUP BY is ok, too. |
| select distinct sum(j) as s from d3303 group by i; |
| |
| -- 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; |
| |
| 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; |
| |
| 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; |
| |
| 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; |
| |
| -- 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; |
| |
| select d3303.i as old_i, sum(d3303.k), d3303.* |
| from d3303 group by k, i, j order by 4; |
| |
| select d3303.i as old_i, sum(d3303.k), d3303.* |
| from d3303 group by k, i, j order by k+2; |
| |
| -- 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; |
| select sum(k) as s from d3303 group by i order by 2; |
| select k from d3303 group by i,k order by 2; |
| select k as s from d3303 group by i,k order by 2; |
| |
| drop table d3303; |
| |
| -- 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; |
| values 1,2 order by 1+0; |
| (values 1 order by 1+0) union (values 1,2 order by 1); |
| -- OK: |
| (values 1 order by 1) union (values 1,2 order by 1); |
| values (1,-1),(3,-3),(2,-2) order by 1; |
| values (1,-1),(3,-3),(2,-2) order by 2; |
| |
| -- DERBY-6027: ORDER BY a cast expression gives NPE |
| values 1 order by int(1); -- expect error |
| values 1 order by 1; -- 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); |
| insert into d6027 values (2,1,3); |
| select i from d6027 order by 1; -- OK, sort |
| select i from d6027 order by int(1); -- OK, don't sort |
| |
| drop table d6027; |
