MySQL Bugs: #120108: MAX_EXECUTION_TIME does not raise error when query involves no table reads

Bug #120108	MAX_EXECUTION_TIME does not raise error when query involves no table reads
Submitted:	19 Mar 8:43	Modified:	28 Mar 20:40
Reporter:	Juan Lago	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	9.6.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	calculated, Exception Error, max_execution_time, query

Description:
When a query is used together with max_execution_time (Example: SELECT /*+ MAX_EXECUTION_TIME(2500) */ * FROM...) and the query expires, an error is raised with the message 'query execution was interrupted, maximum statement execution time exceeded' (3024). This behavior is perfectly correct; however, when a query doesn't read any table, the query is stopped without raising any error, and it outputs the calculations it was able to process up to the max_execution_time limit.

For example, the following queries will not raise any error but will be stopped:

WITH RECURSIVE fib AS (SELECT CAST(0 AS UNSIGNED) AS n, CAST(0 AS UNSIGNED) AS fib_n, CAST(1 AS UNSIGNED) AS next_fib
                       UNION ALL
                       SELECT n + 1, next_fib, fib_n + next_fib
                       FROM fib
                       WHERE n < 92)
SELECT /*+ MAX_EXECUTION_TIME(2500) */ n, fib_n
FROM fib LIMIT 10000000

In the previous case, if your server is not fast enough, it will output an incomplete result without raising any error, so there is no way to know whether the result is complete if you don't already know that it should have 10,000,000 rows. This is a serious issue because calculated queries with max_execution_time may return incomplete data.

Another example:

SELECT /*+ MAX_EXECUTION_TIME(2500) */ SLEEP(5)

This query will never raise the time exceeded error even though it is stopped, so there is no way to know whether the query expired or completed successfully.
Other RDBMSs such as MariaDB (max_statement_time) and PostgreSQL (statement_timeout) always raise their corresponding errors when a query — whether computational or not — expires. MySQL does not share this behavior.

How to repeat:
SELECT /*+ MAX_EXECUTION_TIME(2500) */ SLEEP(5)

or

WITH RECURSIVE fib AS (SELECT CAST(0 AS UNSIGNED) AS n, CAST(0 AS UNSIGNED) AS fib_n, CAST(1 AS UNSIGNED) AS next_fib
                       UNION ALL
                       SELECT n + 1, next_fib, fib_n + next_fib
                       FROM fib
                       WHERE n < 92)
SELECT /*+ MAX_EXECUTION_TIME(2500) */ n, fib_n
FROM fib

Suggested fix:
Make a consistent behavior between calculation and non calculation queries like other RDBMS so the time exceed exception is always raised.

I am sorry but we cannot reproduce your issues.

The recursive query times out after 92 rounds, long before the timer expires.

The query with SLEEP() returns 1, meaning that it was interrupted, according to how it is documented to behave.

A different query using SLEEP() reports correctly that it is interrupted:

  SELECT /*+ MAX_EXECUTION_TIME(2500) */ SLEEP(1)
  FROM (VALUES ROW(1), ROW(2), ROW(3), ROW(4), ROW(5)) AS t(a);

Hi Roy,

Thank you for looking into this, but I think the core issue may have been missed. Let me try to clarify.

The bug is not about whether the query is stopped — it clearly is. The bug is about the absence of an error being raised when it is stopped due to MAX_EXECUTION_TIME.

To be specific:

1. When MAX_EXECUTION_TIME expires on a query that reads from a real table, MySQL correctly raises error 3024: 'Query execution was interrupted, maximum statement execution time exceeded.'

2. However, when MAX_EXECUTION_TIME expires on a purely computational query (one that does not read from any table — such as SELECT SLEEP(5) or the recursive Fibonacci CTE), MySQL silently stops the query and returns whatever partial result it has computed so far, without raising any error or warning.

This is the problem: the caller has no way of knowing whether the result is complete or was cut short. A query returning 6,000,000 Fibonacci rows looks identical to one that was interrupted after 6,000,000 rows of a possible 10,000,000.

