Re: Subselects open issue Nr. 5

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

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

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

Guess what !

It (Informix 9.12 and DB/2 4.1) says syntax error (at the first comma).
(Even looked up the Manuals)
Haha Hihi Hoho. I guess you beat them here Vadim+Bruce+Tom. * hear the cork
popping ? *

Andreas

PS.: from the logical point of view, I think all rows from x should qualify
for a where (a,b) not in (empty set)
because for me NULL is not an empty set, at least it is treated as a value
in a unique index.
On the other hand you could argue: the whole set is NULL so a not in ()
should filter where a not null.
I guess no standard has thought about that so far. (Tom ?)
Summary: I guess it is for us to decide. So I would do exactly as you said
and return all except (NULL,NULL)

Vadim B. Mikheev wrote:

Zeugswetter Andreas SARZ wrote:

Guess what !

It (Informix 9.12 and DB/2 4.1) says syntax error (at the first comma).

...like SyBase 11...

(Even looked up the Manuals)
Haha Hihi Hoho. I guess you beat them here Vadim+Bruce+Tom. * hear the cork
popping ? *

Andreas

PS.: from the logical point of view, I think all rows from x should qualify
for a where (a,b) not in (empty set)
because for me NULL is not an empty set, at least it is treated as a value
in a unique index.
On the other hand you could argue: the whole set is NULL so a not in ()
should filter where a not null.
I guess no standard has thought about that so far. (Tom ?)
Summary: I guess it is for us to decide. So I would do exactly as you said
and return all except (NULL,NULL)

I prefer "logical point of view" and vote for Oracle-like behaviour.
BTW, it's easy to implement...

Vadim

I prefer "logical point of view" and vote for Oracle-like behaviour.
BTW, it's easy to implement...

Sorry I haven't had time to look up the standard. However, in the absence of
that I vote for the "logical point of view" also. This is better called the
"intuitive point of view"?

- Tom