Thread
-
RE: [HACKERS] Subselects and NOTs
Michael Meskes <meskes@topsystem.de> — 1998-02-19T08:58:07Z
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
-
Re: [HACKERS] Subselects and NOTs
ocie@paracel.com — 1998-02-19T18:21:58Z
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 > >
-
Re: [HACKERS] Subselects and NOTs
Vadim Mikheev <vadim@sable.krasnoyarsk.su> — 1998-02-20T01:26:51Z
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
-
Re: [HACKERS] Subselects and NOTs
ocie@paracel.com — 1998-02-20T01:46:09Z
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 -
Re: [HACKERS] Subselects and NOTs
Bruce Momjian <maillist@candle.pha.pa.us> — 1998-02-20T14:33:26Z
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
-
Re: [HACKERS] Subselects and NOTs
Thomas Lockhart <lockhart@alumni.caltech.edu> — 1998-02-20T15:42:05Z
> 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 -
Re: [HACKERS] Subselects and NOTs
Robson Paniago de Miranda <robson@mpdft.gov.br> — 1998-02-20T16:51:22Z
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
-
Re: [HACKERS] Subselects and NOTs
Thomas Lockhart <lockhart@alumni.caltech.edu> — 1998-02-20T16:51:31Z
> 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.
-
Re: [HACKERS] Subselects and NOTs
Bruce Momjian <maillist@candle.pha.pa.us> — 1998-02-20T16:55:23Z
> > > 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
-
Re: [HACKERS] Subselects and NOTs
ocie@paracel.com — 1998-02-20T18:36:27Z
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
-
Re: [HACKERS] Subselects and NOTs
Mattias Kregert <matti@algonet.se> — 1998-02-23T17:11:50Z
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 */
-
Re: [HACKERS] Subselects and NOTs
Vadim Mikheev <vadim@sable.krasnoyarsk.su> — 1998-02-24T01:30:27Z
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
-
Re: [HACKERS] Subselects and NOTs
Thomas Lockhart <lockhart@alumni.caltech.edu> — 1998-02-24T02:16:00Z
> 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 -
Re: [HACKERS] Subselects and NOTs
Vadim Mikheev <vadim@sable.krasnoyarsk.su> — 1998-02-24T02:48:41Z
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