Regarding your specific points:
- The recursive CTE timing out after 92 rounds is expected on a fast server — the bug only manifests if the server is slow enough for the timer to fire before n reaches 92. The SLEEP() example is more reliable for reproduction.
- SLEEP() returning 1 indicates interruption internally, but error 3024 is never surfaced to the client, which is the actual gap.

The suggested fix is simply to raise the same error 3024 consistently for all query types when MAX_EXECUTION_TIME is exceeded, matching the behavior of MariaDB (max_statement_time) and PostgreSQL (statement_timeout).

Hope this helps clarify. Happy to provide additional reproduction steps if needed.

Thank you for the answer.

My investigation shows that SELECT with SLEEP() behaves as it is documented to do.

The case with a CTE is not reproducible with the script that you provided.
We will need a script that proves the issue.

Please find a repro below for "when a query doesn't read any table, the query is stopped without raising any error".

Also note that this bug also affects 8.0.45 and 8.4.8, so please update Version accordingly.

Thanks Juan Lago for the report, interesting finding, hoping this comment helps.

---

# Create environment,
$ ./use -N <<< "
  CREATE DATABASE test_jfg;
  CREATE TABLE test_jfg.t1(id BIGINT PRIMARY KEY, v int);
  INSERT INTO test_jfg.t1 VALUES (1,1);
  SELECT version()"
9.6.0

# Sleep with a table reports an error, this is the expected behavior.
$ time ./use test_jfg <<< "SELECT /*+ MAX_EXECUTION_TIME(500) */ SLEEP(1) FROM t1" > /dev/null
ERROR 3024 (HY000) at line 1: Query execution was interrupted, maximum statement execution time exceeded

real	0m0.541s
user	0m0.015s
sys	0m0.013s

# Sleep without a table does not report an error, this is unexpected, we expect an error similar to above.
# Note that the query was interrupted: it only took 0.543 second to execute below, so the sleep was interrupted.
$ time ./use test_jfg <<< "SELECT /*+ MAX_EXECUTION_TIME(500) */ SLEEP(1)" > /dev/null

real	0m0.543s
user	0m0.015s
sys	0m0.012s

---

# For 8.4.8.
$ ./use -N <<< "
  CREATE DATABASE test_jfg;
  CREATE TABLE test_jfg.t1(id BIGINT PRIMARY KEY, v int);
  INSERT INTO test_jfg.t1 VALUES (1,1);
  SELECT version()"
8.4.8

$ time ./use test_jfg <<< "SELECT /*+ MAX_EXECUTION_TIME(500) */ SLEEP(1) FROM t1" > /dev/null
ERROR 3024 (HY000) at line 1: Query execution was interrupted, maximum statement execution time exceeded

real	0m0.537s
user	0m0.015s
sys	0m0.012s

$ time ./use test_jfg <<< "SELECT /*+ MAX_EXECUTION_TIME(500) */ SLEEP(1)" > /dev/null

real	0m0.538s
user	0m0.014s
sys	0m0.011s

---

# For 8.0.45.
$ ./use -N <<< "
  CREATE DATABASE test_jfg;
  CREATE TABLE test_jfg.t1(id BIGINT PRIMARY KEY, v int);
  INSERT INTO test_jfg.t1 VALUES (1,1);
  SELECT version()"
8.0.45

$ time ./use test_jfg <<< "SELECT /*+ MAX_EXECUTION_TIME(500) */ SLEEP(1) FROM t1" > /dev/null
ERROR 3024 (HY000) at line 1: Query execution was interrupted, maximum statement execution time exceeded

real	0m0.538s
user	0m0.014s
sys	0m0.011s

$ time ./use test_jfg <<< "SELECT /*+ MAX_EXECUTION_TIME(500) */ SLEEP(1)" > /dev/null

real	0m0.536s
user	0m0.014s
sys	0m0.012s

Thanks  Jean-François and Roy. I review the Fibonacci example and it was limited to 92 rows, so it was incorrect. I was tested with other computational queries and all of them trigger the properly the error, only the SLEEP example is the only one that doesn't trigger the error, but according to Roy this behavior was documented so despite that it's inconsistent it's correct.

Roy, I apologize the waste of time trying to reproduce this issue with the incorrect Fibonacci example.

