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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2012-03-19 02:33:31 | Re: BUG #6535: LEFT JOIN on large table is altering data |
Previous Message | Kevin Grittner | 2012-03-18 20:33:38 | Re: BUG #6535: LEFT JOIN on large table is altering data |