Bug #63037 Greater-than operator returns unexpected result with text-field
Submitted: 31 Oct 2011 13:59 Modified: 31 Oct 2011 19:23
Reporter: Jason Kane Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.5.17 OS:Linux (Ubuntu 11.04)
Assigned to: CPU Architecture:Any
Tags: empty string, greater than, ifnull, newline, text
Triage: Needs Triage: D2 (Serious)

[31 Oct 2011 13:59] Jason Kane
Description:
The greater-than operator does not function correctly when used with a text field.

I expected:
A populated string to be "greater than" an empty string

I received:
False where the populated string begins with a newline

In the example provided, I expected rows 2 and 3 to be returned, however only row 2 is returned.

How to repeat:
CREATE TABLE `testbug` (`myid` bigint(20) NOT NULL DEFAULT '0', `mytext` text, PRIMARY KEY (`myid`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO testbug VALUES ('1', null);

INSERT INTO testbug VALUES ('2', 'this row should be fine');

INSERT INTO testbug VALUES ('3', '\r\nThis row will not be fine, which is a bug.');

select * from testbug where ifnull(mytext,'') > '';

/* Rows 2 and 3 should be returned, however only row 2 is returned */

Suggested fix:
[anyone employing similar code should consider length(X) > 0 instead]
[31 Oct 2011 14:05] Shane Bester
on mysql-trunk i get:
mysql> select '\n'>'';
+---------+
| '\n'>'' |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)

mysql> select 'a'>'';
+--------+
| 'a'>'' |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)
[31 Oct 2011 19:23] Valeriy Kravchuk
Verified just as described:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.17-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select '\n'>'';
+---------+
| '\n'>'' |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)

mysql> select '\r'>'';
+---------+
| '\r'>'' |
+---------+
|       0 |
+---------+
1 row in set (0.01 sec)

mysql> select '\t'>'';
+---------+
| '\t'>'' |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)

mysql> select length('\t'), length('');
+--------------+------------+
| length('\t') | length('') |
+--------------+------------+
|            1 |          0 |
+--------------+------------+
1 row in set (0.00 sec)