db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4712) Complex nested joins problems
Date Sat, 11 Sep 2010 01:02:34 GMT

    [ https://issues.apache.org/jira/browse/DERBY-4712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12908258#action_12908258
] 

Bryan Pendleton commented on DERBY-4712:
----------------------------------------

Thank you very much for working on this, Dag. I've been hoping to find time
to read through your thorough notes, but time has been scarce recently.

On the one hand, I'm a bit discouraged that the DERBY-3097 issue has re-arisen.

But on the other hand, I'm glad: I never really felt that we had completely resolved
that problem, because there was always that comment in the code about certain
situations that only arose during deeply-nested joins.

So, I'm glad that you have been able to provoke the strange DERBY-3097 error
with your latest work, as hopefully that will let us get a bit farther into uncovering
what's really going on there.

I still believe that that basic DERBY-3097 change is, at its core, correct, because
it just seems wrong to try to fetch a column value from a non-existent row.

In the other DERBY-3097 scenarios, we were able to find a place higher in the
call tree, where the result set logic could tell that we were initializing a result
set which had not yet been opened, and could handle the initialization logic
more cleanly. Hopefully we can do that again here (and in DERBY-4798).

Once again, thank you very much for exploring these issues in such detail, and
for documenting your findings in your comments and writeups. These work products
will be of *tremendous* value to future students of this code.

And, lastly: 10,000 generated queries! That is fabulous! Someday, if I get the time,
I'd like to use these query generation tools to continue working on the query plan
visualization features, so I'm very pleased that you've been able to make the tools
(and the queries) work properly in Derby.



> Complex nested joins problems
> -----------------------------
>
>                 Key: DERBY-4712
>                 URL: https://issues.apache.org/jira/browse/DERBY-4712
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 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
>            Reporter: Thomas Mueller
>            Assignee: Dag H. Wanvik
>            Priority: Minor
>             Fix For: 10.7.0.0
>
>         Attachments: assert-bind-opt-trees.log, assert-bind-opt-trees.txt, derby.log.analyzed,
derby.log.simpler, derby4712a.diff, derby4712a.stat, drawing.txt
>
>
> 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 );

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message