--
--   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.
--
-- test outer joins
-- (NO NATURAL JOIN)


autocommit off;

-- create some tables

create table t1(c1 int);
create table t2(c1 int);
create table t3(c1 int);
create table tt1(c1 int, c2 int, c3 int);
create table tt2(c1 int, c2 int, c3 int);
create table tt3(c1 int, c2 int, c3 int);
create table empty_table(c1 int);
create table insert_test(c1 int, c2 int, c3 int);
-- following is verifying that oj is not a keyword
create table oj(oj int);

-- populate the tables
insert into t1 values 1, 2, 2, 3, 4;
insert into t2 values 1, 3, 3, 5, 6;
insert into t3 values 2, 3, 5, 5, 7;
insert into tt1 select c1, c1, c1 from t1;
insert into tt2 select c1, c1, c1 from t2;
insert into tt3 select c1, c1, c1 from t3;
-- verifying that oj is not a keyword
insert into oj(oj) values (1);

-- negative tests

-- no outer join type
select * from t1 outer join t2;

-- no join clause
select * from t1 left outer join t2;
select * from t1 right outer join t2;


-- positive tests

select t1.c1 from t1 left outer join t2 on t1.c1 = t2.c1;
select t2.c1 from t1 right outer join t2 on t1.c1 = t2.c1;
select a.x from t1 a (x) left outer join t2 b (x) on a.x = b.x;

-- verify that selects from inner table work
select b.* from (values 9) a left outer join t2 b on 1=1;
select b.* from (values 9) a left outer join t2 b on 1=0;
select b.* from (values 9) a right outer join t2 b on 1=0;
select a.* from (values 9) a right outer join t2 b on 1=1;
select a.* from (values 9) a right outer join t2 b on 1=0;
select a.* from ((values ('a', 'b')) a inner join (values ('c', 'd')) b on 1=1) left outer join (values ('e', 'f')) c on 1=1;
select b.* from ((values ('a', 'b')) a inner join (values ('c', 'd')) b on 1=1) left outer join (values ('e', 'f')) c on 1=1;
select c.* from ((values ('a', 'b')) a inner join (values ('c', 'd')) b on 1=1) left outer join (values ('e', 'f')) c on 1=1;

-- verifying that oj is not a keyword
select * from oj where oj = 1;

--verifying both regular and {oj } in 
select * from t1 left outer join {oj t2 left outer join t3 on t2.c1=t3.c1} on t1.c1=t3.c1;

-- left and right outer join with an empty table
select t1.c1 from t1 left outer join empty_table et on t1.c1 = et.c1;
select t1.c1 from t1 right outer join empty_table et on t1.c1 = et.c1;
select t1.c1 from empty_table et right outer join t1 on et.c1 = t1.c1;

-- this query may make no sense at all, but it's just trying to show that parser works
-- fine with both regular tableexpression and tableexpression with {oj }
select * from t1, {oj t2 join t3 on t2.c1=t3.c1};

-- parameters and join clause
prepare asdf as 'select * from t1 left outer join t2 on 1=? and t1.c1 = t2.c1';
execute asdf using 'values 1';
remove asdf;

prepare asdf as 'select * from t1 left outer join t2 on t1.c1 = t2.c1 and t1.c1 = ?';
execute asdf using 'values 1';
remove asdf;

-- additional predicates outside of the join clause
-- egs of using {oj --} syntax
select * from t1 left outer join t2 on t1.c1 = t2.c1 where t1.c1 = 1;
select * from {oj t1 left outer join t2 on t1.c1 = t2.c1} where t1.c1 = 1;
select * from t1 right outer join t2 on t1.c1 = 1 where t2.c1 = t1.c1;
select * from {oj t1 right outer join t2 on t1.c1 = 1} where t2.c1 = t1.c1;

