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

Thanks. While creating that very file, I discovered that 1 row had blanks
in every field despite a column having a *NOT NULL* constraint and another
column being a* *serial. Removing that column appears to fixed the problem.

Something about that column made Postgres unhappy, though. If I ran these
queries:

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.

Aren

On Fri, Mar 16, 2012 at 12:56 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> aren(at)arencambre(dot)com writes:
> > (Note that this issue is described fully at
> >
> http://stackoverflow.com/questions/9713537/postgres-left-join-is-creating-new-data
> .
> > A few knowledgeable people have weighed in, and there is no solution
> > identified.)
>
> This might be a bug, but you've not provided sufficient information for
> someone else to reproduce the problem. What would be good is a SQL
> script that reproduces the error from a standing start (empty database).
>
> regards, tom lane
>