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

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

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Aren Cambre" <aren(at)arencambre(dot)com>
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-18 20:33:38
Message-ID: 4F66005202000025000463DC@gw.wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-bugs
"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: Aren CambreDate: 2012-03-19 02:14:48
Subject: Re: BUG #6535: LEFT JOIN on large table is altering data
Previous:From: Aren CambreDate: 2012-03-18 20:29:41
Subject: Re: BUG #6535: LEFT JOIN on large table is altering data

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