Re: BUG #6535: LEFT JOIN on large table is altering data - Mailing list pgsql-bugs

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. That seems buggy to me.

Never assume that the rows will be returned in any particular order
from a query unless you specify ORDER BY.  Assuming, as you seem to
be doing, that rows from the left side of a UNION will be output
before rows from the right side is not safe.  You have no way of
knowing which row in a result set like that came from which of the
UNIONed SELECTs.  In this case your assumption is almost certainly
wrong.

-Kevin