Thread: 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

Meskes, Michael wrote:
>
> 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.

Not sure.
IMHO, any element, either with defined value or with undefined value (NULL),
can't be contained by empty set.

Hm, btw, just curious, what Informix returns for

select * from taba where (a,b) not in (<a select returning no row>);

having in taba tuples with (a,b) in

(NULL, a_value)
(NULL, NULL)

?
Does it return all tuples except for (NULL,NULL) ?

Vadim