[
https://issues.apache.org/jira/browse/DERBY-2526?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12487271
]
A B commented on DERBY-2526:
----------------------------
One thing that stands out here: estimated row count that is less than 1.0.
This means that whenever the row counts are multiplied through, we'll actually
estimate fewer rows than we should., which could throw off estimates. I've
noticed this oddity a couple of times myself when working with the
optimizer--most recently while looking at DERBY-1905.
I've always thought this was strange. I wonder if you put a check for this at
the end of FromBaseTable.estimateCost()--ex. set anything with a row count less
than 1 to be 1.0--what would happen? Could be a red herring, but should be
easy enough to check...
@@ -1832,6 +1832,12 @@
/* Put the base predicates back in the predicate list */
currentJoinStrategy.putBasePredicates(predList,
baseTableRestrictionList);
+
+if (costEstimate.rowCount() < 1.0)
+{
+// System.out.println("-=- Oops! " + costEstimate.rowCount() + " -- " +
costEstimate.singleScanRowCount());
+costEstimate.setCost(costEstimate.getEstimatedCost(), 1.0d,
costEstimate.singleScanRowCount());
+}
return costEstimate;
}
Proverbial (and potentialy irrelevant) 2c.
> Wrong query results due to column ordering in UNION view
> --------------------------------------------------------
>
> Key: DERBY-2526
> URL: https://issues.apache.org/jira/browse/DERBY-2526
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.0.2.1, 10.1.3.1, 10.2.2.0, 10.3.0.0
> Reporter: Bryan Pendleton
> Attachments: badQuery.log, derby-2526.sql, goodQuery.log
>
>
> I think both select statements in the attached repro script should return 1
> row, but in fact the first statement returns 1 row and the second returns
> zero rows.
> The only difference between the two statements is that the columns in the
> UNION view are listed in a different order (bvw vs. bvw2).
> This seems like a bug to me; the order of the columns in the view definition
> shouldn't matter, should it?
> As Army noted on the derby-dev list, the fact that this reproduces with 10.0
> means that it is not caused by some of the 10.2 optimizer changes. Something
> else is going wrong.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
