core/sql/regress/executor/TEST015

-- Test: TEST015 (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: MERGE statement support
-- Expected files: EXPECTED015
-- Table created:
-- Limitations:
-- To do:
-- Revision history:

drop table temp;
drop table t015t1 cascade;
drop table t015t2 cascade;
drop table t015t3 cascade;
drop table t015t4 cascade;
drop table t015t5 cascade;
drop table t015t6 cascade;
drop table t015t7 cascade;
drop table t015t8 cascade;
drop table t015t9 cascade;
drop table t015t10 cascade;
drop table t015t11 cascade;
drop table t015t12 cascade ;
drop table t015t13 cascade ;
drop table t015t15 cascade ;
drop table t015t14 cascade ;
drop table temp cascade;
drop table ot;
drop table odt cascade;
drop table de cascade ;
drop table dec1 cascade;
drop table t015t16 cascade;
drop table t015tc1;
drop table t015tc2;

--control query default POS 'OFF';

control query default comp_bool_174 'ON';
control query default comp_bool_187 'ON';
control query default attempt_esp_parallelism 'ON';

#ifNT
control query default ARKCMP_FAKE_HW 'ON';
control query default DEF_NUM_SMP_CPUS '2';
control query default DEF_NUM_NODES_IN_ACTIVE_CLUSTERS '1';
#ifNT

create table t015t1 (a int not null, b int default 2 not null, primary key(a))
#ifNT
#ifndef SEABASE_REGRESS
hash partition by (a)
(add location $$partition2$$, add location $$partition2$$, add location $$partition2$$)
#endif
#ifNT
;
create table t015t2 (a int not null, b int not null, primary key(a))
#ifNT
#ifndef SEABASE_REGRESS
hash partition by (a)
(add location $$partition2$$, add location $$partition2$$, add location $$partition2$$)
#endif
#ifNT
;
create table t015t3 (a int not null, b int not null, c int, primary key(a))
#ifNT
#ifndef SEABASE_REGRESS
hash partition by (a)
(add location $$partition2$$, add location $$partition2$$, add location $$partition2$$)
#endif
#ifNT
;
create table t015t4 (a int not null, b int not null, c int, primary key(a))
#ifNT
#ifndef SEABASE_REGRESS
hash partition by (a)
(add location $$partition2$$, add location $$partition2$$, add location $$partition2$$)
#endif
#ifNT
;
create table t015t5 (a int not null, b int not null, c int, d int, e int, primary key(a))
#ifNT
#ifndef SEABASE_REGRESS
hash partition by (a)
(add location $$partition2$$, add location $$partition2$$, add location $$partition2$$)
#endif
#ifNT
;
create table t015t6 (a int not null, b int not null, c int, d int, e int, f int, g int, primary key(a))
#ifNT
#ifndef SEABASE_REGRESS
hash partition by (a)
(add location $$partition2$$, add location $$partition2$$, add location $$partition2$$)
#endif
#ifNT
;
create table t015t7 (a int not null, b largeint generated by default as identity not null not droppable unique, c int, primary key(a))
#ifNT
#ifndef SEABASE_REGRESS
hash partition by (a)
(add location $$partition2$$, add location $$partition2$$, add location $$partition2$$)
#endif
#ifNT
;

create table t015t8 (i int not null, j int, k int, primary key(i))
#ifNT
#ifndef SEABASE_REGRESS
hash partition by (i)
(add location $$partition2$$, add location $$partition2$$, add location $$partition2$$)
#endif
#ifNT
;

create table t015t9  (i int not null, j int, k int, primary key(i))
#ifNT
#ifndef SEABASE_REGRESS
hash partition by (i)
(add location $$partition2$$, add location $$partition2$$, add location $$partition2$$)
#endif
#ifNT
;

create table t015t10 (i int not null, j int, k int, primary key(i))
#ifNT
#ifndef SEABASE_REGRESS
hash partition by (i)
(add location $$partition2$$, add location $$partition2$$, add location $$partition2$$)
#endif
#ifNT
;

create table t015t11 (i char(6) not null, primary key(i))
#ifNT
#ifndef SEABASE_REGRESS
hash partition by (i)
(add location $$partition2$$, add location $$partition2$$, add location $$partition2$$)
#endif
#ifNT
;

cqd hbase_serialization 'off' ;
create table t015t12 (a int not null, b int not null, c int, primary key(a)) salt using 2 partitions ;
cqd hbase_serialization 'on' ;

create table t015t13 (a largeint generated always as identity, b int not null, c int, primary key(a,b));

create table t015t14 (a int not null primary key, b int not null unique);
create table t015t15 (a int not null primary key, constraint cons1 foreign key (a) references t015t14(b)) ;

-- a table with check constraints on non-key columns
create table t015tc1 (a char(4) not null not droppable primary key, b char(4) not null, c char(4) not null, 
                      check (b > 'BASE'), check (c > 'FUNK'));

-- a table with check constraints on key and non-key columns
create table t015tc2 (a char(4) not null not droppable primary key, b char(4) not null, c char(4) not null, 
                      check (a <> 'NUMB'), check (b > 'BASE'), check (c > 'FUNK'));

set session default ESP_RELEASE_WORK_TIMEOUT '-2';
log log015 clear;

create index t015t1i1 on t015t1(a);
create index t015t1i2 on t015t1(b);

create index t015t2i1 on t015t2(a);
create index t015t2i2 on t015t2(b);

create index t015t3i1 on t015t3(a);
create index t015t3i2 on t015t3(b);
create index t015t3i3 on t015t3(c);

create index t015t4i1 on t015t4(a);
create index t015t4i2 on t015t4(b);
create index t015t4i3 on t015t4(c);

create index t015t5i1 on t015t5(a);
create index t015t5i2 on t015t5(b);
create index t015t5i3 on t015t5(c);
create index t015t5i4 on t015t5(d);

create index t015t6i1 on t015t6(a);
create index t015t6i2 on t015t6(b);
create index t015t6i3 on t015t6(c);
create index t015t6i4 on t015t6(d);

create unique index t015t8i1 on t015t8(j);
create unique index t015t8i2 on t015t8(k);

create unique index t015t10i1 on t015t10(j);
create unique index t015t10i2 on t015t10(k);

create index t015t12i1 on t015t12(b) ;
create index t015t12i2 on t015t12(c) salt like table;

create index t015t13i1 on t015t13(c) ;

CREATE TABLE DEC1
(
ID int unsigned GENERATED BY DEFAULT AS IDENTITY (CACHE 5000) NOT NULL,
codeValue varchar(1000),
title varchar(100),
shortName varchar(200),
description varchar(1000),
path varchar(1000),
synonyms varchar(1000),
objectClassCode varchar(100),
propertyCode varchar(100),
PRIMARY KEY (ID)
);
CREATE INDEX IDX3_DEC1_T_CodeValue ON DEC1(codeValue);


CREATE TABLE DE
(
ID int unsigned GENERATED BY DEFAULT AS IDENTITY (CACHE 5000) NOT NULL,
dataElementConceptID int unsigned,
valueDomainID int unsigned,
PRIMARY KEY (ID)
);

CREATE TABLE ODT
(
ID int unsigned GENERATED BY DEFAULT AS IDENTITY (CACHE 5000) NOT NULL,
objectTypeID int unsigned NOT NULL,
dataElementID int unsigned NOT NULL,
objectCategoryTypeCode varchar(20) NOT NULL,
nounObjectDataTypeID int unsigned,
PRIMARY KEY (ID)
);

ALTER TABLE DE
ADD CONSTRAINT FK1_DE_DEC1
FOREIGN KEY (dataElementConceptID)
REFERENCES DEC1(ID);

CREATE INDEX IDX1_DE ON DE(dataElementConceptID, valueDomainID);

ALTER TABLE ODT
ADD CONSTRAINT FK2_ODT_DE
FOREIGN KEY (dataElementID)
REFERENCES DE(ID);

CREATE INDEX IDX1_ODT ON ODT(objectTypeId,objectCategoryTypeCode, dataElementID);

CREATE TABLE OT
(
ID int unsigned GENERATED BY DEFAULT AS IDENTITY (CACHE 1000) NOT NULL,
objectCode varchar(100),
version varchar(2),
PRIMARY KEY (ID)
);

ALTER TABLE OT 
ADD CONSTRAINT UI1_OT UNIQUE (objectCode, version);

create table t015t16 (a largeint  not null, b int not null, 
c int, d int, e int, primary key(a,b)) ;
create index t015t16i1 on t015t16(c) ;
create unique index t015t16i2 on t015t16(d) ;

prepare explainIt from
select substring(operator,1,16) operator
from table (explain(NULL,'XX')) t
where operator LIKE '%TRAFODION_MERGE%' ;

prepare explainItEff from
select substring(operator,1,16) operator
from table (explain(NULL,'XX')) t
where operator LIKE '%SEQUENCE%' ;


prepare explainItVsbb from
select substring(operator,1,25) operator
from table (explain(NULL,'XX2')) t
where operator LIKE '%SEQUENCE%' OR operator LIKE '%VSBB_UPSERT%'  OR operator LIKE '%VSBB_DELETE%' ;


insert into t015t1 values (1,1), (2,2);
insert into t015t3 values (1,1,1), (2,2,2), (3,3,3);
insert into t015t5 values (1,2,3,4,5);
insert into t015t6 values (1,2,3,4,5,6,7);

-- multi column updates

-- simple updates
update t015t3 set (b,c) = (4,4) where a = 2;
select * from t015t3;
update t015t3 set (b,c) = (5,5);
select * from t015t3;
update t015t3 set (b,c) = (b+1,c-1);
select * from t015t3;
update t015t6 set b = 1, (c,d) = (7,7), (e,f,g) = (8,8,8);
select * from t015t6;

-- updates using subquery
update t015t3 set (b,c) = (select a,b from t015t1 where a = 1)
        where a = 3;
select * from t015t3;
update t015t3 set (b,c) = (select a+1,10 from t015t1 where a = 1)
        where a = 3;
select * from t015t3;
update t015t3 set (b,c) = (select a+1,10 from t015t1 where a = 1);
select * from t015t3;

-- simple merge stmt
delete from t015t1;
merge into t015t1 on a = 1 when matched then update set b = -1
                           when not matched then insert values (1,2);
select * from t015t1;
merge into t015t1 on a = 1 when matched then update set b = -1
                           when not matched then insert values (1,2);
select * from t015t1;
merge into t015t1 on a = 1 when matched then update set b = 1 where b > 0
                           when not matched then insert values (1,2);
select * from t015t1;
merge into t015t1 on a = 1 when matched then update set b = 1 where b < 0
                           when not matched then insert values (1,2);
select * from t015t1;

delete from t015t1;
insert into t015t1 values(1,1),(2,2);
merge into t015t1 on a = 1 when matched then update set b = -b where b = 0
                           when not matched then insert values (3,3);
select * from t015t1;
merge into t015t1 on a = 1 when matched then update set b = -b where b = 1
                           when not matched then insert values (3,3);
select * from t015t1;

delete from t015t1;
merge into t015t1 on a = 1 when matched then update set b = -1
                           when not matched then insert (a) values (1);
select * from t015t1;

delete from t015t1;
merge into t015t1 on a = 1 when not matched then insert values (1,2);
select * from t015t1;

merge into t015t1 on a = 1 when not matched then insert values (1,2);
select * from t015t1;

merge into t015t1 on a = 1 when matched then update set b = -1;
select * from t015t1;

delete from t015t1;
merge into t015t1 on a = 1 when matched then update set b = -1;
select * from t015t1;

-- merge stmt with params
delete from t015t1;
prepare s from
merge into t015t1 on a = ? when matched then update set b = ?
                           when not matched then insert values (?,?);
execute s using 1, -1, 1, 2;
select * from t015t1;
execute s using 1, -1, 1, 2;
select * from t015t1;

set param ?a 1;
set param ?b -1;
set param ?b2 2;
delete from t015t1;
merge into t015t1 on a = ?a when matched then update set b = ?b
                           when not matched then insert values (?a,?b2);
select * from t015t1;
merge into t015t1 on a = ?a when matched then update set b = ?b
                           when not matched then insert values (?a,?b2);
select * from t015t1;
merge into t015t1 on a = ?a when matched then update set b = ?a where b >= ?a
                           when not matched then insert values (?a,?b2);
select * from t015t1;
merge into t015t1 on a = ?a when matched then update set b = ?a where b < ?a
                           when not matched then insert values (?a,?b2);
select * from t015t1;

-- merge using rowsets.
-- prepare only since rowsets are not supported from mxci.
prepare s from
merge into t015t1 on a = ?[10] when matched then update set b = ?[10]
                               when not matched then insert values (?[10], ?[10]);

-- merge rows from one table into another
delete from t015t1;
delete from t015t2;
insert into t015t1 values (2,2), (4,4);
insert into t015t2 values (2,3), (6,7);
merge into t015t1 using (select * from t015t2) as t015t2
     on t015t1.a = t015t2.a 
     when matched then update set b = t015t2.b
     when not matched then insert values (t015t2.a, t015t2.b);
select * from t015t1;
merge into t015t1 using (select * from t015t2) as t015t2
     on t015t1.a = t015t2.a 
     when matched then update set b = t015t2.b+1 where b > a
     when not matched then insert values (t015t2.a, t015t2.b);
select * from t015t1;

delete from t015t1;
delete from t015t2;
insert into t015t1 values (2,2), (4,4);
insert into t015t2 values (2,3), (6,7);
merge into t015t1 using (select * from t015t2) as X(y,z)
     on t015t1.a = X.y 
     when matched then update set b = X.z
     when not matched then insert values (X.y, X.z);
select * from t015t1;

delete from t015t3;
delete from t015t4;
insert into t015t3 values (1,1,1), (2,2,2), (3,3,3);
insert into t015t4 values (2,3,4), (3,7,9), (4,5,6);
merge into t015t3 using (select * from t015t4) as t015t4
  on t015t3.a = t015t4.a 
  when matched then update set (b,c) = (3,4)
  when not matched then insert values (t015t4.a,t015t4.b,t015t4.c);
select * from t015t3;

merge into t015t3 on a = 1
  when matched then update set b = 20, c = 40;
select * from t015t3;

-- test fix to bugzilla 3405
delete from t015t3;
delete from t015t4;
insert into t015t3 values (1,1,1), (2,2,2), (3,3,3);
insert into t015t4 values (2,3,4), (3,7,9), (4,5,6);
merge into t015t3 using (select * from t015t4) as t015t4
  on t015t3.a = t015t4.a 
  when matched then update set (b,c) = (3,4) where b=2
  when not matched then insert values (t015t4.a,t015t4.b,t015t4.c);
select * from t015t3;

delete from t015t3;
delete from t015t4;
insert into t015t3 values (1,1,1), (2,2,2), (3,3,3);
insert into t015t4 values (2,3,4), (3,7,9), (4,5,6);
merge into t015t3 using (select * from t015t4) as t015t4
  on t015t3.a = t015t4.a 
  when matched then update set (b,c) = (3,4) where t015t4.a=3
  when not matched then insert values (t015t4.a,t015t4.b,t015t4.c);
select * from t015t3;

-- merge of a joined table into another
delete from t015t1;
delete from t015t2;
insert into t015t1 values (2,2), (4,4), (6,8);
insert into t015t2 values (2,3), (6,7);
delete from t015t3;
delete from t015t4;
insert into t015t3 values (1,1,1), (2,2,2), (3,3,3);
insert into t015t4 values (2,3,4), (3,7,9), (4,5,6);
select * from t015t3;
merge into t015t3 using (select x.a, x.b  from t015t1 x, t015t2 y where x.a = y.a) as t(a,b)
  on t015t3.a = t.a
  when matched then update set (b,c) = (b+t.a, c-t.b)
  when not matched then insert values (t.a, t.b, 5);
select * from t015t3;

-- reflexive updates
delete from t015t1;
delete from t015t2;
insert into t015t1 values (2,2), (4,4), (6,8);
insert into t015t2 values (2,3), (6,7);
merge into t015t1 on a = 2 when matched then update set b = b+1
                           when not matched then insert values (2,4);
select * from t015t1;
delete from t015t1 where a = 2;
merge into t015t1 on a = 2 when matched then update set b = b+1
                           when not matched then insert values (2,4);
select * from t015t1;

-- reflexive updates using rowsets
prepare s from
  Merge into t015t3 on a = ?[10] 
           When matched then update set (b,c) = (b + ?[10], ?[10])
           When not matched then insert values (?[10], ?[10], ?[10]);

-- reflexive update from one table into another
delete from t015t3;
delete from t015t4;
insert into t015t3 values (1,1,1), (2,2,2), (3,3,3);
insert into t015t4 values (2,3,4), (3,7,9), (4,5,6);
merge into t015t3 using (select * from t015t4) as t4
  on a = t4.a 
  when matched then update set (b,c) = (b+t4.b, t4.c)
  when not matched then insert values (t4.a,t4.b,t4.c);
select * from t015t3;

-- explain for update with multi-col
-- ignored for now due to non-deterministic explain plans

?ignore
-- before merge stmt was added
explain options 'f'
update t015t6 set b = (select a from t015t5 where a = 1),
                  c = (select b from t015t5 where a = 1),
                  d = (select c from t015t5 where a = 1),
                  e = (select d from t015t5 where a = 1),
                  f = (select e from t015t5 where a = 1)
   where a = 3;

-- using update with multi-col
explain options 'f'
update t015t6 set (b,c,d,e,f) = (select a,b,c,d,e from t015t5 where a = 1)
        where a = 3;

-- using merge stmt
explain options 'f'
merge into t015t6 using (select a,b,c,d,e from t015t5 where a = 1) x
  on a = 3
  when matched then update set (b,c,d,e,f) = (x.a, x.b, x.c, x.d, x.e);
?ignore

#ifndef SEABASE_REGRESS
-- embedded tests for rowsets
-- remove all the products of the make
sh rm -f etest015.is;
sh rm -f etest015.c etest015.cpp etest015.exe etest015.ilk etest015.lst etest015.m etest015.obj etest015.pdb;

-- Preprocess, sql compile, C compile application. Redirect output to LOG015.
sh sh ./makefileall.ksh etest015 ${mxcmpdir} ${mxcidir} 2>&1;

delete from t015t1;
delete from t015t8;
delete from t015t10;
-- run the executable. Redirect output to LOG015
sh ./etest015.exe 2>&1 | tee -a LOG015;

select * from t015t1;

select * from t015t8;
set parserflags 1;
select * from table (index_table t015t8i1);
select * from table (index_table t015t8i2);

select * from t015t10;
select * from table (index_table t015t10i1);
select * from table (index_table t015t10i2);
#endif
#ifdef SEABASE_REGRESS
delete from t015t1;
insert into t015t1 values (1,2),(4,8),(9,18),(2,4),(3,6),(6,12),(8,16),(0,0),(5,10),(7,14);

delete from t015t10;
insert into t015t10 values (10,11,12),(1,2,3),(4,5,6),(9,10,11),(2,3,4),(3,4,5),(6,7,8),(5,6,7),(7,8,9),(8,9,10);
#endif

delete from t015t2;
merge into t015t2 using (select * from t015t1) x
                  on a = x.a and mod (a,2) = 0
                  when matched then update set b = -1
                  when not matched then insert values (x.a, -2);
select * from t015t2;

merge into t015t2 using (select * from t015t1) x
                  on a = x.a and mod (a,2) = 0
                  when matched then update set b = -1;
select * from t015t2;

merge into t015t2 using (select * from t015t1) x
                  on a = x.a and mod (a,2) = 0
                  when not matched then insert values (x.a, -4);
select * from t015t2;

control query default olt_query_opt 'OFF';
delete from t015t2;
merge into t015t2 using (select * from t015t1) x
                  on a = x.a and mod (a,2) = 0
                  when matched then update set b = -3
                  when not matched then insert values (x.a, -2);
select * from t015t2;

merge into t015t2 using (select * from t015t1) x
                  on a = x.a and mod (a,2) = 0
                  when matched then update set b = -1;
select * from t015t2;

-- non-unique ON clause with merge/update without insert clause
delete from t015t1;
delete from t015t3;
insert into t015t1 values (1,1), (2,2);
insert into t015t3 values (2,2,3);
merge into t015t3 using (select a,b from t015t1) x
  on t015t3.b = x.b 
  when matched then update set c = x.b;
select * from t015t3;

-- non-unique ON clause with merge/delete without insert clause
merge into t015t3 using (select a,b from t015t1) x
  on t015t3.b = x.b 
  when matched then delete;
select * from t015t3;

-- IDENTITY column allowed if not part of the primary key
merge into t015t7 on a = 1 when matched then update set c = 3
                           when not matched then insert (a,c) values (1,2);
select a,c from t015t7;
merge into t015t7 on a = 1 when matched then update set c = 3
                           when not matched then insert (a,c) values (1,2);
select a,c from t015t7;

control query default olt_query_opt 'ON';

-- negative cases
delete from t015t1;
delete from t015t3;
insert into t015t1 values (1,1), (2,2);
insert into t015t3 values (1,1,1), (2,2,2), (3,3,3);

-- updated columns must be within parens
update t015t3 set b,c = 10,20;

-- updated values must be within parens
update t015t3 set (b,c) = 10,20;

-- error: only one subquery in the set values clause
update t015t3 set (b,c) = (10, (select a from t015t1));

-- error: only one subquery in the set values clause
update t015t3 set (b,c) = ((select a from t015t1), (select a from t015t1));

update t015t5 set (b,c) = (select a,b from t015t1),
                  (d,e) = (1,2);

-- number of columns should match
update t015t3 set (b,c) = (select a,b,10 from t015t1);

-- should return error: more than one rows from the subquery
update t015t3 set (b,c) = (select a,b from t015t1)
        where a = 3;
update t015t3 set (b,c) = (select a,b from t015t1);

-- merge stmt ON clause must be unique if insert clause is specified
merge into t015t1 on a >= 0 when matched then update set b = -1
                           when not matched then insert values (1,2);
merge into t015t1 on a = 1 or b = 2 when matched then update set b = -1
                           when not matched then insert values (1,2);
merge into t015t3 using (select a,b from t015t1) x
  on t015t3.b = x.b 
  when matched then update set c = x.b
  when not matched then insert values (1,2,3);

-- non-unique ON clause with merge/delete not allowed with insert clause
merge into t015t3 using (select a,b from t015t1) x
  on t015t3.b = x.b 
  when matched then delete
  when not matched then insert values (1,2,3);

-- test bugzilla 3424
create table temp(a int not null primary key,b int);

merge into temp on a=1 when matched then update set b=2 where b=1
when not matched then insert(a) values(20);
select * from temp;

insert into temp values(1,1);
merge into temp on a=1 when matched then update set b=2 where b=1
when not matched then insert(a) values(20);
select * from temp;
  
drop table temp;
create table temp (a int, z int not null, primary key(z)) no partition;
merge into temp on a = 1 when matched then update set a = 1;
merge into temp on a = 1 when not matched then insert values (1, 2);
merge into temp on a = 1 
                when matched then update set a = 1
                when not matched then insert values (1, 2);


-- merge stmt ON clause key must be the same as key value in insert stmt
delete from t015t1;
merge into t015t1 on a = 1 when matched then update set b = -1
                           when not matched then insert values (2,2);

-- subquery not allowed in insert...values clause
merge into t015t1 using (select * from t015t2) as t015t2
     on t015t1.a = t015t2.a 
     when matched then update set b = t015t2.b
     when not matched then insert values ((select a from t015t2 where a = 1),
                                          (select b from t015t2 where a = 1));

-- subquery not allowed in update...set clause
merge into t015t3 
  on t015t3.a = 4 
  when matched then update set (b,c) = (select 5,6 from t015t4)
  when not matched then insert values (4,5,6);


-- subquery not allowed in ON clause
merge into t015t1 using (select * from t015t4) x
                  on a = (select a from t015t2 where a = 1) 
                  when matched then update set b = -1
                  when not matched then insert values (1,2);
merge into t015t1 on a in (select a from t015t2) 
                  when matched then update set b = -1
                  when not matched then insert values (1,2);

-- aggregate not allowed in "merge ... update where" clause
merge into t015t1 using (select * from t015t4) x on a = 1
                  when matched then update set b = -1 where max(b) > 0
                  when not matched then insert values (1,2);
merge into t015t1 on a = max(b)
                  when matched then update set b = -1
                  when not matched then insert values (1,2);

-- set on rollback not allowed with list specification in SET clause
update t015t3 set (b,c) = (1,2) set on rollback (b,c) = (-1, -2);
update t015t3 set (b,c) = (1,2) set on rollback (b,c) = (select a,b from t015t1);
update t015t3 set (b,c) = (1,2) set on rollback b = -2;

-- SYSKEY not allowed
drop table temp;
create table temp (a int) no partition;
merge into temp on syskey = 1 when matched then update set a = 10;
merge into temp on syskey = 1 when not matched then insert values (1);

-- IDENTITY column
drop table temp;
create table temp (a largeint generated by default as identity not null not droppable, b int, primary key(a)) no partition;
merge into temp on a = 1 when matched then update set b = 10;
merge into temp on a = 1 when not matched then insert values (1, 2);

-- view
create view vtemp as select * from t015t1;
merge into vtemp on a = 1 when matched then update set b = 10
                          when not matched then insert values (1,2);

-- Merge stmt and unique index
set parserflags 1;
delete from t015t8;
insert into t015t8 values (1,2,3);
prepare xx from MERGE INTO t015t8 ON i = 2
      WHEN MATCHED THEN UPDATE SET j = 3
      WHEN NOT MATCHED THEN INSERT VALUES (2,2,3);
execute xx;
select * from t015t8;
select * from table (index_table t015t8i1);
select * from table (index_table t015t8i2);

prepare xx from MERGE INTO t015t8 ON i = 5
      WHEN MATCHED THEN UPDATE SET j = 6
      WHEN NOT MATCHED THEN INSERT VALUES (5,6,7);
execute xx;
select * from t015t8;
select * from table (index_table t015t8i1);
select * from table (index_table t015t8i2);

prepare xx from MERGE INTO t015t8 ON i = 1
      WHEN MATCHED THEN UPDATE SET (j,k) = (6,7)
      WHEN NOT MATCHED THEN INSERT VALUES (1,6,7);
execute xx;
select * from t015t8;
select * from table (index_table t015t8i1);
select * from table (index_table t015t8i2);

prepare xx from MERGE INTO t015t8 ON i = 1
      WHEN MATCHED THEN UPDATE SET j = 6
      WHEN NOT MATCHED THEN INSERT VALUES (1,6,7);
execute xx;
select * from t015t8;
select * from table (index_table t015t8i1);
select * from table (index_table t015t8i2);

insert into t015t9 values(1,6,3);
prepare xx from MERGE INTO t015t8 using(select * from t015t9) z(i,j,k)
      ON i = z.i
      WHEN MATCHED THEN UPDATE SET j = z.j
      WHEN NOT MATCHED THEN INSERT VALUES (z.i,z.j,z.k);
execute xx;
select * from t015t8;
select * from table (index_table t015t8i1);
select * from table (index_table t015t8i2);

delete from t015t8 ;
upsert into t015t8 values (1,2,3),(11,12,13) ;
select * from t015t8;
select * from table (index_table t015t8i1);
select * from table (index_table t015t8i2);
delete from t015t8 where i > 10;
cqd TRAF_UPSERT_TO_EFF_TREE 'on';
upsert into t015t8 values (1,2,3),(11,12,13) ;
cqd TRAF_UPSERT_TO_EFF_TREE reset;
select * from t015t8;
select * from table (index_table t015t8i1);
select * from table (index_table t015t8i2);
MERGE INTO t015t8 ON i = 1
      WHEN MATCHED THEN UPDATE SET j = 12
      WHEN NOT MATCHED THEN INSERT VALUES (1,3,4);
select * from t015t8;
select * from table (index_table t015t8i1);
select * from table (index_table t015t8i2);




-- Merge stmt and added column.
alter table t015t11 add column j int default 0 not null;
merge into t015t11 on i = 'c' 
  when matched then update set j = 4
  when not matched then insert values ('c', 2);  

-- Upsert that gets transformed
upsert into t015t12 values (1,1,1) ;
select * from t015t12;
upsert into t015t12 values (1,-1,1) ;  
select * from t015t12 ;
upsert into t015t12 values (2,2,2),(3,3,3),(1,-1,-1);
select * from t015t12 ;
select * from table(index_table t015t12i1) ;
select * from table(index_table t015t12i2) ;

upsert into t015t13(b,c) values (1,1);
upsert into t015t13(b,c) values (1,2);
upsert into t015t13(b,c) values (2,2),(3,3),(4,4);
select * from t015t13; 
explain options 'f' upsert into t015t13 (b,c) values (1,1) ;

-- Upsert(transformed) on table with RI constraint & unique index
upsert into t015t14 values (1,2),(11,12);
upsert into t015t15 values (2);
upsert into t015t14 values (1,3);

-- Upsert/merge with self-referntial subquery in values list
delete from t015t7;
upsert into t015t7 values (1, NVL((select b from t015t7 where a = 1),DEFAULT),10) ;
select * from t015t7;
upsert into t015t7 values (1, NVL((select b+1 from t015t7 where a = 1),DEFAULT),20) ;
select * from t015t7;

-- guarding against error 7000 and compiler asserts

prepare XX from UPSERT INTO DEC1 
(ID, codeValue, title , description, path, synonyms, objectClassCode, propertyCode)  
VALUES  (NVL((select a.id from DEC1 a where a.codeValue = ?a[10]), DEFAULT), 
?b[10], ?c[10], ?d[10], ?e[10], null, ?f[10], null) ;
-- missing default value and default upsert_mode=merge so will transform as merge
execute explainIt;

prepare XX from UPSERT INTO DEC1 
(ID, codeValue, title , description, path, synonyms, objectClassCode, propertyCode)  
VALUES  (?a, ?b, ?c, ?d, ?e, null, ?f, null) ;
-- missing default value and default upsert_mode=merge so will transform as merge
execute explainIt;

prepare XX from UPSERT INTO DEC1 
(ID, codeValue, title , description, path, synonyms, objectClassCode, propertyCode)  
VALUES  (NVL((select a.id from DEC1 a where a.codeValue = ?a), DEFAULT), 
?b, ?c, ?d, ?e, null, ?f, null) ;
-- missing default value and default upsert_mode=merge so will transform as merge
execute explainIt;

cqd TRAF_UPSERT_MODE 'OPTIMAL';
-- missing default value and default upsert_mode=optimal so will transform as new efficient tree for the next 3 cases 
prepare XX from UPSERT INTO DEC1 
(ID, codeValue, title , description, path, synonyms, objectClassCode, propertyCode)  
VALUES  (NVL((select a.id from DEC1 a where a.codeValue = ?a[10]), DEFAULT), 
?b[10], ?c[10], ?d[10], ?e[10], null, ?f[10], null) ;
execute explainItEff;

prepare XX from UPSERT INTO DEC1 
(ID, codeValue, title , description, path, synonyms, objectClassCode, propertyCode)  
VALUES  (?a, ?b, ?c, ?d, ?e, null, ?f, null) ;
execute explainItEff;

prepare XX from UPSERT INTO DEC1 
(ID, codeValue, title , description, path, synonyms, objectClassCode, propertyCode)  
VALUES  (NVL((select a.id from DEC1 a where a.codeValue = ?a), DEFAULT), 
?b, ?c, ?d, ?e, null, ?f, null) ;
execute explainItEff;

cqd TRAF_UPSERT_MODE reset;


prepare XX from UPSERT INTO DE 
(ID, dataElementConceptID, valueDomainID)  VALUES  
(NVL((select a.id from DE a where a.dataElementConceptID = 
(select b.id from DEC1 b where b.codeValue = ?) and a.valueDomainID = ? ), DEFAULT),  
NVL((select d.id from DEC1 d where d.codeValue = ?), NULL),  ? ) ;
execute explainItEff;

prepare XX from UPSERT INTO ODT 
(ID, objectTypeID, dataElementID, objectCategoryTypeCode)  
VALUES  (NVL((select a.id from ODT a where a.objectTypeID = 
(select b.id from OT b where b.objectCode = ? and b.version = ?) and  
a.objectCategoryTypeCode = ? and  a.dataElementID = 
(select d.id from DE d where d.dataElementConceptID = 
(select e.id from DEC1 e where e.codeValue = ?) and d.valueDomainID = ? ) ), DEFAULT), 
NVL((select g.id from OT g where g.objectCode = ? and g.version = ?), NULL),   
NVL((select h.id from DE h where h.dataElementConceptID = 
(select i.id from DEC1 i where i.codeValue = ?) and h.valueDomainID = ? ), NULL), ? );
--missing values and traf_mode=merge default transforms into merge
execute explainIt;

prepare XX from UPSERT INTO DE 
(ID, dataElementConceptID, valueDomainID)  VALUES  
(NVL((select a.id from DE a where a.dataElementConceptID = 
(select b.id from DEC1 b where b.codeValue = ?) and a.valueDomainID = ? ), DEFAULT),  
(select d.id from DEC1 d where d.codeValue = ?),  ? ) ;
execute explainItEff;

prepare XX from UPSERT INTO DE (ID, dataElementConceptID, valueDomainID)  
VALUES  (1,  (select d.id from DEC1 d where d.codeValue = ?),  3 ) ;
execute explainItEff;

prepare XX from UPSERT INTO DE (ID, dataElementConceptID, valueDomainID)  
VALUES  (?[10], ?[10], ?[10] ) ;
execute explainItEff;

prepare XX from UPSERT INTO DE (ID, dataElementConceptID, valueDomainID)  
VALUES  (1,  (select d.id from DEC1 d where d.codeValue = 'aa'),  3 ) ;
execute explainItEff;

--upsert transformation to eff tree tests
cqd traf_upsert_to_eff_tree 'ON';
prepare xx from upsert into t015t16 values (1,2,3,4,5),(6,7,8,9,10);
explain options 'f' xx;
execute xx;
select * from t015t16 order by a;
prepare xx2 from upsert into t015t16 values (10,11,12,13,14);
execute xx2;
select * from t015t16 order by a;

prepare xx2 from upsert into t015t16 values (1,2,30,40,50);
execute xx2;
select * from t015t16 order by a;


--For single row param input, ensure plan has no vsbb operators
prepare xx2 from upsert into t015t16 values(?,?,?,?,?);
execute explainItVsbb;
--For multiple rows, ensure plan has vsbb upsert, vsbb delete
prepare xx2 from upsert into t015t16 values(?[10],?[10],?[10],?[10],?[10]);
execute explainItVsbb;
--For upsert-select, ensure plan has vsbb_upsert and vsbb delete
prepare xx2 from UPSERT INTO t015t16  select a,b,c,d,e from t015t6 ;
execute explainItVsbb;

--For multiple values , ensure plan has vsbb upsert and vsbb delete
prepare xx2 from upsert into t015t16 values (3,4,1,1,1),(6,7,80,90,100),(6,7,81,91,101);
execute explainItVsbb;
execute xx2;
select a,b from t015t16 order by a;

cqd traf_upsert_to_eff_tree reset;

-- merge tests (and insert and update tests) on tables with check constraints
insert into t015tc1 values ('ACID','FOUR','GUNK'), ('FIVE','FIVE','HIVE'),('TENT','TEXT','TEST');
select * From t015tc1;

insert into t015tc2 values ('ACID','FOUR','GUNK'), ('FIVE','FIVE','HIVE'),('TENT','TEXT','TEST');
select * From t015tc2;

-- should fail, constraint violation
merge into t015tc1 on a='FIVE' when matched then update set c= 'BALD';

-- should fail, constraint violation
merge into t015tc1 on a = 'TENT' when matched then update set b = 'ACID';

select * From t015tc1; 

-- no constraint violation
merge into t015tc1 on a='FIVE' when matched then update set c= 'GLAD';

-- should fail because 'BOMB' < 'FUNK'
merge into t015tc1 on a = 'NOSH' when matched then update set b = 'GOOD'
  when not matched then insert values ('GOOD','GOOD','BOMB');

-- the row GOOD, GOOD, NICE should successfully be added
merge into t015tc1 on a = 'NOSH' when matched then update set b = 'GOOD'
  when not matched then insert values ('GOOD','GOOD','NICE');

select * from t015tc1;

-- should fail because of constraint on C
insert into t015tc1 values ('ACID','TEST','ACID');

-- should fail because of constraint on B
insert into t015tc1 values ('ACID','ACID','TEST');

-- should fail because of constraint on B
update t015tc1 set a = 'COOL', b = 'ACID' where a = 'TENT';

select * from t015tc1;

-- should fail because of constraint on A
insert into t015tc2 values ('NUMB','NUMB','NUMB');

-- should fail because of constraint on A
update t015tc2 set a = 'NUMB', b = 'OKAY' where a = 'TENT';

-- should fail because of constraint on B
merge into t015tc1 on a = 'NOSH' when matched then update set b = 'ACID';

select * from t015tc2;


log;