Bug #69891 since 5.1.31 REPLACE() function concats it's results under certain conditions

• Submitted: 1 Aug 2013 6:33
• Reporter: Shane Bester (Platinum Quality Contributor)
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.1.31
• OS: Any
• CPU Architecture: Any
• Tags: regression, REPLACE

[1 Aug 2013 6:33] Shane Bester

Description:
When used with a function and variable, the replace function concats it's rows, leading to results that eventually reach max_allow_packet and return null.
The testcase outputs this on 5.1.31+

mysql> select replace(@@session.sort_buffer_size,'1',now()) from `t1`;
+--------------------------------------------------------------------------------------------------------------------------------------+
| replace(@@session.sort_buffer_size,'1',now())                                                                                        |
+--------------------------------------------------------------------------------------------------------------------------------------+
| 2622013-08-01 08:30:5044                                                                                                             |
| 262202013-08-01 08:30:503-08-02013-08-01 08:30:50 08:30:5044                                                                         |
| 26220202013-08-01 08:30:503-08-02013-08-01 08:30:50 08:30:503-08-0202013-08-01 08:30:503-08-02013-08-01 08:30:50 08:30:50 08:30:5044 |
+--------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

On 5.1.30 and less, we get expected:
mysql> select replace(@@session.sort_buffer_size,'1',now()) from `t1`;
+-----------------------------------------------+
| replace(@@session.sort_buffer_size,'1',now()) |
+-----------------------------------------------+
| 2622013-08-01 08:32:2544                      |
| 2622013-08-01 08:32:2544                      |
| 2622013-08-01 08:32:2544                      |
+-----------------------------------------------+
3 rows in set (0.00 sec)

How to repeat:
drop table if exists `t1`;
create table `t1`(`a` int)engine=innodb;
insert `t1` values (1),(2),(3);
set @@session.sort_buffer_size=1024*256;
select replace(@@session.sort_buffer_size,'1',now()) from `t1`;

[17 May 2014 8:51] MySQL Verification Team

still affects 5.7.5....

[18 Sep 2014 21:25] MySQL Verification Team

This haunts my highspeed random tests.  consider this testcase:

-------
drop table if exists t1;
create table t1(c int)engine=innodb;
insert into t1 values (1),(1),(1),(1),(1);
select @@global.max_allowed_packet as a, version() as v;
select 1 from t1 where replace(now(),0,cast(c is null as datetime));
-------

mysql> select @@global.max_allowed_packet as a, version() as v;
+---------+-----------+
| a       | v         |
+---------+-----------+
| 1048576 | 5.7.6-m16 |
+---------+-----------+
1 row in set (0.00 sec)

mysql> select 1 from t1 where replace(now(),0,cast(c is null as datetime));
+---+
| 1 |
+---+
| 1 |
| 1 |
| 1 |
| 1 |
+---+
4 rows in set, 1 warning (3.17 sec)

mysql>
mysql> show warnings;
+---------+------+------------------------------------------------------------------------------+
| Level   | Code | Message                                                                      |
+---------+------+------------------------------------------------------------------------------+
| Warning | 1301 | Result of replace() was larger than max_allowed_packet (1048576) - truncated |
+---------+------+------------------------------------------------------------------------------+
1 row in set (0.00 sec)