prepare execute got wrong result

[aytrack]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

drop table ta;
create table ta (a char(10) collate utf8mb4_general_ci not null, b tinyint, primary key(a(5), b));
prepare stmt from 'select * from ta where b between ? and ? AND a >= ?;';
insert into ta values ('a', 1), ('b', 2), ('c', 3), ('B', 4), ('A', 5);
set @a=2, @b=2, @c='a';
execute stmt using @a,@b,@c;
set @a=2, @b=10, @c='a';
execute stmt using @a,@b,@c;
select * from ta where b between 2 and 10 AND a >= 'a';

It similar to #23390, but I encounter this after #23238 merged

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

mysql > set @a=2, @b=10, @c='a';
Query OK, 0 rows affected
Time: 0.049s
mysql > execute stmt using @a,@b,@c;
+---+---+
| a | b |
+---+---+
| b | 2 |
| c | 3 |
| B | 4 |
| A | 5 |
+---+---+

3. What did you see instead (Required)

mysql > set @a=2, @b=10, @c='a';
Query OK, 0 rows affected
Time: 0.049s
mysql > execute stmt using @a,@b,@c;
+---+---+
| a | b |
+---+---+
| b | 2 |
+---+---+

4. What is your TiDB version? (Required)

tidb_version() | Release Version: v4.0.0-beta.2-2477-g149440854-dirty
Edition: Community
Git Commit Hash: 149440854e539ba5e674109f96af13cf9e34fe7f
Git Branch: master
UTC Build Time: 2021-03-26 13:48:42
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[qw4990]

I'll investigate and try to fix it soon.

[qw4990]

I cannot reproduce this issue:

mysql> select version();
+-------------------------------------------------+
| version()                                       |
+-------------------------------------------------+
| 5.7.25-TiDB-v4.0.0-beta.2-2477-g149440854-dirty |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> create table ta (a char(10) collate utf8mb4_general_ci not null, b tinyint, primary key(a(5), b));
Query OK, 0 rows affected (0.00 sec)

mysql> prepare stmt from 'select * from ta where b between ? and ? AND a >= ?;';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into ta values ('a', 1), ('b', 2), ('c', 3), ('B', 4), ('A', 5);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> set @a=2, @b=2, @c='a';
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt using @a,@b,@c;
+---+---+
| a | b |
+---+---+
| b | 2 |
+---+---+
1 row in set (0.00 sec)

mysql> set @a=2, @b=10, @c='a';
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt using @a,@b,@c;
+---+---+
| a | b |
+---+---+
| b | 2 |
| c | 3 |
| B | 4 |
| A | 5 |
+---+---+
4 rows in set (0.00 sec)

I guess it's probably because your TiDB didn't enable new_collations_enabled_on_first_bootstrap.
Could you please confirm it again? @aytrack

[xuyifangreeneyes]

When I disable new_collations_enabled_on_first_bootstrap,

mysql> SELECT VARIABLE_VALUE FROM mysql.tidb WHERE VARIABLE_NAME='new_collation_enabled';
+----------------+
| VARIABLE_VALUE |
+----------------+
| False          |
+----------------+
1 row in set (0.00 sec)

mysql> create table ta (a char(10) collate utf8mb4_general_ci not null, b tinyint, primary key(a(5), b));
Query OK, 0 rows affected (0.01 sec)

mysql> prepare stmt from 'select * from ta where b between ? and ? AND a >= ?;';
Query OK, 0 rows affected (0.01 sec)

mysql> insert into ta values ('a', 1), ('b', 2), ('c', 3), ('B', 4), ('A', 5);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from ta where b between 2 and 10 AND a >= 'a';
+---+---+
| a | b |
+---+---+
| b | 2 |
| c | 3 |
+---+---+
2 rows in set (0.00 sec)

mysql> set @a=2, @b=10, @c='a';
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt using @a,@b,@c;
+---+---+
| a | b |
+---+---+
| b | 2 |
| c | 3 |
+---+---+
2 rows in set (0.00 sec)

mysql> select version();
+-------------------------------------------+
| version()                                 |
+-------------------------------------------+
| 5.7.25-TiDB-v4.0.0-beta.2-2514-g55580eef8 |
+-------------------------------------------+
1 row in set (0.00 sec)

I guess that it is normal?

[qw4990]

The root cause is that TiDB cached an invalid plan:
After finishing the first exec statement, TiDB got and cached a plan TableReader(Table(ta)->Sel([eq(test.ta.b, 2) ge(test.ta.a, a)])).
And you can see in this plan, the between expression including @a and @b are merged into an eq expression eq(b, 2).
When processing the second exec statement, the cached plan is used again, and since the converted eq expression only contains one variable, only @a can be applied to this plan.

I'll try to fix this soon.

[ti-srebot]

Please edit this comment or add a new comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions