Bug #50523 BENCHMARK(1000,(SELECT...)) runs SELECT only once

• Submitted: 21 Jan 2010 20:35
• Modified: 22 Jan 2010 11:24
• Reporter: Guilhem Bichot
• Status: Verified
• Category: MySQL Server: General
• Severity: S3 (Non-critical)
• Version: 5.1.44-bzr
• OS: Linux (64)
• CPU Architecture: Any

[21 Jan 2010 20:35] Guilhem Bichot

Description:
I use 6.0-codebase-bugfixing revision-id:[e-mail]-20100119125520-r1qh112atxs5m23y and I have not tested earlier versions.
The manual mentions that one can use
SELECT BENCHMARK(N,(some SELECT));
in http://dev.mysql.com/doc/refman/5.4/en/information-functions.html#function_benchmark
Though that manual section gives warnings, I don't think it covers what I am observing.
I have a query which takes <1/100th of a second, thus is hard to time precisely. So I wanted to run it 1000 times to get a measurable number, then I would have divided by 1000.
But SELECT BENCHMARK(1000,(SELECT...)) actually runs the query once. All following 999 iterations of BENCHMARK() are served by a cache (Item_singlerow_subselect::val_int() uses Item_cache_int::val_int()).
So this breaks the idea of BENCHMARK().

How to repeat:
Create a deterministic SELECT which runs for some measurable time, run it with BENCHMARK(1000): it returns in the same time as the query run once.

Suggested fix:
if this cannot be fixed, the manual should be clarified.
See also http://bugs.mysql.com/bug.php?id=26093

[22 Jan 2010 4:25] Valeriy Kravchuk

I'd like to see exact SELECT that demonstrates this problem. In simple cases BENCHMARK() works as expected it seems:

77-52-24-143:6.0-codebase openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.14-alpha-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select benchmark(10, (select sleep(1) from mysql.user limit 1));
+----------------------------------------------------------+
| benchmark(10, (select sleep(1) from mysql.user limit 1)) |
+----------------------------------------------------------+
|                                                        0 |
+----------------------------------------------------------+
1 row in set (10.00 sec)

mysql> select benchmark(3, (select sleep(1) from mysql.user limit 1));
+---------------------------------------------------------+
| benchmark(3, (select sleep(1) from mysql.user limit 1)) |
+---------------------------------------------------------+
|                                                       0 |
+---------------------------------------------------------+
1 row in set (3.00 sec)

[22 Jan 2010 10:12] Guilhem Bichot

Hello Valeriy. Yes, SLEEP() probably sets an "uncachable" flag on the query (otherwise multiple SLEEP()s in a query would not be honoured), so subquery result caching does not happen (it is a mechanism internal to subqueries). Here's a sample query which exhibits the problem. I just verified it in 5.1.
create table t(a varchar(100));
insert into t values("alkjlkjkljklkjljkjhkjhkjsfhkjhkdjhkjfh");
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
select benchmark(1,(select distinct a from t));
select benchmark(1000,(select distinct a from t));
I get 1.8 seconds for 1 and for 1000.

[22 Jan 2010 11:24] Valeriy Kravchuk

Verified just as described with your latest test case on recent 5.1.44 from bzr:

...

mysql> select benchmark(1,(select distinct a from t));
+-----------------------------------------+
| benchmark(1,(select distinct a from t)) |
+-----------------------------------------+
|                                       0 |
+-----------------------------------------+
1 row in set (5.31 sec)

mysql> select benchmark(100,(select distinct a from t));
+-------------------------------------------+
| benchmark(100,(select distinct a from t)) |
+-------------------------------------------+
|                                         0 |
+-------------------------------------------+
1 row in set (5.26 sec)

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