Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Message-ID: <294076333.1128531349249.JavaMail.jira@ajax.apache.org>
Date: Wed, 5 Oct 2005 18:55:49 +0200 (CEST)
From: "A B (JIRA)" <derby-dev@db.apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Resolved: (DERBY-558) Optimizer hangs with query that uses
 more than 6 tables and does subquery flattening.
In-Reply-To: <1867432676.1126214910369.JavaMail.jira@ajax.apache.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

     [ http://issues.apache.org/jira/browse/DERBY-558?page=all ]
     
A B resolved DERBY-558:
-----------------------

    Resolution: Fixed

I ran the repro attached to this issue as well as the new test case in lang/subqueryFlattening.sql against the trunk (10.2) and the 10.1 branch to verify that the changes have been committed and that things are working as they should.  It all looks good, so I'm resolving and closing this issue.  Thanks for committing, Satheesh.

> 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