RE: [HACKERS] Re: Subselects open issue Nr. 5

Yes, and Oracle7 also.

I think with NULL values Andreas is right. The whole statement should be
NULLed. That to me is the intuitive behaviour.

Michael

--
Dr. Michael Meskes, Project-Manager    | topsystem Systemhaus GmbH
meskes@topsystem.de                    | Europark A2, Adenauerstr. 20
meskes@debian.org                      | 52146 Wuerselen
Go SF49ers! Go Rhein Fire!             | Tel: (+49) 2405/4670-44
Use Debian GNU/Linux!                  | Fax: (+49) 2405/4670-10

> -----Original Message-----
> From:    Vadim B. Mikheev [SMTP:vadim@sable.krasnoyarsk.su]
> Sent:    Sunday, February 15, 1998 12:33 PM
> To:    Zeugswetter Andreas SARZ
> Cc:    'pgsql-hackers@hub.org'; Michael Meskes; ocie@paracel.com
> Subject:    Re: [HACKERS] Re: Subselects open issue Nr. 5
>
> Ok. I'll fix this. As I see, this is exactly what Oracle 6 does, but
>
> Zeugswetter Andreas SARZ wrote:
> >
> > Informix treats the subselect as NULL if no rows are returned.
> > Therefore all parent rows that are not null are returned.
> >
> > select * from taba where a not in (<a select returning no row>);
> --
> > is same as
> > select * from taba where a is not null;
>   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> Oracle returns tuples with A being NULL!!! and more of that (table B
> is empty):
>
> SQL> select count(*) from a where x > ALL (select * from b);
>
>   COUNT(*)
> ----------
>          2
>
> and result is the same for all OP-s with ALL modifier... And
>
> SQL> select count(*) from a where x in (select * from b);
>
>   COUNT(*)
> ----------
>          0
> having tuple with NULL in X...
>
> Who's right ?
> What standard says ?
>
> Vadim