-- subquery in join clause. Not allowed in the DB2 compatibility mode. ERROR.
-- egs of using {oj --} syntax
select * from t1 a left outer join t2 b 
on a.c1 = b.c1 and a.c1 = (select c1 from t1 where a.c1 = t1.c1 and a.c1 = 1);
select * from {oj t1 a left outer join t2 b 
on a.c1 = b.c1 and a.c1 = (select c1 from t1 where a.c1 = t1.c1 and a.c1 = 1)};
select * from t1 a left outer join t2 b 
on a.c1 = b.c1 and a.c1 = (select c1 from t1 where a.c1 = t1.c1 and a.c1 <> 2);
select * from {oj t1 a left outer join t2 b 
on a.c1 = b.c1 and a.c1 = (select c1 from t1 where a.c1 = t1.c1 and a.c1 <> 2)};
select * from t1 a right outer join t2 b 
on a.c1 = b.c1 and a.c1 in (select c1 from t1 where a.c1 = t1.c1);

-- outer join in subquery
-- egs of using {oj --} syntax
select * from t1 a
where exists (select * from t1 left outer join t2 on t1.c1 = t2.c1);
select * from t1 a
where exists (select * from {oj t1 left outer join t2 on t1.c1 = t2.c1});
select * from t1 a
where exists (select * from t1 left outer join t2 on 1=0);

-- nested joins
-- egs of using {oj --} syntax
select * from t1 left outer join t2 on t1.c1 = t2.c1 left outer join t3 on t1.c1 = t3.c1;
select * from {oj t1 left outer join t2 on t1.c1 = t2.c1 left outer join t3 on t1.c1 = 
t3.c1};
select * from t1 left outer join t2 on t1.c1 = t2.c1 left outer join t3 on t2.c1 = t3.c1;
select * from t3 right outer join t2 on t3.c1 = t2.c1 right outer join t1 on t1.c1 = t2.c1;

-- parens
select * from (t1 left outer join t2 on t1.c1 = t2.c1) left outer join t3 on t1.c1 = t3.c1;
select * from t1 left outer join (t2 left outer join t3 on t2.c1 = t3.c1) on t1.c1 = t2.c1;

-- left/right outer join combinations
select * from t1 a right outer join t2 b on a.c1 = b.c1 left outer join t3 c on a.c1 = b.c1 and b.c1 = c.c1;
select * from (t1 a right outer join t2 b on a.c1 = b.c1) left outer join t3 c on a.c1 = b.c1 and b.c1 = c.c1;

select * from t1 a left outer join t2 b on a.c1 = b.c1 right outer join t3 c on c.c1 = a.c1 where a.c1 is not null;
select * from (t1 a left outer join t2 b on a.c1 = b.c1) right outer join t3 c on c.c1 = a.c1 where a.c1 is not null;
select * from t1 a left outer join (t2 b right outer join t3 c on c.c1 = b.c1) on a.c1 = c.c1 where c.c1=b.c1;

-- test insert/update/delete
insert into insert_test
select * from t1 a left outer join t2 b on a.c1 = b.c1 left outer join t3 c on a.c1 <> c.c1;
select * from insert_test;

update insert_test
set c1 = (select 9 from t1 a left outer join t1 b on a.c1 = b.c1 where a.c1 = 1)
where c1 = 1;
select * from insert_test;

delete from insert_test
where c1 = (select 9 from t1 a left outer join t1 b on a.c1 = b.c1 where a.c1 = 1);
select * from insert_test;

delete from insert_test;

insert into insert_test
select * from (select * from t1 a left outer join t2 b on a.c1 = b.c1 left outer join t3 c on a.c1 <> c.c1) d (c1, c2, c3);
select * from insert_test;
delete from insert_test;

-- verify that right outer join xforms don't get result columns
-- confused
create table a (c1 int);
create table b (c2 float);
create table c (c3 char(30));

insert into a values 1;
insert into b values 3.3;
insert into c values 'asdf';

select * from a left outer join b on 1=1 left outer join c on 1=1;
select * from a left outer join b on 1=1 left outer join c on 1=0;
select * from a left outer join b on 1=0 left outer join c on 1=1;
select * from a left outer join b on 1=0 left outer join c on 1=0;

select * from c right outer join b on 1=1 right outer join a on 1=1;
select * from c right outer join b on 1=1 right outer join a on 1=0;
select * from c right outer join b on 1=0 right outer join a on 1=1;
select * from c right outer join b on 1=0 right outer join a on 1=0;

-- multicolumn tests
-- c1, c2, and c3 all have the same values
select tt1.c1, tt1.c2, tt1.c3, tt2.c2, tt2.c3 from tt1 left outer join tt2 on tt1.c1 = tt2.c1;
select tt1.c1, tt1.c2, tt1.c3, tt2.c3 from tt1 left outer join tt2 on tt1.c1 = tt2.c1;
select tt1.c1, tt1.c2, tt1.c3 from tt1 left outer join tt2 on tt1.c1 = tt2.c1;

-- nested outer joins
select tt1.c2, tt1.c1, tt1.c3, tt2.c1, tt2.c3 from t1 left outer join tt1 on t1.c1 = tt1.c1 left outer join tt2 on tt1.c2 = tt2.c2;

-- make sure that column reordering is working correctly 
-- when there's an ON clause
create table x (c1 int, c2 int, c3 int);
create table y (c3 int, c4 int, c5 int);
insert into x values (1, 2, 3), (4, 5, 6);
insert into y values (3, 4, 5), (666, 7, 8);

-- qualfied * will return all of the columns of the qualified table
-- including join columns
select x.* from x join y on x.c3 = y.c3;
select x.* from x left outer join y on x.c3 = y.c3;
select x.* from x right outer join y on x.c3 = y.c3;
select y.* from x join y on x.c3 = y.c3;
select y.* from x left outer join y on x.c3 = y.c3;
select y.* from x right outer join y on x.c3 = y.c3;

-- * will return all of the columns of all joined tables
select * from x join y on x.c3 = y.c3;
select * from x left outer join y on x.c3 = y.c3;
select * from x right outer join y on x.c3 = y.c3;

commit;

-- test outer join -> inner join xform
delete from tt1;
delete from tt2;
delete from tt3;

insert into tt1 values (1, 2, 3), (2, 3, 4), (3, 4, 5);
insert into tt2 values (1, 2, 3), (2, 3, 4), (3, 4, 5);
insert into tt3 values (1, 2, 3), (2, 3, 4), (3, 4, 5);

call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
maximumdisplaywidth 4500;

-- no xform, predicate on outer table
select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2 where tt1.c1 = 3;

-- various predicates on inner table
select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2 where tt2.c2 = 3;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2 where tt2.c1 + 1= tt2.c2;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2 where tt2.c1 + 1= 3;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
select * from tt2 right outer join tt1 on tt1.c1 = tt2.c2 where tt2.c1 + 1= 3;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2 left outer join tt3 on tt2.c2 = tt3.c3 where tt3.c3 = 3;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2 left outer join tt3 on tt2.c2 = tt3.c3 where tt2.c2 = 3;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- make sure predicates are null tolerant 
select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2 
where char(tt2.c2) is null;
-- where java.lang.Integer::toString(tt2.c2) = '2';
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
commit;

-- bug 2924, cross join under an outer join
CREATE TABLE inventory(itemno INT NOT NULL PRIMARY KEY, capacity INT);

INSERT INTO inventory VALUES (1, 4);
INSERT INTO inventory VALUES (2, 2);
INSERT INTO inventory VALUES (3, 2);

CREATE TABLE timeslots (slotno INT NOT NULL PRIMARY KEY);

INSERT INTO timeslots VALUES(1);
INSERT INTO timeslots VALUES(2);

create table reservations(slotno INT CONSTRAINT timeslots_fk REFERENCES timeslots, 
itemno INT CONSTRAINT inventory_fk REFERENCES inventory, 
name VARCHAR(100), resdate DATE);

    
INSERT INTO reservations VALUES(1, 1, 'Joe', '2000-04-14');
INSERT INTO reservations VALUES(1, 1, 'Fred', '2000-04-13');

-- This query used to cause a null pointer exception
   
select name, resdate 
from reservations left outer join (inventory join timeslots on inventory.itemno = timeslots.slotno)
on inventory.itemno = reservations.itemno and timeslots.slotno = reservations.slotno
where resdate = '2000-04-14';
rollback;

-- bug 2923, cross join under an outer join
create table inventory(itemno INT NOT NULL PRIMARY KEY, capacity INT);
INSERT into inventory values (1, 4);
INSERT into inventory values (2, 2);
INSERT into inventory values (3, 2);

