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(at)topsystem(dot)de | Europark A2, Adenauerstr. 20
meskes(at)debian(dot)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(at)sable(dot)krasnoyarsk(dot)su]
> Sent: Sunday, February 15, 1998 12:33 PM
> To: Zeugswetter Andreas SARZ
> Cc: 'pgsql-hackers(at)hub(dot)org'; Michael Meskes; ocie(at)paracel(dot)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