Incorrect index_merge on BTREE indices

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.

Environment

mysql Ver 15.1 Distrib 5.5.34-MariaDB, for debian-linux-gnu (x86_64) using readline 5.1

Ubuntu 12.04 LTS

Status

Assignee

Sergei Petrunia

Reporter

Nicholas Hwang

Labels

None

External issue ID

None

External issue ID

None

Fix versions

Affects versions

10.0.8
5.5.34

Priority

Major