| -- Test: TEST029 (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: Updatable Views, View With Check Option |
| -- Following genesis cases are tested: |
| -- 10-970828-6025 - Test for view query with comparison operator |
| -- 10-990518-8420 - Create View With Check Option & |
| -- Tbl-Rename=>scope lookup bind error |
| -- Expected files: EXPECTED029, EXPECTED029.MP |
| -- Revision history: |
| -- (02/12/03) - Combined portions of TEST029 and TEST027 of fullstack2 |
| -- which dealt with DML testing. Remaining portions moved |
| -- to COMPGENERAL as a new test |
| ---------------------------------------------------------------------------- |
| |
| obey TEST029(ddbMXMP); |
| |
| #ifMX |
| obey TEST029(ddbMX); |
| drop table MT; |
| create table MT(a int); -- empty table, for NULL data source |
| #ifMX |
| log LOG029 clear; |
| obey TEST029(tests); |
| obey TEST029(ddbMXMP); |
| |
| #ifMX |
| obey TEST029(ddbMX); |
| #ifMX |
| |
| log; |
| exit; |
| |
| ?section tests |
| #ifMX |
| obey TEST029(database_create); |
| obey TEST029(uv_wco_tests); -- DML on this table and views |
| obey TEST029(uv_wco_rename_tests); -- more DML: Genesis 10-990518-8420 |
| #ifMX |
| |
| obey TEST029(view_query_test); -- added from test027, section for |
| -- genesis 10-970828-6025.View query |
| -- fails with comparison operator & |
| -- subquery, rv 10/28/02 |
| |
| obey TEST029(misc_bind_test); -- added from test027, section for |
| -- misc checkins Checkin1203, |
| -- rv 10/28/02 |
| |
| ?section database_create |
| -- |
| -- Database creation |
| create table T29x(a int default 10 not null, b int, c char(4) default 'c', |
| d int default 1, e int default 0, |
| constraint " T29x " check (a <> b)); |
| |
| showddl T29x; |
| |
| create view T29xv0(a,b,c,d) as select a,b,c,d from T29x -- view on a basetable |
| where a > -99 |
| with check option; |
| showddl T29xv0; |
| |
| create view T29xv1(c,b,z,d) as select c,b,a,d from T29xv0 -- on a view |
| where c >= 'c' and a > 0; |
| showddl T29xv1; |
| |
| create view T29xv2(h,i,j,d) as select T29xv1.z,b,c,d from T29xv1 -- on a view |
| where z < 99 and z <= T29xv1.b |
| with check option; |
| |
| showddl T29xv2; |
| |
| create view T29xv3(s,r,d) as select x.i,h,d from T29xv2 x -- on a view, cubed |
| where h < 10 and x.j < 'j' |
| with check option; |
| |
| showddl T29xv3; |
| |
| create view T29xv4 as select * from T29xv3 x where r > 5; |
| |
| showddl T29xv4; |
| |
| ?section uv_wco_tests |
| -- |
| -- basecol: T29x.A T29x.B T29x.C |
| -- default: 10 null 'c' |
| -- |
| -- T29x: a<>b |
| -- v0 wco: a>-99 |
| -- v1: a>0 c>='c' |
| -- v2 wco: z<99 z<=b |
| -- v3 wco: h<10 j<'j' [col not in v3] |
| -- v4: r>5 |
| -- |
| -- We insert rows setting column D to be the highest numbered view (v 1,2,3) |
| -- in which the row should be visible. |
| |
| insert into T29x(a,b,c,d) values (8,8,'d',-1); -- violates T29X constraint (a<>b) |
| insert into T29x(a,b,c,d) values (-1,0,'d',0), (1,2,'a',0), |
| (1,0,'f',1), (99,100,'f',1), |
| (1,2,'j',2), (10,11,'f',2), |
| (1,3,'f',3); |
| select * from T29x; -- 7 rows |
| select * from T29xv1; -- 5 rows |
| select * from T29xv2; -- 3 rows |
| select * from T29xv3; -- 1 row |
| |
| insert into T29xv3 default values; -- null value in B violates v2 WCO |
| insert into T29xv3(r,s,d) values(10,11,-1); -- violates v3 WCO |
| insert into T29xv3(r,s,d) values(1,0,-1); -- violates v2 WCO |
| insert into T29xv3(r,s,d) values(-1,0,0); -- violates v1 cascaded WCO |
| insert into T29xv3(r,s,d) values(1,1,-1); -- violates T29X constraint |
| insert into T29xv3(r,s,d) values(null,1,-1); -- violates notnull constraint |
| insert into T29xv3(r,s,d) values((select cast(null as int) from MT),1,-1); -- violates notnull constraint |
| insert into T29xv3(r,s,d) values(9,10,3); -- ok |
| select * from T29xv3; -- 1+1 rows (since (-1,0,0) "disappears" from v1 on up) |
| select * from T29x; -- 7+1 rows |
| |
| insert into T29xv4(r,s,d) values(10,11,-1); -- violates v3 WCO |
| insert into T29xv4(r,s,d) values(1,0,-1); -- violates v2 WCO |
| insert into T29xv4(r,s,d) values(-1,0,0); -- violates v1 cascaded WCO |
| insert into T29xv4(r,s,d) values(1,1,-1); -- violates T29X constraint |
| insert into T29xv4(r,s,d) values(null,1,-1); -- violates notnull constraint |
| insert into T29xv3(r,s,d) values((select cast(null as int) from MT),1,-1); -- violates notnull constraint |
| delete from T29xv3 where (r,s,d) = (9,10,3); -- ok. |
| insert into T29xv4(r,s,d) values(9,10,3); -- ok |
| select * from T29xv4; -- 1+1 rows (since (-1,0,0) "disappears" from v1 on up) |
| select * from T29x; -- 7+1 rows |
| insert into T29xv4(r,s,d) values(5,10,3); -- ok, disappears from v4 |
| delete from T29xv4 where (r,s,d) = (5,10,3); -- fails, since disappeared |
| delete from T29xv3 where (r,s,d) = (5,10,3); -- ok. |
| |
| insert into T29xv2 default values; -- null value in B violates v2 WCO |
| insert into T29xv2 values(99,0,'c',1); -- violates v2 WCO |
| insert into T29xv2 values(1,0,'c',1); -- violates v2 WCO |
| insert into T29xv2 values(1,1,'c',-1); -- violates T29X |
| insert into T29xv2 values(null,1,'c',-1); -- violates notnull |
| insert into T29xv2 values((select cast(null as int) from MT),1,'c',-1); -- violates notnull |
| insert into T29xv2 values(19,20,'k',2); -- ok |
| insert into T29xv2 values(19,20,'a',0); -- violates v1 cascaded WCO |
| select * from T29xv2; -- 3+1+1 rows (since 2 rows "disappear") |
| select * from T29xv1; -- 5+1+1 rows (since 2 rows "disappear") |
| select * from T29x; -- 9 rows |
| |
| insert into T29xv1 default values; -- ok |
| insert into T29xv1(z,b,c,d) values(0,1,'c',0); -- ok, disappears |
| insert into T29xv1(z,b,c,d) values(1,0,'b',0); -- ok, disappears |
| insert into T29xv1(z,b,c,d) values(1,0,'d',1); -- ok |
| insert into T29xv1(z,b,c,d) values(-99,0,'d',1); -- violates v0 constraint |
| insert into T29xv1(z,b,c,d) values(1,1,'d',-1); -- violates T29X constraint |
| insert into T29xv1(z,b,c,d) values(null,1,'d',-1); -- violates notnull |
| insert into T29xv1(z,b,c,d) values((select cast(null as int) from MT),1,'d',-1); -- violates notnull |
| select * from T29xv1; -- 7+2 rows (since 2 rows "disappear") |
| select * from T29x; -- 9+4 rows |
| |
| update T29xv3 set r=10; -- violates v2 |
| update T29xv3 set r=10,s=11; -- violates v3 |
| update T29xv3 set r=s+1; -- violates v2 |
| update T29xv3 set r=0,d=0 where r=9; -- violates v1 cascaded WCO |
| update T29xv3 set r=s-1,s=s+10; -- ok, result is rows (r=2,s=13),() |
| update T29xv3 set r=s-1,s=s+10; -- violates v3 |
| update T29xv3 set r=null; -- violates notnull |
| update T29xv3 set r=(select cast(null as int) from MT); -- violates notnull |
| update T29xv3 set s=null; -- violates v2 WCO |
| update T29xv3 set s=2; -- violates T29X |
| select * from T29xv3; -- 2 rows |
| delete from T29xv3; -- ok |
| select * from T29xv3; -- 0 rows |
| select * from T29x; -- 13-2 rows |
| |
| insert into T29xv3(r,s,d) values(9,10,3); -- ok |
| update T29xv2 set h=h*3,i=i*3,d=2 where d=3; -- ok, removes v3's last rows |
| select * from T29xv3; -- 0 rows |
| select * from T29xv2; -- 5-1 rows now (1 row disappeared from v1) |
| select * from T29x; -- 11+1 rows |
| delete from T29xv2 where c like 'f%'; -- err 4001 expected |
| delete from T29xv2 where j like 'f%'; -- ok |
| select * from T29xv2; -- 4-1 rows now |
| select * from T29x; -- 12-1 rows |
| |
| ?section uv_wco_rename_tests -- more DML: Genesis 10-990518-8420 |
| |
| create view T29xvRN1 as select a,b,e from |
| (select * from T29x where a > 1) ren |
| with check option; |
| create view T29xvRN2 as select a,b,e from |
| (select * from T29x where a > 1) ren |
| where a < 10 |
| with check option; |
| create view T29xvRN3 as select a,b,e from |
| (select * from |
| (select * from T29x where a > 1) i |
| where a < 10) o |
| where a <> b |
| with check option; |
| create view T29xvRN4 as select a,b,e from |
| (select * from T29xvRN3 where e > 0) ren |
| where e < 5 |
| with check option; |
| |
| showddl T29xvRN1; showddl T29xvRN2; showddl T29xvRN3; showddl T29xvRN4; |
| |
| delete from T29x; |
| alter table T29x drop constraint " T29x"; |
| |
| insert into T29xvRN1(a,b) values(1,1); -- fail |
| insert into T29xvRN1(a,b) values(10,10); -- ok |
| insert into T29xvRN2(a,b) values(1,1); -- fail |
| insert into T29xvRN2(a,b) values(11,11); -- fail |
| insert into T29xvRN2(a,b) values(9,9); -- ok |
| insert into T29xvRN3(a,b) values(1,1); -- fail |
| insert into T29xvRN3(a,b) values(11,11); -- fail |
| insert into T29xvRN3(a,b) values(9,9); -- fail |
| insert into T29xvRN3(a,b) values(8,9); -- ok |
| insert into T29xvRN4(a,b) values(8,9); -- fail |
| insert into T29xvRN4(a,b,e) values(1,1,1); -- fail |
| insert into T29xvRN4(a,b,e) values(11,11,1); -- fail |
| insert into T29xvRN4(a,b,e) values(9,9,1); -- fail |
| insert into T29xvRN4(a,b,e) values(7,9,1); -- ok |
| insert into T29xvRN4(a,b,e) values(7,9,5); -- fail |
| |
| table T29xvRN1; table T29xvRN2; table T29xvRN3; table T29xvRN4; |
| |
| |
| -- Following section is from test027, test for Genesis 10-970828-6025, rv |
| |
| ?section view_query_test |
| create table T29VQA (a int); |
| insert into T29VQA values (1), (2), (3); |
| create view V29VQA as select a from T29VQA; |
| |
| create table T29VQB (a int, b int); |
| insert into T29VQB values (2,2), (3,3), (4,4); |
| |
| -- check the insertions |
| select a from T29VQA; |
| select a from V29VQA; |
| select * from T29VQB; |
| |
| -- both of these should return 1 row (with value of 3) |
| select a from T29VQA where a > (select a from T29VQB where a = 2); |
| select a from V29VQA where a > (select a from T29VQB where a = 2); |
| |
| ?section misc_bind_test |
| |
| create table T29MISCA (a int); |
| create table T29MISCB (a int); |
| insert into T29MISCA values (0),(1); |
| insert into T29MISCB select -a from T29MISCA; |
| create view V29MISC as select syskey,* from T29MISCA |
| ?ifMP |
| for protection |
| ?ifMP |
| ; |
| |
| -- BindRelExpr fix |
| select * from T29MISCA a, (select * from T29MISCB b where a.a=b.a) x; -- 4002 col A.A not found, tbl A not exposed |
| select * from T29MISCA a, T29MISCB b where a.a=b.a; -- 1 row (0) |
| select * from T29MISCA a join T29MISCB b on a.a=b.a; -- 1 row (0) |
| |
| -- NormRelExpr fix |
| insert into V29MISC values(99,99); -- 4013 |
| insert into V29MISC(a) values(99); -- 1 row inserted |
| update V29MISC set syskey=88; -- 4013 |
| update V29MISC set a=88 where a=99; -- 1 row updated |
| select syskey/syskey sk, a from V29MISC; -- 3 rows |
| |
| -- TableNameMap fix |
| select count(*) from T29MISCA, T29MISCA; -- 4056 |
| select count(*) from T29MISCA T29MISCA, T29MISCA; -- 4057 |
| select count(*) from T29MISCA, T29MISCA T29MISCA; -- 4057 |
| select count(*) from T29MISCA x, T29MISCA x; -- 4056 |
| select count(*) from T29MISCA x, T29MISCA y; -- count is 9 |
| select count(*) from T29MISCA x, T29MISCA; -- count is 9 |
| select count(*) from T29MISCA, T29MISCA x; -- count is 9 |
| |
| -- uptill here, rv |
| ?section ddbMX |
| |
| drop view T29xvRN4; |
| drop view T29xvRN3; |
| drop view T29xvRN2; |
| drop view T29xvRN1; |
| drop view T29xv4; |
| drop view T29xv3; |
| drop view T29xv2; |
| drop view T29xv1; |
| drop view T29xv0; |
| |
| drop table T29x; |
| |
| ?section ddbMXMP |
| -- The following drops are from test027 - rv |
| drop view V29VQA; -- these are from ddb6025 of test027 |
| drop table T29VQA; |
| drop table T29VQB; |
| |
| drop view V29MISC; -- These are from section ddb1203 of test027 |
| drop table T29MISCA; |
| drop table T29MISCB; |
