Thread: 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
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
> 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
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
> 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.
> > > 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
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
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 */
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
> 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
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