Details
Major
Description
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:
Test case:
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
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
MDEV-5512 Wrong result (WHERE clause ignored) with multiple clauses using Percona-XtraDB engine
-
- Closed
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Debugged, this is the same issue as MDEV-5512. The testcase passed after the fix.
Thanks for the bug report, and for the detailed testcase. The fix is pushed into 5.3 and 5.5 trees, and will be available in the next releases.
One fix for this issue and MDEV-5512
Re-opening just to change Resolution from 'Duplicate' to the more appropriate 'Fixed'
Thanks for reporting and for the test case.
Reproducible as described.