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

> Sean Chittenden <sean(at)chittenden(dot)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