[#MDEV-31209] Queries with window functions do not obey KILL / max_statement

Note: If MDEV-31206 is fixed first and the queries become faster, the test case below may no longer do what it is meant to do (the query will end before max_statement_time exceeds); but it's just the matter of the size and the timeout, adjust accordingly if it happens.

SET max_recursive_iterations = 100000;

drop table if exists t;

create table t as with recursive cte as (select 1 as a union select a + 1 as a from cte where a < 30000) select * from cte;

set max_statement_time= 2;

set @start= @@timestamp;

select max(a) over( ) from t;

select @@timestamp - @start;

drop table t;

So, without the timeout, currently the query takes ~30 seconds on a release build (and much longer on a debug build).

The test case sets the timeout to 2 seconds, the expected outcome is that the query fails with ER_STATEMENT_TIMEOUT after ~2 seconds.

Instead, it does fail with ER_STATEMENT_TIMEOUT and does not return the result set, but only after the whole execution time, i.e. 30 seconds.

11.0.1 release
MariaDB [test]> set max_statement_time= 2;
Query OK, 0 rows affected (0.000 sec)

MariaDB [test]>
MariaDB [test]> set @start= @@timestamp;
Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> select max(a) over( ) from t;
ERROR 1969 (70100): Query execution was interrupted (max_statement_time exceeded)
MariaDB [test]> select @@timestamp - @start;
+----------------------+
\| @@timestamp - @start \|
+----------------------+
\| 31.26440405845642 \|
+----------------------+
1 row in set (0.000 sec)

Same happens with direct KILL [QUERY], whenever it is killed during execution, the query remains in the Killed state approx. its usual execution time, and then fails with ER_QUERY_INTERRUPTED.

MariaDB [test]> show processlist;

+----+------+-----------------+------+---------+------+--------------+------------------------------+----------+

| Id | User | Host            | db   | Command | Time | State        | Info                         | Progress |

+----+------+-----------------+------+---------+------+--------------+------------------------------+----------+

| 17 | root | localhost:59258 | test | Killed  |   22 | Sending data | select max(a) over( ) from t |    0.000 |

| 18 | root | localhost:40164 | test | Query   |    0 | starting     | show processlist             |    0.000 |

+----+------+-----------------+------+---------+------+--------------+------------------------------+----------+

[MDEV-31209] Queries with window functions do not obey KILL / max_statement_time Created: 2023-05-06 Updated: 2023-11-28
Status:	Open
Project:	MariaDB Server
Component/s:	Optimizer - Window functions
Affects Version/s:	10.3, 10.4, 10.5, 10.6, 10.8, 10.9, 10.10, 10.11, 11.0
Fix Version/s:	10.4, 10.5, 10.6, 10.11, 11.0

[MDEV-31209] Queries with window functions do not obey KILL / max_statement_time Created: 2023-05-06 Updated: 2023-11-28