Bug #73057 TRIM function not working against trailing space

• Submitted: 20 Jun 2014 3:49
• Modified: 20 Jun 2014 7:43
• Reporter: ricky leung
• Status: Can't repeat
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.6.11
• OS: Linux
• CPU Architecture: Any
• Tags: trim function

[20 Jun 2014 3:49] ricky leung

Description:
TRIM function not trimming trailing spaces as expected.  This contradicts what was written in the document.

Strangely, it always leaves a single trailing space.

How to repeat:
mysql> select ' johnson ';
+-----------+
| johnson   |
+-----------+
|  johnson  |
+-----------+
1 row in set (0.00 sec)

mysql> select concat(trim(' johnson '),'-');
+---------------------------------+
| concat(trim(' johnson '),'-')  |
+---------------------------------+
| johnson -                       |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select concat(trim(' johnson      '),'-');
+-------------------------------------+
| concat(trim(' johnson      '),'-')  |
+-------------------------------------+
| johnson -                           |
+-------------------------------------+
1 row in set (0.00 sec)

mysql>

Suggested fix:
The TRIM function should work as expected

[20 Jun 2014 3:54] ricky leung

Severity should at least be S2.  Users following product document would find their script not working.  Workaround is painful involving some kind of string comparison and char manipulation with other functions.

[20 Jun 2014 6:13] MySQL Verification Team

Thank you for the report.
I could not repeat with latest GA builds.
Could you please check on latest version and confirm the same?

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

mysql> select concat(trim(' johnson '),'-');
+-------------------------------+
| concat(trim(' johnson '),'-') |
+-------------------------------+
| johnson-                      |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select concat(trim(' johnson      '),'-');
+------------------------------------+
| concat(trim(' johnson      '),'-') |
+------------------------------------+
| johnson-                           |
+------------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+-------------------------------------------+
| version()                                 |
+-------------------------------------------+
| 5.6.19-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql>

//

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

mysql> select concat(trim(' johnson '),'-');
+-------------------------------+
| concat(trim(' johnson '),'-') |
+-------------------------------+
| johnson-                      |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select concat(trim(' johnson      '),'-');
+------------------------------------+
| concat(trim(' johnson      '),'-') |
+------------------------------------+
| johnson-                           |
+------------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.5.38-debug-log |
+------------------+
1 row in set (0.00 sec)

[20 Jun 2014 7:06] ricky leung

The problem does not appear on the latest GA version of 5.6.19.

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

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

mysql> select concat(trim('  johnson  '),'-');
+---------------------------------+
| concat(trim('  johnson  '),'-') |
+---------------------------------+
| johnson-                        |
+---------------------------------+
1 row in set (0.01 sec)

So this is magically resolved between 5.6.11 and 5.6.19.  However I have search through the release notes of those versions but couldn't find anything related to this bug.  The closest I suspect is the fix on v5.6.16 (bug 14057034, 69005, 16691598)

[20 Jun 2014 7:43] MySQL Verification Team

Thank you for confirming.

Thanks,
Umesh