Can't use NULL in IN conditional?

pgsql-bugs@postgresql.org writes:

-- This works
SELECT type_id, code FROM product_types WHERE code = '0A' OR code = NULL;
-- This doesn't
SELECT type_id, code FROM product_types WHERE code IN ('OA', NULL);

"code = NULL" is not legal SQL --- or at least, the standard's
interpretation of it is not what you appear to expect. According to the
spec the result must always be NULL, which is effectively FALSE in this
context.

Since certain Microsoft products misinterpret "var = NULL" as "var IS NULL",
we've inserted a hack into our parser to convert a comparison against a
literal NULL to an IS NULL clause. However, that only works for the
specific cases of "var = NULL" and "var <> NULL", not for any other
contexts where a null might be compared against something else.

Personally I regard this hack as a bad idea, and would prefer to take it
out. I'd certainly resist extending it to the IN operator...

regards, tom lane

Since it's probably a "special case" I'd feel it good software engineering
practice to make that fact explicit anyway.

For example:

SELECT type_id, code FROM product_types WHERE code IN ('0A', 'F3', '99')
OR code IS NULL;

I don't know how that would affect the speed but it might be the clearest
expression of intent.

Piers Scannell
Systems Engineer, GlobeCast France Telecom
Tel: +44 1707 667 228 Fax: +44 1707 667 206

On Monday 11 December 2000 10:51, Tom Lane wrote:

pgsql-bugs@postgresql.org writes:

-- This works
SELECT type_id, code FROM product_types WHERE code = '0A' OR code = NULL;
-- This doesn't
SELECT type_id, code FROM product_types WHERE code IN ('OA', NULL);

"code = NULL" is not legal SQL --- or at least, the standard's
interpretation of it is not what you appear to expect. According to the
spec the result must always be NULL, which is effectively FALSE in this
context.

Since certain Microsoft products misinterpret "var = NULL" as "var IS
NULL", we've inserted a hack into our parser to convert a comparison
against a literal NULL to an IS NULL clause. However, that only works for
the specific cases of "var = NULL" and "var <> NULL", not for any other
contexts where a null might be compared against something else.

Personally I regard this hack as a bad idea, and would prefer to take it
out. I'd certainly resist extending it to the IN operator...

regards, tom lane

What you are saying agrees with things I've read elsewhere, and a little
definition/note that I wrote on my "Databasing" Terms page:

three-valued logic:
a logic system that employs TRUE, FALSE, and UNKNOWN. NULL values introduce
UNKNOWN into boolean operations. A truth table must be used to lookup the
proper value (TRUE or FALSE) of UNKNOWN under specific operations. In SQL
implementations that use three-valued logic, you must consult the
documentation for its truth table. Some newer implementations of SQL
eliminate UNKNOWN, and may generally behave as follows: all boolean tests
involving NULL return FALSE except the explicit test IS NULL, e.g., if NULL
is a possibility, it has to be tested for explicity using IS NULL or IS NOT
NULL. (any additions/corrections to this definition/note will be happily
considered)

I think Bruce Momjian's book says this too:
http://www.postgresql.org/docs/aw_pgsql_book/node45.html
(that book is really useful!)

--
-------- Robert B. Easter reaster@comptechnews.com ---------
- CompTechNews Message Board http://www.comptechnews.com/ -
- CompTechServ Tech Services http://www.comptechserv.com/ -
---------- http://www.comptechnews.com/~reaster/ ------------

"Robert B. Easter" <reaster@comptechnews.com> writes:

What you are saying agrees with things I've read elsewhere, and a little
definition/note that I wrote on my "Databasing" Terms page:

three-valued logic:
a logic system that employs TRUE, FALSE, and UNKNOWN. NULL values introduce
UNKNOWN into boolean operations. A truth table must be used to lookup the
proper value (TRUE or FALSE) of UNKNOWN under specific operations. In SQL
implementations that use three-valued logic, you must consult the
documentation for its truth table.

Three-valued logic is perfectly straightforward if you keep in mind the
interpretation of NULL/UNKNOWN: "I don't know if this is true or false".
Thus:

NOT unknown => unknown

