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

    XMLWordPrintable

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

              psergei Sergei Petrunia
              zvictorino Zhixin Zhang
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.