Different results depending on query hint

[dveeden]

Bug Report

1. Minimal reproduce step (Required)

WITH RECURSIVE cte AS (SELECT 1 n UNION ALL SELECT n+1+sleep(n) FROM cte WHERE n<5) SELECT SUM(n) FROM cte;
WITH RECURSIVE cte AS (SELECT 1 n UNION ALL SELECT n+1+sleep(n) FROM cte WHERE n<5) SELECT /*+ MAX_EXECUTION_TIME(9000) */ SUM(n) FROM cte;
WITH RECURSIVE cte AS (SELECT 1 n UNION ALL SELECT n+1+sleep(n) FROM cte WHERE n<5) SELECT /*+ MAX_EXECUTION_TIME(1) */ SUM(n) FROM cte;

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

This is what MySQL 8.0.23 returns:

mysql> WITH RECURSIVE cte AS (SELECT 1 n UNION ALL SELECT n+1+sleep(n) FROM cte WHERE n<5) SELECT SUM(n) FROM cte;
+--------+
| SUM(n) |
+--------+
|     15 |
+--------+
1 row in set (10.00 sec)

mysql> WITH RECURSIVE cte AS (SELECT 1 n UNION ALL SELECT n+1+sleep(n) FROM cte WHERE n<5) SELECT /*+ MAX_EXECUTION_TIME(9000) */ SUM(n) FROM cte;
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded
mysql> WITH RECURSIVE cte AS (SELECT 1 n UNION ALL SELECT n+1+sleep(n) FROM cte WHERE n<5) SELECT /*+ MAX_EXECUTION_TIME(1) */ SUM(n) FROM cte;
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.32    |
+-----------+
1 row in set (0.00 sec)

3. What did you see instead (Required)

sql> WITH RECURSIVE cte AS (SELECT 1 n UNION ALL SELECT n+1+sleep(n) FROM cte WHERE n<5) SELECT SUM(n) FROM cte;
+--------+
| SUM(n) |
+--------+
|     15 |
+--------+
1 row in set (10.0047 sec)

sql> WITH RECURSIVE cte AS (SELECT 1 n UNION ALL SELECT n+1+sleep(n) FROM cte WHERE n<5) SELECT /*+ MAX_EXECUTION_TIME(9000) */ SUM(n) FROM cte;
+--------+
| SUM(n) |
+--------+
|     16 |
+--------+
1 row in set (9.0637 sec)

sql> WITH RECURSIVE cte AS (SELECT 1 n UNION ALL SELECT n+1+sleep(n) FROM cte WHERE n<5) SELECT /*+ MAX_EXECUTION_TIME(1) */ SUM(n) FROM cte;
+--------+
| SUM(n) |
+--------+
|      9 |
+--------+
1 row in set (0.1138 sec)

sql> SELECT TIDB_VERSION()\G
*************************** 1. row ***************************
TIDB_VERSION(): Release Version: v7.0.0
Edition: Community
Git Commit Hash: 7376954cd868dbc44fc3015c9ef89c53749339a7
Git Branch: heads/refs/tags/v7.0.0
UTC Build Time: 2023-03-29 13:32:13
GoVersion: go1.20.2
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv
1 row in set (0.0012 sec)

4. What is your TiDB version? (Required)

Release Version: v7.0.0
Edition: Community
Git Commit Hash: 7376954cd868dbc44fc3015c9ef89c53749339a7
Git Branch: heads/refs/tags/v7.0.0
UTC Build Time: 2023-03-29 13:32:13
GoVersion: go1.20.2
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv
1 row in set (0.0012 sec)

[dveeden]

Note that SLEEP() will have a different return value if it was interrupted (both in MySQL and TiDB)

mysql> SELECT /*+ MAX_EXECUTION_TIME(1) */ SLEEP(1);
+----------+
| SLEEP(1) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT /*+ MAX_EXECUTION_TIME(2000) */ SLEEP(1);
+----------+
| SLEEP(1) |
+----------+
|        0 |
+----------+
1 row in set (1.00 sec)

[dveeden]

Might be the same issue as #34344