Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5337

Wrong result in mariadb 5.5.32 with ORDER BY + LIMIT when index_condition_pushdown=on

    Details

      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

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                psergey Sergei Petrunia
                Reporter:
                zvictorino Zhixin Zhang
              • Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: