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.

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

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.

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

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