MySQL Bugs: #63037: Greater-than operator returns unexpected result with text-field

Bug #63037	Greater-than operator returns unexpected result with text-field
Submitted:	31 Oct 2011 13:59	Modified:	6 Nov 2019 20:33
Reporter:	Jason Kane	Email Updates:
Status:	Not a Bug	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

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]

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)

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)

Posted by developer:
 
This is not a bug.
The default collation for utf8 has PAD SPACE property, ie the shorter string is implicitly extended with space characters until it is the length of the longer string. It is then up to the collation whether a newline is greater than or less than a space character.

Roy, with all due respect, to call this "not a bug" indicates you suffer from a terminal case of nerditis. This may be operating as expected, but to call "\nHello world" !> "" correct behaviour is just ludicrous. It is completely out of step with normal expectations.