Re: 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

Do not write expression = 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(dot)Pilling(at)dsto(dot)defence(dot)gov(dot)au> wrote:
>
> 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
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

"Michael Pilling" <Michael(dot)Pilling(at)dsto(dot)defence(dot)gov(dot)au> wrote:

> A reasonable programmer would expect != NULL, <> NULL and IS NOT
> NULL to be synonyms.

Only if that programmer was not aware of the SQL standard and had
not worked much with a standard-conforming database.

NULL is conceptually intended to indicate "unknown" or "not
applicable". If you have a person table with a date_of_birth
column, which contains NULL for a number of rows for which the date
of birth is unknown, can you say that all such people have the same
date of birth? No; for any such person, the result of comparing
their date of birth to anyone else's (whether or not the other one
is NULL) is UNKNOWN.

You might want to read up on IS [NOT] DISTINCT FROM. In the SQL
language, while NULL is not known to be equal to NULL, you *can* say
that NULL IS NOT DISTINCT FROM NULL.

This is most definitely not a bug in the software. The
documentation does cover it here:

http://www.postgresql.org/docs/9.0/interactive/functions-comparison.html

Is there something you would add to that?

-Kevin

On 06/17/2011 10:20 PM, Kevin Grittner wrote:
> "Michael Pilling"<Michael(dot)Pilling(at)dsto(dot)defence(dot)gov(dot)au> wrote:
>
>> A reasonable programmer would expect != NULL,<> NULL and IS NOT
>> NULL to be synonyms.
>
> Only if that programmer was not aware of the SQL standard and had
> not worked much with a standard-conforming database.

Yep, and if they want to continue working that way, they can use the
flag intended for compatibility with Microsoft Access that makes NULL =
NULL result in 't' instead of NULL.

http://www.postgresql.org/docs/current/static/runtime-config-compatible.html#GUC-TRANSFORM-NULL-EQUALS

Note that this flag is very specifically limited to equality
comparisions using the '=' operator. It won't make NULL behave as a
value in any other way. For example, 1 > NULL will still return NULL.

--
Craig Ringer

Thanks Craig,

The real problem here then is that the documentation showing
the boolean comparison operators does not mention this quirk, which I
accept may be a standard quirk but it's still a quirk. You just
wouldn't go looking for that flag unless you had any inkling that
it might exist.

And indeed the parser does not generate warnings either.

Regards,
Michael

-----Original Message-----
From: Craig Ringer [mailto:craig(at)postnewspapers(dot)com(dot)au]
Sent: Sun 6/19/2011 6:30 PM
To: Kevin Grittner
Cc: Pilling, Michael; pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] BUG #6064: != NULL, <> NULL do not work

On 06/17/2011 10:20 PM, Kevin Grittner wrote:
> "Michael Pilling"<Michael(dot)Pilling(at)dsto(dot)defence(dot)gov(dot)au> wrote:
>
>> A reasonable programmer would expect != NULL,<> NULL and IS NOT
>> NULL to be synonyms.
>
> Only if that programmer was not aware of the SQL standard and had
> not worked much with a standard-conforming database.

Yep, and if they want to continue working that way, they can use the
flag intended for compatibility with Microsoft Access that makes NULL =
NULL result in 't' instead of NULL.

http://www.postgresql.org/docs/current/static/runtime-config-compatible.html#GUC-TRANSFORM-NULL-EQUALS

Note that this flag is very specifically limited to equality
comparisions using the '=' operator. It won't make NULL behave as a
value in any other way. For example, 1 > NULL will still return NULL.

--
Craig Ringer

IMPORTANT: This email remains the property of the Department of Defence and is subject to the jurisdiction of section 70 of the Crimes Act 1914. If you have received this email in error, you are requested to contact the sender and delete the email.

Hi Kevin,

