Missing built-in benchmark function

[morgo]

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.

The built-in benchmark function in MySQL is useful for testing. I was hoping to use it to generate load on TiDB so I could test auto-scaling in AWS easily (using CPU as a metric).

2. What did you expect to see?

mysql57> select benchmark(10000000, md5(rand()));
+----------------------------------+
| benchmark(10000000, md5(rand())) |
+----------------------------------+
|                                0 |
+----------------------------------+
1 row in set (8.79 sec)

3. What did you see instead?

tidb> select benchmark(10000000, md5(rand()));
ERROR 1305 (42000): FUNCTION BENCHMARK does not exist

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

tidb> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v2.1.0-alpha-4-g13050b4
Git Commit Hash: 13050b4d60ebe2da156af7e15774e28119dae165
Git Branch: master
UTC Build Time: 2018-06-04 02:07:21
GoVersion: go version go1.10.2 linux/amd64
Race Enabled: false
TiKV Min Version: 2.0.0-rc.4.1
1 row in set (0.00 sec)

[coocood]

@morgo
Thank you for your suggestion!
But the query you use can only run on a single tidb-server, it won't run faster on a multi-instance cluster.

[wuudjac]

Say we have this query:

select benchmark(1000000000, length('foo'));

In tidb-server, the argument function will be constant folded by core.expressionRewriter.Leave() as a visitor.

So, the query will be rewriten like this:

select benchmark(1000000000, 3);

Making the evaluation of benchmark() just purely looping a constant.

In mysql 5.7, the argument function is not constant folded, so the execurtion time differs, you can compare duration of these queries:

select benchmark(1000000000, length('foo'));
select benchmark(1000000000, 3);
select benchmark(1000000000, sin(123)+sin(456));

In order to make tidb-server behaves like mysql, I think constant folding needs to be turned off while visiting benchmark AST, making core.expressionRewriter.Leave() use expression.NewFunctionBase() instead of expression.NewFunction() in this case.

However, it may change many places where expression rewriter uses expression.NewFunction(), and may add a new flag into expression rewriter to act as a trigger, is it necessary to accomplish this? What's your opinion? @morgo @coocood

[morgo]

I agree with your analysis: constant folding should be disabled for the benchmark function.

Thanks for looking into this!

[morgo]

Added in #9252