Re: [HACKERS] Subselects and NOTs - Mailing list pgsql-hackers

Vadim B. Mikheev wrote:
>
> ocie@paracel.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:

Here's my output.  BTW, this reminds me of a small request (perhaps I
will write a patch for it myself).  In Postgres, a column can be
designated "not null", the default being to allow nulls.  In the
default Sybase configuration, it is the other way around.  In the
interest of writing cross database compatible code, I try to always
call out columns as either "null" (nulls allowed), or "not null"
(nulls not allowed).  Unfortunately, Postgres does not support this
(Please forgive me if this has been added recently, as I have been too
busy to try out the latest code).

In short, it would be nice if Postgres would take "null" as a type
specifier as well as "not null".

And now for the Sybase output (I think it agrees with the rest):

1> select @@version
2> go

 -----------------------------------------------------------------------------
 SQL Server/11.0.2/P/Sun_svr4/OS 5.4/EBF 6536/OPT/Sat Aug 17 11:54:59 PDT 1996

(1 row affected)
1> create table a (a int null,a1 char(8) not null)
2> go
1> create table b (b int null)
2> go
1> insert into a values (1, 'one')
2> insert into a values (NULL, 'null')
3> insert into b values (1)
4> insert into b values (NULL)
5> go
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
1> select * from a where a in (select b from b)
2> go
 a           a1
 ----------- --------
           1 one

(1 row affected)
1> select * from a where a not in (select b from b)
2> go
 a           a1
 ----------- --------

(0 rows affected)
1> select * from a where not a in (select b from b)
2> go
 a           a1
 ----------- --------

(0 rows affected)
1> select * from a where not (a in (select b from b))
2> go
 a           a1
 ----------- --------

(0 rows affected)


On another note, I have been following this "not a in b" vs "not a in
b" discussion and it seems to me that the two statements are logically
equivalent.  Testing for a's membership in the set b and then negating
should be equivalent to testing for a's membership in the compliment
of set b.  In these tests, nulls seem to be treated just like any
other value.

Ocie