Thanks for that. Point entirely taken. I think what I would add would be in the table 9-1 of operators,
an extra column filled in only for =, <> and != saying Important: see difference from IS [NOT] NULL.
Perhaps one reason I didn't pick up on this subtle issue is that IS NULL and IS NOT NULL are not listed in this
table but they are comparison operators, just textual rather than symbolic ones in the grammar so they should be
in the table. I recall specifically looking up what is the not equal operator in this language and only
going forward from the table, not realising I had to read any further.

I'd also add after "Do not write expression = NULL because NULL is not "equal to" NULL."
Do not write expression != NULL or <> NULL because NULL is not "not equal to" NULL.
because while implied, it's not obvious that because = doesn't work with NULL that != doesn't either.

Reading the note after this section saying before version 8.2 postgres was inconsistent with the SQL standard,
I think that really strengthens the case for the parser to issue warnings when it comes across =,<> != used with
null and the transform_null_equals (boolean) compatibility flag isn't set.

BTW while I agree with you that "Only if they hadn't read the SQL standard", how many people read the standard
of any language before they start programming in it? I may have read it 20 years ago but haven't recently.
It's not something you can rely on.

Best regards,
Michael

-----Original Message-----
From: Kevin Grittner [mailto:Kevin(dot)Grittner(at)wicourts(dot)gov]
Sent: Fri 6/17/2011 11:50 PM
To: Pilling, Michael; pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] BUG #6064: != NULL, <> NULL do not work

"Michael Pilling" <Michael(dot)Pilling(at)dsto(dot)defence(dot)gov(dot)au> wrote:

> A reasonable programmer would expect != NULL, <> NULL and IS NOT
> NULL to be synonyms.

Only if that programmer was not aware of the SQL standard and had
not worked much with a standard-conforming database.

NULL is conceptually intended to indicate "unknown" or "not
applicable". If you have a person table with a date_of_birth
column, which contains NULL for a number of rows for which the date
of birth is unknown, can you say that all such people have the same
date of birth? No; for any such person, the result of comparing
their date of birth to anyone else's (whether or not the other one
is NULL) is UNKNOWN.

You might want to read up on IS [NOT] DISTINCT FROM. In the SQL
language, while NULL is not known to be equal to NULL, you *can* say
that NULL IS NOT DISTINCT FROM NULL.

This is most definitely not a bug in the software. The
documentation does cover it here:

http://www.postgresql.org/docs/9.0/interactive/functions-comparison.html

Is there something you would add to that?

-Kevin

IMPORTANT: This email remains the property of the Department of Defence and is subject to the jurisdiction of section 70 of the Crimes Act 1914. If you have received this email in error, you are requested to contact the sender and delete the email.

On 20/06/2011 7:43 AM, Pilling, Michael wrote:
> Thanks Craig,
>
> The real problem here then is that the documentation showing
> the boolean comparison operators does not mention this quirk, which I
> accept may be a standard quirk but it's still a quirk.

What URL are you looking at?

http://www.postgresql.org/docs/current/interactive/functions-comparison.html

certainly does. Are you looking at documentation for a really ancient
version like 6.x ?

> You just
> wouldn't go looking for that flag unless you had any inkling that
> it might exist.

You shouldn't use it, either. As documented, it's an ugly hack to work
around a deficiency in MS Access, which doesn't use SQL correctly. You
may not like how NULL comparisons work, but I *strongly* recommend that
you get used to it because trying to ignore it *will* cause you problems
down the track, and there won't be any other flags to flip to change the
behaviour to how you want it.

SQL's 3-value logic isn't always popular and isn't as logically
consistent as I'd like when you get into messy things like arrays.
Unfortunately, it's not practical to just rip it out of the system
because it's so fundamentally linked into the relational calculus, how
outer joins work, how aggregates work, etc.

See: http://en.wikipedia.org/wiki/Null_(SQL)
See: http://en.wikipedia.org/wiki/Three-valued_logic

> And indeed the parser does not generate warnings either.

Why would it?