false AND unknown => false (it can't possibly be true)
true AND unknown => unknown
unknown AND unknown => unknown

false OR unknown => unknown
true OR unknown => true (it's true no matter what the unknown is)
unknown OR unknown => unknown

For ordinary operators such as "=", the result is generally NULL if any
input is NULL, although there are some specific cases where you can
deduce a correct result knowing only some of the inputs. In particular,
NULL = NULL does not yield TRUE, it yields UNKNOWN. This is correct
when you consider that NULL is not a specific value, but a placeholder
for an unknown value. (Reference: SQL99 section 8.2 general rule 1a.)

IS NULL and IS NOT NULL are not ordinary operators in this sense, since
they can deliver a non-null result for NULL input.

Also, SQL specifies that a WHERE clause that evaluates to "unknown" is
taken as false, ie, the row is not selected.

Bottom line is that in a spec-conformant implementation,
WHERE code = '0A' OR code = NULL
will act the same as if you'd just written "WHERE code = '0A'"; the
second clause always yields unknown and so can never cause the WHERE to
be taken as true.

Some newer implementations of SQL
eliminate UNKNOWN, and may generally behave as follows: all boolean tests
involving NULL return FALSE except the explicit test IS NULL, e.g., if NULL
is a possibility, it has to be tested for explicity using IS NULL or IS NOT
NULL.

They may *appear* to return FALSE if you aren't looking too closely,
since WHERE treats top-level results of FALSE and UNKNOWN the same.
If they really don't make the distinction then they are broken.
AFAICT, neither SQL92 nor SQL99 regard NULL support as optional.

regards, tom lane

On Monday 11 December 2000 12:34, Tom Lane wrote:

Three-valued logic is perfectly straightforward if you keep in mind the
interpretation of NULL/UNKNOWN: "I don't know if this is true or false".
Thus:

NOT unknown => unknown

false AND unknown => false (it can't possibly be true)
true AND unknown => unknown
unknown AND unknown => unknown

false OR unknown => unknown
true OR unknown => true (it's true no matter what the unknown is)
unknown OR unknown => unknown

For ordinary operators such as "=", the result is generally NULL if any
input is NULL, although there are some specific cases where you can
deduce a correct result knowing only some of the inputs. In particular,
NULL = NULL does not yield TRUE, it yields UNKNOWN. This is correct
when you consider that NULL is not a specific value, but a placeholder
for an unknown value. (Reference: SQL99 section 8.2 general rule 1a.)

IS NULL and IS NOT NULL are not ordinary operators in this sense, since
they can deliver a non-null result for NULL input.

Also, SQL specifies that a WHERE clause that evaluates to "unknown" is
taken as false, ie, the row is not selected.

Bottom line is that in a spec-conformant implementation,
WHERE code = '0A' OR code = NULL
will act the same as if you'd just written "WHERE code = '0A'"; the
second clause always yields unknown and so can never cause the WHERE to
be taken as true.

Some newer implementations of SQL
eliminate UNKNOWN, and may generally behave as follows: all boolean tests
involving NULL return FALSE except the explicit test IS NULL, e.g., if
NULL is a possibility, it has to be tested for explicity using IS NULL or
IS NOT NULL.

They may *appear* to return FALSE if you aren't looking too closely,
since WHERE treats top-level results of FALSE and UNKNOWN the same.
If they really don't make the distinction then they are broken.
AFAICT, neither SQL92 nor SQL99 regard NULL support as optional.

regards, tom lane

Thanks for the clarification and SQL reference. I spent some time on this
today and updated a file of mine at

http://www.comptechnews.com/~reaster/dbdesign.html#three-valued-logic

to take into consideration these things. If you do take a look at it and
find an error, I will fix it. This dbdesign.html file is a file linked to
from http://postgresql.readysetnet.com/docs/faq-english.html so I'm hoping to
keep it correct and useful. Thanks :)

--
-------- Robert B. Easter reaster@comptechnews.com ---------
- CompTechNews Message Board http://www.comptechnews.com/ -
- CompTechServ Tech Services http://www.comptechserv.com/ -
---------- http://www.comptechnews.com/~reaster/ ------------