Re: BUG #6535: LEFT JOIN on large table is altering data

Kevin,

Thanks. That was a direct copy/paste. It happened that the *UNION*ed
queries spat out those results in the same order that the queries appeared.

Just want to again emphasize that my database state has changed, so I am
not sure this is remains a good case for finding a bug.

Aren

On Sun, Mar 18, 2012 at 3:33 PM, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov
> wrote:

> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> > Aren Cambre <aren(at)arencambre(dot)com> wrote:
> >
> >> SELECT COUNT(*)
> >> FROM consistent.master
> >> WHERE citation_id IS NOT NULL
> >> UNION
> >> SELECT COUNT(*)
> >> FROM consistent.master
> >> UNION
> >> SELECT COUNT(*)
> >> FROM consistent.master
> >> WHERE citation_id IS NULL
> >>
> >> I got this result:
> >>
> >> 2085344
> >> 2085343
> >> 0
> >>
> >> Not clear how adding a WHERE clause, whose only practical effect
> >> is to reduce the number of rows returned, could cause *more* rows
> >> to be returned.
>
> > Never assume that the rows will be returned in any particular
> > order from a query unless you specify ORDER BY.
>
> Hmm. That doesn't explain why the numbers don't add up, though. Is
> that a copy/paste from an actual query run, or was there some
> hand-editing there? In particular, you might easily get that result
> if that last line was really:
>
> WHERE citation_id = ''
>
> instead of the IS NULL test. In the ANSI standard and in PostgreSQL
> there is a big difference between an empty string and NULL, although
> there is at least one product I know of which breaks from standard
> compliance by treating them as equivalent.
>
> -Kevin
>