BUG #6064: != NULL, <> NULL do not work - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      6064
Logged by:          Michael Pilling
Email address:      Michael.Pilling@dsto.defence.gov.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