>>obey TESTMV850(set_up);
>>create table T850_T1 (
+>          id int no default not null
+>	, name char(30) no default not null
+>	, age smallint no default not null
+>	, primary key(id)
+>)
+>store by (id)
+>attribute all mvs allowed
+>;

--- SQL operation complete.
>>
>>create table T850_T2 (
+>	  owner int no default not null
+>	, car char(30) no default not null
+>	, year_model smallint no default not null
+>)
+>attribute all mvs allowed
+>;

--- SQL operation complete.
>>
>>create materialized view T850_MV1
+>  refresh on request
+>  initialize on create
+>  as select name, car
+>     from T850_T1 a, T850_T2 b
+>     where a.id = b.owner;

*** WARNING[12112] A secondary index CATMVS.MVSCHM.T850_MV1_@ts@_@num@ was created for the materialized view.

--- SQL operation completed with warnings.
>>
>>obey TESTMV850(setup_statements);
>>prepare stat1 from
+>  select name, car
+>  from T850_MV1
+>  order by name,car;

--- SQL command prepared.
>>
>>prepare stat2 from
+>  select name, car
+>  from T850_T1 a, T850_T2 b
+>  where a.id = b.owner
+>  order by name,car;

--- SQL command prepared.
>>
>>obey TESTMV850(test1);
>>insert into T850_T1 values (1,'Mickey Mouse',5);

--- 1 row(s) inserted.
>>
>>-- expected: MV still empty.
>>refresh T850_MV1;

--- SQL operation complete.
>>obey compare;
>>  log TEMP1 clear;
1,2c1,2
< >>  log TEMP1;
< >>  execute stat1;
---
> >>  log TEMP2;
> >>  execute stat2;
>>
>>insert into T850_T2 values (1,'Mustang',1956);

--- 1 row(s) inserted.
>>
>>-- expected: Now Mickey has a Mustang
>>refresh T850_MV1;

--- SQL operation complete.
>>obey compare;
>>  log TEMP1 clear;
1,2c1,2
< >>  log TEMP1;
< >>  execute stat1;
---
> >>  log TEMP2;
> >>  execute stat2;
>>
>>insert into T850_T2 values (1,'Schevrolet Sunbird',2850);

--- 1 row(s) inserted.
>>
>>-- expected: Mickey has both Mustang and Schevrolet Sunbird
>>refresh T850_MV1;

--- SQL operation complete.
>>obey compare;
>>  log TEMP1 clear;
1,2c1,2
< >>  log TEMP1;
< >>  execute stat1;
---
> >>  log TEMP2;
> >>  execute stat2;
>>
>>-- Massive updates
>>insert into T850_T1
+>values  (2,'Donald',3)
+>       ,(3,'Bugs Bunny',43)
+>       ,(4,'Roger Rabbit',21)
+>       ,(5,'Snow white',178)
+>       ,(6,'Sinderella',18)
+>       ,(7,'Homer Simpson',3)
+>       ;

--- 6 row(s) inserted.
>>
>>-- This is just to avoid multi-delta refresh.
>>--refresh T850_MV1;
>>--obey compare;
>>--sh diff TEMP1 TEMP2 >> LOGMV850;
>>--log LOGMV850;
>>
>>insert into T850_T2
+>values  (2,'Mitzubishi',1986)
+>       ,(3,'Megan',2850)
+>       ,(4,'Subaru',2000)
+>       ,(5,'Honda Accord',1988)
+>       ,(6,'Rolls Roise',1972)
+>       ,(8,'Lantis',1968)
+>       ,(8,'Lada',2002)
+>       ,(3,'Suzuki Swift',1980)
+>       ,(4,'Opel Astra',1999)
+>       ,(8,'Ford Fiesta',1980)
+>       ;

--- 10 row(s) inserted.
>>
>>refresh T850_MV1;

--- SQL operation complete.
>>obey compare;
>>  log TEMP1 clear;
1,2c1,2
< >>  log TEMP1;
< >>  execute stat1;
---
> >>  log TEMP2;
> >>  execute stat2;
>>
>>obey TESTMV850(test2);
>>-- direct update of MV ('name' is not part of the join predicate)
>>update T850_T1 set name = 'Donald Duck'
+>where id = 2;

--- 1 row(s) updated.
>>
>>-- expected: 'Donald Duck' owns 'Mitzubishi'
>>refresh T850_MV1;

--- SQL operation complete.
>>obey compare;
>>  log TEMP1 clear;
1,2c1,2
< >>  log TEMP1;
< >>  execute stat1;
---
> >>  log TEMP2;
> >>  execute stat2;
>>
>>-- direct update of MV ('car' is not part of the join predicate)
>>update T850_T2 set car = 'Mazda 626'
+>where owner = 6;

--- 1 row(s) updated.
>>
>>-- expected: 'Sinderella' owns 'Mazda 626'
>>refresh T850_MV1;

--- SQL operation complete.
>>obey compare;
>>  log TEMP1 clear;
1,2c1,2
< >>  log TEMP1;
< >>  execute stat1;
---
> >>  log TEMP2;
> >>  execute stat2;
>>
>>-- direct update of MV. Only 'car' column should be updated in MV.
>>-- 'year' column is not in use (irelevant).
>>update T850_T2 set car = 'Rover', year_model = 2850
+>where owner = 4;

--- 2 row(s) updated.
>>
>>-- expected: 'Roger Rabbit' owns 2 'Rover' cars
>>refresh T850_MV1;

--- SQL operation complete.
>>obey compare;
>>  log TEMP1 clear;
1,2c1,2
< >>  log TEMP1;
< >>  execute stat1;
---
> >>  log TEMP2;
> >>  execute stat2;
>>
>>obey TESTMV850(test3);
>>-- indirect update. owner is part of the join predicate.
>>update T850_T2 set owner = 7
+>where owner = 8 and year_model = 1980;

--- 1 row(s) updated.
>>
>>-- expected: 'Homer Simpson' owns now 'Ford Fiesta'
>>refresh T850_MV1;

--- SQL operation complete.
>>obey compare;
>>  log TEMP1 clear;
1,2c1,2
< >>  log TEMP1;
< >>  execute stat1;
---
> >>  log TEMP2;
> >>  execute stat2;
>>
>>obey TESTMV850(test4);
>>-- irrelevant update.
>>
>>update T850_T2 set year_model = 2002
+>  where owner = 8 and car = 'Lantis';

--- 1 row(s) updated.
>>
>>--expected: 0
>>refresh T850_MV1;

--- SQL operation complete.
>>obey compare;
>>  log TEMP1 clear;
1,2c1,2
< >>  log TEMP1;
< >>  execute stat1;
---
> >>  log TEMP2;
> >>  execute stat2;
>>
>>obey TESTMV850(test5);
>>-- delete records from base tables. MV updated accordingly.
>>delete from T850_T2 where owner = 3 and car = 'Suzuki Swift';

--- 1 row(s) deleted.
>>
>>-- expected: 'Bugs Bunny' sold his 'Suzuki Swift' (doesn't own it anymore)
>>refresh T850_MV1;

--- SQL operation complete.
>>obey compare;
>>  log TEMP1 clear;
1,2c1,2
< >>  log TEMP1;
< >>  execute stat1;
---
> >>  log TEMP2;
> >>  execute stat2;
>>
>>obey TESTMV850(clean_up);
>>drop materialized view T850_MV1;

--- SQL operation complete.
>>drop table T850_T1;

--- SQL operation complete.
>>drop table T850_T2;

--- SQL operation complete.
>>
>>exit;

End of MXCI Session

