-- Test: TEST005 (compGeneral)
-- Functionality: Tests FOJ
-- Expected files: EXPECTED005
-- Tables created: t005t01 - t005t5
-- Partitioned tables and indexes :
--
-- This test is run against MX tables only.
-- Limitations:
--
-- Revision history:
-- (05/09/07) Created TEST005 - Personal testsuite only
--
-- @@@ 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 @@@
 
?section ddl
drop table t005t01;
drop table t005t02;
drop table t005t03;
drop table t005t04;
drop table t005t05;
drop table t005_ex;
drop table t005_gx;
drop table t005_hx;
drop table t005_fx;
drop table witht1;
drop table witht2;

log LOG005 clear;

?section create_tables
create table t005t01(eno int not null ,
                 dno largeint,
                 primary key(eno) );

create table t005t02(dno largeint not null ,
                 dname char(20) not null,
                 primary key (dno) );


create table t005t03
(
 seqno integer  not null not droppable,
 smin1 smallint  default null,
 pict1 pic s9(13)v9(5)  default null,
 primary key (seqno)
) ;

create table t005t04
(
 seqno integer  not null not droppable,
 smin1 smallint  default null,
 pict1 pic s9(13)v9(5)  default null,
 primary key (seqno)
) ;


create table t005t05(seqno integer not null primary key  ) ;

create table t005_ex (
           e1 decimal(10,0) not null,
           e2 decimal(10,0) not null,
           e3 decimal(10,0) not null
        ) no partition;
        
  
create table t005_fx (
    f1 decimal(10,0) not null,
    f2 decimal(10,0) not null,
    f3 decimal(10,0) not null
) no partition;
        
        
create table t005_gx (
    g1 decimal(10,0) default null,
    g2 decimal(10,0) default null
) no partition;
        
create table t005_hx (
	h1 decimal(10,0) not null,
	h2 decimal(10,0) not null,
	h3 decimal(10,0) not null
) no partition;

create table witht1 (c1 int, c2 int);
create table witht2 (c1 int, c2 int);
?section populate_tables 
-- Populate t005t01
insert into t005t01 values (30, 33);
insert into t005t01 values (3, 33);
insert into t005t01 values (4, 44);
insert into t005t01 values (5, 55);
insert into t005t01 values (6, 66);

-- Populate t005t02
insert into t005t02 values (33, 'Sales'),
                       (44, 'Marketing'),
                       (55, 'Production'),
		       (77, 'R&D');
		       
insert into t005_ex (e1, e2, e3) values(1,1,1);
insert into t005_ex (e1, e2, e3) values(1,1,2);
insert into t005_ex (e1, e2, e3) values(1,1,3);
insert into t005_ex (e1, e2, e3) values(1,2,1);
insert into t005_ex (e1, e2, e3) values(1,2,2);
insert into t005_ex (e1, e2, e3) values(1,2,4);
insert into t005_ex (e1, e2, e3) values(1,3,1);
insert into t005_ex (e1, e2, e3) values(1,3,2);
insert into t005_ex (e1, e2, e3) values(1,3,3);

insert into t005_fx select * from t005_ex;

insert into t005_gx values(1,2);
insert into t005_gx values(2,2);
insert into t005_gx values(3,2);
insert into t005_gx values(4,1);

insert into t005_hx values(1,1,1);
insert into t005_hx values(1,1,2);
insert into t005_hx values(1,1,3);
insert into t005_hx values(1,1,4);
insert into t005_hx values(1,2,1);
insert into t005_hx values(1,2,2);
insert into t005_hx values(1,2,3);
insert into t005_hx values(1,3,2);
insert into t005_hx values(1,3,3);
insert into t005_hx values(1,3,4);
insert into t005_hx values(2,1,1);
insert into t005_hx values(2,1,2);
insert into t005_hx values(2,1,3);
insert into t005_hx values(2,1,4);
insert into t005_hx values(2,2,1);
insert into t005_hx values(2,2,2);
insert into t005_hx values(2,2,3);
        
insert into witht1 values(1,1);
insert into witht1 values(2,2);
insert into witht1 values(3,3);
insert into witht1 values(4,4);
insert into witht1 values(5,5);
insert into witht2 values(3,3);
insert into witht2 values(4,4);
insert into witht2 values(5,5);
insert into witht2 values(6,6);
insert into witht2 values(7,7);

?section prep
control query default query_cache '0';
control query default comp_bool_199 'on';

?section positive_tests

-- FOJ1 - Natural Full Outer Join 
-- t005t01 FOJ t005t02
select * from t005t01 natural full outer join t005t02 order by 1,2 DESC;

-- FOJ2 - Natural Full Outer Join 
-- t005t02 FOJ t005t01
select * from t005t02 natural full outer join t005t01 order by 1,3;

