Bug - REPLACE with GROUP BY truncating result

Hello,
There appears to be a bug when using the REPLACE string function in
conjunction with GROUP BY. The result of the replace is getting truncated
erroneously and appears to be dependent on the length of the strings in the
REPLACE function.

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?

How-To-Repeat:
My test case is as follows:
------------------------------------
CREATE DATABASE database1;
USE database1;
CREATE TABLE table1 (value1 CHAR(128));
INSERT INTO table1
VALUES('012345678911234567892123456789312345678941234567895' );
INSERT INTO table1 VALUES('abcdefghijklmnopqrstuvqxyz
abcdefghijklmnopqrstuvwxyz');
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 */;
---------------------------------------

Sincere thanks in advance,
Zach Levow
zlevow@hotmail.com

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread14004@lists.mysql.com
To unsubscribe, e-mail

Re: Bug - REPLACE with GROUP BY truncating result

Hi,

The problem is still here with 4.0 and 4.1 tree.

Regards,
Jocelyn

----- Original Message -----
From: "Zach Levow"
To:
Sent: Thursday, March 20, 2003 9:30 PM
Subject: Bug - REPLACE with GROUP BY truncating result


> Hello,
> There appears to be a bug when using the REPLACE string function in
> conjunction with GROUP BY. The result of the replace is getting truncated
> erroneously and appears to be dependent on the length of the strings in
the
> REPLACE function.
>
> 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?
>
> How-To-Repeat:
> My test case is as follows:
> ------------------------------------
> CREATE DATABASE database1;
> USE database1;
> CREATE TABLE table1 (value1 CHAR(128));
> INSERT INTO table1
> VALUES('012345678911234567892123456789312345678941234567895' );
> INSERT INTO table1 VALUES('abcdefghijklmnopqrstuvqxyz
> abcdefghijklmnopqrstuvwxyz');
> 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 */;
> ---------------------------------------
>
> Sincere thanks in advance,
> Zach Levow
> zlevow@hotmail.com
>
> ------------------------------------------------------------ ---------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail bugs-thread14004@lists.mysql.com
> To unsubscribe, e-mail
>
>
>
>
>
>


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread14005@lists.mysql.com
To unsubscribe, e-mail

Re: Bug - REPLACE with GROUP BY truncating result

Hi,

> There appears to be a bug when using the REPLACE string function in
> conjunction with GROUP BY. The result of the replace is getting truncate=
d
> erroneously and appears to be dependent on the length of the strings in t=
he
> REPLACE function.

Thanks for your excellent bug report.

It has been filed as bug #173, you can follow its progress at:
http://bugs.mysql.com/bug.php?id=3D173


Rgds,
Indrek

--=20
MySQL Users Conference and Expo: http://www.mysql.com/events/uc2003/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Indrek Siitan
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, The Support Guy
/_/ /_/\_, /___/\___\_\___/ Uuem=F5isa, Haapsalu, Estonia
<___/ www.mysql.com



------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread14007@lists.mysql.com
To unsubscribe, e-mail