CREATE TABLE timeslots (slotno INT NOT NULL PRIMARY KEY);
INSERT INTO timeslots VALUES(1);
INSERT INTO timeslots VALUES(2);

create table reservations(slotno INT CONSTRAINT timeslots_fk REFERENCES timeslots,
itemno INT CONSTRAINT inventory_fk REFERENCES inventory,
name VARCHAR(100));
INSERT INTO reservations VALUES(1, 1, 'Joe');
INSERT INTO reservations VALUES(2, 2, 'Fred');

-- This query used to get incorrect results
-- when name is null was the 2nd predicate
-- due to a bug in OJ->IJ xform code.
select timeslots.slotno, inventory.itemno, capacity, name
from inventory left outer join timeslots
on inventory.capacity = timeslots.slotno
left outer join reservations
on timeslots.slotno = reservations.slotno
where capacity > 3 and name is null;
select timeslots.slotno, inventory.itemno, capacity, name
from inventory left outer join timeslots
on inventory.capacity = timeslots.slotno
left outer join reservations
on timeslots.slotno = reservations.slotno
where name is null and capacity > 3;

rollback;

-- bug 2930, cross join under outer join
CREATE TABLE properties (
	name VARCHAR(50),
	value VARCHAR(200));

INSERT INTO properties VALUES ('businessName', 'Cloud 9 Cafe');
INSERT INTO properties VALUES ('lastReservationDate', '2001-12-31');

CREATE TABLE inventory (
	itemno INT NOT NULL PRIMARY KEY,
	capacity INT
);
INSERT INTO inventory VALUES (1, 2);
INSERT INTO inventory VALUES (2, 2);
INSERT INTO inventory VALUES (3, 2);
INSERT INTO inventory VALUES (4, 2);
INSERT INTO inventory VALUES (5, 2);
INSERT INTO inventory VALUES (6, 4);
INSERT INTO inventory VALUES (7, 4);
INSERT INTO inventory VALUES (8, 4);
INSERT INTO inventory VALUES (9, 4);
INSERT INTO inventory VALUES (10, 4);

CREATE TABLE timeslots (
	slot TIME NOT NULL PRIMARY KEY
);

INSERT INTO timeslots VALUES('17:00:00');
INSERT INTO timeslots VALUES('17:30:00');
INSERT INTO timeslots VALUES('18:00:00');
INSERT INTO timeslots VALUES('18:30:00');
INSERT INTO timeslots VALUES('19:00:00');
INSERT INTO timeslots VALUES('19:30:00');
INSERT INTO timeslots VALUES('20:00:00');
INSERT INTO timeslots VALUES('20:30:00');
INSERT INTO timeslots VALUES('21:00:00');
INSERT INTO timeslots VALUES('21:30:00');
INSERT INTO timeslots VALUES('22:00:00');

CREATE TABLE reservations (
	itemno INT CONSTRAINT inventory_fk REFERENCES inventory,
	slot TIME CONSTRAINT timeslots_fk REFERENCES timeslots,
	resdate DATE NOT NULL,
	name VARCHAR(100) NOT NULL,
	quantity INT,
	CONSTRAINT reservations_u UNIQUE(name, resdate));

INSERT INTO reservations VALUES(6, '17:00:00', '2000-07-13', 'Williams', 4);
INSERT INTO reservations VALUES(7, '17:00:00', '2000-07-13', 'Johnson',  4);
INSERT INTO reservations VALUES(8, '17:00:00', '2000-07-13', 'Allen',    3);
INSERT INTO reservations VALUES(9, '17:00:00', '2000-07-13', 'Dexmier',  4);
INSERT INTO reservations VALUES(1, '17:30:00', '2000-07-13', 'Gates', 	 2);
INSERT INTO reservations VALUES(2, '17:30:00', '2000-07-13', 'McNealy',  2);
INSERT INTO reservations VALUES(3, '17:30:00', '2000-07-13', 'Hoffman',  1);
INSERT INTO reservations VALUES(4, '17:30:00', '2000-07-13', 'Sippl',    2);
INSERT INTO reservations VALUES(6, '17:30:00', '2000-07-13', 'Yang',     4);
INSERT INTO reservations VALUES(7, '17:30:00', '2000-07-13', 'Meyers',   4);
select max(name), max(resdate) from inventory join timeslots on inventory.capacity is not null
left outer join reservations on inventory.itemno = reservations.itemno and reservations.slot = timeslots.slot;
rollback;

