tidb_opt_enable_semi_join_rewrite is ignored once the rewritten plan gets cached #64338
Description
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
mysql --host 127.0.0.1 --port 4000 -u root <<'SQL'
CREATE DATABASE IF NOT EXISTS semi_join_rewrite_test;
USE semi_join_rewrite_test;
DROP TABLE IF EXISTS test_exists_a;
DROP TABLE IF EXISTS test_exists_b;
CREATE TABLE test_exists_a (a INT PRIMARY KEY);
CREATE TABLE test_exists_b (a INT PRIMARY KEY);
INSERT INTO test_exists_a VALUES (1),(2);
INSERT INTO test_exists_b VALUES (1);
SET @@tidb_enable_plan_cache_for_subquery = 1;
SET @@tidb_opt_enable_semi_join_rewrite = 1;
PREPARE stmt FROM
'SELECT * FROM test_exists_a
WHERE EXISTS (SELECT 1 FROM test_exists_b WHERE test_exists_b.a = test_exists_a.a)';
EXECUTE stmt;
SELECT @@last_plan_from_cache AS first_plan_from_cache; -- returns 0
SET @@tidb_opt_enable_semi_join_rewrite = 0;
EXECUTE stmt;
SELECT @@last_plan_from_cache AS second_plan_from_cache; -- returns 1
2. What did you expect to see? (Required)
The plan cache should distinguish between ON/OFF values of tidb_opt_enable_semi_join_rewrite (e.g., include the boolean in the cache key), or skip caching when the variable is enabled. After switching the variable OFF, the next EXECUTE
should fall back to the normal semi-join plan instead of continuing to run the cached inner-join plan.
3. What did you see instead (Required)
- With the variable turned ON, a plain EXPLAIN shows that the EXISTS subquery is indeed rewritten into an inner join:
mysql> SET @@tidb_opt_enable_semi_join_rewrite = 1;
mysql> EXPLAIN FORMAT='brief' SELECT ...;
+-----------+---------+------+--------------+------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access obj | operator info |
+-----------+---------+------+--------------+------------------------------------------------------------------------------------------------------------------------+
| IndexJoin | 1.25 | root | | inner join, inner:TableReader, outer key:test_exists_b.a, inner key:test_exists_a.a, equal cond:eq(test_exists_b.a, …) |
| ├─StreamAgg(Build) …
| └─TableReader(Probe) …
+-----------+---------+------+--------------+------------------------------------------------------------------------------------------------------------------------+
- Turning the variable OFF and running EXPLAIN again reverts the plan back to a normal semi join, as expected:
mysql> SET @@tidb_opt_enable_semi_join_rewrite = 0;
mysql> EXPLAIN FORMAT='brief' SELECT ...;
+-----------+---------+------+--------------+----------------------------------------------------------------------------------------------------------------------+
| IndexJoin | 1.60 | root | | semi join, inner:TableReader, outer key:test_exists_a.a, inner key:test_exists_b.a, equal cond:eq(test_exists_a.a, …) |
| ├─TableReader(Build) …
| └─TableReader(Probe) …
+-----------+---------+------+--------------+----------------------------------------------------------------------------------------------------------------------+
- However, following the reproduction script:
mysql> EXECUTE stmt;
+---+
| a |
+---+
| 1 |
+---+
mysql> SELECT @@last_plan_from_cache AS first_plan_from_cache;
+-----------------------+
| first_plan_from_cache |
+-----------------------+
| 0 | <-- first execution builds & caches the rewritten plan
+-----------------------+
mysql> SET @@tidb_opt_enable_semi_join_rewrite = 0;
mysql> EXECUTE stmt;
+---+
| a |
+---+
| 1 |
+---+
mysql> SELECT @@last_plan_from_cache AS second_plan_from_cache;
+------------------------+
| second_plan_from_cache |
+------------------------+
| 1 | <-- cached inner-join plan reused even though the variable is OFF
+------------------------+
So once the rewritten plan is cached with the variable ON, setting the variable to OFF afterwards has no effect—the cached inner-join plan keeps being reused, even though a fresh optimize would produce a semi join.
4. What is your TiDB version? (Required)
v8.5.4-pre