Re: BUG #6064: != NULL, <> NULL do not work

Do not write expression = NULL because NULL is not "equal to" NULL. (The
null value represents an unknown value, and it is not known whether two
unknown values are equal.) This behavior conforms to the SQL standard.

http://www.postgresql.org/docs/9.1/static/functions-comparison.html

On Fri, Jun 17, 2011 at 2:39 AM, Michael Pilling
<Michael(dot)Pilling(at)dsto(dot)defence(dot)gov(dot)au> wrote:
>
> The following bug has been logged online:
>
> Bug reference:      6064
> Logged by:          Michael Pilling
> Email address:      Michael(dot)Pilling(at)dsto(dot)defence(dot)gov(dot)au
> PostgreSQL version: PostgreSQL 9.0
> Operating system:   Windows XP (server) Ubuntu 10.4 (Client)
> Description:        != NULL, <> NULL do not work
> Details:
>
> Server version was PostgreSQL 9.0.1, compiled by Visual C++ build 1500,
> 32-bit running on Windows XP 32 bit.
>
> It is arguable whether this bug is in the documentation, parser or
> implementation. Personally I think it is in the implementation.
>
> A reasonable programmer would expect != NULL, <> NULL and IS NOT NULL to be
> synonyms. However IS NOT NULL works and the others don't.
>
> At the very least the documentation for comparison operators should state
> that != and <> will not work with NULL but this would be an obscure fix.
> Ideally the compiler would implement != NULL and <> NULL like it implements
> IS NOT NULL, failing that the parser should at least flag the combinations
> with != and <> as syntax or semantic errors.
>
> Reproducing the bug:
>
> Execute the following code:
>
>
> DROP TABLE example;
>
> CREATE TABLE example (
>        id              SERIAL PRIMARY KEY,
>        name            varchar(40),
>        content         varchar(40)
> );
>
> INSERT INTO example ( name, content ) VALUES ( 'Test 1', 'Test 1' );
> INSERT INTO example ( name ) VALUES ( 'Test 2' );
>
> CREATE OR REPLACE FUNCTION  show_problem() RETURNS SETOF example AS
>
> $$
> DECLARE
>  result_name varchar(40);
>  result_content varchar(40);
> BEGIN
>    SELECT example.name, example.content INTO result_name, result_content
> FROM example WHERE id=1;
>    IF result_content != NULL THEN
>        RAISE NOTICE '!= THEN part id=1';
>    ELSE
>        RAISE NOTICE '!= ELSE part id=1';
>    END IF;
>
>    SELECT example.name, example.content INTO result_name, result_content
> FROM example WHERE id=2;
>    IF result_content != NULL THEN
>        RAISE NOTICE '!= THEN part id=2';
>    ELSE
>        RAISE NOTICE '!= ELSE part id=2';
>    END IF;
>
>    SELECT example.name, example.content INTO result_name, result_content
> FROM example WHERE id=1;
>    IF result_content <> NULL THEN
>        RAISE NOTICE '<> THEN part id=1';
>    ELSE
>        RAISE NOTICE '<> ELSE part id=1';
>    END IF;
>
>    SELECT example.name, example.content INTO result_name, result_content
> FROM example WHERE id=2;
>    IF result_content <> NULL THEN
>        RAISE NOTICE '<> THEN part id=2';
>    ELSE
>        RAISE NOTICE '<> ELSE part id=2';
>    END IF;
>    SELECT example.name, example.content INTO result_name, result_content
> FROM example WHERE id=1;
>    IF result_content IS NOT NULL THEN
>        RAISE NOTICE 'IS NOT THEN part id=1';
>    ELSE
>        RAISE NOTICE 'IS NOT ELSE part id=1';
>    END IF;
>
>    SELECT example.name, example.content INTO result_name, result_content
> FROM example WHERE id=2;
>    IF result_content IS NOT NULL THEN
>        RAISE NOTICE 'IS NOT THEN part id=2';
>    ELSE
>        RAISE NOTICE 'IS NOT ELSE part id=2';
>    END IF;
> RETURN QUERY Select * from example;
> RETURN;
> END;
> $$ LANGUAGE plpgsql;
>
> select * from show_problem();
>
> The last two NOTICEs are what I would regard to be correct. The if statement
> has executed according to whether the data was NULL or not. For != and <>
> the IF statements always execute the ELSE part regardless of the data
> value.
>
> Regards,
> Michael
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>