Re: "IS NOT NULL" != "NOT NULL"

> Sean Chittenden <sean@chittenden.org> writes:
> > 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