"IS NOT NULL" != "NOT NULL"

"IS NOT NULL" != "NOT NULL"

Post by Tom La » Sun, 20 Jan 2002 04:18:39




> db=> SELECT COUNT(*) FROM pkg_hosts WHERE timestamp_col NOT NULL;
>  count
> -------
>      0
> (1 row)
> db=> SELECT COUNT(*) FROM pkg_hosts WHERE timestamp_col IS NOT NULL;
>  count
> -------
>    1242
> (1 row)
> Why aren't those the same?  Seems like the IS would be an extra word
> that's not necessarily needed.  ::shrug::  -sc

This is more than a tad hard to believe, considering that the parser
converts both of these constructs into the same internal representation:

        | a_expr NOTNULL
                {    $$ = makeA_Expr(NOTNULL, NULL, $1, NULL); }
        | a_expr IS NOT NULL_P
                {    $$ = makeA_Expr(NOTNULL, NULL, $1, NULL); }

Can you provide a reproducible example where the results are different?

                        regards, tom lane

---------------------------(end of broadcast)---------------------------

 
 
 

"IS NOT NULL" != "NOT NULL"

Post by Gregory Woo » Sun, 20 Jan 2002 04:59:17


Quote:> > db=> SELECT COUNT(*) FROM pkg_hosts WHERE timestamp_col NOT NULL;
>         | a_expr NOTNULL

Are NOT NULL and NOTNULL really the same?

Greg

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command


 
 
 

"IS NOT NULL" != "NOT NULL"

Post by Daniel Kalch » Sun, 20 Jan 2002 19:52:01


Quote:>>>Tom Lane said:


 > > db=> SELECT COUNT(*) FROM pkg_hosts WHERE timestamp_col NOT NULL;
 > >  count
 > > -------
 > >      0
 > > (1 row)
 >
 >
 > > db=> SELECT COUNT(*) FROM pkg_hosts WHERE timestamp_col IS NOT NULL;
 > >  count
 > > -------
 > >    1242
 > > (1 row)
 >
 > > Why aren't those the same?  Seems like the IS would be an extra word
 > > that's not necessarily needed.  ::shrug::  -sc
 >
 > This is more than a tad hard to believe, considering that the parser
 > converts both of these constructs into the same internal representation:
 >
 >         | a_expr NOTNULL
 >                 {    $$ = makeA_Expr(NOTNULL, NULL, $1, NULL); }
 >         | a_expr IS NOT NULL_P
 >                 {    $$ = makeA_Expr(NOTNULL, NULL, $1, NULL); }
 >
 > Can you provide a reproducible example where the results are different?

On 7.1.3 the results are:

customer=# select count(*) from croute where archived_at not null;
ERROR:  parser: parse error at or near "null"
customer=# select count(*) from croute where archived_at is not null;
 count
-------
   437
(1 row)

archived_at is timestamp

What Postgres version is this?

By the way,

customer=# select count(*) from croute where archived_at  != NULL;
 count
-------
     0
(1 row)

which may or may not be wrong :-) NULL is thereoretically not possible to be
compared to anything, but...

Daniel

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly

 
 
 

"IS NOT NULL" != "NOT NULL"

Post by Sean Chittend » Mon, 21 Jan 2002 06:21:25



> > db=> SELECT COUNT(*) FROM pkg_hosts WHERE timestamp_col NOT NULL;
> >  count
> > -------
> >      0
> > (1 row)

> > db=> SELECT COUNT(*) FROM pkg_hosts WHERE timestamp_col IS NOT NULL;
> >  count
> > -------
> >    1242
> > (1 row)

> > Why aren't those the same?  Seems like the IS would be an extra word
> > that's not necessarily needed.  ::shrug::  -sc

> This is more than a tad hard to believe, considering that the parser
> converts both of these constructs into the same internal representation:

>         | a_expr NOTNULL
>                 {    $$ = makeA_Expr(NOTNULL, NULL, $1, NULL); }
>         | a_expr IS NOT NULL_P
>                 {    $$ = makeA_Expr(NOTNULL, NULL, $1, NULL); }

> Can you provide a reproducible example where the results are different?

Sure thing.

test_pgsql=# SELECT version();
                           version
--------------------------------------------------------------
 PostgreSQL 7.1.3 on i386--freebsd4.4, compiled by GCC 2.95.3
(1 row)

