[Bug] delete from with is null predicate and then query will get wrong result with is not null predicate

[nextdreamblue]

Search before asking

• I had searched in the issues and found no similar issues.

Version

master

What's Wrong?

CREATE TABLE `test111` (
  `k1` bigint(20) NULL,
  `k2` largeint(40) NULL,
  `k3` largeint(40) NULL
) ENGINE=OLAP
AGGREGATE KEY(`k1`, `k2`, `k3`)
COMMENT 'OLAP'
DISTRIBUTED BY HASH(`k1`) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"in_memory" = "false",
"storage_format" = "V2",
"light_schema_change" = "true",
"disable_auto_compaction" = "true"
);


truncate table test111;

insert into test111 values(0,1,11),(0,1,22),(0,1,33),(0,1,44),(0,1,55),(0,1,66),(0,1,77),(0,1,88),(0,1,99),(0,1,100),(0,1,101),(0,1,102),(0,1,11),(0,1,122),(0,1,133),(0,1,144),(0,1,155),(0,1,166),(0,1,177),(0,1,188),(0,1,199),(0,1,200),(0,1,201),(0,1,202);

执行delete from 然后查询，查询结果不正确

MySQL [test]> delete from test111 where k2 is null;
Query OK, 0 rows affected (0.04 sec)
{'label':'delete_57381566-4b1a-411e-ba14-9d9901496c6b', 'status':'VISIBLE', 'txnId':'12242'}

MySQL [test]> select k2,k3 from test111;
+------+------+
| k2   | k3   |
+------+------+
| 1    | 11   |
| 1    | 22   |
| 1    | 33   |
| 1    | 44   |
| 1    | 55   |
| 1    | 66   |
| 1    | 77   |
| 1    | 88   |
| 1    | 99   |
| 1    | 100  |
| 1    | 101  |
| 1    | 102  |
| 1    | 122  |
| 1    | 133  |
| 1    | 144  |
| 1    | 155  |
| 1    | 166  |
| 1    | 177  |
| 1    | 188  |
| 1    | 199  |
| 1    | 200  |
| 1    | 201  |
| 1    | 202  |
+------+------+
23 rows in set (0.01 sec)

MySQL [test]> select k2,k3 from test111 where k2 is not null;
Empty set (0.01 sec)

What You Expected?

返回正确的结果

How to Reproduce?

CREATE TABLE `test111` (
  `k1` bigint(20) NULL,
  `k2` largeint(40) NULL,
  `k3` largeint(40) NULL
) ENGINE=OLAP
AGGREGATE KEY(`k1`, `k2`, `k3`)
COMMENT 'OLAP'
DISTRIBUTED BY HASH(`k1`) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"in_memory" = "false",
"storage_format" = "V2",
"light_schema_change" = "true",
"disable_auto_compaction" = "true"
);


truncate table test111;

insert into test111 values(0,1,11),(0,1,22),(0,1,33),(0,1,44),(0,1,55),(0,1,66),(0,1,77),(0,1,88),(0,1,99),(0,1,100),(0,1,101),(0,1,102),(0,1,11),(0,1,122),(0,1,133),(0,1,144),(0,1,155),(0,1,166),(0,1,177),(0,1,188),(0,1,199),(0,1,200),(0,1,201),(0,1,202);

delete from test111 where k2 is null;

select k2,k3 from test111;

select k2,k3 from test111 where k2 is not null;

truncate table test111;

insert into test111 values(0,null,11),(0,null,22),(0,null,33),(0,null,44),(0,null,55),(0,null,66),(0,null,77),(0,null,88),(0,null,99),(0,null,100),(0,null,101),(0,null,102),(0,null,11),(0,null,122),(0,null,133),(0,null,144),(0,null,155),(0,null,166),(0,null,177),(0,null,188),(0,null,199),(0,null,200),(0,null,201),(0,null,202);

delete from test111 where k2 is not null;

select k2,k3 from test111;

select k2,k3 from test111 where k2 is null;

两种带where的查询都返回错误数据

Anything Else?

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct