Bug #102150 Performance of repeat() function need to be optimized
Submitted: 5 Jan 2021 11:53 Modified: 6 Jan 2021 7:53
Reporter: dave do Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S5 (Performance)
Version:MySQL 8.0.22 OS:Any
Assigned to: CPU Architecture:Any

[5 Jan 2021 11:53] dave do 
Description:
A query include the repeat() function will show quite different performance with different value of max_allowed_packet.

If the max_allowed_packet is 64M, which is the default value, the query will finish in seconds and with many warnings of 'Warning | 1301 | Result of repeat() was larger than max_allowed_packet (67108864) - truncated'.

If the max_allowed_packet set to 1024M(set global max_allowed_packet=1073741824), the query will take hours to get a same result, but without warnings.

So it is strange the performance of repeat() function, or let's say Item_func_repeat::val_str in the source code has huge performance difference with and without the warnings.

How to repeat:
1. Import the metadata of the case with script: sqltester_for_parallel_with_index_1.sql

2. execute the query with different value of max_allowed_packet. The query is attached in query_1.sql

All of the script will find in repeat_func_query.rar.
[5 Jan 2021 11:54] dave do 
reproduce script of the issue.

Attachment: repeat_func_query.rar (application/octet-stream, text), 39.42 KiB.
[6 Jan 2021 7:53] MySQL Verification Team 
Hello dave do,

Thank you for the report and test case.
Verified as described.

regards,
Umesh