Thread: Can't use NULL in IN conditional?
Charles Tassell (ctassell@isn.net) reports a bug with a severity of 3
The lower the number the more severe it is.
Short Description
Can't use NULL in IN conditional?
Long Description
I don't know if this is a "feature" that comes alogn with NULL values, or somethign I'm doing wrong, but I can't seem
tosearch for the NULL value via a WHERE xx IN clause. I've re-written the query to use OR and it works fine then, but
notwith the IN clause. This is with Postgres 7.02 on a Linux system (PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled
bygcc egcs-2.91.66)
Sample Code
CREATE TABLE product_types (
type_id serial,
code text
)
INSERT INTO product_types (code) VALUES ('0A');
INSERT INTO product_types (code) VALUES (NULL);
-- 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);
No file was uploaded with this report
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
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/ ------------
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
> -----Original Message-----
> From: pgsql-bugs@postgresql.org [mailto:pgsql-bugs@postgresql.org]
> Sent: 11 December 2000 07:49
> To: pgsql-bugs@postgresql.org
> Subject: [BUGS] Can't use NULL in IN conditional?
>
>
> Charles Tassell (ctassell@isn.net) reports a bug with a severity of 3
> The lower the number the more severe it is.
>
> Short Description
> Can't use NULL in IN conditional?
>
> Long Description
> I don't know if this is a "feature" that comes alogn with
> NULL values, or somethign I'm doing wrong, but I can't seem
> to search for the NULL value via a WHERE xx IN clause. I've
> re-written the query to use OR and it works fine then, but
> not with the IN clause. This is with Postgres 7.02 on a
> Linux system (PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled
> by gcc egcs-2.91.66)
>
>
> Sample Code
> CREATE TABLE product_types (
> type_id serial,
> code text
> )
> INSERT INTO product_types (code) VALUES ('0A');
> INSERT INTO product_types (code) VALUES (NULL);
> -- 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);
>
>
>
> No file was uploaded with this report
>
"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/ ------------