file connect ( sut sut ! 
diagnose &1 !
commit release !

*****************************************************************************
* D01SEL
file connect ( sut sut !
create user kern password test dba not exclusive !
commit work release !
file connect (kern test !
create table tab ( s1 fixed(5), s2 fixed(3),s3 fixed(3),s4 char(5))!
insert into tab values (5,1,5,'bla')!
insert into tab values (0,2,5,'arg')!
insert into tab values (3,3,5,'ist')!
insert into tab values (4,4,5,'soll')!
insert into tab values (2,5,5,'wer')!
insert into tab values (1,6,5,'mein')!
create table tab1 (s1 fixed (5),s2 char(5))!
insert into tab1 values (5,'ente')!
commit work release!
file connect ( kern test !
select tab.*,tab1.s2 from tab,tab1 where tab.s3 = tab1.s1 order by 1 !
fetch !
select tab.*,tab1.s2 from tab,tab1 where tab.s3 = tab1.s1
order by 1 DESC!
fetch !
select tab.*,tab1.s2 from tab,tab1 where tab.s3 = tab1.s1 
order by tab.s1 !
fetch !
select tab.*,tab1.s2 from tab,tab1 where tab.s3 = tab1.s1 
order by tab.s1 DESC!
fetch !
select tab.s1,tab.s4,tab1.s2 from tab,tab1 where tab.s3 = tab1.s1
order by tab1.s2 !
fetch !
select tab.s1,tab.s4,tab1.s2 from tab,tab1 where tab.s3 = tab1.s1
order by tab1.s2 DESC!
fetch !
select tab.*,tab1.s2 from tab,tab1 where tab.s3 = tab1.s1
order by substr(tab.s4,2,2) !
fetch !
select tab.*,tab1.s2 from tab,tab1 where tab.s3 = tab1.s1
order by substr(tab.s4,2,2) DESC!
fetch !
commit work release !

file connect ( kern test !
select min(tab.s1),max(tab.s4),tab1.s2 from tab,tab1 where tab.s3 = tab1.s1
group by tab1.s2 !
fetch !
select substr(tab.s4,2,2),min(tab1.s2) from tab,tab1 where tab.s3 = tab1.s1
group by substr(tab.s4,2,2) !
fetch !
commit work release !
file connect ( sut sut !
drop user kern !
commit work release !

*****************************************************************************
* DML.vdnts
file connect ( sut sut !
create user kern password test dba not exclusive !
commit work release !
file connect ( kern test !
create table t ( a char (8) key )!
insert t set a = 'aaa'!
create table t1 (a char (8) key, b char (8) key, c char (8))!
insert t1 set a = 'aaa', b = 'bbb', c = 'ccc' !
create view v1 as select x=t.a, t1.* from t, t1 where t.a = t1.a!
select * into :a from t where a = 'aaa'!
pars_then_ex !
select t.a, b = t1.a into :a, :b from t, t t1 where t1.a = 'aaa'!
select * into :a, :b, :c from v1 where a = 'aaa' !
pars_execute !
drop table t !
drop table t1 !
commit release !
file connect ( sut sut !
drop user kern !
commit work release !

*****************************************************************************
* some catalog join tests 
file connect ( sut sut !
select taskname, statedesc from sysdd.SYSMON_TASK_DETAIL td 
WHERE 
  td.TASKNAME in ('User', 'Timer') AND 
  NOT td.STATEDESC LIKE 'Connect wait' AND 
  NOT td.STATEDESC LIKE 'Inactive' 
  order by statedesc !
fetch !
select * from sysdd.RUNNING_commands!

explain select taskname, statedesc from 
 sysdd.SYSMON_TASK_DETAIL td, 
 sysdd.RUNNING_commands rp
WHERE 
  rp.TASKID (+) = td.DBPID AND 
  td.TASKNAME in ('User', 'Timer') AND 
  NOT td.STATEDESC LIKE 'Connect wait' AND 
  NOT td.STATEDESC LIKE 'Inactive' 
  order by statedesc !
select taskname, statedesc from 
 sysdd.SYSMON_TASK_DETAIL td, 
 sysdd.RUNNING_commands rp
WHERE 
  rp.TASKID (+) = td.DBPID AND 
  td.TASKNAME in ('User', 'Timer') AND 
  NOT td.STATEDESC LIKE 'Connect wait' AND 
  NOT td.STATEDESC LIKE 'Inactive' 
  order by statedesc !
fetch!

* reverse join order
explain select taskname, statedesc from 
 sysdd.RUNNING_commands rp,
 sysdd.SYSMON_TASK_DETAIL td
WHERE 
  rp.TASKID (+) = td.DBPID AND 
  td.TASKNAME in ('User', 'Timer') AND 
  NOT td.STATEDESC LIKE 'Connect wait' AND 
  NOT td.STATEDESC LIKE 'Inactive' 
  order by statedesc !
select taskname, statedesc from 
 sysdd.RUNNING_commands rp,
 sysdd.SYSMON_TASK_DETAIL td
WHERE 
  rp.TASKID (+) = td.DBPID AND 
  td.TASKNAME in ('User', 'Timer') AND 
  NOT td.STATEDESC LIKE 'Connect wait' AND 
  NOT td.STATEDESC LIKE 'Inactive' 
  order by statedesc !
fetch!
commit work release !


*****************************************************************************
* test for PTS 1132276
file connect ( sut sut !
create user kern password test dba not exclusive !
commit work release !
file connect (kern test !

create table tab( c1 int, c2 int, c3 int,
primary key(c3))!
create unique index i1 on tab(c2)!
insert into tab values(0,0,0)!
insert into tab values(1,1,1)!
insert into tab values(2,2,2)!
insert into tab values(3,3,3)!
insert into tab values(3,4,4)!
insert into tab values(4,5,5)!
insert into tab values(4,6,6)!
insert into tab values(0,7,7)!

explain
select /*+ordered,join(access=L2_more_strategies,
  L2_strat_no_optim,indexaccess)*/ 
 t1.c3 from tab t1, tab t2
where
t1.c3=0 or t1.c3=1 or t1.c3=2 or t1.c3=2 !

select /*+ordered,join(access=L2_more_strategies,
  L2_strat_no_optim,indexaccess)*/ 
 t1.c3 from tab t1, tab t2
where
t1.c3=0 or t1.c3=1 or t1.c3=2 or t1.c3=2 !
fetch!

explain
select /*+ordered,join(;access=L2_more_strategies,
  L2_strat_no_optim,indexaccess)*/ 
 t1.c3 from tab t2, tab t1
where
t1.c3=0 or t1.c3=1 or t1.c3=2 or t1.c3=2 !

select /*+ordered,join(;access=L2_more_strategies,
  L2_strat_no_optim,indexaccess)*/ 
 t1.c3 from tab t2, tab t1
where
t1.c3=0 or t1.c3=1 or t1.c3=2 or t1.c3=2 !
fetch!

commit work release !
file connect ( sut sut !
drop user kern !
commit work release !
*****************************************************************************
