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

Incorrect index_merge on BTREE indices

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

        Activity

          Fix pushed into 5.5 tree and will be in the next 5.5 release.

          Thanks for the detailed bug report, and for the testcase.

          psergei Sergei Petrunia added a comment - Fix pushed into 5.5 tree and will be in the next 5.5 release. Thanks for the detailed bug report, and for the testcase.

          Wonderful, thanks!

          We have some very large existing datasets that we'd like to use as is, if possible. Does this bug require that these datasets be regenerated using the patched server? Or just that the indices be re-created? Or just that the patched server be running?

          njhwang Nicholas Hwang added a comment - Wonderful, thanks! We have some very large existing datasets that we'd like to use as is, if possible. Does this bug require that these datasets be regenerated using the patched server? Or just that the indices be re-created? Or just that the patched server be running?

          The patch only changes the way data is read. There is no need to regenerate data or to rebuild indexes. It should be sufficient to start the patched server on the existing data directory.

          psergei Sergei Petrunia added a comment - The patch only changes the way data is read. There is no need to regenerate data or to rebuild indexes. It should be sufficient to start the patched server on the existing data directory.

          Excellent, thanks so much for the quick turn around.

          njhwang Nicholas Hwang added a comment - Excellent, thanks so much for the quick turn around.

          > There is no need to regenerate data or to rebuild indexes. It should be sufficient to start the patched server on the existing data directory.

          Hi Sergi,

          I just wanted to confirm that this was the case. I used patched binaries on our existing data and the correct results were returned without regenerating data or re-indexing.

          Thanks again.

          reschly Michael Reschly added a comment - > There is no need to regenerate data or to rebuild indexes. It should be sufficient to start the patched server on the existing data directory. Hi Sergi, I just wanted to confirm that this was the case. I used patched binaries on our existing data and the correct results were returned without regenerating data or re-indexing. Thanks again.

          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.