Bug #20067 empty string returns NULL as dispalyed value
Submitted: 25 May 2006 15:46 Modified: 1 Sep 2006 7:37
Reporter: john danilson Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version:1.1.20 OS:Linux (Red Hat Enterprise Linux ES rele)
Assigned to: CPU Architecture:Any

[25 May 2006 15:46] john danilson
Description:
The display value NULL should represent either the character string 'NULL' or the true null value.  It should not be substituted for other values.

While an empty string is not null, as defined both in the documentation and emprically via select isnull("") returning 0 (zero), it is shown in a select of a column as the value NULL.  

It appears the problem is only what the client displays and not the values stored in the columns.  A mysqldump of the table correctly outputs and empty string. 

How to repeat:
use test;
drop table if exists foo;

create table foo (x int not null auto_increment primary key, a varchar(10) default null, b varchar(10) null default 'abcxyz');

insert foo (a,b) values (null,null);
insert foo (a,b) values ('NULL','NULL');
insert foo (a,b) values ("","");
insert foo (a) values ('b is missing');
insert foo (b) values ('a is missing');

select * from foo;

the output from the select is:
+---+------------+------------+
| x | a          | b          |
+---+------------+------------+
| 1 | NULL       | NULL       |
| 2 | NULL       | NULL       |
| 3 | NULL       | NULL       |
| 4 | b is missi | abcxyz     |
| 5 | NULL       | a is missi |
+---+------------+------------+
5 rows in set (0.00 sec)

notice that row 3, in both cases, an empty string is shown as NULL, however it is neither a true null, nor the text string NULL; it is in fact the empty string "".

Suggested fix:
An empty string should be displayed as a blank cell and not the word NULL. 
NULL as a display value should be limited to two cases:
a) the actual text characters NULL.
b) a true null as recording missing value in a column which allows nulls.
[25 May 2006 16:07] Jorge del Conde
Thanks for your bug report.  I was able to reporduce this bug under FC5/QB 1.1.20
[30 May 2006 21:20] Christian Hammers
According to bug #20154 this one, too, is a duplicate of #19564 and fixed in 5.0.22.

bye,

-christian-
[6 Jun 2006 22:01] Peter Brodersen
Fixed in 5.0.23, that is :-) I experience the problem as well under 5.0.22.
[1 Sep 2006 7:37] Alfredo Kojima
This is filed under the wrong category, since it's a duplicate of #19564
I'm marking it as such and recategorizing.