Horton

Thread

  1. Can't use NULL in IN conditional?

    PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2000-12-11T07:49:19Z

    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

  2. Re: Can't use NULL in IN conditional?

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-12-11T15:51:56Z

    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

  3. Re: Can't use NULL in IN conditional?

    Robert B. Easter <reaster@comptechnews.com> — 2000-12-11T17:01:40Z

    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/ ------------

  4. Re: Can't use NULL in IN conditional?

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-12-11T17:34:07Z

    "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

  5. Re: Can't use NULL in IN conditional?

    Robert B. Easter <reaster@comptechnews.com> — 2000-12-12T05:01:28Z

    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/ ------------