Re: "IS NOT NULL" != "NOT NULL"
От | Sean Chittenden |
---|---|
Тема | Re: "IS NOT NULL" != "NOT NULL" |
Дата | |
Msg-id | 20020119131913.C1298@ninja1.internal обсуждение исходный текст |
Ответ на | Re: "IS NOT NULL" != "NOT NULL" (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: "IS NOT NULL" != "NOT NULL"
|
Список | pgsql-general |
> 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
В списке pgsql-general по дате отправления: