Bug #5132 Carriage return stored in a varchar field acts like a blank entry
Submitted: 20 Aug 2004 20:34 Modified: 20 Aug 2004 22:59
Reporter: Brad Johnson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:3.23.58 OS:Linux (Fedora Core 2)
Assigned to: CPU Architecture:Any

[20 Aug 2004 20:34] Brad Johnson
Description:
A carriage return (0D in hex) stored in a varchar field is retrieved properly as a carrage return when queried from the database, but when performing the following queries, it's treated as a blank non-null entry.

SELECT Foo from Bar where Foo="";

Above query returns any records that are blank or have carriage returns stored in Foo.

SELECT hex(Foo) from Bar where Foo="";
Above query returns '0' for any records that are blank or have carriage returns stored in Foo.

How to repeat:
Import data from a text file where carriage returns are stored in a varchar field.

Perform above queries.
[20 Aug 2004 20:58] Matthew Lord
I verifed this on a RH 9 box (2.4.21 #12 SMP).  I get the correct results in 4.0.20 and 4.1.3.

create table nltest ( string varchar(10) );
insert into nltest values ('blah'), ('\n'), ('');
select hex(string) from nltest where string='';
[20 Aug 2004 22:59] Brian Aker
Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/