Exactly the same with the latest Oracle7 version.

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:	Zeugswetter Andreas SARZ [SMTP:Andreas.Zeugswetter@telecom.at]
> Sent:	Wednesday, February 18, 1998 6:33 PM
> To:	'pgsql-hackers@hub.org'
> Subject:	Re: [HACKERS] Subselects and NOTs
> 
> This is what I did:
> 	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');
> 
> 
> and this is what I got from Informix:
> > select * from a where a not in (select * from b);
>           a a1
>           1 one
>             null
> 2 row(s) retrieved.
> >  select * from a where not (a in (select * from b));
>           a a1
>           1 one
>             null
> 2 row(s) retrieved.
> > select * from a where not a in (select * from b);
>           a a1
>           1 one
>             null
> 2 row(s) retrieved.
> > select * from a where a<>(select * from b);
>           a a1
> No rows found.
> > select * from a where a=(select * from b);
>           a a1
> No rows found.
> 
> Andreas

Thu, 19 Feb 1998 09:58:07 +0100
"Meskes, Michael" <meskes@topsystem.de>
RE: [HACKERS] Subselects and NOTs
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.

changing this to "select b from b" seems to work though.

Ocie

> 
> 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:	Zeugswetter Andreas SARZ [SMTP:Andreas.Zeugswetter@telecom.at]
> > Sent:	Wednesday, February 18, 1998 6:33 PM
> > To:	'pgsql-hackers@hub.org'
> > Subject:	Re: [HACKERS] Subselects and NOTs
> > 
> > This is what I did:
> > 	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');
> > 
> > 
> > and this is what I got from Informix:
> > > select * from a where a not in (select * from b);
> >           a a1
> >           1 one
> >             null
> > 2 row(s) retrieved.
> > >  select * from a where not (a in (select * from b));
> >           a a1
> >           1 one
> >             null
> > 2 row(s) retrieved.
> > > select * from a where not a in (select * from b);
> >           a a1
> >           1 one
> >             null
> > 2 row(s) retrieved.
> > > select * from a where a<>(select * from b);
> >           a a1
> > No rows found.
> > > select * from a where a=(select * from b);
> >           a a1
> > No rows found.
> > 
> > Andreas
> 
> 


Thu, 19 Feb 1998 10:21:58 -0800 (PST)
ocie@paracel.com
Re: [HACKERS] Subselects and NOTs
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:

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

Fri, 20 Feb 1998 08:26:51 +0700
"Vadim B. Mikheev" <vadim@sable.krasnoyarsk.su>
Re: [HACKERS] Subselects and NOTs
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


Thu, 19 Feb 1998 17:46:09 -0800 (PST)
ocie@paracel.com
Re: [HACKERS] Subselects and NOTs
Added to TODO.

> 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".


-- 
Bruce Momjian
maillist@candle.pha.pa.us

Fri, 20 Feb 1998 09:33:26 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Subselects and NOTs
> Added to TODO.
> > ... 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
> > In short, it would be nice if Postgres would take "null" as a type
> > specifier as well as "not null".

We currently get a shift/reduce parsing conflict on this since NULL can be
specified in other constraint clauses and since the constraint clauses are
only whitespace delimited. It might be that this part of the parser can be
redone, or perhaps the only way around is to restrict the ordering of the
constraints. But NULL constraint is not SQL92 and free ordering is...

                                                       - Tom


