Feature Request: Support MAX_EXECUTION_TIME

[xinwu5]

We use max_execution_time out this variable very often to control the max execution time from serverside for the queries. We would like to request TiDB can support this feature, or at least release a version to not report an error when using this variable at first. So it can unblock us for further testing.

https://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html#optimizer-hints-execution-time

1. What did you do?

mysql> SELECT /*+ MAX_EXECUTION_TIME(1000) */ 1 ;
ERROR 1105 (HY000): line 1 column 38 near " 1" (total length 40)

2. What did you expect to see?

mysql> SELECT /*+ MAX_EXECUTION_TIME(1000) */ 1 ;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

3. What did you see instead?

mysql> SELECT /*+ MAX_EXECUTION_TIME(1000) */ 1 ;
ERROR 1105 (HY000): line 1 column 38 near " 1" (total length 40)

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

mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v2.0.4-15-g14552af
Git Commit Hash: 14552afb4228c87f746c884c1548abc3eea4035e
Git Branch: release-2.0
UTC Build Time: 2018-07-04 11:56:13
GoVersion: go version go1.10.2 linux/amd64
TiKV Min Version: 2.0.0-rc.4.1
1 row in set (0.00 sec)

[morgo]

As an aside, the 5.7+ optimizer hints /*+ */ have a defined semantic of hint and not directive.

i.e. They can raise warnings, but should never raise errors.

[shenli]

@xinwu5 Thanks for your feedback!
We will support this syntax soon. The first step would be accepting the syntax and ignoring it.

[lysu]

relate to this statment level hint, 5.7 also introduce max_execution_time variable to control execution time in global/session level(https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_execution_time), also will need be support in future.

[shenli]

@xinwu5 Please try the latest master.

[morgo]

As described above, the syntax is parsed but currently ignored. I suggest we leave this issue open until the feature is implemented. Here is a minimal test-case:

SELECT /*+ MAX_EXECUTION_TIME(1000) */ sleep(10) ;

mysql57> SELECT /*+ MAX_EXECUTION_TIME(1000) */ sleep(10) ;
+-----------+
| sleep(10) |
+-----------+
|         1 |
+-----------+
1 row in set (1.00 sec)

tidb> SELECT /*+ MAX_EXECUTION_TIME(1000) */ sleep(10) ;
+-----------+
| sleep(10) |
+-----------+
|         0 |
+-----------+
1 row in set (10.00 sec)

tidb> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v2.1.0-rc.3-219-g1e0876fe8-dirty
Git Commit Hash: 1e0876fe810a832721aac52275dd2b7792fd2892
Git Branch: flush
UTC Build Time: 2018-11-24 01:12:47
GoVersion: go version go1.11 linux/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false
1 row in set (0.01 sec)