| -- Test: TEST001 (Executor) |
| -- @@@ 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: Simple queries on basic operators, nulls, set params, |
| -- reverse scanning, and ifdef syntax. |
| -- Expected files: EXPECTED001, EXPECTED001.MX, EXPECTED001.MP |
| -- Table created: t001t1, t001tn2, t001t2, t001t3, t001desc, t001asc, |
| -- t001ut1, t001ut2, t001ut3 |
| -- View created: t001vn |
| -- Limitations: |
| |
| ?section ddl |
| -- CREATE database |
| |
| obey test001(clnup); |
| |
| log LOG001 clear; |
| |
| create table t001t1 (a int, b char(9), c int, d char(4)); |
| create table t001tn (a int, b char(9), c int, d char(4) COLLATE SJIS); |
| create table t001t2 (a int not null, b char(9), c int, d char(4), primary key (a)); |
| create table t001t3 (a int not null, b char(9) not null, c int, d char(4), primary key (a, b)); |
| |
| #ifMX |
| create table t001ut1 (a int, b nchar(9), c int, d nchar(4)); |
| create table t001ut2 (a int not null, b nchar(9), c int, d nchar(4), primary key (a)); |
| create table t001ut3 (a int not null, b nchar(9) not null, c int, d nchar(4), primary key (a, b)); |
| #ifMX |
| |
| ?section dml |
| |
| invoke t001t1; |
| invoke t001tn; |
| invoke t001t2; |
| invoke t001t3; |
| |
| #ifMX |
| invoke $$TEST_SCHEMA$$.t001ut1; |
| invoke $$TEST_SCHEMA_NAME$$.t001ut2; |
| invoke t001ut3; |
| #ifMX |
| |
| -- INSERT queries |
| insert into t001t1 values (10, 'abc', 20, 'xy'); |
| insert into t001t1(b,d,a,c) values ('defg', 'wx', 10+10, 30); |
| insert into t001t2 select * from t001t1; |
| insert into t001t3(a,b,c,d) select a,b,c,d from t001t2; |
| |
| #ifMX |
| insert into t001ut1 values (10, N'abc', 20, N'xy'); |
| insert into t001ut1(b,d,a,c) values (N'defg', N'wx', 10+10, 30); |
| insert into t001ut2 select * from t001ut1; |
| insert into t001ut3(a,b,c,d) select a,b,c,d from t001ut2; |
| #ifMX |
| |
| -- SELECT queries |
| select * from t001t1; |
| select t001t2.* from t001t2; |
| select * from t001t3; |
| |
| #ifMX |
| select * from t001ut1; |
| select t001ut2.* from t001ut2; |
| select * from t001ut3; |
| #ifMX |
| |
| -- Error case |
| select 0, '0' + 1 from t001t1; |
| -- should get type incompatibility error |
| |
| select cast(b as ansivarchar(8)) from t001t1; -- err 3178 |
| create table t001t1_foo(a lsdecimal); -- err 3178 |
| |
| select * from (select * from t001t1) x; |
| |
| select a,b from t001t1; |
| select a,a from t001t1; |
| |
| select a+1,a-1,a*1,a/1 from t001t1; |
| |
| select t001t1.a, t001t1.d from t001t1; |
| |
| select 1 from t001t1; |
| select 1+1 from t001t1; |
| |
| select * from t001t1 where 1 = 1; |
| select * from t001t1 where a = 10; |
| select * from t001t1 where a <> 10; |
| select * from t001t1 where a = 10 or a = 20; |
| select * from t001t1 where a = 10 and a = 20; |
| |
| -- Error case |
| #ifMX |
| select 0, N'0' + 1 from t001ut1; |
| -- should get type incompatibility error |
| |
| select * from (select * from t001ut1) x; |
| |
| select a,b from t001ut1; |
| |
| select t001ut1.a, t001ut1.d from t001ut1; |
| |
| select * from t001ut1 where a = 10 and a = 20; |
| |
| select * from (select * from t001ut1) x , t001ut2; |
| #ifMX |
| |
| #ifMP |
| set envvar NCHAR_SJIS_DEBUG; -- allow charset SJIS on MP |
| #ifMP |
| |
| select a from t001t1 where b like _SJIS'sj'; |
| select a from t001t1 where b like 'sj'COLLATE SJIS; -- ok, B gets coerced |
| select a from t001t1 where b COLLATE DEFAULT like 'sj'COLLATE SJIS; |
| select a from t001t1 where b COLLATE DEFAULT like 'jj' escape 's'COLLATE SJIS; |
| |
| select a from t001t1 where trim(1 from 2) = ''; |
| select a from t001t1 where trim(1 from b) = ''; |
| select a from t001t1 where trim(b from 2) = ''; |
| |
| -- comment out because of the behavior change to NATIONAL_CHARSET in R2. |
| -- It is a read-only CQD. |
| --control query default NATIONAL_CHARSET 'kanji'; |
| --select a from t001t1 where trim(N' ' from b) = ''; |
| --control query default NATIONAL_CHARSET 'sql_text'; |
| --select a from t001t1 where trim(N' ' from b) = ''; -- ok |
| --control query default NATIONAL_CHARSET reset; |
| select a from t001t1 where trim(N' ' from b) = ''; |
| select a from t001t1 where trim(' 'COLLATE SJIS from b COLLATE DEFAULT) = ''; |
| |
| select a from t001t1 where '' = replace(1,2,3); |
| select a from t001t1 where '' = replace('a','x','z'COLLATE SJIS); -- ok, compatible |
| select a from t001t1 where '' = replace('a'COLLATE DEFAULT,'xx'COLLATE SJIS,'z'); -- not comparable |
| select a from t001t1 where '' = replace('a',2,'z'); -- not comparable |
| select a from t001t1 where 99 = position(1 in 2); |
| select a from t001t1 where 99 = position('x' in 2); |
| select a from t001t1 where 99 = position(1 in 'y'); |
| select a from t001t1 where 99 = position('xx'COLLATE SJIS in 'y'COLLATE DEFAULT); |
| |
| select b from t001tn UNION select d from t001tn; |
| -- not comparable |
| select b from t001tn UNION ALL select d from t001tn; |
| -- ok, no resulting collating sequence, but no comparison |
| select * from ( |
| select b from t001tn UNION ALL select d from t001tn) u(b) where b='x'; |
| -- not comparable |
| select * from ( |
| select b from t001tn UNION ALL select d from t001tn) u(b) where b=(select b from t001tn); |
| -- not comparable |
| select b from t001tn UNION select d COLLATE SJIS from t001tn; |
| -- ok, coerced |
| select b COLLATE SJIS from t001tn UNION select d COLLATE SJIS from t001tn; |
| -- ok |
| |
| select a COLLATE SJIS from t001tn; |
| select b COLLATE Xyzw from t001tn; -- ok, unknown-coll warning, coll not used |
| select b from (select b COLLATE Xyzw from t001tn)x where b>'a'; |
| |
| |
| -- JOIN queries |
| select t001t1.a, t001t2.a from t001t1 , t001t2 where t001t1.a = t001t2.a; |
| |
| -- Genesis test case added 12/18/96 |
| select t001t1.a + t001t2.a from t001t1 , t001t2 where t001t1.a = t001t2.a; |
| |
| select t001t1.a, t001t2.a from t001t1 join t001t2 on t001t1.a = t001t2.a and t001t1.a = 10 and t001t2.a = 10; |
| select t001t1.a, t001t2.a from t001t1 join t001t2 on t001t1.a = 10 or t001t2.a = 10; |
| select t001t1.a, t001t2.a from t001t1 join t001t2 on |
| t001t1.a = 10 and (t001t1.a = 10 or t001t1.a = 20) |
| or t001t2.a = 10 and (t001t2.a = 10 or t001t2.a = 20); |
| select t001t1.a, t001t2.a from t001t1 join t001t2 on |
| t001t1.a = 10 and (t001t1.a = 10 or t001t1.a = 20) |
| and t001t2.a = 10 and (t001t2.a = 10 or t001t2.a = 20); |
| |
| select * from (select * from t001t1) x , t001t2; |
| |
| -- NATURAL JOIN queries |
| select a from t001t1 x natural join t001t1 y; |
| select a from t001t1 natural join t001t2; |
| select a from t001t1 natural join t001t2 natural join t001t3; |
| select x.a, x.b from (select a, b, c from t001t1 natural join t001t2) x join t001t3 |
| on x.c = t001t3.c; |
| |
| #ifMX |
| select a from t001ut1 x natural join t001ut1 y; |
| select a from t001ut1 natural join t001ut2; |
| select a from t001ut1 natural join t001ut2 natural join t001ut3; |
| select x.a, x.b from (select a, b, c from t001ut1 natural join t001ut2) x join t001ut3 |
| on x.c = t001ut3.c; |
| |
| select count(*) from (select * from t001ut1) x natural join (select * from t001ut2) y , |
| (select * from t001ut3) z; |
| #ifMX |
| |
| -- AGGREGATE queries |
| |
| select count(*), min(a), max(a), sum(a), avg(a) from t001t1; |
| select count(*), min(a), max(a), sum(a), avg(a) from t001t1 having count(*) > 0; |
| select count(*), min(a), max(a), sum(a), avg(a) from t001t1 having count(*) < 0; |
| select count(*), sum(a) from t001t1 having count(*) = 2 and sum(a) = 30; |
| select sum(t001t1.a) from t001t1 , t001t2; |
| select count(*) from (select * from t001t1) x natural join (select * from t001t2) y , |
| (select * from t001t3) z; |
| |
| |
| -- UPDATE queries |
| update t001t1 set a = 10 where a = 10; |
| select * from t001t1; |
| update t001t1 set a = 100, d = 'yx' where b = 'abc'; |
| select * from t001t1; |
| update t001t1 set c = 55; |
| select * from t001t1; |
| |
| update t001t2 set c = c+1, b = 'gfe' where d = 'wx'; |
| select * from t001t2; |
| |
| |
| #ifMX |
| update t001ut1 set a = 100, d = N'yx' where b = N'abc'; |
| select * from t001ut1; |
| |
| update t001ut2 set c = c+1, b = N'gfe' where d = N'wx'; |
| select * from t001ut2; |
| #ifMX |
| |
| |
| -- DELETE queries |
| delete from t001t1 where a = 100; |
| select * from t001t1; |
| |
| delete from t001t3; |
| select * from t001t3; |
| |
| delete from t001t1; |
| delete from t001t2; |
| |
| #ifMX |
| delete from t001ut3; |
| select * from t001ut3; |
| |
| delete from t001ut1; |
| delete from t001ut2; |
| #ifMX |
| |
| -- PREPAREd queries |
| prepare s1 from insert into t001t1 values (10, 'abc', 20, 'yz'); |
| execute s1; |
| insert into t001t1 values (30, 'def', 40, 'wx'); |
| |
| prepare s2 from select * from t001t1; |
| execute s2; |
| |
| execute s1; |
| execute s2; |
| |
| prepare s1 from select * from t001t1 where a = 10; |
| execute s1; |
| |
| prepare s2 from select * from t001t1 where a = 30; |
| execute s2; |
| |
| show prepared; |
| |
| #ifMX |
| prepare s1 from insert into t001ut1 values (10, N'abc', 20, N'yz'); |
| execute s1; |
| insert into t001ut1 values (30, N'def', 40, N'wx'); |
| |
| prepare s2 from select * from t001ut1; |
| execute s2; |
| |
| execute s1; |
| execute s2; |
| |
| prepare s1 from select * from t001ut1 where a = 10; |
| execute s1; |
| |
| show prepared; |
| #ifMX |
| |
| -- PARAM queries (all moved to TEST001(core)) |
| |
| -- ---------------------- |
| -- test reverse scanning |
| -- ---------------------- |
| create table t001desc |
| ( |
| a numeric(18) signed not null, |
| primary key ( a DESC ) |
| ) |
| ; |
| |
| insert into t001desc values(1), (2), (3), (5), (7), (10); |
| select a from t001desc where a < 5 order by a; |
| |
| ------------------------------ |
| |
| create table t001asc |
| ( |
| a numeric(18) signed not null, |
| primary key ( a ASC ) |
| ) |
| ; |
| |
| insert into t001asc values(1), (2), (3), (5), (7), (10); |
| select a from t001asc where a < 5 order by a DESC; |
| |
| ------------------------------ |
| |
| -- This next bit works if Debug, fails if Release; |
| -- and the results (char_length of E) will change after MX-NSK-Rel1, |
| -- when we correctly support KANJI as double-byte instead of single. |
| -- |
| -- For now, don't run these tests for MX objects on NSK platform. It is too |
| -- difficult to create all the different known results for release, debug, |
| -- MX format objects, etc. (rsm) |
| |
| -- comment out because of the behavior change to NATIONAL_CHARSET in R2. |
| -- It is a read-only CQD. |
| --#ifNT |
| -- control query default NATIONAL_CHARSET 'kanji'; |
| -- set envvar NCHAR_DEBUG; |
| -- create table t001tn2(a int, b char(9), c int, d char(4) COLLATE SJIS, |
| -- e NCHAR(4)); |
| -- create view t001vn(ee) as select cast(e as NCHAR(10)) from t001tn2; |
| -- reset envvar NCHAR_DEBUG; |
| -- invoke t001vn; |
| -- invoke t001tn2; |
| -- table t001vn; |
| -- select * from t001tn2 where b=e; -- not comparable |
| -- create view t001vn(ee) as select cast(e as NCHAR(10)) from t001tn2; |
| -- select e collate sjis from t001tn2; |
| -- set envvar NCHAR_DEBUG 2; |
| -- create view t001vn(ee) as select cast(e as NCHAR(10)) from t001tn2; -- 3179 |
| -- select e collate sjis from t001tn2; -- 3179 |
| -- reset envvar NCHAR_DEBUG; |
| -- update t001tn2 set e = _kanji'km' collate SJIS || e; |
| -- update t001tn2 set e = e || _kanji'km' collate SJISfoo; |
| -- control query default NATIONAL_CHARSET 'reset'; |
| --#ifNT |
| -- |
| --#ifMP |
| -- control query default NATIONAL_CHARSET 'kanji'; |
| -- set envvar NCHAR_DEBUG; |
| -- create table t001tn2(a int, b char(9), c int, d char(4) COLLATE SJIS, |
| -- e NCHAR(4)); |
| -- create view t001vn(ee) as select cast(e as NCHAR(10)) from t001tn2; |
| -- reset envvar NCHAR_DEBUG; |
| -- invoke t001vn; |
| -- invoke t001tn2; |
| -- table t001vn; |
| -- select * from t001tn2 where b=e; -- not comparable |
| -- create view t001vn(ee) as select cast(e as NCHAR(10)) from t001tn2; |
| -- select e collate sjis from t001tn2; |
| -- set envvar NCHAR_DEBUG 2; |
| -- create view t001vn(ee) as select cast(e as NCHAR(10)) from t001tn2; -- 3179 |
| -- select e collate sjis from t001tn2; -- 3179 |
| -- reset envvar NCHAR_DEBUG; |
| -- update t001tn2 set e = _kanji'km' collate SJIS || e; |
| -- update t001tn2 set e = e || _kanji'km' collate SJISfoo; |
| -- control query default NATIONAL_CHARSET 'reset'; |
| --#ifMP |
| |
| ------------------------------ |
| |
| log; |
| obey test001(clnup); |
| exit; |
| |
| |
| ?section clnup |
| -- CLEANUP database |
| drop view t001vn; |
| drop table t001tn; |
| drop table t001tn2; |
| drop table t001t1; |
| drop table t001t2; |
| drop table t001t3; |
| drop table t001desc; |
| drop table t001asc; |
| |
| #ifMX |
| drop table t001ut1; |
| drop table t001ut2; |
| drop table t001ut3; |
| #ifMX |
| |
