[
https://issues.apache.org/jira/browse/DERBY-6317?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mike Matrigali resolved DERBY-6317.
-----------------------------------
Resolution: Fixed
Fix Version/s: 10.11.0.0
10.10.1.3
10.9.2.2
10.8.3.1
Fixed in trunk, backported to 10.10, 10.9, 10.8. Fix is applicable to previous
releases and can be backported if anyone is interested. I don't plan on
backporting it any further right now.
> 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
> Fix For: 10.8.3.1, 10.9.2.2, 10.10.1.3, 10.11.0.0
>
> Attachments: derby6317_2.diff, derby6317.diff,
> DERBY_6317_junit_test_v1_diff.txt, DERBY_6317_junit_test_v2_diff.txt,
> DERBY_6317_junit_test_v3_diff.txt, DERBY_6317_junit_test_v4_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
