[ http://issues.apache.org/jira/browse/DERBY-558?page=all ]
A B updated DERBY-558:
----------------------
Attachment: d558.patch
Attaching a patch for this problem. The patch does the following:
1) Fixes the logic in OptimizerImpl.java that was causing the hang (an indirect
infinite loop).
2) Adds some comments describing the "JUMPING" logic that is in OptimizerImpl
so that developers looking at the code can (hopefully) figure out what's going
on more quickly in the future.
3) Adds a test case to the lang/subqueryFlattening.sql test for verification of
the fix. The test case is based on the repro attached to this issue. NOTE: I
had to set the "derby.optimizer.noTimeout" property to true for this entire
test--I think this is okay since everything still passes (on my machine), but
if anyone feels otherwise, please let me know...
I ran derbyall on Windows 2000 w/ Sun jdk 1.4.2 and saw no failures. If
someone could review this, I'd be grateful..
> Optimizer hangs with query that uses more than 6 tables and does subquery
> flattening.
> -------------------------------------------------------------------------------------
>
> Key: DERBY-558
> URL: http://issues.apache.org/jira/browse/DERBY-558
> Project: Derby
> Type: Bug
> Versions: 10.1.1.0, 10.0.2.1, 10.0.2.0, 10.1.2.0, 10.2.0.0, 10.0.2.2
> Environment: Running query in "ij" with derby.optimizer.noTimeout=true
> Reporter: A B
> Assignee: A B
> Fix For: 10.1.2.0, 10.2.0.0
> Attachments: d558.patch, repro.sql
>
> I was running a query that has a large number (hundreds) of tables in it and
> I set the derby property "derby.optimizer.noTimeout" to true to see what plan
> Derby would choose as the _best_ plan for the query. When doing so, I ran
> into a situation where the optimizer hung forever--which is wrong. I expect
> that setting "noTimeout" to true might cause the query to run more slowly
> (since it has to evaluate ALL possible join orders for all of the tables in
> question), but it should _not_ cause the optimizer to hang forever.
> I noticed that "subquery flattening" is peformed on the query, which
> introduces dependencies between the various tables and thus restricts the
> possible join orders that the optimizer can choose (see
> http://db.apache.org/derby/docs/10.1/tuning/ctuntransform25868.html). I was
> eventually able to track the problem down to code in OptimizerImpl where, for
> queries with more than 6 tables, a certain "jumping" algorithm is used to try
> to allow the optimizer to find a better plan more quickly.
> Long story short, there is logic in the "jumping" mechanism that tries to put
> the tables into a legal join order, but in certain (rare) cases where
> multiple join order dependencies have to be enforced, the jump logic can end
> up looping indefinitely, causing the "hang" in the optimizer.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
