Set_var is ineffective in subqueries

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: set_var在子查询中失效

| username: dba-kit

As the topic states, when testing SET_VAR, it was found that using this hint in a subquery does not take effect.

mysql> SELECT /*+ SET_VAR(MAX_EXECUTION_TIME=1234) */ @@MAX_EXECUTION_TIME;
+----------------------+
| @@MAX_EXECUTION_TIME |
+----------------------+
|                 1234 |
+----------------------+
1 row in set (0.01 sec)

mysql> SELECT /*+ SET_VAR(MAX_EXECUTION_TIME=1234) */ @@MAX_EXECUTION_TIME, a.inner_time from (select /*+ SET_VAR(MAX_EXECUTION_TIME=12345) */ @@MAX_EXECUTION_TIME as inner_time) a;
+----------------------+------------+
| @@MAX_EXECUTION_TIME | inner_time |
+----------------------+------------+
|                 1234 |       1234 |
+----------------------+------------+
1 row in set (0.00 sec)

mysql> SELECT a.inner_time from (select /*+ SET_VAR(MAX_EXECUTION_TIME=12345) */ @@MAX_EXECUTION_TIME as inner_time) a;
+------------+
| inner_time |
+------------+
|     600000 |
+------------+
1 row in set (0.00 sec)
2
| username: dba-kit | Original post link

Based on the description, SET_VAR is a query block scope hint, not a query scope effective hint, so it should be a BUG.

3
| username: Billmay表妹 | Original post link

I raised an issue