[MDEV-10529] SELECT SLEEP(n) with MAX_STATEMENT_TIME produces no error Created: 2016-08-10  Updated: 2016-08-10  Resolved: 2016-08-10

Status: Closed
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: None
Fix Version/s: N/A

Type: Bug Priority: Trivial
Reporter: Federico Razzoli Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

This is a minor problem, but I think it's natural to use the following query to test the behavior of max_statement_time:

MariaDB [(none)]> SET STATEMENT max_statement_time = 1 FOR SELECT SLEEP(10);
+-----------+
| SLEEP(10) |
+-----------+
|         1 |
+-----------+
1 row in set (1.01 sec)

The natural conclusion is that MAX_STATEMENT_TIME forces MariaDB to send the results it read until now, without any error. But this is not true:

MariaDB [test]> SET STATEMENT max_statement_time = 1 FOR SELECT * FROM t WHERE SLEEP(10);
ERROR 1969 (70100): Query execution was interrupted (max_statement_time exceeded)



 Comments   
Comment by Elena Stepanova [ 2016-08-10 ]

The natural conclusion is that MAX_STATEMENT_TIME forces MariaDB to send the results it read until now, without any error. But this is not true:

I don't think it's a natural conclusion (assumption) at all. How would this work in general, if it were a normal SELECT, which was caught by the timeout on the phase of query optimization, or DDL in the middle of the operation, etc.?
SELECT returning an empty result (or partial result set) would be a disaster, because the end user wouldn't know whether it's the real result set or not. DDL which leaves things "as they are" in the middle of the operation would be even more of a disaster.

The description of the variable also clearly says that the operation is aborted:
https://mariadb.com/kb/en/mariadb/server-system-variables/#max_statement_time

Description: Maximum time in seconds that a query can execute before being aborted. This includes all queries, not just SELECT statements, but excludes statements in stored procedures.

Comment by Elena Stepanova [ 2016-08-10 ]

Re-opening the bug – sorry, I've misread the initial complain, which is about SLEEP not producing the error. The previous comment is still true, of course max_statement_time is not meant to make a query return intermediate results.

Comment by Elena Stepanova [ 2016-08-10 ]

For SELECT SLEEP(N), it works the same way as if you interrupted the query manually – that is, if you run SELECT SLEEP(10) in one connection, and while it's sleeping, run KILL QUERY n, where n is the first connection's ID, from another connection, SLEEP will also return 1 rather then ERROR 1969. That's how SLEEP function is defined:
http://dev.mysql.com/doc/refman/5.5/en/miscellaneous-functions.html#function_sleep

When SLEEP() is the only thing invoked by a query that is interrupted, it returns 1 and the query itself returns no error.

Generated at Thu Feb 08 07:42:55 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.