wrong result when point get new collation column for unknown reason

[time-and-fate]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

start tidb with real tikv and with new collation turned on.

backup.zip

unzip the file and restore it using BR:

./br restore full --pd "PDIP:2379" -s "local:///path/to/this/backup"

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

get correct result

3. What did you see instead (Required)

got different results of the same row

root:4000[clustered_index_go_c]> select col_7, col_8 from tbl_1 where col_7 = 1124440639;
+------------+-------+
| col_7      | col_8 |
+------------+-------+
| 1124440639 | J    |
+------------+-------+
1 row in set (0.005 sec)

root:4000[clustered_index_go_c]> select col_7, col_8 from tbl_1  where col_7 > 1124440630 and col_7 < 1124440640;
+------------+-------+
| col_7      | col_8 |
+------------+-------+
| 1124440639 | B     |
+------------+-------+
1 row in set (0.004 sec)

root:4000[clustered_index_go_c]> explain select col_7, col_8 from tbl_1  where col_7 > 1124440630 and col_7 < 1124440640;
+------------------------+---------+-----------+---------------------------------+---------------------------------------------------------------+
| id                     | estRows | task      | access object                   | operator info                                                 |
+------------------------+---------+-----------+---------------------------------+---------------------------------------------------------------+
| IndexReader_6          | 250.00  | root      |                                 | index:IndexRangeScan_5                                        |
| └─IndexRangeScan_5     | 250.00  | cop[tikv] | table:tbl_1, index:idx_2(col_7) | range:(1124440630,1124440640), keep order:false, stats:pseudo |
+------------------------+---------+-----------+---------------------------------+---------------------------------------------------------------+
2 rows in set (0.002 sec)

root:4000[clustered_index_go_c]> explain select col_7, col_8 from tbl_1 where col_7 = 1124440639;
+-------------+---------+------+---------------------------------+---------------+
| id          | estRows | task | access object                   | operator info |
+-------------+---------+------+---------------------------------+---------------+
| Point_Get_1 | 1.00    | root | table:tbl_1, index:idx_2(col_7) |               |
+-------------+---------+------+---------------------------------+---------------+
1 row in set (0.001 sec)

4. What is your TiDB version? (Required)

root:4000[clustered_index_go_c]> select tidb_version();
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                                     |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v5.0.0-nightly
Edition: Community
Git Commit Hash: e2740f54b6b29d99f3396bed4e66779549becf75
Git Branch: heads/refs/tags/v5.0.0-nightly
UTC Build Time: 2021-04-02 16:25:23
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.021 sec)

[time-and-fate]

It's very strange that you can't reproduce this issue after

create table tt like tbl_1;
insert into tt select * from tbl_1;

You also can't reproduce this issue after dumping the table into .sql file and restore it.

[lysu]

It's very strange that you can't reproduce this issue after

create table tt like tbl_1;
insert into tt select * from tbl_1;

You also can't reproduce this issue after dumping the table into .sql file and restore it.

how about

set global tidb_row_format_version=1 or 2

and reconnect & retry

[time-and-fate]

It's very strange that you can't reproduce this issue after

create table tt like tbl_1;
insert into tt select * from tbl_1;

You also can't reproduce this issue after dumping the table into .sql file and restore it.

how about

set global tidb_row_format_version=1 or 2

and reconnect & retry

Currently the tidb_row_format_version is 2. I set it to 1 and reconnect & retry, the issue still exists.

[time-and-fate]

The original SQL which makes the test fail is different from the issue description.
The original output of the rand test: a.log
(clustered index and new collation are both enabled when testing)

I extract SQLs from the output (a.sql.txt)
and tried to reproduce the issue by source a.sql; but also failed.

[wshwsh12]

/assign @lzmhhh123

[wjhuang2016]

This the because the new cluster doesn't enable the new collation. After enabling the new collation, it gets the correct result.

[wjhuang2016]

Not a bug.