[jira] [Updated] (DERBY-6317) Optmizer can choose the wrong path when BTreeCostController.java returns an estimate cost and row count of 0.0

["Mamta A. Satoor (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-6317?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Mamta A. Satoor updated DERBY-6317:
-----------------------------------

    Attachment: DERBY_6317_junit_test_v1_diff.txt

I have been working on a junit test for this jira and for some strange reason, the problem does not reproduce yet. The test is based on the stand alone test case. I had made one optimization in the junit test case to create the indexes after the database was loaded and that is when the problem did not reproduce. Since then, I have removed the optimization so now the junit test looks similar to stand alone test case and I have fired that junit run this morning. Will post the results when the test is over. I did want to mention that the data load takes close to 45minutes with the test. Mike mentioned in his case it took only 10 minutes. I am attaching a patch for junit test DERBY_6317_junit_test_v1_diff.txt. May be Mike or some one else can see why the load in my case is taking much longer. This patch is not ready for commit. The patch has some changes to largedata suite, please ignore it. I commented out other largedata tests so I can focus on just my junit repro.

I am running on Windows 7 machine with IBM jdk 1.6
                
> Optmizer can choose the wrong path when BTreeCostController.java returns an estimate cost and row count of 0.0
> --------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-6317
>                 URL: https://issues.apache.org/jira/browse/DERBY-6317
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.8.2.2
>         Environment: Derby 10.8.2.2 on Oracle Solaris 10 
>            Reporter: Brett Bergquist
>            Assignee: Mike Matrigali
>         Attachments: derby6317_2.diff, derby6317.diff, DERBY_6317_junit_test_v1_diff.txt, DERBY_6317_temp_changes_for_debugging.txt, testRepro_v1.txt
>
>
> The optimizer can chose the wrong path when BTreeCostController.java returns an estimate cost and row count of 0.0.  
> Assume that you have two tables that are being joined like:
> SELECT * FROM T1, T0
> WHERE T1.ID = T0.F_ID and
> T0.ID = 3;
> Also assume that T0 has two columns, ID and F_ID and F_ID is a foreign key on T1.ID.   Assume that T1.ID is the primary key of T1 and (T0.F_ID, T0.ID) is the primary key on T0.  Assume that there is a non-unique index on T0.ID.
> The correct query plan for this should be to query T0 using the non-unique index on T0.ID and then use the foreign key value in those rows to do query T1 using the primary key on T1.
> With some values of T0.ID in the above query this query plan is chosen and works.  With other values of T0.ID , the query plan does an query on T0 using the non-unique index on T0.ID and then does a table scan on T1.
> For example, in my case the query:
> SELECT * FROM T1, T0
> WHERE T1.ID = T0.F_ID and
> T0.ID = 22112129;
> has this query plan.   
> The problem appears to be in BTreeCostController.java.  When this returns the same value for the "left_of_start" and the "left_of_stop" (which is being used to estimate the number of rows and cost), then the estimate cost and row count becomes 0.0.   When this is used in the join order of T0, T1, then the cost of the table scan for T1 becomes 0.0 as well.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira