--
--   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 subquery flattening into outer query block

set isolation to rr;

-- tests for flattening a subquery based on a
-- uniqueness condition

-- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
NoHoldForConnection;

-- create some tables
create table outer1 (c1 int, c2 int, c3 int);
create table outer2 (c1 int, c2 int, c3 int);
create table noidx (c1 int);
create table idx1 (c1 int);
create unique index idx1_1 on idx1(c1);
create table idx2 (c1 int, c2 int);
create unique index idx2_1 on idx2(c1, c2);
create table nonunique_idx1 (c1 int);
create index nonunique_idx1_1 on nonunique_idx1(c1);


insert into outer1 values (1, 2, 3);
insert into outer1 values (4, 5, 6);
insert into outer2 values (1, 2, 3);
insert into outer2 values (4, 5, 6);
insert into noidx values 1, 1;
insert into idx1 values 1, 2;
insert into idx2 values (1, 1), (1, 2);
insert into nonunique_idx1 values 1, 1;

-- cases where subqueries don't get flattened
-- (we would get incorrect results with 
-- incorrect flattening)
-- one of tables in subquery doesn't have index
select * from outer1 where c1 in (select idx1.c1 from noidx, idx1 where idx1.c1 = noidx.c1);
-- group by in subquery
select * from outer1 o where c1 <= (select c1 from idx1 i group by c1);
-- otherwise flattenable subquery under an or 
-- subquery returns no rows
select * from outer1 o where c1 + 0 = 1 or c1 in (select c1 from idx1 i where i.c1 = 0);
select * from outer1 o where c1 in (select c1 from idx1 i where i.c1 = 0) or c1 + 0 = 1;
-- empty subquery in select list which is otherwise flattenable
select (select c1 from idx1 where c1 = 0) from outer1;
-- multiple tables in subquery
-- no one table's equality condition based
-- solely on constants and correlation columns
select * from outer1 o where exists (select * from idx2 i, idx1 where o.c1 = i.c1 and i.c2 = idx1.c1);

-- subqueries that should get flattened
call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
maximumdisplaywidth 40000;

-- simple IN
select * from outer1 o where o.c1 in (select c1 from idx1);
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- simple EXISTS
select * from outer1 o where exists (select * from idx1 i where o.c1 = i.c1);
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- simple ANY
select * from outer1 o where o.c1 = ANY (select c1 from idx1);
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- another simple ANY
select * from outer1 o where o.c2 > ANY (select c1 from idx1 i where o.c1 = i.c1);
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- comparisons with parameters
prepare p1 as 'select * from outer1 o where exists (select * from idx1 i where i.c1 = ?)';
execute p1 using 'values 1';
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
prepare p2 as 'select * from outer1 o where ? = ANY (select c1 from idx1)';
execute p2 using 'values 1';
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- mix constants with correlation columns
select * from outer1 o where exists (select * from idx2 i where o.c1 = i.c1 and i.c2 = 2);
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- multiple tables in subquery
select * from outer1 o where exists (select * from idx2 i, idx1 where o.c1 = i.c1 and i.c2 = idx1.c1 and i.c2 = 1);
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- comparisons with non-join expressions
select * from outer1 o where exists (select * from idx1 where idx1.c1 = 1 + 0);
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
select * from outer1 o where exists (select * from idx2 i, idx1 where o.c1 + 0 = i.c1 and i.c2 + 0 = idx1.c1 and i.c2 = 1 + 0);
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- multilevel subqueries
-- only flatten bottom of where exists, any, or in with 
-- exists, any, or in in its own where clause. DERBY-3301.
select * from outer1 o where exists
    (select * from idx2 i where exists
        (select * from idx1 ii 
         where o.c1 = i.c1 and i.c2 = ii.c1 and i.c2 = 1));
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- only flatten bottom
select * from outer1 o where exists
    (select * from idx2 i where exists
        (select * from idx1 ii 
         where o.c1 = i.c1 and i.c2 = ii.c1));
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- flatten innermost into exists join, but dont flatten middle into outer as it
-- is a where exists, any, or in with exists, any, or in in its own where clause. 
-- DERBY-3301.
select * from outer1 o where exists
    (select * from idx2 i 
     where  o.c1 = i.c1 and i.c2 = 1 and exists
        (select * from idx1 ii));
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- flatten a subquery that has a subquery in its select list
-- verify that subquery gets copied up to outer block
select * from outer1 o where c1 in
    (select (select c1 from idx1 where c1 = i.c1)
     from idx2 i where o.c1 = i.c1 and i.c2 = 1);
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- expression subqueries
-- simple =
select * from outer1 o where o.c1 = (select c1 from idx1 i where o.c1 = i.c1);
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
select * from outer1 o where o.c1 <= (select c1 from idx1 i where o.c1 = i.c1);
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- multiple tables in subquery
select * from outer1 o where c1 =  (select i.c1 from idx2 i, idx1 where o.c1 = i.c1 and i.c2 = idx1.c1 and i.c2 = 1);
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- flattening to an exists join
-- no index on subquery table
select * from outer1 where c1 in (select c1 from noidx);
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- no unique index on subquery table
select * from outer1 where c1 in (select c1 from nonunique_idx1);
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- columns in subquery are not superset of unique index
select * from outer1 where c1 in (select c1 from idx2);
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- single table subquery, self join on unique column
select * from outer1 where exists (select * from idx1 where c1 = c1);
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- flattening values subqueries
-- flatten unless contains a subquery
select * from outer1 where c1 in (values 1);
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
select * from outer1 where c1 in (values (select max(c1) from outer1));
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- beetle 4459 - problems with flattening to exist joins and then flattening to 
-- normal join
-- non correlated exists subquery with conditional join
maximumdisplaywidth 40000;
select o.c1 from outer1 o join outer2 o2 on (o.c1 = o2.c1) 
where exists (select c1 from idx1);
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- in predicate (will be flattened to exists)
select o.c1 from outer1 o join outer2 o2 on (o.c1 = o2.c1) 
where o.c1 in (select c1 from idx1);
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- flattened exists join in nested subquery
select c1 from (select t.c1 from (select o.c1 from outer1 o join outer2 o2 on (o.c1 = o2.c1) where exists (select c1 from idx1)) t, outer2 where t.c1 = outer2.c1) t2;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- original reported bug
create table business(businesskey int, name varchar(50), changedate int);
create table nameelement(parentkey int, parentelt varchar(50), seqnum int);
create table categorybag(cbparentkey int, cbparentelt varchar(50), 
	krtModelKey varchar(50), keyvalue varchar(50));
