[
https://issues.apache.org/jira/browse/DERBY-6317?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13751421#comment-13751421
]
Mike Matrigali commented on DERBY-6317:
---------------------------------------
>From a "get it done" perspective, making the routine return 1 row rather than
>0 matches similar instances in other
parts of the code (and there is a RESOLVE in the code that asks even if that
should be the case). It is still an
estimate even though it is using real data, so there is always part that is a
guess and since we know "0's" seem
to cause problems it seem reasonable to me to make it 1. It matches current
assumptions about unique key indexes where
we return estimates of 1 row for matching key scan, when it could really return
either 0 or 1 row.
It may hide other
bugs in optimizer costing but seems like a reasonable incremental fix if it
seems to fix your bug and passes existing
tests.
Your app is definitely exploring areas that have not been tested in Derby.
When that code was written, it is likely no
disk existed in development that could have held your db.
> 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