Fri, 20 Feb 1998 15:42:05 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] Subselects and NOTs
I think my original patch about NOT NULL constraint allows this type of
construction. However, thinks have changed in the parser since I made
this patch, and now seems that a construction like 
CREATE TABLE a (b int constraint not_null NOT NULL)
is now valid (I only saw gram.y - maybe I'm wrong). I can make the
patches to allow NULL, but I only want a litte help: is the name of this
type of constraint beeing saved anywere?

	Robson.


Bruce Momjian wrote:
> 
> Added to TODO.
> 
> > 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".
> 
> --
> Bruce Momjian
> maillist@candle.pha.pa.us

Fri, 20 Feb 1998 13:51:22 -0300
Robson Paniago de Miranda <robson@mpdft.gov.br>
Re: [HACKERS] Subselects and NOTs
> I think my original patch about NOT NULL constraint allows this type of
> construction. However, thinks have changed in the parser since I made
> this patch, and now seems that a construction like
> CREATE TABLE a (b int constraint not_null NOT NULL)
> is now valid (I only saw gram.y - maybe I'm wrong). I can make the
> patches to allow NULL, but I only want a litte help: is the name of this
> type of constraint beeing saved anywere?

Yes, I think so.


Fri, 20 Feb 1998 16:51:31 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] Subselects and NOTs
> 
> > Added to TODO.
> > > ... 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
> > > In short, it would be nice if Postgres would take "null" as a type
> > > specifier as well as "not null".
> 
> We currently get a shift/reduce parsing conflict on this since NULL can be
> specified in other constraint clauses and since the constraint clauses are
> only whitespace delimited. It might be that this part of the parser can be
> redone, or perhaps the only way around is to restrict the ordering of the
> constraints. But NULL constraint is not SQL92 and free ordering is...

OK, removed from TODO.

-- 
Bruce Momjian
maillist@candle.pha.pa.us

Fri, 20 Feb 1998 11:55:23 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Subselects and NOTs
Bruce Momjian wrote:
> 
> > 
> > > Added to TODO.
> > > > ... 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
> > > > In short, it would be nice if Postgres would take "null" as a type
> > > > specifier as well as "not null".
> > 
> > We currently get a shift/reduce parsing conflict on this since NULL can be
> > specified in other constraint clauses and since the constraint clauses are
> > only whitespace delimited. It might be that this part of the parser can be
> > redone, or perhaps the only way around is to restrict the ordering of the
> > constraints. But NULL constraint is not SQL92 and free ordering is...
> 
> OK, removed from TODO.

I'll look into this since I requested it.  I'll see if I can add this
without breaking everything else.

Ocie Mitchell

Fri, 20 Feb 1998 10:36:27 -0800 (PST)
ocie@paracel.com
Re: [HACKERS] Subselects and NOTs
ocie@paracel.com wrote:
>
> 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

According to the SQL standard: Where 'NOT' and 'IN' are written next to
each other, this is an alias for "<>ALL", and 'IN' is an alias for
"=ANY". Therefore:

"a NOT IN b" evaluates as: (a) <>ALL (b)
"NOT a IN b" evaluates as: NOT ( (a) =ANY (b) )

...which give these results:

 NOT 1 IN 2	true
 1 NOT IN 2	true

 NOT 1 IN NULL	true [NOT (1 =ANY NULL)]
 1 NOT IN NULL	false [1 <>ALL NULL]

Using "NOT IN" is a little confusing, since you might not think about
the two words as only one operator, which cannot be split in two.

/* m */

Mon, 23 Feb 1998 18:11:50 +0100
Mattias Kregert <matti@algonet.se>
Re: [HACKERS] Subselects and NOTs
Mattias Kregert wrote:
> 
> ocie@paracel.com wrote:
> >
> > 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
> 
> According to the SQL standard: Where 'NOT' and 'IN' are written next to
> each other, this is an alias for "<>ALL", and 'IN' is an alias for
> "=ANY". Therefore:
> 
> "a NOT IN b" evaluates as: (a) <>ALL (b)
> "NOT a IN b" evaluates as: NOT ( (a) =ANY (b) )
> 
> ...which give these results:
> 
>  NOT 1 IN 2     true
>  1 NOT IN 2     true
> 
>  NOT 1 IN NULL  true [NOT (1 =ANY NULL)]
>  1 NOT IN NULL  false [1 <>ALL NULL]

This is exactly how Postgres works now and differ from 3 "big boys".
If there are no objections then I'll leave this as is. We can return
to this issue latter.

Vadim

Tue, 24 Feb 1998 08:30:27 +0700
"Vadim B. Mikheev" <vadim@sable.krasnoyarsk.su>
Re: [HACKERS] Subselects and NOTs
> This is exactly how Postgres works now and differ from 3 "big boys".
> If there are no objections then I'll leave this as is. We can return
> to this issue latter.

I interprete SQL92 the same way. Let's leave as-is indefinitely/forever...

                                      - Tom


Tue, 24 Feb 1998 02:16:00 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] Subselects and NOTs
Thomas G. Lockhart wrote:
> 
> > This is exactly how Postgres works now and differ from 3 "big boys".
> > If there are no objections then I'll leave this as is. We can return
> > to this issue latter.
> 
> I interprete SQL92 the same way. Let's leave as-is indefinitely/forever...

Nice for me. There are so many more important issues :)

Vadim

Tue, 24 Feb 1998 09:48:41 +0700
"Vadim B. Mikheev" <vadim@sable.krasnoyarsk.su>
Re: [HACKERS] Subselects and NOTs