This bug was marked as "Can't repeat" on Thu, Mar 19.  However, I provided a reproduction ([20 Mar 13:35]): has it been considered ?

I can also read below from a comment ([20 Mar 7:28] Roy Lyseng):

> My investigation shows that SELECT with SLEEP() behaves as it is documented to do.

Unclear where it is documented that an interrupted sleep without a table will not report an ERROR 3024, can a link to the documentation be added to this bug ?

Also, if MySQL behaves as expected, the bug should not be marked as "Can't repeat" but as "Not a bug".  I would challenge this "Not a bug" though, because my expectation is that an interrupted sleep should show an error in all cases.  Moreover, with the new repro below, we can see that the interrupted query without a table (as already mentioned, it was interrupted as shown by time) leads the mysql client to not report an error, which is also unexpected (see "|| echo ko" showing the mysql client reports the error with a table, and "&& echo ok" not reporting an error without).

Many thanks for updating this bug accordingly (Status and Version).

for v in 9.6.0 8.4.8 8.0.45; do dbdeployer deploy single $v& done; cd ~/sandboxes/; wait

for d in msb_*; do
 ./$d/use -N <<< "
    CREATE DATABASE test_jfg;
    CREATE TABLE test_jfg.t1(id BIGINT PRIMARY KEY, v int);
    INSERT INTO test_jfg.t1 VALUES (1,1);"
done

# Errors reported below, with exit code being non-ok (both as expected).
sql="SELECT /*+ MAX_EXECUTION_TIME(500) */ SLEEP(1)"
for d in msb_*; do
  echo
  ./$d/use -N test_jfg <<< "SELECT VERSION()"
  time ./$d/use test_jfg <<< "$sql FROM t1" > /dev/null || echo ko
done

8.0.45
ERROR 3024 (HY000) at line 1: Query execution was interrupted, maximum statement execution time exceeded

real	0m0.519s
user	0m0.006s
sys	0m0.004s
ko

8.4.8
ERROR 3024 (HY000) at line 1: Query execution was interrupted, maximum statement execution time exceeded

real	0m0.519s
user	0m0.006s
sys	0m0.004s
ko

9.6.0
ERROR 3024 (HY000) at line 1: Query execution was interrupted, maximum statement execution time exceeded

real	0m0.518s
user	0m0.006s
sys	0m0.004s
ko

# No errors reported below, with exit code being ok, both being unexpected.
for d in msb_*; do
  echo
  ./$d/use -N test_jfg <<< "SELECT VERSION()"
  time ./$d/use test_jfg <<< "$sql" > /dev/null && echo ok
done

8.0.45

real	0m0.518s
user	0m0.006s
sys	0m0.004s
ok

8.4.8

real	0m0.517s
user	0m0.006s
sys	0m0.004s
ok

9.6.0

real	0m0.521s
user	0m0.007s
sys	0m0.005s
ok

It could be nice that "SELECT SLEEP" can raise an error like it happens with other RDBMS. Right now I have some unit tests in my projects that are using the following workaround in order of have the "maximum statement" error raised:

 WITH response AS(
   SELECT sleep(3), NOW() as time_now
 )
 select /*+ MAX_EXECUTION_TIME(2000) */ time_now from response;

Sorry, I had some urgent things to do...

First, I'll quote the documentation of SLEEP():

Sleeps (pauses) for the number of seconds given by the duration argument, then returns 0. The duration may have a fractional part. If the argument is NULL or negative, SLEEP() produces a warning, or an error in strict SQL mode.

When sleep returns normally (without interruption), it returns 0:

When SLEEP() is the only thing invoked by a query that is interrupted, it returns 1 and the query itself returns no error. This is true whether the query is killed or times out.

When SLEEP() is only part of a query that is interrupted, the query returns an error.

I think this explains the repro case provided by Jean-François.

This statement returns with an error:
SELECT /*+ MAX_EXECUTION_TIME(500) */ SLEEP(1) FROM t1;

This statement does not return an error:
SELECT /*+ MAX_EXECUTION_TIME(500) */ SLEEP(1);

The second statement complies with "SLEEP() is the only thing invoked by a query", thus it should return 1 if interrupted.

Based on this analysis, I think it is reasonable to close this as "Not a bug".