[Bug]: repeatedly execute sql: select count(*) from table{snapshot= "}, the result is inconsistent.

[Ariznawlll]

Is there an existing issue for the same bug?

• [X] I have checked the existing issues.

Branch Name

main

Commit ID

678695151728749f36c28d8f5972758664e4c6fb

Other Environment Information

- Hardware parameters:
- OS type:
- Others:

Actual Behavior

Expected Behavior

No response

Steps to Reproduce

drop database if exists test01;
create database test01;
use test01;

drop table if exists rs01;
create table rs01 (col1 int, col2 decimal(6), col3 varchar(30));
insert into rs01 values (1, null, 'database');
insert into rs01 values (2, 38291.32132, 'database');
insert into rs01 values (3, null, 'database management system');
insert into rs01 values (4, 10, null);
insert into rs01 values (1, -321.321, null);
insert into rs01 values (2, -1, null);
select count(*) from rs01;

drop snapshot if exists sp01;
create snapshot sp01 for account sys;  ->或者create snapshot sp01 for cluster;

select count(*) from rs01 {snapshot = 'sp01'};
insert into rs01 values (2, -1, null);
insert into rs01 values (1, -321.321, null);
select * from rs01;

restore account sys from snapshot sp01;
select count(*) from rs01 {snapshot = 'sp01'};   --> 6
select count(*) from rs01 {snapshot = 'sp01'};   --> 8，多执行几次能出现

集群级别的快照或者account级别的快照都能复现

Additional information

No response

[triump2020]

Not working on this

[triump2020]

Not working on this

[triump2020]

Not working on this

[triump2020]

Not working on this

[triump2020]

Not working on this

[triump2020]

Not working on this

[triump2020]

Not working on this

[triump2020]

Working on other S-1 issue.

[triump2020]

Not working on this.

[triump2020]

Not working on this

[Wenbin1002]

这是一个count(*)问题，正常读数据没有问题。根本原因是对于聚合操作是直接读的block meta，但是对于快照读这样会有问题

[Wenbin1002]

这是必现方法，刷盘后结果为8，正确应该是6

drop database if exists test01;
create database test01;
use test01;

drop table if exists rs01;
create table rs01 (col1 int, col2 decimal(6), col3 varchar(30));
insert into rs01 values (1, null, 'database');
insert into rs01 values (2, 38291.32132, 'database');
insert into rs01 values (3, null, 'database management system');
insert into rs01 values (4, 10, null);
insert into rs01 values (1, -321.321, null);
insert into rs01 values (2, -1, null);
select count(*) from rs01;

drop snapshot if exists sp01;
create snapshot sp01 for account sys;

select count(*) from rs01 {snapshot = 'sp01'};
insert into rs01 values (2, -1, null);
insert into rs01 values (1, -321.321, null);
select * from rs01;

select mo_ctl('dn','flush','test01.rs01');

restore account sys from snapshot sp01;
select count(*) from rs01 {snapshot = 'sp01'};

[triump2020]

@Ariznawlll It still exists in 1.2-dev , i 'll cherry pick the fix.

[Ariznawlll]

测试case

drop database if exists test01;
create database test01;
use test01;

drop table if exists rs01;
create table rs01 (col1 int, col2 decimal(6), col3 varchar(30));
insert into rs01 values (1, null, 'database');
insert into rs01 values (2, 38291.32132, 'database');
insert into rs01 values (3, null, 'database management system');
insert into rs01 values (4, 10, null);
insert into rs01 values (1, -321.321, null);
insert into rs01 values (2, -1, null);
select count(*) from rs01;

drop snapshot if exists sp01;
create snapshot sp01 for account sys;

select count(*) from rs01 {snapshot = 'sp01'};
insert into rs01 values (2, -1, null);
insert into rs01 values (1, -321.321, null);
select * from rs01;

select mo_ctl('dn','flush','test01.rs01');

restore account sys from snapshot sp01;
select count(*) from rs01 {snapshot = 'sp01'};

main commit:830d1bbf3

测试结果（执行10+次快照读）：

1.2-dev commit：968c8f7d6

测试结果（执行10+次快照读）：

测试通过

[matrix-meow]

Hello @Ariznawlll. The bug issue in the BVT test code has not been removed,issues automatically open.