range partition pruning does not work when plan cache is enabled

[eurekaka]

Development Task

mysql> CREATE TABLE employees (id INT NOT NULL, name VARCHAR(30)) PARTITION BY RANGE (id) ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into employees values(2, 'dddd'), (10, 'kkk');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> prepare stmt from 'select * from employees where id < ?';
Query OK, 0 rows affected (0.00 sec)

mysql> set @a=3;
Query OK, 0 rows affected (0.01 sec)

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.00 sec)

mysql> execute stmt using @a;
+----+------+
| id | name |
+----+------+
|  2 | dddd |
+----+------+
1 row in set (0.00 sec)

mysql> explain for connection 1;
+-----------------------------+----------+-----------+-------------------------------+--------------------------------+
| id                          | estRows  | task      | access object                 | operator info                  |
+-----------------------------+----------+-----------+-------------------------------+--------------------------------+
| Union_8                     | 16000.00 | root      |                               |                                |
| ├─TableReader_11            | 8000.00  | root      |                               | data:Selection_10              |
| │ └─Selection_10            | 8000.00  | cop[tikv] |                               | lt(cast(test.employees.id), 3) |
| │   └─TableFullScan_9       | 10000.00 | cop[tikv] | table:employees, partition:p0 | keep order:false, stats:pseudo |
| └─TableReader_14            | 8000.00  | root      |                               | data:Selection_13              |
|   └─Selection_13            | 8000.00  | cop[tikv] |                               | lt(cast(test.employees.id), 3) |
|     └─TableFullScan_12      | 10000.00 | cop[tikv] | table:employees, partition:p1 | keep order:false, stats:pseudo |
+-----------------------------+----------+-----------+-------------------------------+--------------------------------+
7 rows in set (0.00 sec)

partition p1 should be pruned as the behavior when plan cache is disabled.

[eurekaka]

RCA: prepared parameters are all treated as string types now, so in buildSelection for id < ?, if plan cache is disabled, refineArgs would convert string type 3 to a int constant, while if plan cache is enabled, refineArgs is skipped due to #14120, and then getFunction infers that the compare type of the two arguments is ETReal, so id < ? is built as cast(id as real) < 3.0, therefore this filter is not chosen as partition pruner later.

We have 2 approaches to fix this problem:

• differentiate types for prepared parameters, this solves the problem from the beginning;
• extract logics in refineArgs to a builtin scalar function, and save this function as DeferredExpr in the refined constant, so that it can be re-evaluated when the prepared parameter is changed;

I prefer the first one, the second one is pretty tricky.

Quick workaround for this problem: add a explicit cast() for the prepared parameter in the query like:

mysql> prepare stmt from 'select * from employees where id < cast(? as signed)';
Query OK, 0 rows affected (0.00 sec)

mysql> set @a=3;
Query OK, 0 rows affected (0.00 sec)

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.00 sec)

mysql> execute stmt using @a;
+----+------+
| id | name |
+----+------+
|  2 | dddd |
+----+------+
1 row in set (0.00 sec)

mysql> explain for connection 1;
+-------------------------+---------+-----------+-------------------------------+--------------------------------+
| id                      | estRows | task      | access object                 | operator info                  |
+-------------------------+---------+-----------+-------------------------------+--------------------------------+
| TableReader_8           | 0.33    | root      |                               | data:Selection_7               |
| └─Selection_7           | 0.33    | cop[tikv] |                               | lt(test.employees.id, 3)       |
|   └─TableFullScan_6     | 1.00    | cop[tikv] | table:employees, partition:p0 | keep order:false, stats:pseudo |
+-------------------------+---------+-----------+-------------------------------+--------------------------------+
3 rows in set (0.00 sec)

[eurekaka]

This problem can be fixed by #16375, since it marks queries containing partition tables as un-cacheable, i.e, UseCache is set false so refineArgs would not be skipped anymore even if plan cache is enabled.