Bug #19306 null not reflected in select statement
Submitted: 24 Apr 2006 19:33 Modified: 24 Apr 2006 23:03
Reporter: Roy Martin Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.19 OS:SunOS fin 5.9 Generic_118558-21
Assigned to: CPU Architecture:Any

[24 Apr 2006 19:33] Roy Martin
Description:
UNIX Command Line Client (see above)

Table created with char  and int fields (nulls allowed)
Row inserted without any reference for char or int field
Subsequent SELECT statement does NOT reflect a NULL value for char field ONLY

MySQL Query Browser (GUI interface) shows content to be NULL

Numeric fields behave differently: for them, the SELECT statement shows NULL for null values.

How to repeat:
CREATE TABLE `trial` (
  `name1` char(20) default NULL,
  `name2` char(100) default NULL,
  `number1` tinyint(4) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

insert into trial set name2='other stuff';

mysql> select * from trial;
+-------+-------------+---------+
| name1 | name2         | number1 |
+-------+-------------+---------+
|          | other stuff    | NULL     |
+-------+-------------+---------+
1 row in set (0.00 sec)

Suggested fix:
SELECT should render consistent results for char and int fields when encountering NULL.

Expected display from example (above):

mysql> select * from trial;
+-------+-------------+---------+
| name1 | name2         | number1 |
+-------+-------------+---------+
|  NULL  | other stuff    | NULL     |
+-------+-------------+---------+
1 row in set (0.00 sec)
[24 Apr 2006 23:03] Miguel Solorzano
Thank you for the bug report. Duplicate of bugs:
http://bugs.mysql.com/bug.php?id=19228
http://bugs.mysql.com/bug.php?id=18265