Subselects and NOTs

Oracle 6, there is NULL into table b:

SQL> select * from a where x in (select * from b);

X
----------
2

SQL> select * from a where x not in (select * from b);

no rows selected

SQL> select * from a where not x in (select * from b);

no rows selected

Is 'not X in' the same as 'X not in' ? Currently we have:

I am not sure about this, but I believe 'not X in subselect' is
evaluated as 'not (x in subselect)' and not as 'X not in subselect'. Am
I missing something?

There is also some interesting stuff about comparisons:

( 1,2,NULL) = (3, NULL,4) false
( 1,2,NULL) < (3, NULL,4) true
( 1,2,NULL) = (1, NULL,4) unknown
( 1,2,NULL) > (NULL, 2,4) unknown

This happens because the comparisons are:

left < right is true of and only if there exists some j such
that Lj < Rj is true and for all i < j, and Li = Ri is true

so it seems it compares these things from left to right, trying to make
the comparison. For = and <>, is doesn't matter, but for the <, it does
matter.

Also they show:

select *
from test
where x <> (select y
from test2)

When test2 returns no rows, the query returns no rows because the
subquery returns a single row of NULL values.

Hope this helps. I can give more detail if you want it.

vac=> select * from a where not x in (select * from b);
x
-
1
(1 row)

: subselect clause is "atomic" and NOT-s are never pushed into it.

Once again - what standard says ?

Vadim

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

Bruce Momjian wrote:

Is 'not X in' the same as 'X not in' ? Currently we have:

I am not sure about this, but I believe 'not X in subselect' is
evaluated as 'not (x in subselect)' and not as 'X not in subselect'. Am
I missing something?

Even 'not (x in subselect)' doesn't help in Oracle! This works just
as 'x not in subselect'.

Ok, if we don't know what standard say then could you test this
in Informix, etc. Don't forget put NULL into table in subselect
to get 'no rows selected' for 'x not in subselect'...

Also they show:

select *
from test
where x <> (select y
from test2)

When test2 returns no rows, the query returns no rows because the
subquery returns a single row of NULL values.

This is exactly how it works in postgres and Oracle.

Vadim

Is 'not X in' the same as 'X not in' ? Currently we have:

I am not sure about this, but I believe 'not X in subselect' is
evaluated as 'not (x in subselect)' and not as 'X not in subselect'.

Even 'not (x in subselect)' doesn't help in Oracle! This works just
as 'x not in subselect'.

Also they show:

select *
from test
where x <> (select y
from test2)

When test2 returns no rows, the query returns no rows because the
subquery returns a single row of NULL values.

The SQL92 standard sez:

1) If NOT is specified in a <boolean test>, then let BP be the
contained <boolean primary> and let TV be the contained <truth
value>. The <boolean test> is equivalent to:

( NOT ( BP IS TV ) )

However, "a NOT IN b" is not the same as "NOT (a IN b)", and my SQL book
points out that "IN" is shorthand for "=ANY" and "NOT IN" is shorthand for
"<>ALL". Also, my Date book sez:

In general, an all-or-any condition evaluates to TRUE if and only
if
the corresponding comparison condition without the ALL (or ANY,
respectively) evaluates to TRUE for ALL (or ANY, respectively) of

the rows in the table represented by the table expression.
(NOTE: If that table is empty, the ALL conditions return TRUE,
the
ANY conditions return FALSE).

So, it looks to me that

WHERE x IN (SELECT y FROM empty_table)

evaluates to FALSE, and

WHERE x NOT IN (SELECT y FROM empty_table)

evaluates to TRUE.

I looked through my two reference books and my online draft copy of the SQL92
standard, and could not find much mention of operator precendence, and even
less on "NOT" precendence. The only mention I could find was the statement

... Where
the precedence of operators is determined by the Formats of this
International Standard or by parentheses, those operators are ef-
fectively applied in the order specified by that precedence. Where
the precedence is not determined by the Formats or by parentheses,
effective evaluation of expressions is generally performed from
left to right. However, it is implementation-dependent whether ex-
pressions are actually evaluated left to right, particularly when
operands or operators might cause conditions to be raised or if
the results of the expressions can be determined without completely
evaluating all parts of the expression.

However, it wasn't clear to me whether the above statement was referring to
operators in expressions or to operators in the BNF notation used to define
the language.

Also, in a completely different place:

Operations on numbers are performed according to the normal rules
of arithmetic, within implementation-defined limits, except as
provided for in Subclause 6.12, "<numeric value expression>".

I can't believe that the standard isn't more explicit somewhere about
operator precedence, or that it is strictly a left-to-right evaluation.

- Tom

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

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

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

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

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:

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

Vadim B. Mikheev <vadim@sable.krasnoyarsk.su> wrote:

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 ???

Should be 0 row(s) expected. Here's why: three-value logic.

a in (select...)
a == any (select...)

(a=1) == (b==1) is 'true'
(a=1) == (b==NULL) is 'unknown'
'true' OR 'unknown' is 'true'.

(a=NULL) == (b==1) is 'unknown'
(a=NULL) == (b==NULL) is 'unknown'
'unknown' OR 'unknown' is 'unknown'
not ('unknown') is 'unknown'
shouldn't be in 'where not a in...' query

Hope this helps.

--
Yurik

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