Re: [HACKERS] Subselects and NOTs

ocie(at)paracel(dot)com wrote:
>
> Meskes, Michael wrote:
> >
> > Exactly the same with the latest Oracle7 version.
>
> I also get the same results with Sybasem, with one interesting
> exception. Sybase will not let me select * in a subquery! It gives
> me the following:
>
> The symbol '*' can only be used for a subquery select list when the subquery is
> introduced with EXISTS or NOT EXISTS.

It's funny... SyBases could check subquery' target list length...

Ok, thanks. Pg returns the same results for these queries. But I asked
Andreas to run other queries and they show that both Oracle & Informix
push NOT into subquery clause (and so, 'NOT x IN' is equal 'x NOT IN'),
Pg doesn't, currently.

Ocie, could you run this in SyBase:

create table a (a int, a1 char(8));
create table b (b int);
insert into a values (1, 'one');
insert into a values (NULL, 'null');

insert into b values (1);
insert into b values (NULL);

select * from a where a in (select * from b);
-- 1 row with a == 1 expected
select * from a where a not in (select * from b);
-- 0 row expected
select * from a where not a in (select * from b);
-- 0 row in Oracle & Informix, 1 row in Pg (with a == NULL), SyBase ???
select * from a where not (a in (select * from b));
-- the same. SyBase ???

Vadim