db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-2526) Wrong query results due to column ordering in UNION view
Date Sat, 07 Apr 2007 19:39:32 GMT

    [ https://issues.apache.org/jira/browse/DERBY-2526?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12487411

Bryan Pendleton commented on DERBY-2526:

For both the "good" and "bad" queries, the optimizer examines the complete join orders:
 - 0,1,2
 - 2,0,1
 - 2,1,0

In the "good" case, the optimizer assigns the costs:
 - 1405
 - 1405
 - 401
And thus selects the join order 2,1,0

In the "bad" case, the optimizer assigns the costs:
 - 1405
 - 401
 - 401
And thus selects the join order 2,0,1

This leaves me with two "next questions" to study:
1) Why does the optimizer compute a different cost for the join order 2,0,1 in the two cases?
2) If the only problem is that the optimizer gets the costs wrong, it might pick a poorer
query plan, but it still seems like it should pick a *valid* query plan, right? So, why, in
the "bad" case where the optimizer uses the join order 2,0,1, does it actually get *incorrect
results*? That is, is 2,0,1 actually a valid plan for executing this query? If so, why do
we get wrong results? If it's *not* valid, why does the optimizer proceed to fully cost it
out, rather than dismissing it during optimization?

> 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:,,,
>            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.

View raw message