BUG #6064: != NULL, <> NULL do not work
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
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.Pilling@dsto.defence.gov.au> wrote:
Show quoted text
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--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
"Michael Pilling" <Michael.Pilling@dsto.defence.gov.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.Pilling@dsto.defence.gov.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.
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@postnewspapers.com.au]
Sent: Sun 6/19/2011 6:30 PM
To: Kevin Grittner
Cc: Pilling, Michael; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #6064: != NULL, <> NULL do not work
On 06/17/2011 10:20 PM, Kevin Grittner wrote:
"Michael Pilling"<Michael.Pilling@dsto.defence.gov.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.
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.
"Pilling, Michael" wrote:
The real problem here then is that the documentation showing
the boolean comparison operators does not mention this
This page does, at length:
http://www.postgresql.org/docs/9.0/interactive/functions-comparison.html
What page are you looking at?
And indeed the parser does not generate warnings either.
It would be totally wrong for the parser to generate warnings about
correct behavior.
-Kevin
P.S. A copy/paste of part of the above-mentioned page:
To check whether a value is or is not null, use the constructs:
expression IS NULL
expression IS NOT NULL
or the equivalent, but nonstandard, constructs:
expression ISNULL
expression NOTNULL
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.
Tip: Some applications might expect that expression = NULL
returns true if expression evaluates to the null value. It is highly
recommended that these applications be modified to comply with the
SQL standard. However, if that cannot be done the
transform_null_equals configuration variable is available. If it is
enabled, PostgreSQL will convert x = NULL clauses to x IS NULL.
Note: If the expression is row-valued, then IS NULL is true when
the row expression itself is null or when all the row's fields are
null, while IS NOT NULL is true when the row expression itself is
non-null and all the row's fields are non-null. Because of this
behavior, IS NULL and IS NOT NULL do not always return inverse
results for row-valued expressions, i.e., a row-valued expression
that contains both NULL and non-null values will return false for
both tests. This definition conforms to the SQL standard, and is a
change from the inconsistent behavior exhibited by PostgreSQL
versions prior to 8.2.
Ordinary comparison operators yield null (signifying "unknown"), not
true or false, when either input is null. For example, 7 = NULL
yields null. When this behavior is not suitable, use the IS [ NOT ]
DISTINCT FROM constructs:
expression IS DISTINCT FROM expression
expression IS NOT DISTINCT FROM expression
For non-null inputs, IS DISTINCT FROM is the same as the <> operator.
However, if both inputs are null it returns false, and if only one
input is null it returns true. Similarly, IS NOT DISTINCT FROM is
identical to = for non-null inputs, but it returns true when both
inputs are null, and false when only one input is null. Thus, these
constructs effectively act as though null were a normal data value,
rather than "unknown".
Boolean values can also be tested using the constructs
expression IS TRUE
expression IS NOT TRUE
expression IS FALSE
expression IS NOT FALSE
expression IS UNKNOWN
expression IS NOT UNKNOWN
These will always return true or false, never a null value, even when
the operand is null. A null input is treated as the logical value
"unknown". Notice that IS UNKNOWN and IS NOT UNKNOWN are effectively
the same as IS NULL and IS NOT NULL, respectively, except that the
input expression must be of Boolean type.
Import Notes
Resolved by subject fallback
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.Grittner@wicourts.gov]
Sent: Fri 6/17/2011 11:50 PM
To: Pilling, Michael; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #6064: != NULL, <> NULL do not work
"Michael Pilling" <Michael.Pilling@dsto.defence.gov.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.
The real problem here then is that the documentation showing
the boolean comparison operators does not mention this
This page does, at length:
But not in the table, in fact it doesn't even mention the IS NULL, IS NOT NULL operators at all.
http://www.postgresql.org/docs/9.0/interactive/functions-comparison.html
What page are you looking at?
I was looking at that page but didn't read the detail because I thought the information was in the
table and the detail was just textual examples. In so much of the documentation, the rest of the page
just writes out in long hand what can be inferred from the syntax. People do not read online like a book,
they scan. If the information is not upfront it just won't be seen.
And indeed the parser does not generate warnings either.
It would be totally wrong for the parser to generate warnings about
correct behavior.
The behaviour of the generated code may well be correct and indeed I agree that it is but from
everything you and the detailed documentation have said column != NULL is at least deprecated
and is highly likely to indicate a programming error. It is totally normal for a parser to warn
against archaic or dangerous constructs. The idea being to avoid subtle runtime bugs that are
hard to track down. This happens in Ada, Java and even C and many other languages.
Regards,
Michael
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/
On 20/06/2011 8:30 AM, Pilling, Michael wrote:
I was looking at that page but didn't read the detail because I thought
the information was in the table and the detail was just textual examples.
I do think that adding "IS DISTINCT FROM" and "IS NULL / IS NOT NULL" to
the summary table would be helpful, with a superscript * linking to the
note about null handling below.
In general, though, there's only so much information that can be
condensed into the brief summary and tables.
Reading the documentation in detail is a really, really good idea. It'll
tell you a lot about Pg and about SQL in general. I've generally found
it really helpful and not at all long-winded. It's pretty much how I
learned to use relational databases.
In this case the documentation even has a big box-out saying:
"Tip: Some applications might expect that expression = NULL returns true
if expression evaluates to the null value."
... that carries on to explain about NULLs.
--
Craig Ringer
Tech-related writing at http://soapyfrogs.blogspot.com/
On Mon, 2011-06-20 at 10:00 +0930, Pilling, Michael wrote:
The behaviour of the generated code may well be correct and indeed I
agree that it is but from
everything you and the detailed documentation have said column != NULL
is at least deprecated
and is highly likely to indicate a programming error.
The right side of the expression may be an expression as well; e.g.:
a != b (or a <> b)
The DBMS would not know that one side is NULL until runtime.
It is totally normal for a parser to warn
against archaic or dangerous constructs.
It's only an obvious mistake in the trivial case you show where one side
is a constant NULL (therefore making the entire expression a constant
NULL). The more general form "a != b" is quite common, even if somewhat
dangerous in the presence of NULL.
The idea being to avoid subtle runtime bugs that are
hard to track down. This happens in Ada, Java and even C and many
other languages.
NULL is one place in SQL that hides possible mistakes that could
otherwise be caught by the compiler, leaving your queries in danger of
subtle runtime bugs. There's not a good way to conform to the SQL spec
and catch the kind of subtle NULL problems to which you're referring.
It may be possible to make a static analysis "safety check" tool to warn
users about dangerous constructs like that, but it would be a fairly
major effort (and would probably just end up telling you to put COALESCE
everywhere). Trying to only catch the kind of trivial mistakes involving
constants and known operators is counterproductive, in my opinion.
The bottom line is that NULLs are a little on the dangerous side. If you
think your example is bad, consider the semantics of NOT IN with respect
to NULL -- that's a trap even for experts. If you want to be safe, make
liberal use of COALESCE and WHERE x IS NOT NULL on any expression that
you think might ever evaluate to NULL.
Also note that NULLs can be created by outer joins and aggregates even
if your source data has no NULLs at all.
PostgreSQL is a SQL DBMS, and in SQL, NULL affects everything. I'm sure
there are places in the documentation that could be improved, but
warnings on every page would be counterproductive.
Regards,
Jeff Davis
On 20/06/11 14:19, Jeff Davis wrote:
On Mon, 2011-06-20 at 10:00 +0930, Pilling, Michael wrote:
The behaviour of the generated code may well be correct and indeed I
agree that it is but from
everything you and the detailed documentation have said column != NULL
is at least deprecated
and is highly likely to indicate a programming error.The right side of the expression may be an expression as well; e.g.:
a != b (or a <> b)The DBMS would not know that one side is NULL until runtime.
If he's talking specifically about the case of a NULL literal, a
parser-level warning could be emitted because the parser *does* know it
was a literal NULL.
I'm not convinced it's a good idea to warn about this case myself, but
for a NULL literal it's at least vaguely practical.
It may be possible to make a static analysis "safety check" tool to warn
users about dangerous constructs like that, but it would be a fairly
major effort (and would probably just end up telling you to put COALESCE
everywhere).
To be even remotely useful, it'd have to be able to prove that certain
variables cannot be NULL in certain places. For example in this trivial
made-up case:
SELECT a FROM tablename
WHERE a IS NOT NULL AND b IS NOT NULL
GROUP BY a, b HAVING a > b;
... looks like a dangerous test ("a > b" without excluding/handling
NULL) but in fact the WHERE clause already excluded potentially
problematic tuples so it can never match a NULL result. Warning on that
test would be incorrect, and adding a NULL check / CASE / COALESCE to it
would make the query marginally slower and significantly less readable.
In some places it is not possible to handle NULL inputs explicitly
without multiply evaluating an expensive function or subquery. That not
only has performance implications but may be a real issue if the
expression uses volatile functions or functions with side effects.
Even if that wasn't complicated enough, to be useful in the real world
the tool would probably have to be able to work on SQL embedded in
source code, including C and Java, probably including code that
assembles SQL from fragments.
All in all, it strikes me as a vast amount of work that's only
questionably even possible for little to no gain.
The bottom line is that NULLs are a little on the dangerous side. If you
think your example is bad, consider the semantics of NOT IN with respect
to NULL -- that's a trap even for experts. If you want to be safe, make
liberal use of COALESCE and WHERE x IS NOT NULL on any expression that
you think might ever evaluate to NULL.
Agreed. I don't think anyone is a total fan of NULL and SQL's
three-valued logic, or would argue that it's properly consistent and ...
logical. Unfortunately, we're kind of stuck with it because of the SQL
standards process.
Other solutions to the no-value problem are pretty darn ugly in
different ways, anyway. In particular, throwing an error when any
operation is performed on an undef/unknown value is one possibility that
gets old REALLY fast.
--
Craig Ringer
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@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
Attachments:
/rtmp/nulltext/x-diffDownload+4-4
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@momjian.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 <> 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +