Bug #21618 NULL shown as empty string in client
Submitted: 14 Aug 2006 11:31 Modified: 16 Sep 2006 17:47
Reporter: Olaf van der Spek (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version:5.0.24\5.0\5.1BK OS:Windows (Windows\Linux)
Assigned to: Chad MILLER CPU Architecture:Any

[14 Aug 2006 11:31] Olaf van der Spek
Description:
The result is shown as an empty string while it should be NULL (although the documentation doesn't specify that either).
phpMyAdmin shows the result right.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 53 to server version: 5.0.24-community-nt

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

mysql> select unhex('zz');
+-------------+
| unhex('zz') |
+-------------+
|             |
+-------------+
1 row in set (0.00 sec)

mysql>

How to repeat:
select unhex('zz');
[14 Aug 2006 12:04] Olaf van der Spek
Fixed category, as the bug report system itself is still broken.
[14 Aug 2006 12:57] MySQL Verification Team
Thank you for the bug report. Verified as described:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.1.12-beta-debug

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

mysql> select unhex('zz');
+-------------+
| unhex('zz') |
+-------------+
|             | 
+-------------+
1 row in set (0.00 sec)
--------------------------------------------------------------
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.25-debug

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

mysql> select unhex('zz');
+-------------+
| unhex('zz') |
+-------------+
|             | 
+-------------+
1 row in set (0.01 sec)

mysql> 
--------------------------------------------------------------
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.22-debug

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

mysql> select unhex('zz');
+-------------+
| unhex('zz') |
+-------------+
| NULL        |
+-------------+
1 row in set (0.01 sec)

mysql>
[13 Sep 2006 15:37] 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/11859

ChangeSet@1.2234, 2006-09-13 11:41:41-04:00, cmiller@zippy.cornsilk.net +3 -0
  Bug #21618: NULL shown as empty string in client
  
  The column's NOT NULL flag doesn't affect what we should print.  Remove the
  wrong logic that does check it.
  
  Also, verify that this and the previous two tests print the same data as 
  other output formats.
[14 Sep 2006 18:47] Ryan Payne
I am not sure if this issue is related to the inverse issue that we are seeing on 5.0.22. We have a field that is an empty string in the database. When displaying the record using the command line client, it reports the field as NULL. Using the MySQL connector and DbVisualizer, the field is correctly showing as an empty string. We verified that this is an issue in 5.0.22 on Linux, Windows and OS X. We had the 4.0.24 client and ran the same test and it returned the empty string. 

Please advise if this fix takes care of this issue or if this is a whole new issue. Thanks!

Ryan C. Payne
[15 Sep 2006 22:22] Chad MILLER
Available in 5.1.12-beta.
[15 Sep 2006 23:35] Paul DuBois
Noted in 5.1.12 changelog.

mysql displayed an empty string for NULL values.

The bug appears to affect 5.0 as well. Will the
fix appear in 5.0?
[16 Sep 2006 14:37] Chad MILLER
Available in 5.0.26 .
[16 Sep 2006 17:47] Paul DuBois
Noted in 5.0.26 changelog.