MySQL Bugs: #20752: BENCHMARK with many iterations returns too quickly

Bug #20752	BENCHMARK with many iterations returns too quickly
Submitted:	28 Jun 2006 11:31	Modified:	9 Apr 2008 15:12
Reporter:	Dean Swift	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.22/4.1/5.0/5.1BK	OS:	Windows (Windows/Linux Suse)
Assigned to:	Tatiana Azundris Nuernberg	CPU Architecture:	Any

Description:
Use of BENCHMARK with a large number of iterations returns prematurely. Reliable threshold is approximately two billion iterations and therefore cause is suspected to be 32 bit interger wrap-around.

How to repeat:
mysql> select benchmark(1,1);
+-------------------+
| benchmark(1000,1) |
+-------------------+
|                 0 |
+-------------------+
1 row in set (0.00 sec)

mysql> select benchmark(10,1);
+-------------------+
| benchmark(1000,1) |
+-------------------+
|                 0 |
+-------------------+
1 row in set (0.00 sec)

mysql> select benchmark(100,1);
+-------------------+
| benchmark(1000,1) |
+-------------------+
|                 0 |
+-------------------+
1 row in set (0.00 sec)

mysql> select benchmark(1000,1);
+-------------------+
| benchmark(1000,1) |
+-------------------+
|                 0 |
+-------------------+
1 row in set (0.00 sec)

mysql> select benchmark(10000,1);
+--------------------+
| benchmark(10000,1) |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)

mysql> select benchmark(100000,1);
+---------------------+
| benchmark(100000,1) |
+---------------------+
|                   0 |
+---------------------+
1 row in set (0.00 sec)

mysql> select benchmark(1000000,1);
+----------------------+
| benchmark(1000000,1) |
+----------------------+
|                    0 |
+----------------------+
1 row in set (0.02 sec)

mysql> select benchmark(10000000,1);
+-----------------------+
| benchmark(10000000,1) |
+-----------------------+
|                     0 |
+-----------------------+
1 row in set (0.13 sec)

mysql> select benchmark(100000000,1);
+------------------------+
| benchmark(100000000,1) |
+------------------------+
|                      0 |
+------------------------+
1 row in set (1.36 sec)

mysql> select benchmark(1000000000,1);
+-------------------------+
| benchmark(1000000000,1) |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set (13.61 sec)

mysql> select benchmark(10000000000,1);
+--------------------------+
| benchmark(10000000000,1) |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (19.20 sec)

mysql> select benchmark(100000000000,1);
+---------------------------+
| benchmark(100000000000,1) |
+---------------------------+
|                         0 |
+---------------------------+
1 row in set (16.56 sec)

mysql> select benchmark(1000000000000,1);
+----------------------------+
| benchmark(1000000000000,1) |
+----------------------------+
|                          0 |
+----------------------------+
1 row in set (48.61 sec)

mysql> select benchmark(10000000000000,1);
+-----------------------------+
| benchmark(10000000000000,1) |
+-----------------------------+
|                           0 |
+-----------------------------+
1 row in set (17.94 sec)

mysql> select benchmark(100000000000000,1);
+------------------------------+
| benchmark(100000000000000,1) |
+------------------------------+
|                            0 |
+------------------------------+
1 row in set (3.77 sec)

Suggested fix:
Workaround is to not benchmark with more than one billion iterations.

Thank you for the bug report.

mysql> select benchmark(10000000000000,1);
+-----------------------------+
| benchmark(10000000000000,1) |
+-----------------------------+
|                           0 | 
+-----------------------------+
1 row in set (1 min 17.15 sec)