test_pgsql=# CREATE TABLE test (
test_pgsql(# col1 SERIAL,
test_pgsql(# col2 INT,
test_pgsql(# PRIMARY KEY(col1));
CREATE
test_pgsql=# INSERT INTO test (col2) values (NULL);
test_pgsql=# INSERT INTO test (col2) values (NULL);
test_pgsql=# INSERT INTO test (col2) values (NULL);
test_pgsql=# INSERT INTO test (col2) values (NULL);
test_pgsql=# INSERT INTO test (col2) values (NULL);
test_pgsql=# INSERT INTO test (col2) values (NULL);
test_pgsql=# INSERT INTO test (col2) values (1);
test_pgsql=# INSERT INTO test (col2) values (2);
test_pgsql=# INSERT INTO test (col2) values (3);
test_pgsql=# SELECT COUNT(*) FROM test WHERE col2 IS NOT NULL;
 count
-------
     3
(1 row)

test_pgsql=# SELECT COUNT(*) FROM test WHERE col2 NOTNULL;
 count
-------
     3
(1 row)

test_pgsql=# SELECT COUNT(*) FROM test WHERE col2 != NULL;
 count
-------
     0
(1 row)

Any ideas?  -sc

--
Sean Chittenden

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command

 
 
 

"IS NOT NULL" != "NOT NULL"

Post by Tom La » Mon, 21 Jan 2002 06:51:18



> test_pgsql=# SELECT COUNT(*) FROM test WHERE col2 NOTNULL;
>  count
> -------
>      3
> (1 row)
> test_pgsql=# SELECT COUNT(*) FROM test WHERE col2 != NULL;
>  count
> -------
>      0
> (1 row)

These are not the same thing.  See any of the past discussions about
why "null = null" and "null != null" and so forth do not do what a
novice might expect.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------

 
 
 

"IS NOT NULL" != "NOT NULL"

Post by Sean Chittend » Mon, 21 Jan 2002 07:41:25


Quote:> > test_pgsql=# SELECT COUNT(*) FROM test WHERE col2 NOTNULL;
> >  count
> > -------
> >      3
> > (1 row)

> > test_pgsql=# SELECT COUNT(*) FROM test WHERE col2 != NULL;
> >  count
> > -------
> >      0
> > (1 row)

> These are not the same thing.  See any of the past discussions about
> why "null = null" and "null != null" and so forth do not do what a
> novice might expect.

http://www.postgresql.org/idocs/index.php?functions-comparison.html

The archives appear to be broken, but the above link seems to explain
things well enough.  I still can't grok the rationale as to why NULL
is interpreted as unknown and not interpreted as empty.  I understand
that you can't compare two values that are unknown until the unknowns
are known.  However why null isn't interpreted as empty is something I
haven't grasped.  '' is different than empty ('' is a defined string
that's 0 characters in length), which is different than null
(unknown).  In my mind: "col2 != NULL" is the same as "col2 IS NOT
NULL", but I fully understand why "col2 = NULL" is an invalid
statement.  Not a biggie, just a source of curiosity.  -sc

--
Sean Chittenden

---------------------------(end of broadcast)---------------------------

 
 
 

"IS NOT NULL" != "NOT NULL"

Post by Vince Vielhab » Mon, 21 Jan 2002 08:12:53



> > > test_pgsql=# SELECT COUNT(*) FROM test WHERE col2 NOTNULL;
> > >  count
> > > -------
> > >      3
> > > (1 row)

> > > test_pgsql=# SELECT COUNT(*) FROM test WHERE col2 != NULL;
> > >  count
> > > -------
> > >      0
> > > (1 row)

> > These are not the same thing.  See any of the past discussions about
> > why "null = null" and "null != null" and so forth do not do what a
> > novice might expect.

> http://www.postgresql.org/idocs/index.php?functions-comparison.html

> The archives appear to be broken, but the above link seems to explain
> things well enough.  I still can't grok the rationale as to why NULL
> is interpreted as unknown and not interpreted as empty.  I understand
> that you can't compare two values that are unknown until the unknowns
> are known.  However why null isn't interpreted as empty is something I
> haven't grasped.  '' is different than empty ('' is a defined string
> that's 0 characters in length), which is different than null
> (unknown).  In my mind: "col2 != NULL" is the same as "col2 IS NOT
> NULL", but I fully understand why "col2 = NULL" is an invalid
> statement.  Not a biggie, just a source of curiosity.  -sc

I had a hell of a time with that at first too.  What you need to
understand is that NULL isn't necessarily empty as you would expect.
It's not the same as a null string - a null string actually has a
real definition, a zero length string.  I probably didn't help much.

Vince.
--
==========================================================================

         56K Nationwide Dialup from $16.00/mo at Pop4 Networking
        Online Campground Directory    http://www.camping-usa.com
       Online Giftshop Superstore    http://www.cloudninegifts.com
==========================================================================

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command

 
 
 

"IS NOT NULL" != "NOT NULL"

Post by Tom La » Mon, 21 Jan 2002 08:12:59



> I had a hell of a time with that at first too.  What you need to
> understand is that NULL isn't necessarily empty as you would expect.
> It's not the same as a null string - a null string actually has a
> real definition, a zero length string.  I probably didn't help much.

Right.  The common phrase "null string" doesn't help to reduce the
confusion any; perhaps "empty string" for zero-length string would
be a better phrase to use when you are working with SQL.  NULL is
absolutely not the same as an empty string.  NULL is outside the
domain of normal data for every datatype; it is better thought of
as the absence of a value than as any particular value.

I've been told that Oracle fails to distinguish empty strings from
NULL, which if true is a clear violation of the SQL specification.
If you're used to Oracle then that might help explain your confusion :-(

Another problem is that SQL's boolean operations act as though NULL
is the logical value UNKNOWN, rather than explicitly setting up a
boolean datatype with the three allowed values TRUE, FALSE, UNKNOWN.
While the rules for propagation of NULL happen to be similar to the
results that logic dictates you get for UNKNOWN, this is still a kind
of type pun, and it doesn't help to reduce the confusion any.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

 
 
 

"IS NOT NULL" != "NOT NULL"

Post by Tom La » Mon, 21 Jan 2002 08:13:07



> In my mind: "col2 != NULL" is the same as "col2 IS NOT
> NULL", but I fully understand why "col2 = NULL" is an invalid
> statement.

To me, "col2 != NULL" means "NOT (col2 = NULL)".  Does that help it
make more sense to you?

The reason SQL has the special IS NULL and IS NOT NULL constructs
is exactly that you can't do anything useful with "foo = NULL" or
"foo != NULL".

If you want to get into language-lawyering: I believe that in pure
SQL92 you can't even validly write an unadorned NULL as a constant
in an expression; you're supposed to cast it to some type, viz
"CAST(NULL AS something)".  Postgres is lax about this since we have
ambiguous-type resolution machinery in the parser anyway.  Perhaps
the reason why certain other DBMSes thought they could get away with
interpreting "foo = NULL" as "foo IS NULL" is that "foo = NULL" is
illegal according to the strict text of the standard, and thus arguably
doesn't have a standardized meaning; even though anyone who's grasped
the SQL rules for NULL would expect it to yield NULL.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command

 
 
 

"IS NOT NULL" != "NOT NULL"

Post by Bruce Momji » Mon, 21 Jan 2002 09:41:47


Quote:> I've been told that Oracle fails to distinguish empty strings from
> NULL, which if true is a clear violation of the SQL specification.
> If you're used to Oracle then that might help explain your confusion :-(

> Another problem is that SQL's boolean operations act as though NULL
> is the logical value UNKNOWN, rather than explicitly setting up a
> boolean datatype with the three allowed values TRUE, FALSE, UNKNOWN.
> While the rules for propagation of NULL happen to be similar to the
> results that logic dictates you get for UNKNOWN, this is still a kind
> of type pun, and it doesn't help to reduce the confusion any.

My book does deal with this NULL distinction:

        http://www.postgresql.org/docs/awbook.html

--
  Bruce Momjian                        |  http://candle.pha.pa.us

  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

 
 
 

"IS NOT NULL" != "NOT NULL"

Post by Bruce Momji » Mon, 21 Jan 2002 09:41:49


Quote:> Another problem is that SQL's boolean operations act as though NULL
> is the logical value UNKNOWN, rather than explicitly setting up a
> boolean datatype with the three allowed values TRUE, FALSE, UNKNOWN.
> While the rules for propagation of NULL happen to be similar to the
> results that logic dictates you get for UNKNOWN, this is still a kind
> of type pun, and it doesn't help to reduce the confusion any.

Sorry for not mentioning section, it is Customizing Queries/Using NULL
values.  Content on the web:

        http://www.postgresql.org/docs/awbook.html

--
  Bruce Momjian                        |  http://candle.pha.pa.us

  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command

 
 
 

1. Setting not Null field to ""(empty string) does not work with CRecordset

Hello,

I am trying set not Null field in MS SQL Server table to ""(empty string)
using CRecordset.  
It comes back saying that the field can not be Null.  
I have tried to use SetFieldNull( NULL, FALSE) after AddNew() so it would
set all fields to not NULL, it does not seem to help.
I am using VC4.2b and set CRecordset derived class property to "" before I
do Update().

Any help is appreciated,
Michael

2. Locking problem

3. "NOT NULL WITH DEFAULT" date field

4. Printing to an output file

5. Changing column to "not null"

6. Help Query: Count the number of repetition of an expression in a field

7. Data driven "Not Null" Constraints

8. Setup.INI

9. ALTER field from "NOT NULL" to "NULL"

10. max of ("...","...","..")

11. "= null" & "is null"

12. Difference between "REQUIRED" and "NOT NULL"?