MySQL Bugs: #26093: SELECT BENCHMARK() for SELECT statements does not produce valid results

Bug #26093	SELECT BENCHMARK() for SELECT statements does not produce valid results
Submitted:	5 Feb 2007 18:45	Modified:	15 Mar 2007 15:08
Reporter:	Chris Calender	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.34, 5.1.14	OS:	Any (All)
Assigned to:	Marc ALFF	CPU Architecture:	Any
Tags:	BENCHMARK, invalid results, SELECT BENCHMARK()

Description:
SELECT BENCHMARK() for SELECT statements does not produce valid results.

This function is listed on the following page in the manual, which is titled "Optimizing SELECT and Other Statements":

http://dev.mysql.com/doc/refman/5.0/en/query-speed.html

This page does not explicitly say you can use SELECT statements with SELECT BENCHMARK(), but it does not say you shouldn't either.  From reading other bug reports about SELECT BENCHMARK(), they clearly show users using this for SELECT statements though.

mysql> select avg(id) from t;
+---------+
| avg(id) |
+---------+
| 5.5000  |
+---------+
1 row in set (1.17 sec)

mysql> select benchmark(10, (select avg(id) from t));
+----------------------------------------+
| benchmark(10, (select avg(id) from t)) |
+----------------------------------------+
| 0                                      |
+----------------------------------------+
1 row in set (0.02 sec)

In the above simple example, you see that the SELECT statement alone takes 1.17 seconds.  However, when invoked 10 times, it says it would only take 0.02 seconds.

This is clearly the wrong results.  However, I am unsure if this is not supposed to be used for SELECT statements (although it allows them).

If this should not be allowed, then we should simply change the docs, and possible not allow SELECT statements to be evaluated.  Otherwise, it needs to be changed to provide appropriate results.

I should note that it will only work with SELECTs that return 1 row.  Otherwise, you will receive an error regarding that.

I have verified this on 5.0.34 and 5.1.14 (as well as 5.0.28).

How to repeat:
I created a simple table and populated it with very simple data.

use test;
create table t (id int(7));
insert into t (id) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
insert into t (select * from t);

(run the above command several times to load more data ~ 10-15 iterations)

select avg(id) from t;
select benchmark(10, (select avg(id) from t));

(compare results from above 2 commands and you clearly see the problem.

Suggested fix:
It could be that this is not intended to be allowed.  If this is the case, then an update to the online documentation would be fine.

Note that the query cache was off in my tests.

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/19920

ChangeSet@1.2395, 2007-02-14 20:42:33-07:00, malff@weblab.(none) +3 -0
  Bug#26093 (SELECT BENCHMARK() for SELECT statements does not produce valid
    results)
  
  Before this fix, the function BENCHMARK() would fail to evaluate expressions
  like "(select avg(a) from t1)" in debug builds (with an assert),
  or would report a time of zero in non debug builds.
  
  The root cause is that evaluation of DECIMAL_RESULT expressions was not
  supported in Item_func_benchmark::val_int().
  
  This is a bug, which has been fixed by this change.
  
  Independently of this fix, the behavior of BENCHMARK() needs to be better
  documented in the user manual to clarify expectations:
  
  "select BENCHMARK(N, expression)" is intended to measure the performance of
  the runtime evaluation of scalar expressions.
  This has major implications:
  
  a)
  Only scalar expressions can be used: a statement like "select * from t1",
  when returning multiple rows, or a more general statement like "create
  table" can not be benchmarked using this function.
  
  b)
    [1] select expression;
    [2] select expression;
    ...
    [N] select expression;
  and
    select BENCHMARK(N, expression)
  have a very different execution profile.
  
  With the former, the parser, the optimizer, table locking, and runtime
  evaluation are each invoked N times.
  With the later, the parser is invoked once, the optimizer is invoked once,
  tables are opened and locked once, and runtime evaluation is executed N
  times, while reusing memory structures already allocated.
  In addition, runtime optimizations, like local caching of results already
  evaluated for aggregate functions like avg(), can alter the results.
  
  There is no expectation that
    time("select BENCHMARK(N, expression)")
  is equivalent to
    N * time("select expression")
  since to the contrary, the purpose of BENCHMARK() is to remove the noise
  created by the network, the parser, the optimizer, etc,
  to measure the performance of the runtime component itself,
  by giving more weight (repeat the evaluation N times) to this part.

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/20970

ChangeSet@1.2395, 2007-03-01 19:20:47-07:00, malff@weblab.(none) +3 -0
  Bug#26093 (SELECT BENCHMARK() for SELECT statements does not produce valid
    results)
  
  Before this fix, the function BENCHMARK() would fail to evaluate expressions
  like "(select avg(a) from t1)" in debug builds (with an assert),
  or would report a time of zero in non debug builds.
  
  The root cause is that evaluation of DECIMAL_RESULT expressions was not
  supported in Item_func_benchmark::val_int().
  
  This has been fixed by this change.

Pushed into  5.0.38, 5.1.17

Noted in 5.0.38, 5.1.17 changelogs.

BENCHMARK() did not work correctly for expressions that produced a
DECIMAL result. 

Also updated the BENCHMARK() function section with additional
information provided by Marc.