the results is unstable

[ChenPeng2013]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

use test;
source [test.txt](https://github.com/pingcap/tidb/files/8242651/test.txt)
 rename table IDT_26427 to IDT_26428;
CREATE TABLE `IDT_26427` (   `COL102` int(11) DEFAULT NULL,   `COL103` int(11) DEFAULT NULL,   `COL1` int(11) GENERATED ALWAYS AS (`COL102` - 10) VIRTUAL,   `COL2` varchar(20) DEFAULT NULL,   `COL4` datetime DEFAULT NULL,   `COL3` bigint(20) DEFAULT NULL,   `COL5` float DEFAULT NULL,   KEY `UK_COL1` (`COL1`) /*!80000 INVISIBLE */ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
insert into IDT_26427(col102, col103, col2, col4, col3, col5) select col102, col103, col2, col4, col3, col5 from IDT_26428;
alter table IDT_26427 cache;

# you can execute many time to reproduce
select * from IDT_26427 where col3 between 9183752530835879216 and -5217915181309793614 or col1 in (1789240358, 1957533766, 1957533766) and col2 not between "鹖躅鐕幧轇璆屹忐薦篌鲙睆梐鎐郙謮蕿廌讼仭" and "XvfosWb69DZX4V0Y";

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

the results is stable

3. What did you see instead (Required)

MySQL [test]> select * from IDT_26427 where col3 between 9183752530835879216 and -5217915181309793614 or col1 in (1789240358, 1957533766, 1957533766) and col2 not between "鹖躅鐕幧轇璆屹忐薦篌鲙睆梐鎐郙謮蕿廌讼仭" and "XvfosWb69DZX4V0Y";
+------------+-------------+------------+--------------------------------------------------------------+---------------------+----------------------+-------------+
| COL102     | COL103      | COL1       | COL2                                                         | COL4                | COL3                 | COL5        |
+------------+-------------+------------+--------------------------------------------------------------+---------------------+----------------------+-------------+
| 1789240368 | -1180677962 | 1789240358 | 璒鄵湮圪獵璩霦獁荾迻赦o韺畬ȿ退滃凂爟阣    | 8757-12-13 18:40:14 |  1676216278663111876 | -8.97071e37 |
| 1957533776 |   844649914 | 1957533766 | 船絴ᚸ帆穡搴甌俽琛壈裩迟苐澴曳糿簫夤篂茞 | 2775-10-05 13:19:16 | -4221247225604105648 | -2.27761e38 |
+------------+-------------+------------+--------------------------------------------------------------+---------------------+----------------------+-------------+
2 rows in set (0.00 sec)

MySQL [test]> select * from IDT_26427 where col3 between 9183752530835879216 and -5217915181309793614 or col1 in (1789240358, 1957533766, 1957533766) and col2 not between "鹖躅鐕幧轇璆屹忐薦篌鲙睆梐鎐郙謮蕿廌讼仭" and "XvfosWb69DZX4V0Y";
+------------+-------------+------------+-----------------------------------------------------------+---------------------+---------------------+-------------+
| COL102     | COL103      | COL1       | COL2                                                      | COL4                | COL3                | COL5        |
+------------+-------------+------------+-----------------------------------------------------------+---------------------+---------------------+-------------+
| 1789240368 | -1180677962 | 1789240358 | 璒鄵湮圪獵璩霦獁荾迻赦o韺畬ȿ退滃凂爟阣 | 8757-12-13 18:40:14 | 1676216278663111876 | -8.97071e37 |
+------------+-------------+------------+-----------------------------------------------------------+---------------------+---------------------+-------------+
1 row in set (0.00 sec)

4. What is your TiDB version? (Required)

Release Version: v6.0.0-alpha-73-ga79be59
Edition: Community
Git Commit Hash: a79be59293f279f5fa539365430815d146a00bda
Git Branch: master
UTC Build Time: 2022-03-14 06:24:31
GoVersion: go1.17.6
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[tiancaiamao]

OK, it's a bug and I've located the root cause...

[tiancaiamao]

Since this is a generated column bug, instead of a cache table bug, I remove the feature/developing label.

[tiancaiamao]

A simpler reproduce:

CREATE TABLE `yy` (
  `id` int(11) DEFAULT NULL,
  `c` int(11) GENERATED ALWAYS AS (`id`) VIRTUAL
)

begin;
insert into yy(id) values (1),(2),(3),(4);
insert into yy(id) select id from yy; 
insert into yy(id) select id from yy; 
... repeat several times, until the rows count > 32
insert into yy(id) values (5); 

select * from  yy  where c = 5

Expected:

mysql> select * from  yy  where c = 5;
+------+------+
| id   | c    |
+------+------+
|    5 |    5 |
+------+------+
1 row in set (0.00 sec)

But get:

mysql> select * from  yy  where c = 5;
Empty set (0.00 sec)

[bb7133]

Change it to 'major' since generated column is experimental.