Bug #173 REPLACE with GROUP BY truncating result

• Submitted: 21 Mar 2003 1:21
• Modified: 26 Mar 2003 8:07
• Reporter: Indrek Siitan
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: any
• OS: Any (any)
• Assigned to: Igor Babaev
• CPU Architecture: Any

[21 Mar 2003 1:21] Indrek Siitan

Description:
The result of the replace is getting truncated erroneously and appears to be dependent on the length of the strings in the REPLACE function.

How to repeat:
mysql> SELECT value1, REPLACE(value1, 'aaaaaaaaaaaaaaaaaaaa','bbbbbbbbbbbbbbbbbbbbb') FROM table1 GROUP BY value1;
+-------------------------------------------------------+------------------------------------------------------------------+
| value1                                                | REPLACE(value1,'aaaaaaaaaaaaaaaaaaaa', 'bbbbbbbbbbbbbbbbbbbbb')  |
+-------------------------------------------------------+------------------------------------------------------------------+
| 012345678911234567892123456789312345678941234567895   | 0123456                                                          |
| abcdefghijklmnopqrstuvqxyz abcdefghijklmnopqrstuvwxyz | abcdefg                                                          |
+-------------------------------------------------------+------------------------------------------------------------------+
2 rows in set (0.00 sec)

The query shows the orignal value next to the replaced value.  Notice that
the replace shouldn't even match anything, but the result is still
truncated.

The truncation changes (not proportionally) depending on the length of any
of the REPLACE parameters.  For example, increasing the third parameter by 1
character causes the truncation to occur 7 characters later:
 mysql> SELECT value1, REPLACE(value1, 'aaaaaaaaaaaaaaaaaaaa','bbbbbbbbbbbbbbbbbbbbbb') FROM table1 GROUP BY value1;
+-------------------------------------------------------+-------------------------------------------------------------------+
| value1                                                | REPLACE(value1,'aaaaaaaaaaaaaaaaaaaa', 'bbbbbbbbbbbbbbbbbbbbbb')  |
+-------------------------------------------------------+-------------------------------------------------------------------+
| 012345678911234567892123456789312345678941234567895   | 01234567891123                                                    |
| abcdefghijklmnopqrstuvqxyz abcdefghijklmnopqrstuvwxyz | abcdefghijklmn                                                    |
+-------------------------------------------------------+-------------------------------------------------------------------+
2 rows in set (0.00 sec)

Getting rid of GROUP BY fixes the bug (in this simplified example, the
values are unique - group by isn't even doing anything here except forcing
the bug):
mysql> SELECT value1, REPLACE(value1, 'aaaaaaaaaaaaaaaaaaaa','bbbbbbbbbbbbbbbbbbbbb') FROM table1;
+-------------------------------------------------------+------------------------------------------------------------------+
| value1                                                | REPLACE(value1,'aaaaaaaaaaaaaaaaaaaa', 'bbbbbbbbbbbbbbbbbbbbb')  |
+-------------------------------------------------------+------------------------------------------------------------------+
| 012345678911234567892123456789312345678941234567895   | 012345678911234567892123456789312345678941234567895              |
| abcdefghijklmnopqrstuvqxyz abcdefghijklmnopqrstuvwxyz | abcdefghijklmnopqrstuvqxyz abcdefghijklmnopqrstuvwxyz            |
+-------------------------------------------------------+------------------------------------------------------------------+
2 rows in set (0.00 sec)

Shortening the parameters to replace also hides the problem:
mysql> SELECT value1, REPLACE(value1, 'aaaa', 'bbbb') FROM table1 GROUP BY value1;
+-------------------------------------------------------+-------------------------------------------------------+
| value1                                                | REPLACE(value1,'aaaa', 'bbbb')                        |
+-------------------------------------------------------+-------------------------------------------------------+
| 012345678911234567892123456789312345678941234567895   | 012345678911234567892123456789312345678941234567895   |
| abcdefghijklmnopqrstuvqxyz abcdefghijklmnopqrstuvwxyz | abcdefghijklmnopqrstuvqxyz abcdefghijklmnopqrstuvwxyz |
+-------------------------------------------------------+-------------------------------------------------------+
2 rows in set (0.00 sec)

I am running 3.23.41 on RH 7.2 (2.4.7-10) - I haven't had the time to
install 4.x to see if the problem's been fixed, but I couldn't find anything
in any release notes regarding this bug.  Could someone let me know if this
has been fixed or if it can be reproduced in 4.x?

Test case follows:
------------------------------------
CREATE DATABASE database1;
USE database1;
CREATE TABLE table1 (value1 CHAR(128));
INSERT INTO table1 VALUES('012345678911234567892123456789312345678941234567895');
INSERT INTO table1 VALUES('abcdefghijklmnopqrstuvqxyzabcdefghijklmnopqrstuvwxyz');
SELECT value1, REPLACE(value1, 'aaaaaaaaaaaaaaaaaaaa','bbbbbbbbbbbbbbbbbbbbb') FROM table1 /* works */;
SELECT value1, REPLACE(value1, 'aaaaaaaaaaaaaaaaaaaa','bbbbbbbbbbbbbbbbbbbbb') FROM table1 GROUP BY value1 /* group by breaks things */;
SELECT value1, REPLACE(value1, 'aaaaaaaaaaaaaaaaaaaa','bbbbbbbbbbbbbbbbbbbbbb') FROM table1 GROUP BY value1 /* changing fields changes bug */;
SELECT value1, REPLACE(value1, 'aaaa', 'bbbb') FROM table1 GROUP BY value1 /* sometimes works depending on length */;
---------------------------------------

[21 Mar 2003 4:28] Alexander Keremidarski

I did some more tests. 
Result is truncated, but weird part is that most of string functions on this result will give correct output.

LENGTH() returns correct not truncated length
RIGHT() returns "truncated" part of result
etc.

[26 Mar 2003 8:07] MySQL Verification Team

Commited and pushed