Details
Major
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.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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?
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.
> 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.
Hi,
Yes, it would be great if you could provide a data dump for the tables involved into the query, and the actual query. If the data is sensitive, you can upload it to the private section of our FTP (ftp://ftp.askmonty.org/private/)
We have some open bugs regarding index_merge, but it's difficult to say whether your case is related to them or not without the actual data.