pingcap / pingcap/tidb

The selection on a single table cannot use the plan cache due to predicate simplification

Open
#63,754 0 comments 0 reactions 1 assignee Claimed by @fixdb View on GitHub
sig/planner type/enhancement
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.