Why doesn't my query find a NULL value?

Take a look at this series of queries.

> mysql> select * from people where country is null;
> Empty set (0.00 sec)
>
> mysql> select * from people where country = "";
> Empty set (0.01 sec)
>
> mysql> select * from people where country = " ";
> Empty set (0.00 sec)
>
> mysql> select country from people where userid = "murgatroyd";
> +---------+
> | country |
> +---------+
> | NULL |
> +---------+
> 1 row in set (0.00 sec)

This is driving me nuts. Why don't the first three queries find
the record for murgatroyd?

Here's the table definition, abridged to save space:

CREATE TABLE `people` (
`userid` varchar(20) NOT NULL default '',
`country` varchar(15) default NULL, PRIMARY KEY (`userid`),
<etc., etc.>
) ENGINE=InnoDB DEFAULT CHARSET=latin1

It's MySQL version 5.0.45 on Linux.

Patrick Nolan wrote:
> Take a look at this series of queries.
>
>> mysql> select * from people where country is null;
>> Empty set (0.00 sec)
>>
>> mysql> select * from people where country = "";
>> Empty set (0.01 sec)
>>
>> mysql> select * from people where country = " ";
>> Empty set (0.00 sec)
>>
>> mysql> select country from people where userid = "murgatroyd";
>> +---------+
>> | country |
>> +---------+
>> | NULL |
>> +---------+
>> 1 row in set (0.00 sec)
>
> This is driving me nuts. Why don't the first three queries find
> the record for murgatroyd?
>
> Here's the table definition, abridged to save space:
>
> CREATE TABLE `people` (
> `userid` varchar(20) NOT NULL default '',
> `country` varchar(15) default NULL, PRIMARY KEY (`userid`),
> <etc., etc.>
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
>
> It's MySQL version 5.0.45 on Linux.
>

What happens if you try:

mysql> select * from people where country = 'NULL';

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On 2008-07-07, Jerry Stuckle <jstucklex@attglobal.net> wrote:
> Patrick Nolan wrote:
>> Take a look at this series of queries.
>>
>>> mysql> select * from people where country is null;
>>> Empty set (0.00 sec)
>>>
>>> mysql> select * from people where country = "";
>>> Empty set (0.01 sec)
>>>
>>> mysql> select * from people where country = " ";
>>> Empty set (0.00 sec)
>>>
>>> mysql> select country from people where userid = "murgatroyd";
>>> +---------+
>>> | country |
>>> +---------+
>>> | NULL |
>>> +---------+
>>> 1 row in set (0.00 sec)
>>
>> This is driving me nuts. Why don't the first three queries find
>> the record for murgatroyd?
>>
>> Here's the table definition, abridged to save space:
>>
>> CREATE TABLE `people` (
>> `userid` varchar(20) NOT NULL default '',
>> `country` varchar(15) default NULL, PRIMARY KEY (`userid`),
>> <etc., etc.>
>> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
>>
>> It's MySQL version 5.0.45 on Linux.
>>
>
> What happens if you try:
>
> mysql> select * from people where country = 'NULL';
>
Well done. That was the problem. Now I have to figure out
how that happened. Thank you.

On Jul 7, 10:26 pm, Patrick Nolan <p...@glast2.Stanford.EDU> wrote:
> Take a look at this series of queries.
>
>
>
> > mysql> select * from people where country is null;
> > Empty set (0.00 sec)
>
> > mysql> select * from people where country = "";
> > Empty set (0.01 sec)
>
> > mysql> select * from people where country = " ";
> > Empty set (0.00 sec)
>
> > mysql> select country from people where userid = "murgatroyd";
> > +---------+
> > | country |
> > +---------+
> > | NULL |
> > +---------+
> > 1 row in set (0.00 sec)
>
> This is driving me nuts. Why don't the first three queries find
> the record for murgatroyd?
>
> Here's the table definition, abridged to save space:
>
> CREATE TABLE `people` (
> `userid` varchar(20) NOT NULL default '',
> `country` varchar(15) default NULL, PRIMARY KEY (`userid`),
> <etc., etc.>
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
>
> It's MySQL version 5.0.45 on Linux.

It looks like your default value is the text value "NULL"! Remove the
default value!
Best wishes,
John, LV

On Jul 7, 10:26 pm, Patrick Nolan <p...@glast2.Stanford.EDU> wrote:
> Take a look at this series of queries.
>
>
>
> > mysql> select * from people where country is null;
> > Empty set (0.00 sec)
>
> > mysql> select * from people where country = "";
> > Empty set (0.01 sec)
>
> > mysql> select * from people where country = " ";
> > Empty set (0.00 sec)
>
> > mysql> select country from people where userid = "murgatroyd";
> > +---------+
> > | country |
> > +---------+
> > | NULL |
> > +---------+
> > 1 row in set (0.00 sec)
>
> This is driving me nuts. Why don't the first three queries find
> the record for murgatroyd?
>
> Here's the table definition, abridged to save space:
>
> CREATE TABLE `people` (
> `userid` varchar(20) NOT NULL default '',
> `country` varchar(15) default NULL, PRIMARY KEY (`userid`),
> <etc., etc.>
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
>
> It's MySQL version 5.0.45 on Linux.

It looks to me that your setting of the default value to NULL is
converted into the text value "NULL" thus giving the result!
Remove the default setting for your table!
Best wishes,
John

Ps. Seems my previous post was mislaid :/

John Andersen wrote:
> On Jul 7, 10:26 pm, Patrick Nolan <p...@glast2.Stanford.EDU> wrote:
>> Take a look at this series of queries.
>>
>>
>>
>>> mysql> select * from people where country is null;
>>> Empty set (0.00 sec)
>>> mysql> select * from people where country = "";
>>> Empty set (0.01 sec)
>>> mysql> select * from people where country = " ";
>>> Empty set (0.00 sec)
>>> mysql> select country from people where userid = "murgatroyd";
>>> +---------+
>>> | country |
>>> +---------+
>>> | NULL |
>>> +---------+
>>> 1 row in set (0.00 sec)
>> This is driving me nuts. Why don't the first three queries find
>> the record for murgatroyd?
>>
>> Here's the table definition, abridged to save space:
>>
>> CREATE TABLE `people` (
>> `userid` varchar(20) NOT NULL default '',
>> `country` varchar(15) default NULL, PRIMARY KEY (`userid`),
>> <etc., etc.>
>> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
>>
>> It's MySQL version 5.0.45 on Linux.
>
> It looks like your default value is the text value "NULL"! Remove the
> default value!
> Best wishes,
> John, LV
>

No, "default 'NULL'" would be the string 'NULL'. "default NULL" is a
non-existent value.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Tue, 08 Jul 2008 06:02:04 -0400, Jerry Stuckle wrote:
> John Andersen wrote:
>> On Jul 7, 10:26 pm, Patrick Nolan <p...@glast2.Stanford.EDU> wrote:
>>> Take a look at this series of queries.
>>>
>>>
>>>
>>>> mysql> select * from people where country is null;
>>>> Empty set (0.00 sec)
>>>> mysql> select * from people where country = "";
>>>> Empty set (0.01 sec)
>>>> mysql> select * from people where country = " ";
>>>> Empty set (0.00 sec)
>>>> mysql> select country from people where userid = "murgatroyd";
>>>> +---------+
>>>> | country |
>>>> +---------+
>>>> | NULL |
>>>> +---------+
>>>> 1 row in set (0.00 sec)
>>> This is driving me nuts. Why don't the first three queries find
>>> the record for murgatroyd?
>>>
>>> Here's the table definition, abridged to save space:
>>>
>>> CREATE TABLE `people` (
>>> `userid` varchar(20) NOT NULL default '',
>>> `country` varchar(15) default NULL, PRIMARY KEY (`userid`),
>>> <etc., etc.>
>>> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
>>>
>>> It's MySQL version 5.0.45 on Linux.
>>
>> It looks like your default value is the text value "NULL"! Remove the
>> default value!
>> Best wishes,
>> John, LV
>>
>
> No, "default 'NULL'" would be the string 'NULL'. "default NULL" is a
> non-existent value.

Even if the "abridged" CREATE TABLE is accurate with regard to the
quotes, it doesn't matter in the case where the string 'NULL' came in as
part of an INSERT or UPDATE.

--
Cunningham's First Law:
Any sufficiently complex deterministic system will exhibit
non-deterministic behaviour.

On 2008-07-08, John Andersen <j.andersen.lv@gmail.com> wrote:
>
> It looks to me that your setting of the default value to NULL is
> converted into the text value "NULL" thus giving the result!
> Remove the default setting for your table!
> Best wishes,
> John
>
> Ps. Seems my previous post was mislaid :/

No. The first response was correct. I had a script which created
an INSERT with "NULL" when I really meant NULL. The default value
doesn't have quotes.

Peter H. Coffin wrote:
> On Tue, 08 Jul 2008 06:02:04 -0400, Jerry Stuckle wrote:
>> John Andersen wrote:
>>> On Jul 7, 10:26 pm, Patrick Nolan <p...@glast2.Stanford.EDU> wrote:
>>>> Take a look at this series of queries.
>>>>
>>>>
>>>>
>>>>> mysql> select * from people where country is null;
>>>>> Empty set (0.00 sec)
>>>>> mysql> select * from people where country = "";
>>>>> Empty set (0.01 sec)
>>>>> mysql> select * from people where country = " ";
>>>>> Empty set (0.00 sec)
>>>>> mysql> select country from people where userid = "murgatroyd";
>>>>> +---------+
>>>>> | country |
>>>>> +---------+
>>>>> | NULL |
>>>>> +---------+
>>>>> 1 row in set (0.00 sec)
>>>> This is driving me nuts. Why don't the first three queries find
>>>> the record for murgatroyd?
>>>>
>>>> Here's the table definition, abridged to save space:
>>>>
>>>> CREATE TABLE `people` (
>>>> `userid` varchar(20) NOT NULL default '',
>>>> `country` varchar(15) default NULL, PRIMARY KEY (`userid`),
>>>> <etc., etc.>
>>>> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
>>>>
>>>> It's MySQL version 5.0.45 on Linux.
>>> It looks like your default value is the text value "NULL"! Remove the
>>> default value!
>>> Best wishes,
>>> John, LV
>>>
>> No, "default 'NULL'" would be the string 'NULL'. "default NULL" is a
>> non-existent value.
>
> Even if the "abridged" CREATE TABLE is accurate with regard to the
> quotes, it doesn't matter in the case where the string 'NULL' came in as
> part of an INSERT or UPDATE.
>

No argument there, Peter. I was just correcting John's comment
regarding the CREATE TABLE statement.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================