MySQL Bugs: #72240: Overly simplistic implementation of MAX_STATEMENT

Bug #72240	Overly simplistic implementation of MAX_STATEMENT_TIME with UNION
Submitted:	4 Apr 2014 17:45	Modified:	15 Mar 2016 15:56
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.7.4	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
MAX_STATEMENT_TIME -clause for SELECT statements was introduced in 5.7.4 - documented at https://dev.mysql.com/doc/refman/5.7/en/select.html. Quote: 

"MAX_STATEMENT_TIME = N sets a statement execution timeout of N milliseconds. If this option is absent or N is 0, the statement timeout established by the max_statement_time system variable applies. 

The MAX_STATEMENT_TIME option is applicable as follows: 

It applies to top-level SELECT statements. It does not apply to non-top-level statements, such as subqueries. 

It applies to read-only SELECT statements. Statements that are not read only are those that invoke a stored function that modifies data as a side effect. 
 
It does not apply to SELECT statements in stored programs; an error occurs. 

This option was added in MySQL 5.7.4."

There is alos a good blog here:
http://mysqlserverteam.com/server-side-select-statement-timeouts/

But what *EXACTLY* does a "top-level SELECT statement" mean? In the case of a query of the form "SELECT .. UNION SELECT .." I don't find neither the 1st nor 2nd SELCT more 'top-level' than the other (logically). 

How to repeat:
SELECT max_statement_time = 1000 1
UNION
SELECT max_statement_time = 1000 1;
-- Error Code: 1234
-- Incorrect usage/placement of 'MAX_STATEMENT_TIME'

SELECT max_statement_time = 1000 1
UNION
SELECT 1;
-- OK

SELECT 1
UNION
SELECT max_statement_time = 1000 1;
-- Error Code: 1234
-- Incorrect usage/placement of 'MAX_STATEMENT_TIME'

Suggested fix:
This simply looks like a much too simplistic implementation in the parser to me and an unnecessary restriction. If the parser did not block the execution of 1st and 3rd query in above test case, I believe they would all succeed without problems.

(for same reason I am setting category as 'parser' and not as 'DML')

If the cost of fixing this is considered too high, this case should be listed explicitly in docs.

Fixed typo in synopsis.

Thank you for the report.

SELECT in UNION is top-level SELECT:

mysql> select MAX_STATEMENT_TIME=1 1 union select sleep(3);
ERROR 1907 (HY000): Query execution was interrupted, max_statement_time exceeded

So this can be feature request at best.

But which feature do you want? Apply MAX_STATEMENT_TIME to all kind of statements or improve integration with SELECT statements? Please be more specific.

It may  be a documentation request then (elaborating the exact meaning of the term "top-level SELECT" in MySQL). I searched the docs but I did not find that term explained.  Is it a SQL standard term at all? Or something that Santa Claus gave to the MySQL docs team last Xmas? :-)

In my (commmon sense) understanding as query of the form "SELECT .. UNION SELECT .." none of the SELECTS are more top-level than the other (This is also common mathematical understanding of UNION in mathematical set theory).  The MySQL parser may implement it (parsing sequentially from left to right) like your example shows. What I would expect from your example ("select MAX_STATEMENT_TIME=1 1 union select sleep(3);") is that it should sleep for 3 seconds and then return "1".  In other words, I'd expect it to for both SELECTSs to complete and next aggregate results (unless an error results).  What does standards say about this? What do otehr RDBMS do?

This means that I'd expect the same result from "select MAX_STATEMENT_TIME=1 1 union select sleep(3);" and " select sleep(3) union select MAX_STATEMENT_TIME=1 1;". But in that case it is probably rather the implementation of UNION in MySQL, rather than MAX_STATEMENT_TIME, I am complaining about.

As a minimum please add to docs https://dev.mysql.com/doc/refman/5.7/en/select.html something like: "This also means that the MAX_STATEMENT_TIME -clause may be used only with the first SELECT of a "SELECT .. UNION SELECT .." -construct and not the following" (limitation with subqueries are explicitly explained there, but limitations with UNION are not currently).

I realize now, that what I have observed and what we have discussed, is not a problem with MAX_STATEMENT_TIME implementation as such but rather with UNION in MySQL (if there is an issue at all in MySQL different from other RDBMS).

Maybe also http://dev.mysql.com/doc/refman/5.7/en/union.html could be updated with details of how UNION is handled in MySQL with examples like yours. It is actually a rather short article and it is not cleaar to be that the first SELECT controls execution of the complete statement. I expected a behavior similar to mathematical set theory (UNION set operation is an equivalent of logical OR (and INTERSECTION set operation is an equivalent of logical AND).

Also using a sleep() in a UNION makes little sense for teh discussion, I think - except that it illustrates some details of MySQL UNION statement evalution/execution (and does this quite well indeed - so this was a rather good catch!)

(Apart from that, I still think that there is no reason - except for MySQL internals - why not the 3 statements in my test case should not all succeed and return the same result ("1").  But as I originally wrote: if costs fixing this are considered too high then document it)

Consider also this

