[jira] Updated: (DERBY-2526) Wrong query results due to column ordering in UNION view

["A B (JIRA)" <ji...@apache.org>]

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