Skip site navigation (1) Skip section navigation (2)

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

From: "Michael Pilling" <Michael(dot)Pilling(at)dsto(dot)defence(dot)gov(dot)au>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #6064: != NULL, <> NULL do not work
Date: 2011-06-17 07:39:21
Message-ID: 201106170739.p5H7dLOe043882@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
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

Responses

pgsql-bugs by date

Next:From: Abel Abraham Camarillo OjedaDate: 2011-06-17 07:43:41
Subject: Re: BUG #6064: != NULL, <> NULL do not work
Previous:From: PraveenDate: 2011-06-17 05:34:46
Subject: BUG #6063: compatability issues

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group