-- FOJ3 - Use ON clause - non equijoin predicate
-- t005t02 FOJ t005t01 
select * from t005t02 full outer join t005t01 on t005t01.dno = 33 order by 1,3;

-- FOJ4 - Use ON clause - non equijoin predicate
-- t005t01 FOJ t005t02
select * from t005t01 full outer join t005t02 on t005t02.dno = 33 order by 1,4;


-- FOJ5 - Use ON clause - equijoin predicate
-- t005t02 FOJ t005t01 
select * from t005t02 full outer join t005t01 on t005t02.dno = t005t01.dno order by 1,3;

-- FOJ6 - Use ON clause - equijoin predicate
-- t005t01 FOJ t005t02
select * from t005t01 full outer join t005t02 on t005t01.dno = t005t02.dno order by 1;

-- FOJ7 - Use WHERE clause on Full Outer 
-- Join t005t02 FOJ t005t01
-- WHERE clause on t005t02 and the column is part of a join column
-- Must Convert FOJ to LJ - TBD - Hema
select * from t005t02 full outer join t005t01 on t005t01.dno = t005t02.dno 
		where t005t02.dno = 33 order by 3;

-- FOJ8 - Use WHERE clause on Full Outer 
-- Join t005t02 FOJ t005t01
-- WHERE clause on t005t01 and the column is part of a join column
-- Must Convert FOJ to RJ - TBD - Hema
select * from t005t02 full outer join t005t01 on t005t01.dno = t005t02.dno 
		where t005t01.dno = 33 order by 3;


-- FOJ9 - Use AND in ON clause on Full Outer 
-- Join t005t02 FOJ t005t01
select * from t005t02 full outer join t005t01 on t005t01.dno = t005t02.dno 
		AND t005t01.dno = 33 order by 1,3;

-- FOJ10 - Use AND in ON clause on Full Outer 
-- Join t005t01 FOJ t005t02
select * from t005t01 full outer join t005t02 on t005t01.dno = t005t02.dno 
		AND t005t01.dno = 33 order by 1,4;


-- FOJ11 - Use OR in ON clause on Full Outer 
-- Join t005t02 FOJ t005t01
select * from t005t02 full outer join t005t01 on t005t01.dno = t005t02.dno 
		OR t005t01.dno = 33 order by 1,3;

-- FOJ12 - Use OR in ON clause on Full Outer 
-- Join t005t01 FOJ t005t02
select * from t005t01 full outer join t005t02 on t005t01.dno = t005t02.dno 
		OR t005t01.dno = 33 order by 1, 3;


?section DERIVED_TABLE
-- FOJ13 - use derived table syntax
-- Join t005t02 FOJ t005t01
-- WHERE clause
select * from (select * from t005t02 full outer join t005t01 
	on t005t01.dno = t005t02.dno) as T(a,b,c,d) where T.a = 33 order by 3; 

-- FOJ13 - use derived table syntax
-- Join t005t02 FOJ t005t01
select * from (select * from t005t02 full outer join 
     t005t01 on t005t01.dno = t005t02.dno) as T(a,b,c,d) order by 3; 


?section predicates
delete from t005t03;
delete from t005t04;

insert INTO t005t03 values (1, 1,1);
insert INTO t005t03 values (2, 1,1);
insert INTO t005t03 (seqno) values (3);
insert INTO t005t03 values (4,-32768,1);
insert INTO t005t03 values (5, 32767,1);
insert INTO t005t03 values (6,7892,1);
insert INTO t005t03 values (7,2834,1);
insert INTO t005t03 values (8,123,1);

 
insert INTO t005t04 values (1, 1,1);
insert INTO t005t04 values (2, 1,1);
insert INTO t005t04 values (3,24923,1);
insert INTO t005t04 values (4,-32768,1);
insert INTO t005t04 values (5,25065,1);
insert INTO t005t04 values (6,7892,1);
insert INTO t005t04 values (12,9374,1);

-- FOJ14 - selection predicate on left table columns
-- Join t005t03 FOJ t005t04
select a.seqno, a.smin1, b.seqno, b.smin1 FROM 
	t005t03 a 
	FULL OUTER JOIN                                                   
       t005t04 b ON a.seqno = b.seqno 
       where (a.seqno > 3 and a.smin1 < 123) or a.seqno < 5                
ORDER BY a.seqno, b.seqno, a.smin1, b.smin1 ;

-- FOJ15 - selection predicate on right table columns
-- Join t005t03 FOJ t005t04
select a.seqno, a.smin1, b.seqno, b.smin1 FROM t005t03 a 
	FULL OUTER JOIN 
