-- @@@ 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 @@@
--
-- Ensures that the join over the temp-table scans disappear when not needed
--

set schema CAT1.SCHM;
obey TEST_6_2_1_5(clean_up);
set schema CAT1.SCHM_ALT;
obey TEST_6_2_1_5(clean_up);
log  LOG_6_2_1_5 clear;
set schema CAT1.SCHM;
obey TEST_6_2_1_5(set_up);
obey TEST_6_2_1_5(tests);
set schema CAT1.SCHM_ALT;
obey TEST_6_2_1_5(set_up);
obey TEST_6_2_1_5(tests);
set schema CAT1.SCHM;
obey TEST_6_2_1_5(clean_up);
set schema CAT1.SCHM_ALT;
obey TEST_6_2_1_5(clean_up);
LOG;
exit;

?section clean_up
DROP TRIGGER RT1;
DROP TRIGGER BT1;

?section set_up
prepare countIt from
  select count(*) as NO_OF_TEMP_SCANS
  from table (explain(NULL, 'XX'))
  where operator like 'FILE_SCAN%' and tname like '%__TEMP%';

?section tests
------------------------------------------------------------------------------
-- Test case 1: using NEW only
------------------------------------------------------------------------------

create trigger RT1
after update on tab1A
referencing new as newr
for each row
insert into tab1A values(newr.a, newr.b, newr.c, newr.d);

prepare XX from update tab1A set b=b+1,c=c+1;

-- expected: 1
execute countIt;

drop trigger RT1;

------------------------------------------------------------------------------
-- Test case 2: using OLD only
------------------------------------------------------------------------------

create trigger RT1
after update on tab1A
referencing old as oldr
for each row
insert into tab1A values(oldr.a, oldr.b, oldr.c, oldr.d);

prepare XX from update tab1A set b=b+1,c=c+1;

-- expected: 1
execute countIt;

drop trigger RT1;

------------------------------------------------------------------------------
-- Test case 3: using OLD & NEW
------------------------------------------------------------------------------

create trigger RT1
after update on tab1A
referencing old as oldr, new as newr
for each row
insert into tab1A values(oldr.a, oldr.b, newr.c, newr.d);

prepare XX from update tab1A set b=b+1,c=c+1;

-- expected: 2
execute countIt;

drop trigger RT1;

------------------------------------------------------------------------------
-- Test case 4: using CI cols only
------------------------------------------------------------------------------

create trigger RT1
after update on tab1A
referencing old as oldr
for each row
insert into tab1A values(oldr.a, 0, 0, 0);

prepare XX from update tab1A set b=b+1,c=c+1;

-- expected: 1
execute countIt;

drop trigger RT1;

------------------------------------------------------------------------------
-- Test case 5: using CI cols and OLD only
------------------------------------------------------------------------------

create trigger RT1
after update on tab1A
referencing old as oldr
for each row
insert into tab1A values(oldr.a, oldr.b, 0, 0);

prepare XX from update tab1A set b=b+1,c=c+1;

-- expected: 1
execute countIt;

drop trigger RT1;

------------------------------------------------------------------------------
-- Test case 6: using OLD only although referencing NEW & OLD
------------------------------------------------------------------------------

create trigger RT1
after update on tab1A
referencing old as oldr, new as newr
for each row
insert into tab1A values(oldr.a, newr.b, 0, 0);

prepare XX from update tab1A set c=c+1;

-- expected: 1
execute countIt;

drop trigger RT1;

------------------------------------------------------------------------------
-- Test case 7: using neither OLD nor NEW
------------------------------------------------------------------------------

create trigger RT1
after update on tab1A
referencing old as oldr, new as newr
for each row
insert into tab1A values(0, 0, 0, 0);

prepare XX from update tab1A set b=b+1,c=c+1;

-- expected: 1
execute countIt;

drop trigger RT1;

------------------------------------------------------------------------------
-- Test case 8: using before triggers
------------------------------------------------------------------------------

create trigger BT1
before update on tab1A
referencing new as newr
set newr.b=newr.b+1;

create trigger RT1
after update on tab1A
referencing old as oldr, new as newr
for each row
insert into tab1A values(0, 0, 0, 0);

prepare XX from update tab1A set b=b+1,c=c+1;

-- expected: 4
execute countIt;

drop trigger RT1;
drop trigger BT1;