SELECT SLEEP(2) UNION SELECT SLEEP(3); -- returns "0"
SELECT SLEEP(3) UNION SELECT SLEEP(2); -- returns "0" 
-- and the query execution time for both is just above 5 seconds.
-- this - I think - must be explained by the fact that such query runs in a
-- single thread. One of the SELECTs is executed, next the other and results 
-- are aggregated. There are not spawned separate threads for each so that they -- can run in parallel (what may have been what I naively expected!)

-- what also this query confirms
SELECT max_statement_time = 4500 SLEEP(2) UNION SELECT SLEEP(3);
-- Error Code: 1907
-- Query execution was interrupted, max_statement_time exceeded

In conclusion: to use 'max_statement_time' -clause with a UNION it must be specified with the 1st UNION'ed SELECT and it has effect for the complete query (and not only the first SELECT only). Maybe this is obvious for developers and other techies at MySQL/Oracle, but it is not for me. So please document it explicitly.

Thank you for the feedback.

Having the fact that 

mysql> select MAX_STATEMENT_TIME=1 sleep(3);
+----------+
| sleep(3) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

(no sleep happens at all) I verify this as code bug and suggest developers to look into it first. Then it can be converted to documentation. Because current behavior if not wrong is really confusing.

MySQL supports two kinds of options: statement options and "query block" options.
A statement option is applicable to a whole statement (e.g a SELECT statement), whereas a query block option is only applicable to the local query block (which can be a "top-level" query or a subquery).

Statement options must be placed after the first SELECT keyword in a SELECT statement:

SELECT MAX_STATEMENT_TIME=1000 * FROM t1;

It is by convention, as it probably would be confusing to find this option in a subquery. Would this also mean the whole statement, or would it mean only execution of this subquery? Considering that subqueries may execute multiple times would only add to the confusion.

What I call "query block" options are applicable to the query block only, e.g

SELECT STRAIGHT_JOIN * FROM t1 JOIN t2 JOIN t3
UNION
SELECT * FROM t4 JOIN t5 ...;

The STRAIGHT_JOIN option is applicable only to the join between t1, t2 and t3, but not to the join between t4 and t5.

It is however correct that "top-level" is ambiguous when referring to a UNION query. It should be specified further as: If the query is a UNION, the statement option must be placed in the first query block of the UNION.

The following passage in the documentation seems to be inexact: "It applies to top-level SELECT statements. It does not apply to non-top-level statements, such as subqueries.".

It actually applies to the whole statement execution, but as MAX_STATEMENT_TIME is a statement option, it must be placed in the first "top-level" query block, just after the first SELECT keyword.

The SQL standard does not use the term top-level to specify query blocks. It does however refer to queries within other query blocks as subqueries, but AFAIK, there is no specific term for the "top-level" query block.

(query block is my term. The SQL standard uses the term "query specification" for the clause starting with SELECT and "query expression" for one standalone query specification or for multiple query specifications combined with UNION. This is still a bit imprecise: query specification also covers VALUES clauses and TABLE clauses.)

Sveta: On your example:

select MAX_STATEMENT_TIME=1 sleep(3);

I think you are just confusing that statement time is measured in milliseconds whereas SLEEP time is measured in seconds. So timing out after 1 millisecond is not noticed on the execution time report, which has a granularity of 10 milliseconds. If you try with higher timeout values, you should be able to see higher execution times.

Roy,

> Sveta: On your example:

> select MAX_STATEMENT_TIME=1 sleep(3);

> I think you are just confusing that statement time is measured in milliseconds whereas SLEEP time is measured in seconds. So timing out after 1 millisecond is not noticed on the execution time report, which has a granularity of 10 milliseconds. If you try with higher timeout values, you should be able to see higher execution times.

Actually I am concerned about two other issues:

1. Function sleep(3) does not work in case if I use it together with MAX_STATEMENT_TIME

Please compare:

mysql> select MAX_STATEMENT_TIME=1 sleep(3);
+----------+
| sleep(3) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> select sleep(3);
+----------+
| sleep(3) |
+----------+
|        0 |
+----------+
1 row in set (3.01 sec)

Notice that in the first case execution time was 0.00 sec (which is totally wrong) and in second case execution time was 3.01 sec (which is expected).

2. Looks like you are wrong about MAX_STATEMENT_TIME for UNION. Compare:

mysql> select 1 union select sleep(3);
+---+
| 1 |
+---+
| 1 |
| 0 |
+---+
2 rows in set (3.00 sec)

mysql> select MAX_STATEMENT_TIME=1 1 union select sleep(3);
ERROR 1907 (HY000): Query execution was interrupted, max_statement_time exceeded

Roy,

please ignore. Reason for this behavior is function sleep which returns 1 if interrupted (http://dev.mysql.com/doc/refman/5.6/en/miscellaneous-functions.html#function_sleep) Behavior is correct, but still have to be documented better as was suggested in the original description.

Okay, let's get rid of the "top-level" term as follows:

For statements with multiple SELECT keywords, such as unions or
statements with subqueries, MAX_STATEMENT_TIME applies to the entire
statement and must appear after the first SELECT.

Posted by developer:
 
Use of SLEEP() for testing max statement execution time is a bit problematic due to its nonintuitive behavior regarding whether it returns a value or the statement containing it returns an error. This is now explained better at:
http://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_sleep