Bug #19564 mysql displays NULL instead of space
Submitted: 5 May 2006 14:39 Modified: 15 May 2006 19:20
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version:5.0.22-debug/5.0BK/5.1BK OS:Linux (SUSE 10.0)
Assigned to: Chad MILLER CPU Architecture:Any

[5 May 2006 14:39] Peter Gulutzan
Description:
CHAR/VARCHAR columns which contain nothing,
or nothing but spaces, are displayed as NULL.
I believe that this is a recent change.

How to repeat:
mysql> create table th (s1 char(1));
Query OK, 0 rows affected (0.10 sec)

mysql> insert into th values (' ');
Query OK, 1 row affected (0.00 sec)

mysql> select * from th;
+------+
| s1   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
[5 May 2006 15:09] MySQL Verification Team
Thank you for the bug report.

 miguel@hegel:~/dbs/5.1> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.1.10-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table th (s1 char(1));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into th values (' ');
Query OK, 1 row affected (0.01 sec)

mysql> select * from th;
+------+
| s1   |
+------+
| NULL | 
+------+
1 row in set (0.01 sec)

mysql> alter table th engine=innodb;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from th;
+------+
| s1   |
+------+
| NULL | 
+------+
1 row in set (0.01 sec)

mysql> 

miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.22-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table th (s1 char(1));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into th values (' ');
Query OK, 1 row affected (0.01 sec)

mysql> select * from th;
+------+
| s1   |
+------+
| NULL | 
+------+
1 row in set (0.00 sec)

mysql> alter table th engine=innodb;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from th;
+------+
| s1   |
+------+
| NULL | 
+------+
1 row in set (0.00 sec)

mysql> 

miguel@hegel:~/dbs/4.1> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.19-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table th (s1 char(1));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into th values (' ');
Query OK, 1 row affected (0.00 sec)

mysql> select * from th;
+------+
| s1   |
+------+
|      |
+------+
1 row in set (0.02 sec)

mysql> alter table th engine=innodb;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from th;
+------+
| s1   |
+------+
|      |
+------+
1 row in set (0.00 sec)

mysql>
[10 May 2006 2:23] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6174
[10 May 2006 20:11] Chad MILLER
Autopushed to 5.0.22 this morning.  Due in 5.1.11.
[11 May 2006 16:44] Paul DuBois
Noted in 5.0.22, 5.1.11 changelogs.

<command>mysql</command> displayed <literal>NULL</literal> for
strings that are empty or contain only spaces.
[15 May 2006 15:35] Chad MILLER
This will be available in 5.1.10 also.
[15 May 2006 18:59] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6408
[15 May 2006 19:20] Paul DuBois
Moved 5.1.11 changelog entry to 5.1.10.