Bug #73973 repeat() wastes cpu time on large input values that gaurantee empty result

• Submitted: 18 Sep 2014 20:50
• Modified: 4 Nov 2014 15:29
• Reporter: Shane Bester (Platinum Quality Contributor)
• Status: Closed
• Category: MySQL Server: Data Types
• Severity: S5 (Performance)
• Version: 5.7.6
• OS: Any
• CPU Architecture: Any
• Tags: repeat

[18 Sep 2014 20:50] Shane Bester

Description:
This hangs up random tests even though max-allowed-packet is small enough that it shouldn't

BAD:
-----
mysql> select length(repeat("",1024*1024*1024)) as a,
    -> @@global.max_allowed_packet as b;
+---+---------+
| a | b       |
+---+---------+
| 0 | 1048576 |
+---+---------+
1 row in set (3.54 sec)

OK:
----
mysql> select length(repeat("1",1024*1024)) as a;
+---------+
| a       |
+---------+
| 1048576 |
+---------+
1 row in set (0.00 sec)

OK:
-----
mysql> select length(repeat("1",1024*1024*1024)) as a;
+------+
| a    |
+------+
| NULL |
+------+
1 row in set, 1 warning (0.00 sec)

How to repeat:
Start server with --max-allowed-packet=1M and run:

select length(repeat("",1024*1024*1024)) as a,
@@global.max_allowed_packet as b;
select length(repeat("1",1024*1024)) as a;
select length(repeat("1",1024*1024*1024)) as a;

Suggested fix:
return empty resultset sooner:

=== modified file 'sql/item_strfunc.cc'
--- sql/item_strfunc.cc 2014-09-12 14:42:43 +0000
+++ sql/item_strfunc.cc 2014-09-18 20:12:13 +0000
@@ -4046,7 +4046,7 @@
     goto err;                          // string and/or delim are null
   null_value= 0;

-  if (count <= 0 && (count == 0 || !args[1]->unsigned_flag))
+  if ((0 == res->length()) || (count <= 0 && (count == 0 || !args[1]->unsigned_flag)))
     return make_empty_result();

   /* Assumes that the maximum length of a String is < INT_MAX32. */

[3 Oct 2014 12:22] Catalin Besleaga

Posted by developer:
 
Ran the tests with mark-progress and noticed that the some of the queries take less time than before.

[4 Nov 2014 15:29] Paul DuBois

Noted in 5.7.6 changelog.

REPEAT() wasted time concatenating empty strings.