You might argue that performing an equality comparison to a literal NULL
is probably a mistake. Unfortunately, many queries are written by query
generators that will quite happily substitute null into placeholders.
This is often correct and will return the expected result so long as you
know what it means, eg:

WHERE a = NULL OR b = 1;

will return 't' if b is 1, and false (or null, which evaluates to false
for WHERE clauses) when b is not 1, irrespective of the value of 'a'.
This may well be the application author's intent, and it's certainly valid.

Maybe Pg should have a warning when " = NULL " is seen that can be
emitted at INFO log level, the same log level as the notices about
implicit index creation etc. I doubt you'll find anyone enthusiastic
about implementing it, though, and the added parser time cost hardly
seems worth it.

--
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.com/

Pilling, Michael wrote:
> Hi Kevin,
>
> Thanks for that. Point entirely taken. I think what I would add would
> be in the table 9-1 of operators, an extra column filled in only for
> =, <> and != saying Important: see difference from IS [NOT] NULL.
> Perhaps one reason I didn't pick up on this subtle issue is that IS
> NULL and IS NOT NULL are not listed in this table but they are
> comparison operators, just textual rather than symbolic ones in the
> grammar so they should be in the table. I recall specifically looking
> up what is the not equal operator in this language and only going
> forward from the table, not realising I had to read any further.
>
> I'd also add after "Do not write expression = NULL because NULL is not
> "equal to" NULL." Do not write expression != NULL or <> NULL because
> NULL is not "not equal to" NULL. because while implied, it's not
> obvious that because = doesn't work with NULL that != doesn't either.

I have written the attached patch to mention <> NULL also returns NULL.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Applied.

---------------------------------------------------------------------------

Bruce Momjian wrote:
> Pilling, Michael wrote:
> > Hi Kevin,
> >
> > Thanks for that. Point entirely taken. I think what I would add would
> > be in the table 9-1 of operators, an extra column filled in only for
> > =, <> and != saying Important: see difference from IS [NOT] NULL.
> > Perhaps one reason I didn't pick up on this subtle issue is that IS
> > NULL and IS NOT NULL are not listed in this table but they are
> > comparison operators, just textual rather than symbolic ones in the
> > grammar so they should be in the table. I recall specifically looking
> > up what is the not equal operator in this language and only going
> > forward from the table, not realising I had to read any further.
> >
> > I'd also add after "Do not write expression = NULL because NULL is not
> > "equal to" NULL." Do not write expression != NULL or <> NULL because
> > NULL is not "not equal to" NULL. because while implied, it's not
> > obvious that because = doesn't work with NULL that != doesn't either.
>
> I have written the attached patch to mention <> NULL also returns NULL.
>
> --
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + It's impossible for everything to be true. +

[ text/x-diff is unsupported, treating like TEXT/PLAIN ]

> diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
> new file mode 100644
> index be92e6a..ddfb29a
> *** a/doc/src/sgml/func.sgml
> --- b/doc/src/sgml/func.sgml
> ***************
> *** 364,371 ****
> </indexterm>
> Ordinary comparison operators yield null (signifying <quote>unknown</>),
> not true or false, when either input is null. For example,
> ! <literal>7 = NULL</> yields null. When this behavior is not suitable,
> ! use the
> <literal>IS <optional> NOT </> DISTINCT FROM</literal> constructs:
> <synopsis>
> <replaceable>expression</replaceable> IS DISTINCT FROM <replaceable>expression</replaceable>
> --- 364,371 ----
> </indexterm>
> Ordinary comparison operators yield null (signifying <quote>unknown</>),
> not true or false, when either input is null. For example,
> ! <literal>7 = NULL</> yields null, as does <literal>7 &lt;&gt; NULL</>. When
> ! this behavior is not suitable, use the
> <literal>IS <optional> NOT </> DISTINCT FROM</literal> constructs:
> <synopsis>
> <replaceable>expression</replaceable> IS DISTINCT FROM <replaceable>expression</replaceable>

>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +