| 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: | |
| 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 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)
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]