-- bug 2931, cross join under outer join
CREATE TABLE properties (
	name VARCHAR(50),
	value VARCHAR(200));

INSERT INTO properties VALUES ('businessName', 'Cloud 9 Cafe');
INSERT INTO properties VALUES ('lastReservationDate', '2001-12-31');

CREATE TABLE inventory (
	itemno INT NOT NULL PRIMARY KEY,
	capacity INT
);
INSERT INTO inventory VALUES (1, 2);
INSERT INTO inventory VALUES (2, 2);
INSERT INTO inventory VALUES (3, 2);
INSERT INTO inventory VALUES (4, 2);
INSERT INTO inventory VALUES (5, 2);
INSERT INTO inventory VALUES (6, 4);
INSERT INTO inventory VALUES (7, 4);
INSERT INTO inventory VALUES (8, 4);
INSERT INTO inventory VALUES (9, 4);
INSERT INTO inventory VALUES (10, 4);

CREATE TABLE timeslots (
	slot TIME NOT NULL PRIMARY KEY
);

INSERT INTO timeslots VALUES('17:00:00');
INSERT INTO timeslots VALUES('17:30:00');
INSERT INTO timeslots VALUES('18:00:00');
INSERT INTO timeslots VALUES('18:30:00');
INSERT INTO timeslots VALUES('19:00:00');
INSERT INTO timeslots VALUES('19:30:00');
INSERT INTO timeslots VALUES('20:00:00');
INSERT INTO timeslots VALUES('20:30:00');
INSERT INTO timeslots VALUES('21:00:00');
INSERT INTO timeslots VALUES('21:30:00');
INSERT INTO timeslots VALUES('22:00:00');

CREATE TABLE reservations (
	itemno INT CONSTRAINT inventory_fk REFERENCES inventory,
	slot TIME CONSTRAINT timeslots_fk REFERENCES timeslots,
	resdate DATE NOT NULL,
	name VARCHAR(100) NOT NULL,
	quantity INT,
	CONSTRAINT reservations_u UNIQUE(name, resdate));

INSERT INTO reservations VALUES(6, '17:00:00', '2000-07-13', 'Williams', 4);
INSERT INTO reservations VALUES(7, '17:00:00', '2000-07-13', 'Johnson',  4);
INSERT INTO reservations VALUES(8, '17:00:00', '2000-07-13', 'Allen',    3);
INSERT INTO reservations VALUES(9, '17:00:00', '2000-07-13', 'Dexmier',  4);
INSERT INTO reservations VALUES(1, '17:30:00', '2000-07-13', 'Gates', 	 2);
INSERT INTO reservations VALUES(2, '17:30:00', '2000-07-13', 'McNealy',  2);
INSERT INTO reservations VALUES(3, '17:30:00', '2000-07-13', 'Hoffman',  1);
INSERT INTO reservations VALUES(4, '17:30:00', '2000-07-13', 'Sippl',    2);
INSERT INTO reservations VALUES(6, '17:30:00', '2000-07-13', 'Yang',     4);
INSERT INTO reservations VALUES(7, '17:30:00', '2000-07-13', 'Meyers',   4);


-- this query should return values from the 'slot' column (type date)
-- but it seems to be returning integers!
select max(timeslots.slot) from inventory inner join timeslots on inventory.capacity is not null
left outer join reservations on inventory.capacity = reservations.itemno and reservations.slot = timeslots.slot;
rollback;

-- bug 2897 Push join predicates from where clause
-- to right
select * from t1 inner join t2 on 1=1 left outer join t3 on t1.c1 = t3.c1
where t1.c1 = t2.c1;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- Test fix for bug 5659
create table xxx (a int not null);
create table yyy (a int not null);

insert into xxx values (1);
select * from xxx left join yyy on (xxx.a=yyy.a);
insert into xxx values (null);
select * from xxx;

drop table xxx;
drop table yyy;

