db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "A B (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-2526) Wrong query results due to column ordering in UNION view
Date Fri, 06 Apr 2007 15:33:32 GMT

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

A B commented on DERBY-2526:
----------------------------

Thank you for your investigation of this issue, Bryan.  I've been a bit busy so I haven't
had time to look at this more, but it sounds like you're doing a great job of narrowing this
down.

One suggestion (without having actually run done any investigation of my own): does this problem
reproduce if you set "derby.optimizer.noTimeout=true"?  If it does, then you could set that
property and then step through the code to see if there are any obvious differences in the
optimization paths for the two statements.  I usually start by putting a break point in the
"getNextPermutation()" method of OptimizerImpl and then doing a quick step through of that
method for the different queries, to see if any there are any obvious differences in code
flows (esp. number of times the method is called).

Another technique I use (which is a tad less scientific) is to put System.outs in the "rememberBestCost()"
method of OptimizerImpl to print out: a) the join order that is being saved as "best", and
b) the cost of that join order.  Then add a similar System.out to the "modifyAccessPaths()"
method of OptimizerImpl and print out the final value of bestJoinOrder, along with the final
cost estimate.  If you do that and then run the two different queries,  you can often see
a difference in plans/costs that helps track down the issue at hand...

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


Mime
View raw message