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

Kevin,

You're absolutely correct; there's no telling what order the planner will
use.

I did in fact run the queries separately and got the same result that I
intimated in the UNIONed queries. I can no longer reproduce, however,
because I have since altered the table. So if there is a bug, I may not be
much help in nailing it down.

The only thing I can help with is this appears to have happened at row
# 583847 of just over 2 million rows, per a straight CSV dump of the table.

Aren

On Sun, Mar 18, 2012 at 3:22 PM, 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. 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
>