Prepare statement execution plan cache does not support CTE

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

Original topic: Prepare 语句执行计划缓存不支持CTE

| username: TiDB_C罗

【TiDB Usage Environment】Production Environment
【TiDB Version】
【Reproduction Path】What operations were performed when the issue occurred
【Encountered Issue: Issue Phenomenon and Impact】
【Resource Configuration】
Background: There are slow queries online, and generating execution plans takes a long time, as shown below

The prepared-plan-cache.enabled has already been enabled.
Tested, prepare/stmt does not support CTE.

CREATE TABLE `a` (
  `name` varchar(20) DEFAULT NULL,
  `v_int` int(11) DEFAULT NULL,
  `v_decimal` decimal(10,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

insert into a values("a",18,100),("b",19,200),("c",20,300);
mysql>prepare stmt1 from 'with tmpa as (select name from a where name =?) select * from tmpa';
Query OK, 0 rows affected (0.00 sec)
mysql>prepare stmt2 from 'select name from (select name from a where name =?) tmpa';
Query OK, 0 rows affected (0.00 sec)
mysql> set @user="a" ;
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt1 using @user;
+------+
| name |
+------+
| a    |
+------+
1 row in set (0.01 sec)

mysql> execute stmt1 using @user;
+------+
| name |
+------+
| a    |
+------+
1 row in set (0.00 sec)

mysql> select @@last_plan_from_cache;
+------------------------+
| @@last_plan_from_cache |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.00 sec)

mysql> execute stmt2 using @user;
+------+
| name |
+------+
| a    |
+------+
1 row in set (0.01 sec)

mysql> execute stmt2 using @user;
+------+
| name |
+------+
| a    |
+------+
1 row in set (0.00 sec)

mysql> select @@last_plan_from_cache;
+------------------------+
| @@last_plan_from_cache |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)
| username: redgame | Original post link

(prepare/stmt) currently does not support CTE

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.