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] Updated: (DERBY-2526) Wrong query results due to column ordering in UNION view
Date Fri, 25 May 2007 22:50:16 GMT

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


Mime
View raw message