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 Fri, 06 Apr 2007 01:34:32 GMT

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

Bryan Pendleton commented on DERBY-2526:

In the "good" query plan, table B4 is joined against the UNION, and the result 
is then joined against B3. This makes sense, and seems like a reasonable plan.

In the "bad" query plan, table B4 is joined against table B3, and the result is
then joined against the UNION. This does *not* make sense. The query does
not specify any criteria which would support such a join (unless it were a
cartesian product I guess, but still I don't understand this join). In this query plan,
here's an excerpt from the portion of the plan where table B4 is joined against
table B3:

        Right result set:
            Table Scan ResultSet for B3 at read committed isolation level using instantaneous
share row locking chosen by the optimizer
            Rows seen = 0
            Rows filtered = 0

            scan information:
                Bit set of columns fetched=All
                Number of columns fetched=4
                Number of pages visited=1
                Number of rows qualified=0
                Number of rows visited=1

The "bad" query plan visits the B3 row, but the B3 row does not qualify, so apparently
the query plan specified some sort of strange join criteria for this join between
table B4 and table B3, and according to that criteria no rows qualified, causing
the end result to be no rows found by the query.

> 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