set_var not works for the subquery

[aytrack]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

SELECT /*+ SET_VAR(MAX_EXECUTION_TIME=1234) */ @@MAX_EXECUTION_TIME;
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;
SELECT a.inner_time from (select /*+ SET_VAR(MAX_EXECUTION_TIME=12345) */ @@MAX_EXECUTION_TIME as inner_time) a;

2. What did you expect to see? (Required)

set_var affect the subquery

3. What did you see instead (Required)

set_var not works for the subquery

[14:55:54]TiDB root:test> SELECT /*+ SET_VAR(MAX_EXECUTION_TIME=1234) */ @@MAX_EXECUTION_TIME;
+----------------------+
| @@MAX_EXECUTION_TIME |
+----------------------+
| 1234                 |
+----------------------+
1 row in set
Time: 0.003s
[14:56:04]TiDB root:test> 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
Time: 0.003s
[14:56:12]TiDB root:test> SELECT a.inner_time from (select /*+ SET_VAR(MAX_EXECUTION_TIME=12345) */ @@MAX_EXECUTION_TIME as inner_time) a;
+------------+
| inner_time |
+------------+
| 0          |
+------------+
1 row in set
Time: 0.004s

4. What is your TiDB version? (Required)

[14:58:09]TiDB root:test> select tidb_version();
+-----------------------------------------------------------+
| tidb_version()                                            |
+-----------------------------------------------------------+
| Release Version: v8.1.0                                   |
| Edition: Community                                        |
| Git Commit Hash: da39e1ca55c6051a1cbcd84dc230b8ba42f8075f |
| Git Branch: HEAD                                          |
| UTC Build Time: 2024-05-06 05:55:37                       |
| GoVersion: go1.21.6                                       |
| Race Enabled: false                                       |
| Check Table Before Drop: false                            |
| Store: tikv                                               |
+-----------------------------------------------------------+
1 row in set

[qw4990]

For the second case which we want to set 2 different MAX_EXECUTION_TIME, the result seems acceptable. set_var is used to set variables for a query, we can't set 2 different values for the same variable in a single query, so it seems acceptable that both the outside query and the sub-query return 1234.

[qw4990]

For the third case, the reason is that set_var is kind of specially handled at the optimization phase, but the uncorrelated sub-query is also specially evaluated at the rewriting phase, so the set_var doesn't take effect in this case.
It's hard to handle set_var at the rewriting phase, which can make our code complex and chaos.
I'll update our hint doc to guide the user to avoid this case.
Degrade this issue from Major to Minor.

[qw4990]

Doc will be updated by pingcap/docs-cn#17315