planner: Plan Cache returns unexpected error when encountering large negative number

[YangKeao]

Bug Report

It has two issues:

1. The behavior is not compatible with MySQL. MySQL didn't return a warning, but TiDB did.
2. The behavior is not consistent with or without plan cache. With plan cache, it returns an error but not warning.

1. Minimal reproduce step (Required)

create table t (v bigint);
prepare stmt5 from 'select * from t where v = -?;';
set @arg=1;
execute stmt5 using @arg;
set @arg=-9223372036854775808;
execute stmt5 using @arg;

2. What did you expect to see? (Required)

mysql> create table t (v bigint);
Query OK, 0 rows affected (0.06 sec)

mysql> prepare stmt5 from 'select * from t where v = -?;';
Query OK, 0 rows affected (0.01 sec)
Statement prepared

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

mysql> execute stmt5 using @arg;
Empty set (0.00 sec)

mysql> set @arg=-9223372036854775808;
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt5 using @arg;
Empty set (0.00 sec)

3. What did you see instead (Required)

mysql> create table t (v bigint);
Query OK, 0 rows affected (0.11 sec)

mysql> prepare stmt5 from 'select * from t where v = -?;';
Query OK, 0 rows affected (0.00 sec)

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

mysql> execute stmt5 using @arg;
Empty set (0.01 sec)

mysql> set @arg=-9223372036854775808;
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt5 using @arg;
ERROR 1815 (HY000): expression eq(test.t.v, unaryminus(-9223372036854775808)) cannot be pushed down

However, if you execute the second statement without plan cache, it'll return a warning but not error

mysql> prepare stmt5 from 'select * from t where v = -?;';
Query OK, 0 rows affected (0.00 sec)

mysql> set @arg=-9223372036854775808;
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt5 using @arg;
Empty set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                          |
+---------+------+------------------------------------------------------------------------------------------------------------------+
| Warning | 1105 | skip prepared plan-cache: 'unaryminus(cast(-9223372036854775808, decimal(20,0) BINARY))' may be converted to INT |
+---------+------+------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

4. What is your TiDB version? (Required)

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                               |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v8.0.0
Edition: Community
Git Commit Hash: 8ba1fa452b1ccdbfb85879ea94b9254aabba2916
Git Branch: HEAD
UTC Build Time: 2024-03-28 14:22:15
GoVersion: go1.21.4
Race Enabled: false
Check Table Before Drop: false
Store: tikv |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[qw4990]

The root cause is that the first cached plan created with @arg=1 can't be reused for the second argument @arg=-9223372036854775808, and for safety the Plan Cache returns an error directly.

In the first plan below, we can see it uses eq(test.t.v, -1) to represent v=-1, but for the second one, we have to use cast inside the eq, which means the first plan is not compatible with the second argument.

mysql> explain select * from t where v = -1;
+-------------------------+----------+-----------+---------------+--------------------------------+
| id                      | estRows  | task      | access object | operator info                  |
+-------------------------+----------+-----------+---------------+--------------------------------+
| TableReader_7           | 10.00    | root      |               | data:Selection_6               |
| └─Selection_6           | 10.00    | cop[tikv] |               | eq(test.t.v, -1)               |
|   └─TableFullScan_5     | 10000.00 | cop[tikv] | table:t       | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+---------------+--------------------------------+

mysql> explain select * from t where v = --9223372036854775808;
+-------------------------+----------+-----------+---------------+---------------------------------------------------------------+
| id                      | estRows  | task      | access object | operator info                                                 |
+-------------------------+----------+-----------+---------------+---------------------------------------------------------------+
| TableReader_7           | 8000.00  | root      |               | data:Selection_6                                              |
| └─Selection_6           | 8000.00  | cop[tikv] |               | eq(cast(test.t.v, decimal(20,0) BINARY), 9223372036854775808) |
|   └─TableFullScan_5     | 10000.00 | cop[tikv] | table:t       | keep order:false, stats:pseudo                                |
+-------------------------+----------+-----------+---------------+---------------------------------------------------------------+

This is an extreme case, we'll degrade this bug to severity/minor.