The selection on a single table cannot use the plan cache due to predicate simplification
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Enhancement
The simple reproduction method is as follows:
``` go
func TestABC(t *testing.T) {
store := testkit.CreateMockStore(t)
tk1 := testkit.NewTestKit(t, store)
tk1.MustExec("use test;")
tk1.MustExec(`CREATE TABLE t (
c1 VARCHAR(10),
c2 INT,
c3 VARCHAR(100),
c4 VARCHAR(50),
c5 VARCHAR(50),
c6 DATETIME,
c7 DECIMAL(10,2)
);`)
tk1.MustExec(`prepare stmt from 'select
c1, c2, c3, c4, c5, c6, c7
FROM t
WHERE c1 = 1 AND c2 = 20250922 AND (c5 = ? or ISNULL(?) OR LENGTH(TRIM(?)) < 1)';`)
tk1.MustExec(`set @a = ' ';`)
tk1.MustExec(`set @b = 'abc';`)
tk1.MustQuery(`execute stmt using @b,@a,@a;`).Check(testkit.Rows())
tk1.MustQuery(`show warnings;`).Check(testkit.Rows()) // skip plan cache
}
```
We have found that many customers like to add some short-circuiting expressions in their queries, which ultimately leads to the inability to use the plan cache.
Even if we could force it to be written and then perform secondary optimization on the plan retrieved from the plan cache, the rule would be to select the appropriate index.
Contributor guide
Assessment
This issue has not been assessed yet.