nextdreamblue opened a new issue, #17145: URL: https://github.com/apache/doris/issues/17145
### Search before asking - [X] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) 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? - [X] Yes I am willing to submit a PR! ### Code of Conduct - [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org For additional commands, e-mail: commits-h...@doris.apache.org