t005t04 b ON a.seqno = b.seqno 
where (b.seqno > 3 and b.smin1 < 123) or b.seqno = 2 order by a.seqno;


delete from t005t03;
delete from t005t04;

insert INTO t005t04 values(0, 1,1);
insert INTO t005t04 values(1,1,1);
insert INTO t005t04 (seqno) values (3);
insert INTO t005t04 values(-2147483648, -32768,1);
insert INTO t005t04 values(2147483647, 32767,1);
insert INTO t005t04 values(293847923, 7892,1);
insert INTO t005t04 values(450,2834,1);
insert INTO t005t04 values(789,123,1);


insert INTO  t005t04 values(2,1,1);

-- FOJ15 - selection predicate on left and right table columns
-- Join t005t03 FOJ t005t04
select a.seqno, a.smin1, b.seqno, b.smin1 FROM t005t03 a 
	FULL OUTER JOIN 
t005t04 b ON a.seqno = b.seqno 
where (a.seqno > 3 and a.seqno < 123) or b.seqno = 2 order by a.seqno;

?section PIC_Datatype
delete from t005t03;
delete from t005t04;

insert INTO t005t03 values(0,1, 0);
insert INTO t005t04 values(0,1, 2);

select a.pict1, b.pict1 FROM t005t03 a 
	FULL OUTER JOIN t005t04 b 
ON a.pict1 = b.pict1 where  ( b.pict1  > 1 or a.pict1  < 1 ) order by 1;

?section 3-way FOJ
delete from t005t03;
delete from t005t04;
delete from t005t05;

insert INTO t005t03 values (1, 1,1);
insert INTO t005t03 values (2, 1,1);
insert INTO t005t03 (seqno) values (3);
insert INTO t005t03 values (4,-32768,1);
insert INTO t005t03 values (5, 32767,1);
insert INTO t005t03 values (6,7892,1);
insert INTO t005t03 values (7,2834,1);
insert INTO t005t03 values (8,123,1);

 
insert INTO t005t04 values (1, 1,1);
insert INTO t005t04 values (2, 1,1);
insert INTO t005t04 values (3,24923,1);
insert INTO t005t04 values (4,-32768,1);
insert INTO t005t04 values (5,25065,1);
insert INTO t005t04 values (6,7892,1);
insert INTO t005t04 values (12,9374,1);


insert into t005t05 values (7);

-- FOJ16 - columns projected from the left table only.
-- Join t005t03 FOJ t005t04 FOJ t005t05
SELECT a.seqno, b.seqno FROM t005t03 a       
  FULL OUTER JOIN  t005t04 b ON a.seqno = b.seqno      
       FULL OUTER JOIN t005t05 e ON b.seqno = e.seqno order by 1,2;

-- FOJ17 - columns projected from the left table only, with a WHERE clasue
-- on the right column.
-- Join t005t03 FOJ t005t04 FOJ t005t05
SELECT a.seqno, e.seqno FROM t005t03 a       
  FULL OUTER JOIN  t005t04 b ON a.seqno = b.seqno      
       FULL OUTER JOIN t005t05 e ON b.seqno = e.seqno where e.seqno = 7;


-- FOJ18 - columns projected from the right table only.
-- Join t005t03 FOJ t005t04 FOJ t005t05
SELECT e.seqno FROM t005t03 a       
  FULL OUTER JOIN  t005t04 b ON a.seqno = b.seqno 
       FULL OUTER JOIN t005t05 e ON b.seqno = e.seqno order by 1;


-- FOJ19 - columns projected from the right table only with a WHERE clause
-- on the right table column.
-- Join t005t03 FOJ t005t04 FOJ t005t05
SELECT e.seqno FROM t005t03 a       
  FULL OUTER JOIN  t005t04 b ON a.seqno = b.seqno 
       FULL OUTER JOIN t005t05 e ON b.seqno = e.seqno where e.seqno = 7;


?section FOJ_other_join_combination
delete from t005t03;
delete from t005t04;
delete from t005t05;

insert INTO t005t03 values (0, 1,1);
insert INTO t005t03 values (1, 1,1);
insert INTO t005t03 (seqno) values (3);
insert INTO t005t03 values (4,-32768,1);


insert INTO t005t04 values (0, 1,1);
insert INTO t005t04 values (1, 1,1);
insert INTO t005t04 (seqno) values (3);
insert INTO t005t04 values (4,-32768,1);

insert into t005t05 values (1);
insert into t005t05 values (2);
insert into t005t05 values (3);

-- FOJ20 - FOJ-INNER with ON clause
-- Join t005t03 FOJ t005t04 INNER t005t05
SELECT seqno, smin1     
        FROM                                                             
     t005t03                                                        
   FULL OUTER JOIN                                                   
        (select t005t04.seqno  as b                                            
              from t005t04 INNER JOIN t005t05                         
              ON t005t04.seqno = t005t05.seqno                      
         ) as t2                                                      
