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

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote:
> Aren Cambre <aren@arencambre.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