Re: Missing numbers

On Wed, 2005-06-01 at 00:27 -0400, Alvaro Herrera wrote:
> On Wed, Jun 01, 2005 at 01:21:28AM +0100, Simon Riggs wrote:
> > On Tue, 2005-05-31 at 18:28 +0200, Harald Fuchs wrote:
>
> > > SELECT g.num
> > > FROM generate_series ((SELECT min(doc_numero) FROM bdocs),
> > > (SELECT max(doc_numero) FROM bdocs)) AS g(num)
> > > LEFT JOIN bdocs ON bdocs.doc_numero = g.num
> > > WHERE bdocs.doc_numero IS NULL
> >
> > SELECT g.num
> > FROM generate_series ((SELECT min(doc_numero) FROM bdocs),
> > (SELECT max(doc_numero) FROM bdocs)) AS g(num)
> > WHERE g.num NOT IN (select doc_numero
> > from bdocs
> > where doc_numero is not null)
> >
> > is more likely to return a correct answer, since
> > bdocs.doc_numero will never equal g,num when it is also NULL
>
> Oh, but it is an outer join, so it should generate the NULLs, yes?

You have a point, but so do I.

I wonder what the SQL spec should happen in this case? It depends upon
whether the NOT NULLs are excluded before or after the join takes
place.

If the WHERE clause said bdocs.doc_numero > 7 we would hope that this
was applied before the join. The correct answer, in that case, would
result whether we applied such a WHERE clause before or after the join.

But a WHERE clause that specifically disagrees with a join clause is
harder, and I would imagine we don't have a specific test for such a
thing, other than to exclude the push-down of the clause before the join
in all cases.

Perhaps we should test this...

Either way, I still prefer my phrasing of the SQL, which seems clearer,
but I would say that wouldn't I?

Best Regards, Simon Riggs