select businesskey, name, changedate 
from business as biz left outer join nameelement as nameElt 
	on (businesskey = parentkey and parentelt = 'businessEntity') 
where (nameElt.seqnum = 1) 
	and businesskey in 
		 (select cbparentkey 
			from categorybag 
			where (cbparentelt = 'businessEntity') and 
				(krtModelKey = 'UUID:CD153257-086A-4237-B336-6BDCBDCC6634' and keyvalue = '40.00.00.00.00'))  order by name asc , biz.changedate asc;

values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- clean up
drop table outer1;
drop table outer2;
drop table noidx;
drop table idx1;
drop table idx2;
drop table nonunique_idx1;
drop table business;
drop table nameelement;
drop table categorybag;


-- --------------------------------------------------------------------
-- TEST CASES for different kinds of subquery flattening, Beetle 5173
-- --------------------------------------------------------------------

drop table colls;
drop table docs;

CREATE TABLE "APP"."COLLS" ("ID" VARCHAR(128) NOT NULL, "COLLID" SMALLINT NOT NULL);
CREATE INDEX "APP"."NEW_INDEX3" ON "APP"."COLLS" ("COLLID");
CREATE INDEX "APP"."NEW_INDEX2" ON "APP"."COLLS" ("ID");
ALTER TABLE "APP"."COLLS" ADD CONSTRAINT "NEW_KEY2" UNIQUE ("ID", "COLLID");

CREATE TABLE "APP"."DOCS" ("ID" VARCHAR(128) NOT NULL);
CREATE INDEX "APP"."NEW_INDEX1" ON "APP"."DOCS" ("ID");
ALTER TABLE "APP"."DOCS" ADD CONSTRAINT "NEW_KEY1" PRIMARY KEY ("ID");

insert into colls values ('123', 2);
insert into colls values ('124', -5);
insert into colls values ('24', 1);
insert into colls values ('26', -2);
insert into colls values ('36', 1);
insert into colls values ('37', 8);

insert into docs values '24', '25', '36', '27', '124', '567';

call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
maximumdisplaywidth 40000;

