On Fri, 18 Jun 2004, [iso-8859-1] SZUCS Gábor wrote:
> Dear Gurus,
>
> ----- Original Message -----
> From: "Stephan Szabo" <sszabo@megazone.bigpanda.com>
> Sent: Thursday, June 10, 2004 7:14 PM
>
>
> >
> > On Thu, 10 Jun 2004, Stephan Szabo wrote:
> >
> > >
> > > On Thu, 10 Jun 2004, Jean-Luc Lachance wrote:
> > >
> > > > I agree, but it should be a simple rewrite. No?
> > >
> > > It's NULLs inside the subselect that are the issue.
> > >
> > > select 1 in (select a from foo)
> > > select exists ( select 1 from foo where a=1)
>
> Just a dumb try :)
>
> SELECT (exists(select 1 from foo where a isnull) AND NULL)
> OR exists(select 1 from foo where a=1)
>
> AFAIK this returns
> * NULL if (NULL in foo.a) and (1 not in foo.a)
> * (1 in foo.a) otherwise.
>
> The weakness is the doubled exists clause. I'm sure it makes most cases at
> least doubtful...
Well, once you take into account the lhs being potentially null
lhe in (select rhe from foo) is something like:
case when lhe is null then
not exists(select 1 from foo limit 1) or null
else
(exists(select 1 from foo where rhe is null) and null)
or exists(select 1 from foo where rhe=lhe)
end
I think the real win occurs for where clause cases if it can pull up the
exists that references lhe so that it doesn't try to evaluate it on every
row and that's unlikely to occur in something like the above.
