The query following:
select aid,bid from tbl where bid in (...) and cid = ? order by aid limit 20000,20
when the set (...) is large and the limit offset is big enough like 20000, then it will return wrong result that here the value of column bid is not in the where condition. And if I turn off the index_condition_pushdown, it will return correct result.
Note: I tested it in mariadb 5.5.33a, 5.5.34, 5.5.32 and 10.0.6, all these version have the issue. Also, the issue only occurs in innodb/xtradb. Myisam does not have this issue.
How to repeat:
I put all the test cases and data source file in the attachement.
There are two test cases: test-innodb-9090.sql, test-myisam-9090.sql.
The 9090 means that the "where in (...)" contains 9090 items.
The test case do the following:
create table and load data.
run the query
explain the query
turn off the index_condition_pushdown
run the query again
explain the query again
run the two test cases:
mysql test < test-innodb-9090.sql > r/result-innodb-9090
mysql test < test-myisam-9090.sql > r/result-myisam-9090