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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Abel Abraham Camarillo Ojeda | 2011-06-17 07:43:41 | Re: BUG #6064: != NULL, <> NULL do not work |
Previous Message | Praveen | 2011-06-17 05:34:46 | BUG #6063: compatability issues |