From: | "Vadim B(dot) Mikheev" <vadim(at)sable(dot)krasnoyarsk(dot)su> |
---|---|
To: | Zeugswetter Andreas SARZ <Andreas(dot)Zeugswetter(at)telecom(dot)at> |
Cc: | "'pgsql-hackers(at)hub(dot)org'" <pgsql-hackers(at)hub(dot)org>, Michael Meskes <meskes(at)topsystem(dot)de>, ocie(at)paracel(dot)com |
Subject: | Re: [HACKERS] Re: Subselects open issue Nr. 5 |
Date: | 1998-02-15 11:33:09 |
Message-ID: | 34E6D275.4E1B3ECB@sable.krasnoyarsk.su |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Michael Meskes wrote:
>
> > 5. I need in advice: if subquery introduced with NOT IN doesn't return
> > any tuples then qualification is failed, yes ?
>
> Do you mean something like this:
>
> select * from table1 where x not in (select x from table2)
>
> table1.x: a,b
>
> table2.x is empty
>
> The correct answer IMO is 'a,b' in this case.
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
From | Date | Subject | |
---|---|---|---|
Next Message | Vadim B. Mikheev | 1998-02-15 11:41:29 | Re: [HACKERS] Subselects are in CVS... |
Previous Message | Meskes, Michael | 1998-02-15 11:01:00 | RE: [HACKERS] Re: [PORTS] v6.3 release ToDo list and supported po rts |