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

Incorrect index_merge on BTREE indices

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5.34, 10.0.8
    • 5.5.36, 10.0.9
    • None
    • None
    • mysql Ver 15.1 Distrib 5.5.34-MariaDB, for debian-linux-gnu (x86_64) using readline 5.1

      Ubuntu 12.04 LTS

    Description

      Trying to simplify the context for this problem, but let me know if you require additional information.

      I have a table named base with several columns, including a field named id.

      Each field has a BTREE index created via create index X on base(X)

      I've found that queries of the type select id from base where X = 'value1' and Y = 'value2' do not return the correct number of results.

      If I do a select id, X from base where Y = 'value2' order by X, I can see a full and accurate list of all combinations of X and Y = 'value2', since this query does not execute an index_merge (running an explain on this shows that its a ref query using the index on Y).

      But running select id from base where X = 'value1' and Y = 'value2' clearly shows that some rows are dropped from the results when compared to what you see with select id, X from base where Y = 'value2' order by X. Running an explain shows that an index_merge is run on the indices on both X and Y.

      Rebuilding the indices does not fix the problem, and results in the same rows consistently being dropped from the query results.

      I can provide firmer context with actual query outputs if this is too abstract. Thanks for your help.

      Attachments

        1. 1krows_dump_min.sql
          3.13 MB
          Michael Reschly
        2. mdev5555.test
          20 kB
          Elena Stepanova

        Activity

          People

            psergei Sergei Petrunia
            njhwang Nicholas Hwang
            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.