-- Defect 5658. Disable querries with ambiguous references.
create table ttab1 (a int, b int);
insert into ttab1 values (1,1),(2,2);
create table ttab2 (c int, d int);
insert into ttab2 values (1,1),(2,2);

-- this statement should raise an error because 
-- more than one object table includes column "b"
select cor1.*, cor2.* from ttab1 cor1 left outer join ttab2 on (b = d),
		ttab1 left outer join ttab2 cor2 on (b = d);
select cor1.*, cor2.* from ttab1 cor1 left outer join ttab2 on (b = d),
		ttab1 left outer join ttab2 cor2 on (b = cor2.d);

-- This should pass
select cor1.*, cor2.* from ttab1 left outer join ttab2 on (b = d), 
		ttab1 cor1 left outer join ttab2 cor2 on (cor1.b = cor2.d);

-- These should fail too
select * from ttab1, ttab1 left outer join ttab2 on (a=c);
select * from ttab1 cor1, ttab1 left outer join ttab2 on (cor1.a=c);

-- This should pass
select * from ttab1, ttab1 cor1 left outer join ttab2 on (cor1.a=c);

drop table ttab1;
drop table ttab2;

-- Test 5164

CREATE TABLE "APP"."GOVT_AGCY" ("GVA_ID" NUMERIC(20,0) NOT NULL, "GVA_ORL_ID" NUMERIC(20,0) NOT NULL, "GVA_GAC_ID" NUMERIC(20,0));

CREATE TABLE "APP"."GEO_STRC_ELMT" ("GSE_ID" NUMERIC(20,0) NOT NULL, "GSE_GSET_ID" NUMERIC(20,0) NOT NULL, "GSE_GA_ID_PRNT" NUMERIC(20,0) NOT NULL, "GSE_GA_ID_CHLD" NUMERIC(20,0) NOT NULL);

CREATE TABLE "APP"."GEO_AREA" ("GA_ID" NUMERIC(20,0) NOT NULL, "GA_GAT_ID" NUMERIC(20,0) NOT NULL, "GA_NM" VARCHAR(30) NOT NULL, "GA_ABRV_NM" VARCHAR(5));

CREATE TABLE "APP"."REG" ("REG_ID" NUMERIC(20,0) NOT NULL, "REG_NM" VARCHAR(60) NOT NULL, "REG_DESC" VARCHAR(240), "REG_ABRV_NM" VARCHAR(15), "REG_CD" NUMERIC(8,0) NOT NULL, "REG_STRT_DT" TIMESTAMP NOT NULL, "REG_END_DT" TIMESTAMP NOT NULL DEFAULT '4712-12-31 00:00:00', "REG_EMPR_LIAB_IND" CHAR(1) NOT NULL DEFAULT 'N', "REG_PAYR_TAX_SURG_CRTF_IND" CHAR(1) NOT NULL DEFAULT 'N', "REG_PYT_ID" NUMERIC(20,0), "REG_GA_ID" NUMERIC(20,0) NOT NULL, "REG_GVA_ID" NUMERIC(20,0) NOT NULL, "REG_REGT_ID" NUMERIC(20,0) NOT NULL, "REG_PRNT_ID" NUMERIC(20,0));

-- This should not get ArrayIndexOutofBound exception
SELECT 1
FROM reg
     JOIN geo_area jrsd ON (jrsd.ga_id = reg.reg_ga_id)
     LEFT OUTER
JOIN geo_strc_elmt gse ON (gse.gse_ga_id_chld =
reg.reg_ga_id)
     LEFT OUTER
JOIN geo_area prnt ON (prnt.ga_id =
reg.reg_ga_id)
     JOIN govt_agcy gva ON (reg.reg_gva_id = gva.gva_id);

DROP TABLE "APP"."GOVT_AGCY";
DROP TABLE "APP"."GEO_STRC_ELMT";
DROP TABLE "APP"."GEO_AREA";
DROP TABLE "APP"."REG";

-- reset autocommit
autocommit on;

-- drop the tables
drop table t1;
drop table t2;
drop table t3;
drop table tt1;
drop table tt2;
drop table tt3;
drop table insert_test;
drop table empty_table;
drop table a;
drop table b;
drop table c;
drop table oj;
drop table x;
drop table y;
