[Bug] will get wrong result when have delete predicates #17145
Description
Search before asking
- I had searched in the issues and found no similar issues.
Version
master
What's Wrong?
当通过delete from 来删除某个行的时候，如果使用与删除条件相同的列去进行查询的时候，会产生把不该删的行也过滤掉的情况。
这种情况与使用delete predicates去过滤zone map的page有关。
例子：
建表sql
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" = "false"
);
导入数据和查询
MySQL [test]> truncate table test111;
Query OK, 0 rows affected (0.01 sec)
MySQL [test]>
MySQL [test]> 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);
Query OK, 24 rows affected (0.04 sec)
{'label':'insert_a6a4918318c24917_92243984d913b406', 'status':'VISIBLE', 'txnId':'11041'}
MySQL [test]>
MySQL [test]> delete from test111 where k2=1 and k3=11;
Query OK, 0 rows affected (0.02 sec)
{'label':'delete_c80835e3-3c0a-405f-9284-b0b0edad080a', 'status':'VISIBLE', 'txnId':'11042'}
MySQL [test]>
MySQL [test]> select k2,k3 from test111;
+------+------+
| k2 | k3 |
+------+------+
| 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 |
+------+------+
22 rows in set (0.02 sec)
MySQL [test]>
MySQL [test]> select k2,k3 from test111 where k2=1;
Empty set (0.00 sec)
在上边的例子中，使用k2和k3列一同去删除某一个行数据，select * 的时候返回的正确结果显示数据已经被删除，还剩下其余的22条数据
但是我使用k2作为查询条件时，确没有返回任何结果
更多的例子见 Reproduce。
经过分析，这个问题与删除条件有多个，但是查询条件使用了删除列，然后在使用zone map和delete predicates来选择和淘汰page时有关。
像上边的例子，应该是两个删除条件都满足的行来被过滤掉，但是在现在的代码中，这两个逻辑成了或关系，只要有page满足一个条件，就会整个page都过滤掉，导致不满足另外一个删除列条件的行也不能读取
另外如果使用相同列去作为删除条件，也有类似情况，应该是与的场景，都按或处理的，导致并不是满足所有删除条件的page被过滤掉了，从而产生了错误的结果。
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" = "false"
);
MySQL [test]> truncate table test111;
Query OK, 0 rows affected (0.01 sec)
MySQL [test]>
MySQL [test]> 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);
Query OK, 24 rows affected (0.04 sec)
{'label':'insert_a6a4918318c24917_92243984d913b406', 'status':'VISIBLE', 'txnId':'11041'}
MySQL [test]>
MySQL [test]> delete from test111 where k2=1 and k3=11;
Query OK, 0 rows affected (0.02 sec)
{'label':'delete_c80835e3-3c0a-405f-9284-b0b0edad080a', 'status':'VISIBLE', 'txnId':'11042'}
MySQL [test]>
MySQL [test]> select k2,k3 from test111;
+------+------+
| k2 | k3 |
+------+------+
| 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 |
+------+------+
22 rows in set (0.02 sec)
MySQL [test]>
MySQL [test]> select k2,k3 from test111 where k2=1;
Empty set (0.00 sec)
MySQL [test]>
MySQL [test]> delete from test111 where k2!=0 and k3=22;
Query OK, 0 rows affected (0.04 sec)
{'label':'delete_95ad73aa-43ae-498d-b310-972303ed19a4', 'status':'VISIBLE', 'txnId':'11044'}
MySQL [test]>
MySQL [test]> select k2,k3 from test111;
+------+------+
| k2 | k3 |
+------+------+
| 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 |
+------+------+
21 rows in set (0.01 sec)
MySQL [test]>
MySQL [test]> select k2,k3 from test111 where k2!=0;
Empty set (0.02 sec)
MySQL [test]>
MySQL [test]> delete from test111 where k2=1 and k3 >= 11 and k3 <=200;
Query OK, 0 rows affected (0.03 sec)
{'label':'delete_ac8851d1-a69d-44f3-b349-7a258a9eeb93', 'status':'VISIBLE', 'txnId':'11046'}
MySQL [test]>
MySQL [test]> select k2,k3 from test111;
+------+------+
| k2 | k3 |
+------+------+
| 1 | 201 |
| 1 | 202 |
+------+------+
2 rows in set (0.01 sec)
MySQL [test]>
MySQL [test]> select k2,k3 from test111 where k3 = 201;
Empty set (0.01 sec)
MySQL [test]>
MySQL [test]> truncate table test111;
Query OK, 0 rows affected (0.01 sec)
MySQL [test]>
MySQL [test]> 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);
Query OK, 24 rows affected (0.03 sec)
{'label':'insert_97bbcb5c4ce5449c_b7316dbe6e5a6d0f', 'status':'VISIBLE', 'txnId':'11048'}
MySQL [test]>
MySQL [test]> delete from test111 where k2=1 and k3 <=202 and k3 >= 33;
Query OK, 0 rows affected (0.02 sec)
{'label':'delete_16073c70-9caf-4be2-85f3-f4ea9be4330f', 'status':'VISIBLE', 'txnId':'11049'}
MySQL [test]>
MySQL [test]> select k2,k3 from test111;
+------+------+
| k2 | k3 |
+------+------+
| 1 | 11 |
| 1 | 22 |
+------+------+
2 rows in set (0.00 sec)
MySQL [test]>
MySQL [test]> select k2,k3 from test111 where k3 = 11;
Empty set (0.01 sec)
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