db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "A B (JIRA)" <derby-...@db.apache.org>
Subject [jira] Updated: (DERBY-558) Optimizer hangs with query that uses more than 6 tables and does subquery flattening.
Date Thu, 08 Sep 2005 21:38:30 GMT
     [ http://issues.apache.org/jira/browse/DERBY-558?page=all ]

A B updated DERBY-558:

    Attachment: repro.sql

Attaching a simplified reproduction of the hang.  Note that this particular reproduction is
completely contrived and nonsensical, but it nonetheless demonstrates the problem.  In order
to reproduce, start ij with the "derby.optimizer.noTimeout" property set to true, connect
to a database, and then run the attached sql script:

> java -Dderby.optimizer.noTimeout=true org.apache.derby.tools.ij
ij version 10.2
ij> connect 'jdbc:derby:testdb;create=true';
ij> run 'repro.sql';

Note that the hang won't reproduce if "noTimeout" is false (which is the default) because
eventually the optimizer will decide that it's taking too long and will quit.  That's nice
because it means most people won't ever see this problem :)  However, when noTimeout is set
to true the query _should_ still finish (even if it takes longer), so I _do_ think this is
a bug.

> 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:,,,,,
>  Environment: Running query in "ij"  with derby.optimizer.noTimeout=true
>     Reporter: A B
>     Assignee: A B
>      Fix For:,
>  Attachments: 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:
For more information on JIRA, see:

View raw message