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