Re: IN vs EXISTS equivalence

On Wed, Sep 3, 2008 at 9:17 AM, daveg <daveg(at)sonic(dot)net> wrote:

> On Thu, Aug 14, 2008 at 06:50:09PM +0100, Simon Riggs wrote:
> >
> > On Fri, 2008-08-08 at 16:23 -0400, Tom Lane wrote:
> >
> > > NOT IN is a lot trickier,
> > > condition: you must also assume that the comparison operator involved
> > > never yields NULL for non-null inputs. That might be okay for btree
> > > comparison functions but it's not a very comfy assumption in general;
> > > we certainly haven't got any explicit knowledge that any functions are
> > > guaranteed to act that way. So this case might be worth doing later
> ...
> > Just found this comment, after reading what you said on other thread
> > about NOT IN.
> >
> > NOT IN is a serious performance issue for most people. We simply can't
> > say to people "you were told not to".
> >
> > If we can fix it easily for the majority of cases, we should. We can't
> > let the "it won't work in certain cases" reason prevent various
>
> A suggestion: what about adding an attribute to functions to declare that
> they never return null?
>
And if function still returns null then error will be raised?
Then you will end up adding NOT NULL also to IN and OUT parameters.
IIRC it was possible in Oracle to declare local variables NOT NULL.

>
> declare foo(int, int) returns int immutable not null as ...
>
>
> -dg
>
>
> --
> David Gould daveg(at)sonic(dot)net 510 536 1443 510 282 0869
> If simplicity worked, the world would be overrun with insects.
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>