[ http://issues.apache.org/jira/browse/DERBY-558?page=all ]
Rick Hillegas updated DERBY-558:
--------------------------------
Component: SQL
Assigning to SQL component.
> 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
> Components: SQL
> 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
