MySQL Bugs: #102903: CTAS or ITAS

Bug #102903	CTAS or ITAS - max_execution_time
Submitted:	10 Mar 2021 13:50	Modified:	7 Aug 2021 18:12
Reporter:	Matias Sánchez	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S4 (Feature request)
Version:	MySQL 8.0.22	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	max_execution_time

Description:
Hi! Got a concern regarding execution time limit for specific type of SQL statements. We are migrating our database from Mariadb to Mysql and we are used to rely on the "set statement max_statement_time for " (Mariadb scope) apart for select statements, the main extra usage is in Create [temporary] table as Select or Insert as Select.
I’ve further checked that when applying the limit on the select clause of a CTAS, as this:

CREATE TEMPORARY TABLE tmp (id int) AS SELECT /*+ MAX_EXECUTION_TIME(xx) */ …

the limit is not working properly. I’ve tested the case when an specific select gets interrupted, but when the same select is part of a CTAS , the interruption is not raised.

Also the same check was done as a CREATE TABLE + INSERT AS SELECT , but got same results. Another check was on a set local variable max_execution_time, but again not success.

This CTAS max_execution_time are not supported, right? or is there any workaround possible to set this limit in this type of clauses?

I've tagged this report as a Feature Request as I can further check no workaround as this optimizer-hint on  https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html#optimizer-hints-execution-tim... is intended only to main select clauses. 

Thanks you in advance for any help 

How to repeat:

CREATE TEMPORARY TABLE tmp (id int) AS SELECT /*+ MAX_EXECUTION_TIME(xx) */ …

Easily reproducible on any select as for example

mysql>  SELECT /*+ MAX_EXECUTION_TIME(1) */ VERSION
    -> from INFORMATION_SCHEMA.TABLES;
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

But when same select as part of CTAS

create database test_limit;
use test_limit;
CREATE TEMPORARY TABLE tmp_versions (id int) AS
 SELECT /*+ MAX_EXECUTION_TIME(1) */ VERSION
from INFORMATION_SCHEMA.TABLES;
Query OK, 335 rows affected, 1 warning (0.01 sec)
Records: 335  Duplicates: 0  Warnings: 1

Hello Matias,

Thank you for feature request.

regards,
Umesh

Hi guys!! :) 
Could you tell us if this feature request is being considered on short term dev & releases? 
For our platform, this "max_execution_time limit" for any kind of statement is needed, mostly on "create temporary table as select" statements because we rely on many dynamic temp table creations but we need also to have max_execution_time there. 

Just tested on 8.0.26 and

create temporary table tmp_version (id int) as SELECT /*+ MAX_EXECUTION_TIME(1) */ VERSION from INFORMATION_SCHEMA.TABLES;

is not returning "Query execution was interrupted" as it should for it's select part;

when the select itself does

mysql> SELECT /*+ MAX_EXECUTION_TIME(1) */ VERSION from INFORMATION_SCHEMA.TABLES;;
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

We appreciate any news :)

Best regards.