[DERBY-4712] Complex nested joins problems

• Type: Bug
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0
• Fix Version/s: 10.5.3.2, 10.6.2.1, 10.7.1.1
• Component/s: SQL
• Issue & fix info: Repro attached
• Bug behavior facts: Regression Test Failure, Wrong query result
• Assignee: Dag H. Wanvik
• Reporter: Thomas Mueller
• Created: 24/Jun/10 18:13
• Updated: 07/Feb/11 22:52
• Resolved: 07/Feb/11 22:52

Description

I ran a randomized test for nested joins against PostgreSQL, Derby, and H2, and found some problems with Derby. The queries below throw NullPointerExceptions; the last query an assertion. The test case is here: http://code.google.com/p/h2database/source/browse/trunk/h2/src/test/org/h2/test/db/TestNestedJoins.java . There are probably shorter queries that are problematic, but I can't test it because Derby closes the connection after the assertion.

create table t0(x int);
create table t1(x int);
create table t2(x int);
create table t3(x int);
create table t4(x int);
insert into t4 values(0);
insert into t4 values(1);
insert into t4 values(2);
insert into t4 values(3);
create table t5(x int);
insert into t5 values(0);
insert into t5 values(1);
insert into t5 values(2);
insert into t5 values(3);
insert into t5 values(4);
create table t6(x int);
insert into t6 values(0);
insert into t6 values(1);
insert into t6 values(2);
insert into t6 values(3);
insert into t6 values(4);
insert into t6 values(5);
create table t7(x int);
insert into t7 values(0);
insert into t7 values(1);
insert into t7 values(2);
insert into t7 values(3);
insert into t7 values(4);
insert into t7 values(5);
insert into t7 values(6);
create table t8(x int);
insert into t8 values(0);
insert into t8 values(1);
insert into t8 values(2);
insert into t8 values(3);
insert into t8 values(4);
insert into t8 values(5);
insert into t8 values(6);
insert into t8 values(7);
create table t9(x int);
insert into t9 values(0);
insert into t9 values(1);
insert into t9 values(2);
insert into t9 values(3);
insert into t9 values(4);
insert into t9 values(5);
insert into t9 values(6);
insert into t9 values(7);
insert into t9 values(8);
insert into t0 values(1);
insert into t1 values(2);
insert into t0 values(3);
insert into t1 values(3);
insert into t2 values(4);
insert into t0 values(5);
insert into t2 values(5);
insert into t1 values(6);
insert into t2 values(6);
insert into t0 values(7);
insert into t1 values(7);
insert into t2 values(7);
insert into t3 values(8);
insert into t0 values(9);
insert into t3 values(9);
insert into t1 values(10);
insert into t3 values(10);
insert into t0 values(11);
insert into t1 values(11);
insert into t3 values(11);
insert into t2 values(12);
insert into t3 values(12);
insert into t0 values(13);
insert into t2 values(13);
insert into t3 values(13);
insert into t1 values(14);
insert into t2 values(14);
insert into t3 values(14);
insert into t0 values(15);
insert into t1 values(15);
insert into t2 values(15);
insert into t3 values(15);
select t0.x , t1.x , t2.x , t3.x , t4.x , t5.x , t6.x , t7.x , t8.x from (((t0 inner join ((t1 right outer join (t2 inner join t3 on t2.x = t3.x ) on t1.x = t2.x ) left outer join (t4 inner join t5 on t4.x = t5.x ) on t1.x = t4.x ) on t0.x = t2.x ) left outer join (t6 inner join t7 on t6.x = t7.x ) on t1.x = t6.x ) inner join t8 on t5.x = t8.x );
select t0.x , t1.x , t2.x , t3.x , t4.x , t5.x , t6.x , t7.x from ((t0 right outer join t1 on t0.x = t1.x ) inner join (((t2 inner join (t3 left outer join t4 on t3.x = t4.x ) on t2.x = t3.x ) right outer join t5 on t2.x = t5.x ) left outer join (t6 inner join t7 on t6.x = t7.x ) on t4.x = t6.x ) on t0.x = t5.x );
select t0.x , t1.x , t2.x , t3.x , t4.x , t5.x , t6.x , t7.x from ((((t0 left outer join t1 on t0.x = t1.x ) right outer join t2 on t0.x = t2.x ) right outer join t3 on t0.x = t3.x ) inner join ((t4 inner join t5 on t4.x = t5.x ) right outer join (t6 right outer join t7 on t6.x = t7.x ) on t4.x = t6.x ) on t1.x = t4.x );
select t0.x , t1.x , t2.x , t3.x , t4.x , t5.x from (((t0 inner join t1 on t0.x = t1.x ) right outer join (t2 right outer join t3 on t2.x = t3.x ) on t0.x = t2.x ) inner join (t4 left outer join t5 on t4.x = t5.x ) on t1.x = t4.x );
select t0.x , t1.x , t2.x , t3.x , t4.x , t5.x , t6.x from ((t0 right outer join (t1 right outer join (t2 left outer join (t3 left outer join t4 on t3.x = t4.x ) on t2.x = t3.x ) on t1.x = t3.x ) on t0.x = t1.x ) left outer join (t5 inner join t6 on t5.x = t6.x ) on t2.x = t5.x );

Issue Links

• is required by: DERBY-4994 10.5 backport effort issue (2011)
• relates to: DERBY-4471 Left outer join reassociation rewrite gives wrong result
• relates to: DERBY-4798 NPE in nested outer join