Skip site navigation (1) Skip section navigation (2)

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

From: Aren Cambre <aren(at)arencambre(dot)com>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: pgsql-bugs(at)postgresql(dot)org, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: BUG #6535: LEFT JOIN on large table is altering data
Date: 2012-03-19 02:14:48
Message-ID: CAA1mBroifKG9CO8-07XfFmGASN__9PqeRvDUcHrtb8iV0GNE+w@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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
>

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2012-03-19 02:33:31
Subject: Re: BUG #6535: LEFT JOIN on large table is altering data
Previous:From: Kevin GrittnerDate: 2012-03-18 20:33:38
Subject: Re: BUG #6535: LEFT JOIN on large table is altering data

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group