[
https://issues.apache.org/jira/browse/DERBY-2526?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
A B updated DERBY-2526:
-----------------------
Attachment: d2526_v1.patch
DERBY-2526.html
I started looking at this issue and, using Bryan's excellent analysis of the
problem thus far, was able to track down what I believe is the underlying
cause. After reading all of Bryan's comments (thanks Bryan!), I found myself
wondering why it was that "bvw2.c1" in the predicate "bvw2.c1 = b4.c7" had a
column number w.r.t to the JoinNode while "bvw.c5" in the predicate "b3.c8 =
bvw2.c5" had a column number w.r.t to the view (BVW) itself. Given that both
column references have the same tableNumber (by the time we get to transitive
closure computation), I was expecting that their column numbers would be w.r.t
to the same object--i.e. to "BVW". But that did not appear to be the case (as
Bryan discovered).
So I did some tracing through the "preprocess()" code and was able to come up
with what I think is a reasonable explanation of the problem. It also agrees
with (and builds on) all of Bryan's previous findings (I think). I'm attaching
the writeup as "d2526_v1.html".
If this explanation is correct, the fix is a two-liner--which I'm attaching as
d2526_v1.patch. I ran derbyall and suites.All with this change and there were
no failures. I also verified that both of the queries in derby-2526 now return
the same result (1 row). I have not added any tests for this problem yet, as
I'm just looking for validation of the explanation and proposed fix from anyone
who might have the time to read it over. The fact that derbyall/suites.All
both ran cleanly is encouraging.
Many many thanks again to Bryan for all of his effort and analysis. Regardless
of whether or not the attached writeup is an accurate assessment of the
problem, Bryan's comments have been very helpful.
> 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
> Assigned To: Bryan Pendleton
> Attachments: badQuery.log, d2526_v1.patch, DERBY-2526.html,
> derby-2526.sql, DistinctTestNotes.txt, firstTryPatch.diff, 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.