-- NOT IN is flattened
SELECT COUNT(*) FROM
( SELECT ID FROM DOCS WHERE
        ( ID NOT IN (SELECT ID FROM COLLS WHERE COLLID IN (-2,1) ) )
) AS TAB;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- NOT EXISTS is flattened
SELECT COUNT(*) FROM
( SELECT ID FROM DOCS WHERE
        ( NOT EXISTS  (SELECT ID FROM COLLS WHERE DOCS.ID = COLLS.ID
AND COLLID IN (-2,1) ) )
) AS TAB;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- EXISTS is flattened
SELECT COUNT(*) FROM
( SELECT ID FROM DOCS WHERE
        ( EXISTS  (SELECT ID FROM COLLS WHERE DOCS.ID = COLLS.ID
AND COLLID IN (-2,1) ) )
) AS TAB;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- IN is flattened
SELECT count(ID) FROM DOCS WHERE ID IN (SELECT ID FROM COLLS WHERE COLLID IN (-2,1) );
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- ANY is flattened
SELECT count(ID) FROM DOCS WHERE ID > ANY (SELECT ID FROM COLLS WHERE COLLID IN (-2,1) );
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- ANY is flattened
SELECT count(ID) FROM DOCS WHERE ID <> ANY (SELECT ID FROM COLLS WHERE COLLID IN (-2,1) );
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- ALL is flattened, what's not?
SELECT count(ID) FROM DOCS WHERE ID = ALL (SELECT ID FROM COLLS WHERE COLLID IN (-2,1) );
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- ALL is flattened, what's not?
SELECT count(ID) FROM DOCS WHERE ID < ALL (SELECT ID FROM COLLS WHERE COLLID IN (-2,1) );
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-- ALL is flattened, what's not?
SELECT count(ID) FROM DOCS WHERE ID <> ALL (SELECT ID FROM COLLS WHERE COLLID IN (-2,1) );
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- Now test nullable correlated columns
drop table colls;
-- the only change is ID is now nullable
CREATE TABLE "APP"."COLLS" ("ID" VARCHAR(128), "COLLID" SMALLINT NOT NULL);
CREATE INDEX "APP"."NEW_INDEX3" ON "APP"."COLLS" ("COLLID");
CREATE INDEX "APP"."NEW_INDEX2" ON "APP"."COLLS" ("ID");
insert into colls values ('123', 2);
insert into colls values ('124', -5);
insert into colls values ('24', 1);
insert into colls values ('26', -2);
insert into colls values ('36', 1);
insert into colls values ('37', 8);
insert into colls values (null, -2);
insert into colls values (null, 1);
insert into colls values (null, 8);

-- NOT EXISTS should be flattened
SELECT COUNT(*) FROM
( SELECT ID FROM DOCS WHERE
        ( NOT EXISTS  (SELECT ID FROM COLLS WHERE DOCS.ID = COLLS.ID
AND COLLID IN (-2,1) ) )
) AS TAB;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- EXISTS should be flattened
SELECT COUNT(*) FROM
( SELECT ID FROM DOCS WHERE
        ( EXISTS  (SELECT ID FROM COLLS WHERE DOCS.ID = COLLS.ID
AND COLLID IN (-2,1) ) )
) AS TAB;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- IN should be flattened
SELECT count(ID) FROM DOCS WHERE ID IN (SELECT ID FROM COLLS WHERE COLLID IN (-2,1) );
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- ANY should be flattened
SELECT count(ID) FROM DOCS WHERE ID > ANY (SELECT ID FROM COLLS WHERE COLLID IN (-2,1) );
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- ALL should NOT be flattened, but subquery should be materialized
SELECT count(ID) FROM DOCS WHERE ID <> ALL (SELECT ID FROM COLLS WHERE COLLID IN (-2,1) );
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();


-- Now we make the other correlated column also nullable
drop table docs;
CREATE TABLE "APP"."DOCS" ("ID" VARCHAR(128));
CREATE INDEX "APP"."NEW_INDEX1" ON "APP"."DOCS" ("ID");

insert into docs values '24', '25', '36', '27', '124', '567';
insert into docs values null;

-- NOT EXISTS should be flattened
SELECT COUNT(*) FROM
( SELECT ID FROM DOCS WHERE
        ( NOT EXISTS  (SELECT ID FROM COLLS WHERE DOCS.ID = COLLS.ID
AND COLLID IN (-2,1) ) )
) AS TAB;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- EXISTS should be flattened
SELECT COUNT(*) FROM
( SELECT ID FROM DOCS WHERE
        ( EXISTS  (SELECT ID FROM COLLS WHERE DOCS.ID = COLLS.ID
AND COLLID IN (-2,1) ) )
) AS TAB;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- IN should be flattened
SELECT count(ID) FROM DOCS WHERE ID IN (SELECT ID FROM COLLS WHERE COLLID IN (-2,1) );
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- ANY should be flattened
SELECT count(ID) FROM DOCS WHERE ID > ANY (SELECT ID FROM COLLS WHERE COLLID IN (-2,1) );
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- ALL should NOT be flattened, but subquery should be materialized, watch out results
SELECT count(ID) FROM DOCS WHERE ID <> ALL (SELECT ID FROM COLLS WHERE COLLID IN (-2,1) );
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

