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

Do not write expression =3D NULL because NULL is not "equal to" NULL. (The
null value represents an unknown value, and it is not known whether two
unknown values are equal.) This behavior conforms to the SQL standard.

http://www.postgresql.org/docs/9.1/static/functions-comparison.html


On Fri, Jun 17, 2011 at 2:39 AM, Michael Pilling
<Michael.Pilling@dsto.defence.gov.au> wrote:
>
> The following bug has been logged online:
>
> Bug reference: =C2=A0 =C2=A0 =C2=A06064
> Logged by: =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Michael Pilling
> Email address: =C2=A0 =C2=A0 =C2=A0Michael.Pilling@dsto.defence.gov.au
> PostgreSQL version: PostgreSQL 9.0
> Operating system: =C2=A0 Windows XP (server) Ubuntu 10.4 (Client)
> Description: =C2=A0 =C2=A0 =C2=A0 =C2=A0!=3D 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 !=3D NULL, <> NULL and IS NOT NULL t=
o be
> synonyms. However IS NOT NULL works and the others don't.
>
> At the very least the documentation for comparison operators should state
> that !=3D and <> will not work with NULL but this would be an obscure fix.
> Ideally the compiler would implement !=3D NULL and <> NULL like it implem=
ents
> IS NOT NULL, failing that the parser should at least flag the combinations
> with !=3D and <> as syntax or semantic errors.
>
> Reproducing the bug:
>
> Execute the following code:
>
>
> DROP TABLE example;
>
> CREATE TABLE example (
> =C2=A0 =C2=A0 =C2=A0 =C2=A0id =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0SERIAL PRIMARY KEY,
> =C2=A0 =C2=A0 =C2=A0 =C2=A0name =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
varchar(40),
> =C2=A0 =C2=A0 =C2=A0 =C2=A0content =C2=A0 =C2=A0 =C2=A0 =C2=A0 varchar(40)
> );
>
> INSERT INTO example ( name, content ) VALUES ( 'Test 1', 'Test 1' );
> INSERT INTO example ( name ) VALUES ( 'Test 2' );
>
> CREATE OR REPLACE FUNCTION =C2=A0show_problem() RETURNS SETOF example AS
>
> $$
> DECLARE
> =C2=A0result_name varchar(40);
> =C2=A0result_content varchar(40);
> BEGIN
> =C2=A0 =C2=A0SELECT example.name, example.content INTO result_name, resul=
t_content
> FROM example WHERE id=3D1;
> =C2=A0 =C2=A0IF result_content !=3D NULL THEN
> =C2=A0 =C2=A0 =C2=A0 =C2=A0RAISE NOTICE '!=3D THEN part id=3D1';
> =C2=A0 =C2=A0ELSE
> =C2=A0 =C2=A0 =C2=A0 =C2=A0RAISE NOTICE '!=3D ELSE part id=3D1';
> =C2=A0 =C2=A0END IF;
>
> =C2=A0 =C2=A0SELECT example.name, example.content INTO result_name, resul=
t_content
> FROM example WHERE id=3D2;
> =C2=A0 =C2=A0IF result_content !=3D NULL THEN
> =C2=A0 =C2=A0 =C2=A0 =C2=A0RAISE NOTICE '!=3D THEN part id=3D2';
> =C2=A0 =C2=A0ELSE
> =C2=A0 =C2=A0 =C2=A0 =C2=A0RAISE NOTICE '!=3D ELSE part id=3D2';
> =C2=A0 =C2=A0END IF;
>
> =C2=A0 =C2=A0SELECT example.name, example.content INTO result_name, resul=
t_content
> FROM example WHERE id=3D1;
> =C2=A0 =C2=A0IF result_content <> NULL THEN
> =C2=A0 =C2=A0 =C2=A0 =C2=A0RAISE NOTICE '<> THEN part id=3D1';
> =C2=A0 =C2=A0ELSE
> =C2=A0 =C2=A0 =C2=A0 =C2=A0RAISE NOTICE '<> ELSE part id=3D1';
> =C2=A0 =C2=A0END IF;
>
> =C2=A0 =C2=A0SELECT example.name, example.content INTO result_name, resul=
t_content
> FROM example WHERE id=3D2;
> =C2=A0 =C2=A0IF result_content <> NULL THEN
> =C2=A0 =C2=A0 =C2=A0 =C2=A0RAISE NOTICE '<> THEN part id=3D2';
> =C2=A0 =C2=A0ELSE
> =C2=A0 =C2=A0 =C2=A0 =C2=A0RAISE NOTICE '<> ELSE part id=3D2';
> =C2=A0 =C2=A0END IF;
> =C2=A0 =C2=A0SELECT example.name, example.content INTO result_name, resul=
t_content
> FROM example WHERE id=3D1;
> =C2=A0 =C2=A0IF result_content IS NOT NULL THEN
> =C2=A0 =C2=A0 =C2=A0 =C2=A0RAISE NOTICE 'IS NOT THEN part id=3D1';
> =C2=A0 =C2=A0ELSE
> =C2=A0 =C2=A0 =C2=A0 =C2=A0RAISE NOTICE 'IS NOT ELSE part id=3D1';
> =C2=A0 =C2=A0END IF;
>
> =C2=A0 =C2=A0SELECT example.name, example.content INTO result_name, resul=
t_content
> FROM example WHERE id=3D2;
> =C2=A0 =C2=A0IF result_content IS NOT NULL THEN
> =C2=A0 =C2=A0 =C2=A0 =C2=A0RAISE NOTICE 'IS NOT THEN part id=3D2';
> =C2=A0 =C2=A0ELSE
> =C2=A0 =C2=A0 =C2=A0 =C2=A0RAISE NOTICE 'IS NOT ELSE part id=3D2';
> =C2=A0 =C2=A0END 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 statem=
ent
> has executed according to whether the data was NULL or not. For !=3D and =
<>
> the IF statements always execute the ELSE part regardless of the data
> value.
>
> Regards,
> Michael
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>