[
https://issues.apache.org/jira/browse/DERBY-6317?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13750905#comment-13750905
]
Brett Bergquist commented on DERBY-6317:
----------------------------------------
When BTreeCostController.java returns the same value for "left_of_start" and
"left_of_stop", this conceptually means that very few rows are going to be
returned using the index but it does not mean that 0 (zero rows) are going to
be returned which is what the code currently does.
> 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
>
> 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