drop table t1;
drop table t2;
drop table t3;
drop table t4;
create table t1 (c1 int not null);
create table t2 (c1 int not null);
create table t3 (c1 int not null);
create table t4 (c1 int);

insert into t1 values 1,2,3,4,5,1,2;
insert into t2 values 1,4,5,1,1,5,4;
insert into t3 values 4,4,3,3;
insert into t4 values 1,1,2,2,3,4,5,5;

-- should return 2,3,2
select * from t1 where not exists (select * from t2 where t1.c1=t2.c1);
-- should be flattened
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

select * from t1 where not exists (select * from t2 where t1.c1=t2.c1 and t2.c1 not in (select t3.c1 from t3, t4));
-- watch out result, should return 2,3,4,2
-- can not be flattened, should be materialized
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

select * from t1 where exists (select * from t2 where t1.c1=t2.c1 and t2.c1 not in (select t3.c1 from t3, t4));
-- should return 1,5,1
-- can not be flattened, should be materialized
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

drop table colls;
drop table docs;
drop table t1;
drop table t2;
drop table t3;
drop table t4;

-- Test case for DERBY-558: optimizer hangs in rare cases where
-- multiple subqueries flattened to EXISTS put multiple restrictions
-- on legal join orders.

create table digits (d int);
insert into digits values 1, 2, 3, 4, 5, 6, 7, 8, 9, 0;

create table odd (o int);
insert into odd values 1, 3, 5, 7, 9;
commit;

-- In order to test this, "noTimeout" must be true so that
-- the optimizer will run through all of the possible join
-- orders before it quits.  In the case of DERBY-558 the
-- optimizer was getting stuck in a logic loop and thus never
-- quit, causing the hang.  NOTE: The "noTimeout" property
-- is set in the subqueryFlattening_derby.properties file.

select distinct temp_t0.d from 
	(select d from digits where d > 3) temp_t0,
	(select o from odd) temp_t1,
	odd temp_t4,
	(select o from odd) temp_t3
	where temp_t0.d = temp_t1.o
		and temp_t0.d = temp_t3.o
		and temp_t0.d in (select o from odd where o = temp_t1.o)
 		and exists (
			select d from digits
				where d = temp_t0.d)

-- Before fix for DERBY-558, we would HANG (loop indefinitely) here;
-- after fix, we should see three rows returned.
;

-- clean-up.

drop table digits;
drop table odd;

-- regression test from old Cloudscape bug 4736 which demonstrates that
-- the fix for DERBY-3097 is correct. This query used to cause a NPE in
-- BaseActivation.getColumnFromRow, but the optimizer data structures are
-- now generated correctly and the NPE no longer occurs.

create table a (a1 int not null primary key, a2 int, a3 int, a4
int, a5 int, a6 int);
create table b (b1 int not null primary key, b2 int, b3 int, b4
int, b5 int, b6 int);
create table c (c1 int not null, c2 int, c3 int not null, c4
int, c5 int, c6 int);
create table d (d1 int not null, d2 int, d3 int not null, d4
int, d5 int, d6 int);

alter table c add primary key (c1,c3);
alter table d add primary key (d1,d3);

insert into a values
(1,1,3,6,NULL,2),(2,3,2,4,2,2),(3,4,2,NULL,NULL,NULL),
                     
(4,NULL,4,2,5,2),(5,2,3,5,7,4),(7,1,4,2,3,4),
                     (8,8,8,8,8,8),(6,7,3,2,3,4);

insert into b values
(6,7,2,3,NULL,1),(4,5,9,6,3,2),(1,4,2,NULL,NULL,NULL),
                     
(5,NULL,2,2,5,2),(3,2,3,3,1,4),(7,3,3,3,3,3),(9,3,3,3,3,3);

insert into c values
(3,7,7,3,NULL,1),(8,3,9,1,3,2),(1,4,1,NULL,NULL,NULL),
                     
(3,NULL,1,2,4,2),(2,2,5,3,2,4),(1,7,2,3,1,1),(3,8,4,2,4,6);

insert into d values
(1,7,2,3,NULL,3),(2,3,9,1,1,2),(2,2,2,NULL,3,2),
                     
(1,NULL,3,2,2,1),(2,2,5,3,2,3),(2,5,6,3,7,2);



select a1,b1,c1,c3,d1,d3
  from D join (A left outer join (B join C on b2=c2) on a1=b1)
on d3=b3 and d1=a2; 

drop table a;
drop table b;
drop table c;
drop table d;

-- DERBY-3288: Optimizer does not correctly enforce EXISTS join order
-- dependencies in the face of "short-circuited" plans.  In this test
-- an EXISTS subquery is flattened into the outer query and thus has
-- has a dependency on another FromTable (esp. HalfOuterJoinNode).
-- The tables are such that the optimizer will attempt to short-
-- circuit some relatively bad plans (namely, any join order that
-- starts with { TAB_V, HOJ, ...}), and needs to correctly enforce
-- join order dependencies in the process.

CREATE TABLE tab_a (PId BIGINT NOT NULL); 
CREATE TABLE tab_c (Id BIGINT NOT NULL PRIMARY KEY,
  PAId BIGINT NOT NULL, PBId BIGINT NOT NULL);

INSERT INTO tab_c VALUES (91, 81, 82);
INSERT INTO tab_c VALUES (92, 81, 84);
INSERT INTO tab_c VALUES (93, 81, 88);
INSERT INTO tab_c VALUES (96, 81, 83);

CREATE TABLE tab_v (OId BIGINT NOT NULL,
  UGId BIGINT NOT NULL, val CHAR(1) NOT NULL);

CREATE UNIQUE INDEX tab_v_i1 ON tab_v (OId, UGId, val);
CREATE INDEX tab_v_i2 ON tab_v (UGId, val, OId);

INSERT INTO tab_v VALUES (81, 31, 'A');
INSERT INTO tab_v VALUES (82, 31, 'A');
INSERT INTO tab_v VALUES (83, 31, 'A');
INSERT INTO tab_v VALUES (84, 31, 'A');
INSERT INTO tab_v VALUES (85, 31, 'A');
INSERT INTO tab_v VALUES (86, 31, 'A');
INSERT INTO tab_v VALUES (87, 31, 'A');
INSERT INTO tab_v VALUES (81, 32, 'A');
INSERT INTO tab_v VALUES (82, 32, 'A');
INSERT INTO tab_v VALUES (83, 32, 'A');
INSERT INTO tab_v VALUES (84, 32, 'A');
INSERT INTO tab_v VALUES (85, 32, 'A');
INSERT INTO tab_v VALUES (86, 32, 'A');
INSERT INTO tab_v VALUES (87, 32, 'A');

CREATE TABLE tab_b (Id BIGINT NOT NULL PRIMARY KEY, OId BIGINT NOT NULL);

INSERT INTO tab_b VALUES (141, 81);
INSERT INTO tab_b VALUES (142, 82);
INSERT INTO tab_b VALUES (143, 84);
INSERT INTO tab_b VALUES (144, 88);
INSERT INTO tab_b VALUES (151, 81);
INSERT INTO tab_b VALUES (152, 83);

CREATE TABLE tab_d (Id BIGINT NOT NULL PRIMARY KEY,
  PAId BIGINT NOT NULL, PBId BIGINT NOT NULL);

INSERT INTO tab_d VALUES (181, 141, 142);
INSERT INTO tab_d VALUES (182, 141, 143);
INSERT INTO tab_d VALUES (186, 151, 152); 

-- Query should return 2 rows; before DERBY-3288 was fixed, it would
-- only return a single row due to violation of join order dependencies.

SELECT tab_b.Id
FROM tab_b JOIN tab_c ON (tab_b.OId = tab_c.PAId OR tab_b.OId = tab_c.PBId)
LEFT OUTER JOIN tab_a ON tab_b.OId = PId
WHERE EXISTS
  (SELECT 'X' FROM tab_d
    WHERE (PAId = 141 AND PBId = tab_b.Id)
        OR (PBId = 141 AND PAId = tab_b.Id))
  AND EXISTS
    (SELECT 'X' FROM tab_v
      WHERE OId = tab_b.OId AND UGId = 31 AND val = 'A');


drop table tab_d;
drop table tab_b;
drop table tab_v;
drop table tab_c;