f



When is null not null?

I'm puzzled by the results of some queries.  I have a table called
"people" which includes, among others, these columns:

userid      varchar(20) NOT NULL default ''
formal_name varchar(50) default NULL

The query 
 select userid, formal_name from people where formal_name is null
produces a response with 78 rows.  The query
 select userid, formal_name from people where formal_name = ""
returns 142 rows.  The two sets are distinct.  Then
 select userid, formal_name from people where (formal_name is null
 or formal_name = "")
returns 220 rows.  220 = 78+142

The first odd thing is that all the formal_name entries are NULL
for both queries.  I expect blanks for the second.  Then I 
choose random people from the second query and do
 select formal_name from people where userid = "joeuser"
and it tells me that the formal_name is indeed NULL.

It seems to be partitioning the rows with NULL formal_name into
two sets, and I can't figure out why.

Linux.  MySQL version 5.0.22.  InnoDB.
0
Patrick
11/27/2007 2:38:12 AM
comp.databases.mysql 6944 articles. 1 followers. laredotornado (869) is leader. Post Follow

4 Replies
809 Views

Similar Articles

[PageSpeed] 24

Patrick Nolan wrote:
> I'm puzzled by the results of some queries.  I have a table called
> "people" which includes, among others, these columns:
> 
> userid      varchar(20) NOT NULL default ''
> formal_name varchar(50) default NULL
> 
> The query 
>  select userid, formal_name from people where formal_name is null
> produces a response with 78 rows.  The query
>  select userid, formal_name from people where formal_name = ""
> returns 142 rows.  The two sets are distinct.  Then
>  select userid, formal_name from people where (formal_name is null
>  or formal_name = "")
> returns 220 rows.  220 = 78+142
> 
> The first odd thing is that all the formal_name entries are NULL
> for both queries.  I expect blanks for the second.  Then I 
> choose random people from the second query and do
>  select formal_name from people where userid = "joeuser"
> and it tells me that the formal_name is indeed NULL.
> 
> It seems to be partitioning the rows with NULL formal_name into
> two sets, and I can't figure out why.
> 
> Linux.  MySQL version 5.0.22.  InnoDB.
> 

null is not the same as ''.  Null is the lack of any value; '' is a 
string of zero bytes.  It is NOT blanks.

If the programming language you're using indicates '' is null, you need 
to look at the doc for the language.

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

0
Jerry
11/27/2007 2:55:15 AM
On 2007-11-27, Jerry Stuckle <jstucklex@attglobal.net> wrote:
> Patrick Nolan wrote:
>> I'm puzzled by the results of some queries.  I have a table called
>> "people" which includes, among others, these columns:
>> 
>> userid      varchar(20) NOT NULL default ''
>> formal_name varchar(50) default NULL
>> 
>> The query 
>>  select userid, formal_name from people where formal_name is null
>> produces a response with 78 rows.  The query
>>  select userid, formal_name from people where formal_name = ""
>> returns 142 rows.  The two sets are distinct.  Then
>>  select userid, formal_name from people where (formal_name is null
>>  or formal_name = "")
>> returns 220 rows.  220 = 78+142
>> 
>> The first odd thing is that all the formal_name entries are NULL
>> for both queries.  I expect blanks for the second.  Then I 
>> choose random people from the second query and do
>>  select formal_name from people where userid = "joeuser"
>> and it tells me that the formal_name is indeed NULL.
>> 
>> It seems to be partitioning the rows with NULL formal_name into
>> two sets, and I can't figure out why.
>> 
>> Linux.  MySQL version 5.0.22.  InnoDB.
>> 
>
> null is not the same as ''.  Null is the lack of any value; '' is a 
> string of zero bytes.  It is NOT blanks.
>
> If the programming language you're using indicates '' is null, you need 
> to look at the doc for the language.
>
I'm using the mysql CLI application.  It takes SQL with no translation.
0
Patrick
11/27/2007 4:39:42 PM
Patrick Nolan wrote:
> I'm puzzled by the results of some queries.  I have a table called
> "people" which includes, among others, these columns:
> 
> userid      varchar(20) NOT NULL default ''
> formal_name varchar(50) default NULL
> 
> The query 
>  select userid, formal_name from people where formal_name is null
> produces a response with 78 rows.  The query
>  select userid, formal_name from people where formal_name = ""
> returns 142 rows.  The two sets are distinct.  Then
>  select userid, formal_name from people where (formal_name is null
>  or formal_name = "")
> returns 220 rows.  220 = 78+142
> 
> The first odd thing is that all the formal_name entries are NULL
> for both queries.  I expect blanks for the second.  Then I 
> choose random people from the second query and do
>  select formal_name from people where userid = "joeuser"
> and it tells me that the formal_name is indeed NULL.
> 
> It seems to be partitioning the rows with NULL formal_name into
> two sets, and I can't figure out why.
> 
> Linux.  MySQL version 5.0.22.  InnoDB.

   It appears that you have some entries that are '' and some that are 
NULL... maybe you should make a backup, and perform an:

UPDATE people set formal_name = NULL where formal_name = '';

Norm
0
Norman
11/27/2007 6:22:38 PM
Patrick Nolan wrote:
> On 2007-11-27, Jerry Stuckle <jstucklex@attglobal.net> wrote:
>> Patrick Nolan wrote:
>>> I'm puzzled by the results of some queries.  I have a table called
>>> "people" which includes, among others, these columns:
>>>
>>> userid      varchar(20) NOT NULL default ''
>>> formal_name varchar(50) default NULL
>>>
>>> The query 
>>>  select userid, formal_name from people where formal_name is null
>>> produces a response with 78 rows.  The query
>>>  select userid, formal_name from people where formal_name = ""
>>> returns 142 rows.  The two sets are distinct.  Then
>>>  select userid, formal_name from people where (formal_name is null
>>>  or formal_name = "")
>>> returns 220 rows.  220 = 78+142
>>>
>>> The first odd thing is that all the formal_name entries are NULL
>>> for both queries.  I expect blanks for the second.  Then I 
>>> choose random people from the second query and do
>>>  select formal_name from people where userid = "joeuser"
>>> and it tells me that the formal_name is indeed NULL.
>>>
>>> It seems to be partitioning the rows with NULL formal_name into
>>> two sets, and I can't figure out why.
>>>
>>> Linux.  MySQL version 5.0.22.  InnoDB.
>>>
>> null is not the same as ''.  Null is the lack of any value; '' is a 
>> string of zero bytes.  It is NOT blanks.
>>
>> If the programming language you're using indicates '' is null, you need 
>> to look at the doc for the language.
>>
> I'm using the mysql CLI application.  It takes SQL with no translation.
> 

OK, then for the '' values the CLI returns an empty string.  For actual 
null values, the CLI returns null, i.e.

mysql> insert into test(col1) values('');
mysql> insert into test(col1) values(null);
mysql> select * from test;

+----+------+
| id | col1 |
+----+------+
|  1 |      |
|  2 | NULL |
+----+------+

The first row

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

0
Jerry
11/27/2007 8:35:28 PM
Reply: