BUG #6064: != NULL, <> NULL do not work - Mailing list pgsql-bugs
From | Michael Pilling |
---|---|
Subject | BUG #6064: != NULL, <> NULL do not work |
Date | |
Msg-id | 201106170739.p5H7dLOe043882@wwwmaster.postgresql.org Whole thread Raw |
Responses |
Re: BUG #6064: != NULL, <> NULL do not work
Re: BUG #6064: != NULL, <> NULL do not work |
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
pgsql-bugs by date: