[ http://issues.apache.org/jira/browse/DERBY-558?page=all ] A B closed DERBY-558: ---------------------
> 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