on t005t03.seqno = t2.b 
order by 1,2;

-- FOJ21 - FOJ-INNER with ON clause
-- and WHERE clause on left table
-- Join t005t03 FOJ t005t04 INNER t005t05
SELECT seqno, smin1     
        FROM                                                             
     t005t03 t1                                                    
   FULL OUTER JOIN                                                   
        (select t005t04.seqno  as b                                            
              from t005t04 INNER JOIN  t005t05
              ON t005t04.seqno = t005t05.seqno                      
         ) as t2 
on t1.seqno = t2.b                                                     
where  t1.seqno < 4 order by 1;


-- FOJ22 - FOJ-INNER with ON clause
-- and WHERE clause on right table
-- Join t005t03 FOJ t005t04 INNER t005t05
SELECT seqno, smin1     
        FROM                                                             
     t005t03 t1                                                    
   FULL OUTER JOIN                                                   
        (select t005t04.seqno  as b                                            
              from t005t04 INNER JOIN  t005t05                         
              ON t005t04.seqno = t005t05.seqno                      
         ) as t2 
on t1.seqno = t2.b                                                     
where  t2.b < 4 order by 1;

-- FOJ23, FOJ, INNER and LOJ.
-- Soln 10-081027-6839 
-- predicate was lost because a column was incorrectly flagged as NOT outerrerfernce

select * from t005_ex left outer join t005_fx
on (t005_ex.e3 = t005_fx.f3 and t005_ex.e2 = t005_fx.f2 and t005_ex.e1 = t005_fx.f1)
join t005_gx on (t005_ex.e1 = t005_gx.g1)
full outer join t005_hx on (t005_gx.g2 = t005_hx.h1 and t005_ex.e3 = t005_hx.h3 and t005_ex.e2 = t005_hx.h2)
order by t005_ex.e1 asc, t005_ex.e2 asc, t005_ex.e3 asc, t005_hx.h1, t005_hx.h2, t005_hx.h3;

-- FOJ24
-- actual query from previous solution. Order of FOJ is different

select * from t005_ex full outer join t005_fx
on (t005_ex.e3 = t005_fx.f3 and t005_ex.e2 = t005_fx.f2 and t005_ex.e1 = t005_fx.f1)
join t005_gx on (t005_ex.e1 = t005_gx.g1)
left outer join t005_hx on (t005_gx.g2 = t005_hx.h1 and t005_ex.e3 = t005_hx.h3 and t005_ex.e2 = t005_hx.h2)
order by t005_ex.e1 asc, t005_ex.e2 asc, t005_ex.e3 asc;

 
?section NEGATIVE_VALUES

insert into t005t01 values (-1, -5);
insert into t005t02 values (-5, 'REWS');

select * from t005t02 full outer join t005t01 on t005t01.dno = t005t02.dno order by 3;

delete from t005t01 where dno = -5;
delete from t005t02 where dno = -5;

?section 3-Way Join

?section FOJ_restrictions
-- FOJ50 - Must raise an error? FOJ is only supported for Hash Joins.
-- control query default hash_joins 'off';

-- FOJ51 - Subquery in the join predicate of FOJ is not supported.
select * from t005t02 full outer join t005t01 on t005t01.dno = (select min(dno
) from t005t01);

-- FOJ52 - Broadcast join is not supported for FOJ.

-- FOJ503 - MV not supported
create materialized view T_MV1
        Refresh on request
        initialize on create
        AS
        select A.dno,B.eno
        from t005t02 A full outer join t005t01 B on A.dno = B.dno;

create materialized view T_MV1
        Refresh on statement
        initialize on create
        AS
        select A.dno,B.eno
        from t005t02 A full outer join t005t01 B on A.dno = B.dno;

with w1 as (select * from witht1),
w2 as (select * from w1)
select * from w2; 
 
with w1 as (select c1, c2 from witht1),
w2 as (select c1,c2 from witht2)
select * from w1 , w2 where w1.c1 = w2.c1;

with w1 as (select * from witht1)
select * from w1
union all
select * from w1;

with recursive w1 as (select c1, c2 from witht1 union all select origin.c1 , origin.c2 from w1 join t1 origin on origin.c1 = w1.c1 );
with w1 as (select * from witht1), w1 as (select * from witht2) select * from w1;

?section cleanup

-- Clean up test
drop table t005t01;
drop table t005t02;
 
drop table t005t03;
drop table t005t04;
drop table t005t05;

drop table t005_ex;
drop table t005_gx;
drop table t005_hx;
drop table t005_fx;
drop table witht1;
drop table witht2;
