Bug #77459 Harmonize MAX_EXECUTION_TIME hint with MAX_STATEMENT_TIME

• Submitted: 23 Jun 2015 18:43
• Modified: 19 Jul 2015 17:16
• Reporter: Gleb Shchepa
• Status: Closed
• Category: MySQL Server: Parser
• Severity: S3 (Non-critical)
• Version: 5.7
• OS: Any
• CPU Architecture: Any

[23 Jun 2015 18:43] Gleb Shchepa

Description:
The new-style MAX_EXECUTION_TIME hint is a reimplemented interface for the old-style MAX_STATEMENT_TIME hint:

Old-style: SELECT MAX_STATEMENT_TIME=10 ...
New-style: SELECT /*+ MAX_EXECUTION_TIME(10) */ ...

Obviously, the new interface should implement the same functionality as the original hint (the only exclusion is: new-style optimizer hints fail with a warning instead of a error).

However, there are some unexpected differences:

1. From WL#6936 "Implementation of server-side statement timeout":

  F-7: The MAX_STATEMENT_TIME clause is allowed for top-level SELECT
       statements only....

But the new MAX_EXECUTION_TIME is acceptable in subqueries as well as in top-level SELECTs:

mysql> SELECT 1 FROM (SELECT /*+ MAX_EXECUTION_TIME(10) */ 1) a;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0,00 sec)
# no warnings!

2. From WL#6936:

  F-8: The MAX_STATEMENT_TIME clause is not allowed in SELECT statements
       within stored programs (stored procedures, stored functions, triggers,
       events).

OTOH MAX_EXECUTION_TIME is allowed in SPs etc.:

mysql> DELIMITER |
mysql> CREATE FUNCTION f1() RETURNS INT BEGIN SELECT /*+ MAX_EXECUTION_TIME(10) */ 1 INTO @a; RETURN 1; END|
Query OK, 0 rows affected (0,00 sec)
# no warnings!

How to repeat:
# issue #1
SELECT 1 FROM (SELECT /*+ MAX_EXECUTION_TIME(10) */ 1) a;

# issue #2
DELIMITER |
CREATE FUNCTION f1() RETURNS INT BEGIN SELECT /*+ MAX_EXECUTION_TIME(10) */ 1 INTO @a; RETURN 1; END|

Suggested fix:
diff --git a/sql/parse_tree_hints.cc b/sql/parse_tree_hints.cc
index 2cd9973..f71d0d6 100644
--- a/sql/parse_tree_hints.cc
+++ b/sql/parse_tree_hints.cc
@@ -417,14 +417,20 @@ bool PT_hint_max_execution_time::contextualize(Parse_context *pc)
   if (super::contextualize(pc))
     return true;
 
-  if (pc->thd->lex->sql_command != SQLCOM_SELECT)
+  if (pc->thd->lex->sql_command != SQLCOM_SELECT || // not a SELECT statement
+      pc->thd->lex->sphead ||                       // or in a SP/trigger/event
+      pc->select != pc->thd->lex->select_lex)       // or in a subquery
+  {
     push_warning(pc->thd, Sql_condition::SL_WARNING,
                  ER_WARN_UNSUPPORTED_MAX_EXECUTION_TIME,
                  ER_THD(pc->thd, ER_WARN_UNSUPPORTED_MAX_EXECUTION_TIME));
+    return false;
+  }
 
   Opt_hints_global *global_hint= get_global_hints(pc);
   if (global_hint->is_specified(type()))
   {
+    // Hint duplication: /*+ MAX_EXECUTION_TIME ... MAX_EXECUTION_TIME */
     print_warn(pc->thd, ER_WARN_CONFLICTING_HINT,
                NULL, NULL, NULL, this);
     return false;

[19 Jul 2015 17:16] Paul DuBois

Noted in 5.7.8 changelog.

The MAX_STATEMENT_TIME option for SELECT statements was removed
because its functionality is now available using the more general
optimizer hint syntax (see ). Statements that begin like this:

SELECT MAX_STATEMENT_TIME = N ...
      
Should be rewritten to begin like this:

SELECT /*+ MAX_EXECUTION_TIME(N) */ ...

There are some minor implementation differences between the two.
MAX_STATEMENT_TIME was not permitted in non-top-level SELECT
statements such as subqueries, or in stored programs, and produced an
error. MAX_EXECUTION_TIME() is permitted in those contexts, but is
ignored.