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

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