mysql> select benchmark(100000000000000,1);
+------------------------------+
| benchmark(100000000000000,1) |
+------------------------------+
|                            0 | 
+------------------------------+
1 row in set (16.29 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.23-debug | 
+--------------+
1 row in set (0.00 sec)

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.1.12-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select benchmark(10000000000000,1);
+-----------------------------+
| benchmark(10000000000000,1) |
+-----------------------------+
|                           0 | 
+-----------------------------+
1 row in set (1 min 15.16 sec)

mysql> select benchmark(100000000000000,1);
+------------------------------+
| benchmark(100000000000000,1) |
+------------------------------+
|                            0 | 
+------------------------------+
1 row in set (15.87 sec)

mysql> 

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.21-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select benchmark(10000000000000,1);
+-----------------------------+
| benchmark(10000000000000,1) |
+-----------------------------+
|                           0 |
+-----------------------------+
1 row in set (30.36 sec)

mysql> select benchmark(100000000000000,1);
+------------------------------+
| benchmark(100000000000000,1) |
+------------------------------+
|                            0 |
+------------------------------+
1 row in set (6.37 sec)

mysql>

This should be fixed in 5.1 by a patch for Bug#22684 "BENCHMARK, ENCODE, DECODE and FORMAT are not real functions"

That is, in 5.1 unsigned long long will be used, not unsigned long.
DECIMAL won't be used, still.
This may just need a documentation entry.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/42019

ChangeSet@1.2588, 2008-02-11 10:06:52+01:00, tnurnberg@mysql.com +3 -0
  Bug#20752: BENCHMARK with many iterations returns too quickly
  
  In BENCHMARK(count, expr), count could overflow/wrap-around.
  Patch changes to a sufficiently large data-type. Adds a warning
  for negative count values.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/42913

ChangeSet@1.2669, 2008-02-24 00:45:09+01:00, tnurnberg@mysql.com +3 -0
  Bug#20752: BENCHMARK with many iterations returns too quickly
  
  In BENCHMARK(count, expr), count could overflow/wrap-around.
  Patch changes to a sufficiently large data-type. Adds a warning
  for negative count values.

pushed to 5.1.24-rc, 6.0.5-alpha in opt

Pushed into 6.0.5-alpha

Pushed into 5.1.24-rc

Hi,

It looks like there's been an unintended feature change. 5.0 errors out when the number of iterations is negative, as shown here:
mysql> SELECT VERSION();
+--------------+
| VERSION()    |
+--------------+
| 5.0.60-debug |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT BENCHMARK(-1,1);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1,1)' at line 1

Both 5.1 and 6.0 with this patch do not error out; instead, they return NULL with a warning, as shown here:

mysql> SELECT VERSION();
+-----------------+
| VERSION()       |
+-----------------+
| 5.1.24-rc-debug |
+-----------------+
1 row in set (0.03 sec)

mysql> SELECT BENCHMARK(-1,1);
+-----------------+
| BENCHMARK(-1,1) |
+-----------------+
|            NULL |
+-----------------+
1 row in set, 1 warning (0.04 sec)

mysql> SHOW WARNINGS;
+-------+------+----------------------------------------------------+
| Level | Code | Message                                            |
+-------+------+----------------------------------------------------+
| Error | 1411 | Incorrect count value: '-1' for function benchmark |
+-------+------+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT VERSION();
+-------------------+
| VERSION()         |
+-------------------+
| 6.0.5-alpha-debug |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT BENCHMARK(-1,1);
+-----------------+
| BENCHMARK(-1,1) |
+-----------------+
|            NULL |
+-----------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+-------+------+----------------------------------------------------+
| Level | Code | Message                                            |
+-------+------+----------------------------------------------------+
| Error | 1411 | Incorrect count value: '-1' for function benchmark |
+-------+------+----------------------------------------------------+
1 row in set (0.00 sec)

Please indicate if this change is intentional or not. Thanks!

Keen eye there Jon, thank you.
Yes, the change was intentional, the rationale being this:

mysql> select version();
5.0.56-debug

mysql> SELECT BENCHMARK(-1,1);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1,1)' at line 1

mysql> SELECT BENCHMARK(NULL,1);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL,1)' at line 1

mysql> select version();
5.1.24-rc-debug

mysql> SELECT BENCHMARK(-1,1);
NULL

mysql> SELECT BENCHMARK(NULL,1);
NULL

IOW, the behaviour of BENCHMARK(NULL,...) had already changed away from "Syntax error" as a response to nonsensical input. The patch accomplishes several things (if you count generously): accept large values for iteration-argument (ulonglong), distinguish that correctly from negative values (which get the same NULL-result in response to rubbish input as a NULL-argument does now), and the advisory is more precise now ("Incorrect count value: '-1'" to previous "There's something wrong here, but we won't tell you what it is"). So in real terms, "it was indeed changed a) for clarity and b) so it will consistently render the same result on invalid input, no matter whether that it a negative or NULL number of iterations."

Tatjana - Thanks for the info. I'll take care of this one soon.

Documented in the 5.1.23-ndb-6.3.11, 5.1.24, and 6.0.5 changelogs as follows:

        The BENCHMARK() function, invoked with more than 2147483648 iterations
        (the size of a signed 32-bit integer), terminated prematurely.