core/sql/regress/core/TEST010 - trafodion - Git at Google

 -- Test: TEST010 (Core)
 -- @@@ START COPYRIGHT @@@
 --
 -- 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.
 --
 -- @@@ END COPYRIGHT @@@
 --
 -- Functionality: ORDER BY clause on table and view, plus VSBB insert
 -- Expected files: EXPECTED010, EXPECTED010.MP
 -- Other files: DIFF010.KNOWN - VSBB insert disabled for small tables
 -- Table created: t010ta, t010tx, t010t1, t010tmp1, t010uta, t010utx,
 --                t010ut1
 -- View created: t010va, t010uva
 -- To do: - Add VSBB insert test in the insert-select stmt. (Done.)
 --        - Remove DIFF010.KNOWN.NSK file when Unicode is supported on
 --          MX tables, while Unicode may never be supported on MP tables
 --          (Done 5/19/02)
 --        - Enable VSBB insert for small tables
 -- Limitations:
 -- Revision history:
 --     (1/28/02) - Copied from fullstack/TEST010
 --     (2/15/02) - Merged with fullstack/TEST010U
 --     (2/18/02) - Added VSBB insert verification via statistics SP.
 --     (3/01/02) - Comment off Unicode test if running on MP tables.
 --     (5/19/02) - Removed DIFF010.KNOWN.NSK as unicode is supported.
 --     (6/13/02) - Added DIFF010.KNOWN as VSBB insert disabled for small
 --                 tables (<100 rows)

 -- Tests ORDER BY clause:  binder and executor.
 -- Ordering is done using BTREE implementation.

 ?section ddl
 drop view t010va;
 drop table t010ta;
 drop table t010tx;
 drop table t010t1;
 drop table t010tmp1;

 #ifMX
 drop view t010uva;
 drop table t010uta;
 drop table t010utx;
 drop table t010ut1;
 #ifMX

 cqd attempt_esp_parallelism 'OFF';

 ?section crdb
 log LOG010 clear;

 create table t010ta (a varchar(4) not null, b varchar(3) not null, c int);
 create table t010tx (aaa varchar(4) not null, bbb varchar(3) not null, ccc int);
 create table t010t1 (a int not null, b int, c char(7) not null, d char(8), ee varchar(9));
 create table t010tmp1 (a int not null, b int, c char(7) not null, d char(8), ee varchar(9));

 create view  t010va (va, vb, vc) as select a,b,c from t010ta;

 #ifMX
 create table t010uta (a char varying (4) character set ucs2 not null, b char varying (3) character set ucs2 not null, c int);

 create table t010utx (aaa national char varying (4) not null, bbb char varying (3) character set ucs2 not null, ccc int);
 create table t010ut1 (a int not null, b int, c char(7) character set ucs2 not null, d char(8) character set ucs2, ee char varying (9) character set ucs2);

 create view  t010uva (va, vb, vc) as select a,b,c from t010uta;
 #ifMX

 ?section dml
 insert into t010ta values 	('a','b',3),
 			('m','z',1),
 			('r','q',15),
 			('z','y',25);

 -- Test for VSBB insert via statisitcs SP
 control query default detailed_statistics 'ALL';
 prepare IS from
 insert into t010tx select * from t010ta order by b;
 execute IS;
 select seq_num, tdb_name from table (statistics (NULL, 'IS')) order by 1,2;

 -- Back to default value
 control query default detailed_statistics 'OPERATOR';

 #ifMX
 insert into t010uta values 	(N'a',N'b',3),
 			(N'm',N'z',1),
 			(N'r',N'q',15),
 			(N'z',N'y',25);

 -- Another VSBB insert
 control query default detailed_statistics 'ALL';
 prepare ISU from
 insert into t010utx select * from t010uta order by c;

 execute ISU;
 select seq_num, tdb_name from table (statistics (NULL, 'ISU')) order by 1,2;
 #ifMX

 -- Back to default value
 control query default detailed_statistics 'OPERATOR';


 ?section general_tests

 -- empty table
 select * from t010t1 order by a;

 insert into t010t1 values (10,10, 'q','q','q');
 insert into t010t1 values (10,5, 'q','q','q');
 insert into t010t1 values (10,10, 'a', 'a', 'a');

 insert into t010t1 values (5,5,'d','d','d');
 insert into t010t1 values (20,null,'b','e','f');
 insert into t010t1 values (20,null,'b','e','f');
 insert into t010t1 values (25, null, 'w', null, 'w');

 select * from t010t1;

 #ifMX
 insert into t010ut1 values (10,10, N'q',N'q',N'q');
 insert into t010ut1 values (10,5, N'q',N'q',N'q');
 insert into t010ut1 values (10,10, N'a', N'a', N'a');

 insert into t010ut1 values (5,5,N'd',N'd',N'd');
 insert into t010ut1 values (20,null,N'b',N'e',N'f');
 insert into t010ut1 values (20,null,N'b',N'e',N'f');
 insert into t010ut1 values (25, null, N'w', null, N'w');

 select * from t010ut1;
 #ifMX

 -- Ascending ordering
 select * from t010t1 order by a,b,c;
 select * from t010t1 order by b,a,c;
 select * from t010t1 order by c,b;
 select * from t010t1 order by d,b;
 select * from t010t1 order by ee,b;

 select * from t010t1 order by a,c;
 select * from t010t1 order by a,b,c,d,ee;
 select * from t010t1 order by ee,d,c,b,a;

 #ifMX
 select * from t010ut1 order by c,b;
 select * from t010ut1 order by d,b;
 select * from t010ut1 order by ee,b;

 select * from t010ut1 order by a,c;
 select * from t010ut1 order by a,b,c,d,ee;
 select * from t010ut1 order by ee,d,c,b,a;
 #ifMX

 -- Descending ordering
 select * from t010t1 order by a desc,b desc,c desc;
 select * from t010t1 order by b desc,ee desc;
 select * from t010t1 order by c desc,b desc;
 select * from t010t1 order by d desc,b desc;
 select * from t010t1 order by ee desc,b desc;

 select * from t010t1 order by a desc,c desc,b desc;
 select * from t010t1 order by a desc,b desc,c desc,d desc,ee desc;
 select * from t010t1 order by ee desc,d desc,c desc,b desc,a desc;

 #ifMX
 select * from t010ut1 order by c desc,b desc;
 select * from t010ut1 order by d desc,b desc;
 select * from t010ut1 order by ee desc,b desc;

 select * from t010ut1 order by a desc,c desc,b desc;
 select * from t010ut1 order by a desc,b desc,c desc,d desc,ee desc;
 select * from t010ut1 order by ee desc,d desc,c desc,b desc,a desc;
 #ifMX

 -- mix of ascending and descending ordering
 select * from t010t1 order by a, b desc, c asc;
 select * from t010t1 order by a desc, b asc, c asc;

 -- expression in select list
 select a+1 from t010t1 order by a;

 #ifMX
 select a+1 from t010ut1 order by a;
 #ifMX

 -- select list contains a columns not in the order by list.
 select a from t010t1 order by b,c;

 #ifMX
 select a from t010ut1 order by c;
 #ifMX

 -- GROUP BY and ORDER BY
 -- gives syntax error
 -- select a from t010t1 order by a group by a;

 -- INSERT...SELECT with ORDER BY
 -- gives syntax error
 insert into t010tmp1 select * from t010t1 order by a;

 ?section negative_tests

 -- nonex. column
 select tl.b from t010ta tl, t010ta tr order by tl.x;
 -- nonex. column
 select tl.b from t010ta tl, t010ta tr order by tr.x;
 -- nonex. column
 select tl.b from t010ta tl, t010ta tr order by x;
 -- nonex. table
 select tl.b from t010ta tl, t010ta tr order by tt.x;

 -- illeg. syntax
 select tl.b from t010ta tl, t010ta tr order by *;

 -- list index out of range
 select tl.b from t010ta tl, t010ta tr order by 0;
 -- list index out of range
 select tl.b from t010ta tl, t010ta tr order by 2;

 -- ambig. column
 select b, b from t010ta order by b;

 -- ambig. column
 select tl.b from t010ta tl, t010ta tr order by a;
 -- ambig. column
 select tl.b from t010ta tl, t010ta tr order by c;
 -- ambig. column
 select * from t010ta tl, t010ta tr order by b;
 -- ambig. column
 select tl.b,* from t010ta tl, t010ta tr order by b;
 -- ambig. column
 select * from (select a,b as a from t010ta) as aa order by aa.a;

 ?section positive_tests

 -- b q y z
 select b from t010ta order by b;

 -- b z q y
 select tl.b from t010ta tl, t010ta tr;
 -- b q y z
 select tl.b from t010ta tl, t010ta tr order by tl.b;
 -- (bzqy)*4
 select tl.b from t010ta tl, t010ta tr order by tr.b, tl.b;
 -- b q y z
 select tl.b from t010ta tl, t010ta tr order by b;
 -- b q y z
 select tl.b from t010ta tl, t010ta tr order by 1;
 -- b z q y
 select tl.b from t010ta tl, t010ta tr order by tl.a, tl.b;
 -- (bzqy)*4
 select tl.b from t010ta tl, t010ta tr order by tr.a, tl.b;
 -- z b q y
 select tl.b from t010ta tl, t010ta tr order by tl.c, tl.b;
 -- (bzqy)*4
 select tl.b from t010ta tl, t010ta tr order by tr.c, tl.b;

 -- (bzqy)*4
 select tl.b xx,* from t010ta tl, t010ta tr order by tr.c, xx;

 -- (bzqy)*4
 select b from t010ta, t010tx order by ccc, b;

 -- (bqyz)*4
 select b from t010ta, t010tx order by ccc desc, b;


 -- 3 1 15 25 (original bug from JoanZ)
 select tr.vc from t010va tl, t010va tr order by tr.va, tr.vc;


 -- b q y z
 #ifMX
 select b from t010uta order by b;

 -- b z q y
 select tl.b from t010uta tl, t010uta tr;
 -- b q y z
 select tl.b from t010uta tl, t010uta tr order by tl.b;
 -- (bzqy)*4
 select tl.b from t010uta tl, t010uta tr order by tr.b, tl.b;
 -- b q y z
 select tl.b from t010uta tl, t010uta tr order by b;
 -- b q y z
 select tl.b from t010uta tl, t010uta tr order by 1;
 -- b z q y
 select tl.b from t010uta tl, t010uta tr order by tl.a, tl.b;
 -- (bzqy)*4
 select tl.b from t010uta tl, t010uta tr order by tr.a, tl.b;
 -- z b q y
 select tl.b from t010uta tl, t010uta tr order by tl.c, tl.b;
 -- (bzqy)*4
 select tl.b from t010uta tl, t010uta tr order by tr.c, tl.b;

 -- (bzqy)*4
 select tl.b as D,* from t010uta tl, t010uta tr order by tr.c, D;

 -- (bzqy)*4
 select b from t010uta, t010utx order by ccc, b;

 -- (bqyz)*4
 select b from t010uta, t010utx order by ccc desc, b;


 -- 3 1 15 25 (original bug from JoanZ)
 select tr.vc from t010uva tl, t010uva tr order by tr.va, tr.vc;
 #ifMX

 ?section clnup

 drop view t010va;
 drop table t010ta;
 drop table t010tx;
 drop table t010t1;
 drop table t010tmp1;

 #ifMX
 drop view t010uva;
 drop table t010uta;
 drop table t010utx;
 drop table t010ut1;
 #ifMX

 log;
	-- Test: TEST010 (Core)
	-- @@@ START COPYRIGHT @@@
	--
	-- 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.
	--
	-- @@@ END COPYRIGHT @@@
	--
	-- Functionality: ORDER BY clause on table and view, plus VSBB insert
	-- Expected files: EXPECTED010, EXPECTED010.MP
	-- Other files: DIFF010.KNOWN - VSBB insert disabled for small tables
	-- Table created: t010ta, t010tx, t010t1, t010tmp1, t010uta, t010utx,
	-- t010ut1
	-- View created: t010va, t010uva
	-- To do: - Add VSBB insert test in the insert-select stmt. (Done.)
	-- - Remove DIFF010.KNOWN.NSK file when Unicode is supported on
	-- MX tables, while Unicode may never be supported on MP tables
	-- (Done 5/19/02)
	-- - Enable VSBB insert for small tables
	-- Limitations:
	-- Revision history:
	-- (1/28/02) - Copied from fullstack/TEST010
	-- (2/15/02) - Merged with fullstack/TEST010U
	-- (2/18/02) - Added VSBB insert verification via statistics SP.
	-- (3/01/02) - Comment off Unicode test if running on MP tables.
	-- (5/19/02) - Removed DIFF010.KNOWN.NSK as unicode is supported.
	-- (6/13/02) - Added DIFF010.KNOWN as VSBB insert disabled for small
	-- tables (<100 rows)

	-- Tests ORDER BY clause: binder and executor.
	-- Ordering is done using BTREE implementation.

	?section ddl
	drop view t010va;
	drop table t010ta;
	drop table t010tx;
	drop table t010t1;
	drop table t010tmp1;

	#ifMX
	drop view t010uva;
	drop table t010uta;
	drop table t010utx;
	drop table t010ut1;
	#ifMX

	cqd attempt_esp_parallelism 'OFF';

	?section crdb
	log LOG010 clear;

	create table t010ta (a varchar(4) not null, b varchar(3) not null, c int);
	create table t010tx (aaa varchar(4) not null, bbb varchar(3) not null, ccc int);
	create table t010t1 (a int not null, b int, c char(7) not null, d char(8), ee varchar(9));
	create table t010tmp1 (a int not null, b int, c char(7) not null, d char(8), ee varchar(9));

	create view t010va (va, vb, vc) as select a,b,c from t010ta;

	#ifMX
	create table t010uta (a char varying (4) character set ucs2 not null, b char varying (3) character set ucs2 not null, c int);

	create table t010utx (aaa national char varying (4) not null, bbb char varying (3) character set ucs2 not null, ccc int);
	create table t010ut1 (a int not null, b int, c char(7) character set ucs2 not null, d char(8) character set ucs2, ee char varying (9) character set ucs2);

	create view t010uva (va, vb, vc) as select a,b,c from t010uta;
	#ifMX

	?section dml
	insert into t010ta values ('a','b',3),
	('m','z',1),
	('r','q',15),
	('z','y',25);

	-- Test for VSBB insert via statisitcs SP
	control query default detailed_statistics 'ALL';
	prepare IS from
	insert into t010tx select * from t010ta order by b;
	execute IS;
	select seq_num, tdb_name from table (statistics (NULL, 'IS')) order by 1,2;

	-- Back to default value
	control query default detailed_statistics 'OPERATOR';

	#ifMX
	insert into t010uta values (N'a',N'b',3),
	(N'm',N'z',1),
	(N'r',N'q',15),
	(N'z',N'y',25);

	-- Another VSBB insert
	control query default detailed_statistics 'ALL';
	prepare ISU from
	insert into t010utx select * from t010uta order by c;

	execute ISU;
	select seq_num, tdb_name from table (statistics (NULL, 'ISU')) order by 1,2;
	#ifMX

	-- Back to default value
	control query default detailed_statistics 'OPERATOR';


	?section general_tests

	-- empty table
	select * from t010t1 order by a;

	insert into t010t1 values (10,10, 'q','q','q');
	insert into t010t1 values (10,5, 'q','q','q');
	insert into t010t1 values (10,10, 'a', 'a', 'a');

	insert into t010t1 values (5,5,'d','d','d');
	insert into t010t1 values (20,null,'b','e','f');
	insert into t010t1 values (20,null,'b','e','f');
	insert into t010t1 values (25, null, 'w', null, 'w');

	select * from t010t1;

	#ifMX
	insert into t010ut1 values (10,10, N'q',N'q',N'q');
	insert into t010ut1 values (10,5, N'q',N'q',N'q');
	insert into t010ut1 values (10,10, N'a', N'a', N'a');

	insert into t010ut1 values (5,5,N'd',N'd',N'd');
	insert into t010ut1 values (20,null,N'b',N'e',N'f');
	insert into t010ut1 values (20,null,N'b',N'e',N'f');
	insert into t010ut1 values (25, null, N'w', null, N'w');

	select * from t010ut1;
	#ifMX

	-- Ascending ordering
	select * from t010t1 order by a,b,c;
	select * from t010t1 order by b,a,c;
	select * from t010t1 order by c,b;
	select * from t010t1 order by d,b;
	select * from t010t1 order by ee,b;

	select * from t010t1 order by a,c;
	select * from t010t1 order by a,b,c,d,ee;
	select * from t010t1 order by ee,d,c,b,a;

	#ifMX
	select * from t010ut1 order by c,b;
	select * from t010ut1 order by d,b;
	select * from t010ut1 order by ee,b;

	select * from t010ut1 order by a,c;
	select * from t010ut1 order by a,b,c,d,ee;
	select * from t010ut1 order by ee,d,c,b,a;
	#ifMX

	-- Descending ordering
	select * from t010t1 order by a desc,b desc,c desc;
	select * from t010t1 order by b desc,ee desc;
	select * from t010t1 order by c desc,b desc;
	select * from t010t1 order by d desc,b desc;
	select * from t010t1 order by ee desc,b desc;

	select * from t010t1 order by a desc,c desc,b desc;
	select * from t010t1 order by a desc,b desc,c desc,d desc,ee desc;
	select * from t010t1 order by ee desc,d desc,c desc,b desc,a desc;

	#ifMX
	select * from t010ut1 order by c desc,b desc;
	select * from t010ut1 order by d desc,b desc;
	select * from t010ut1 order by ee desc,b desc;

	select * from t010ut1 order by a desc,c desc,b desc;
	select * from t010ut1 order by a desc,b desc,c desc,d desc,ee desc;
	select * from t010ut1 order by ee desc,d desc,c desc,b desc,a desc;
	#ifMX

	-- mix of ascending and descending ordering
	select * from t010t1 order by a, b desc, c asc;
	select * from t010t1 order by a desc, b asc, c asc;

	-- expression in select list
	select a+1 from t010t1 order by a;

	#ifMX
	select a+1 from t010ut1 order by a;
	#ifMX

	-- select list contains a columns not in the order by list.
	select a from t010t1 order by b,c;

	#ifMX
	select a from t010ut1 order by c;
	#ifMX

	-- GROUP BY and ORDER BY
	-- gives syntax error
	-- select a from t010t1 order by a group by a;

	-- INSERT...SELECT with ORDER BY
	-- gives syntax error
	insert into t010tmp1 select * from t010t1 order by a;

	?section negative_tests

	-- nonex. column
	select tl.b from t010ta tl, t010ta tr order by tl.x;
	-- nonex. column
	select tl.b from t010ta tl, t010ta tr order by tr.x;
	-- nonex. column
	select tl.b from t010ta tl, t010ta tr order by x;
	-- nonex. table
	select tl.b from t010ta tl, t010ta tr order by tt.x;

	-- illeg. syntax
	select tl.b from t010ta tl, t010ta tr order by *;

	-- list index out of range
	select tl.b from t010ta tl, t010ta tr order by 0;
	-- list index out of range
	select tl.b from t010ta tl, t010ta tr order by 2;

	-- ambig. column
	select b, b from t010ta order by b;

	-- ambig. column
	select tl.b from t010ta tl, t010ta tr order by a;
	-- ambig. column
	select tl.b from t010ta tl, t010ta tr order by c;
	-- ambig. column
	select * from t010ta tl, t010ta tr order by b;
	-- ambig. column
	select tl.b,* from t010ta tl, t010ta tr order by b;
	-- ambig. column
	select * from (select a,b as a from t010ta) as aa order by aa.a;

	?section positive_tests

	-- b q y z
	select b from t010ta order by b;

	-- b z q y
	select tl.b from t010ta tl, t010ta tr;
	-- b q y z
	select tl.b from t010ta tl, t010ta tr order by tl.b;
	-- (bzqy)*4
	select tl.b from t010ta tl, t010ta tr order by tr.b, tl.b;
	-- b q y z
	select tl.b from t010ta tl, t010ta tr order by b;
	-- b q y z
	select tl.b from t010ta tl, t010ta tr order by 1;
	-- b z q y
	select tl.b from t010ta tl, t010ta tr order by tl.a, tl.b;
	-- (bzqy)*4
	select tl.b from t010ta tl, t010ta tr order by tr.a, tl.b;
	-- z b q y
	select tl.b from t010ta tl, t010ta tr order by tl.c, tl.b;
	-- (bzqy)*4
	select tl.b from t010ta tl, t010ta tr order by tr.c, tl.b;

	-- (bzqy)*4
	select tl.b xx,* from t010ta tl, t010ta tr order by tr.c, xx;

	-- (bzqy)*4
	select b from t010ta, t010tx order by ccc, b;

	-- (bqyz)*4
	select b from t010ta, t010tx order by ccc desc, b;


	-- 3 1 15 25 (original bug from JoanZ)
	select tr.vc from t010va tl, t010va tr order by tr.va, tr.vc;


	-- b q y z
	#ifMX
	select b from t010uta order by b;

	-- b z q y
	select tl.b from t010uta tl, t010uta tr;
	-- b q y z
	select tl.b from t010uta tl, t010uta tr order by tl.b;
	-- (bzqy)*4
	select tl.b from t010uta tl, t010uta tr order by tr.b, tl.b;
	-- b q y z
	select tl.b from t010uta tl, t010uta tr order by b;
	-- b q y z
	select tl.b from t010uta tl, t010uta tr order by 1;
	-- b z q y
	select tl.b from t010uta tl, t010uta tr order by tl.a, tl.b;
	-- (bzqy)*4
	select tl.b from t010uta tl, t010uta tr order by tr.a, tl.b;
	-- z b q y
	select tl.b from t010uta tl, t010uta tr order by tl.c, tl.b;
	-- (bzqy)*4
	select tl.b from t010uta tl, t010uta tr order by tr.c, tl.b;

	-- (bzqy)*4
	select tl.b as D,* from t010uta tl, t010uta tr order by tr.c, D;

	-- (bzqy)*4
	select b from t010uta, t010utx order by ccc, b;

	-- (bqyz)*4
	select b from t010uta, t010utx order by ccc desc, b;


	-- 3 1 15 25 (original bug from JoanZ)
	select tr.vc from t010uva tl, t010uva tr order by tr.va, tr.vc;
	#ifMX

	?section clnup

	drop view t010va;
	drop table t010ta;
	drop table t010tx;
	drop table t010t1;
	drop table t010tmp1;

	#ifMX
	drop view t010uva;
	drop table t010uta;
	drop table t010utx;
	drop table t010ut1;
	#ifMX

	log;