MySQL is not correctly selecting rows (sometimes)

Question

This is an update to this question, wherein I was casting around trying to work out what on earth was going on:

MySQL sometimes erroneously returns 0 for count(*)

I ended up accepting an answer there because it did answer the question I posed ("why might this happen") even though it didn't answer the question I really wanted to know about ("why is this happening to me"). But I've managed to narrow things down a little bit on the latter question, and think I can definitively say that something is wrong in a way that I don't understand and have never seen before.

The issue has been really difficult to debug because, for reasons beyond my comprehension, logging in to the database automagically fixes it. However, today I managed to trigger the problematic state while having an open MySQL session in a terminal. Here are some queries and the subsequent responses taken from that session:

First, this is my table layout:

mysql> describe forum_posts;                                                    
+-----------+------------+------+-----+---------+----------------+
| Field     | Type       | Null | Key | Default | Extra          |
+-----------+------------+------+-----+---------+----------------+
| post_id   | int(11)    | NO   | PRI | NULL    | auto_increment |
| thread_id | int(11)    | YES  | MUL | NULL    |                |
| forum_id  | int(11)    | YES  | MUL | NULL    |                |
| user_id   | int(11)    | YES  | MUL | NULL    |                |
| moderator | tinyint(1) | NO   |     | 0       |                |
| message   | mediumtext | YES  | MUL | NULL    |                |
| date      | int(11)    | NO   | MUL | NULL    |                |
| edited    | int(11)    | YES  |     | NULL    |                |
| deleted   | tinyint(1) | YES  | MUL | 0       |                |
| bbcode    | tinyint(1) | NO   |     | 1       |                |
+-----------+------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)

Now, lets look at how many posts there are in a given forum thread:

mysql> SELECT count(post_id) as num FROM `forum_posts` where thread_id=5243;
+-----+
| num |
+-----+
| 195 |
+-----+
1 row in set (0.00 sec)

OK, but I only want forum posts that don't have the deleted flag set:

mysql> SELECT count(post_id) as num FROM `forum_posts` where thread_id=5243 and deleted=0;
+-----+
| num |
+-----+
|   0 |
+-----+
1 row in set (0.06 sec)

mysql> select post_id,deleted from forum_posts where thread_id=5243 and deleted=0;
Empty set (0.06 sec)

OK, lets just double-make-sure that they aren't actually all deleted:

mysql> select post_id,deleted from forum_posts where thread_id=5243;
+---------+---------+
| post_id | deleted |
+---------+---------+
|  104081 |       0 |
|  104082 |       0 |

[snip]

|  121162 |       0 |
|  121594 |       0 |
+---------+---------+
195 rows in set (0.00 sec)

Every row in that table has 'deleted' set to 0, and yet adding and deleted=0 to the query yields no results. Until I open a new session by logging in to MySQL again from a terminal window, after which I can once again properly select rows where 'deleted' is 0.

What on earth?

---

UPDATES:

@miken32 in the comments below suggested I try an EXPLAIN SELECT ..., so:

mysql> explain select post_id,deleted from forum_posts where thread_id='5243' and deleted=0;
+----+-------------+-------------+-------------+-------------------+-------------------+---------+------+------+--------------------------------------------------------------+
| id | select_type | table       | type        | possible_keys     | key               | key_len | ref  | rows | Extra                                                        |
+----+-------------+-------------+-------------+-------------------+-------------------+---------+------+------+--------------------------------------------------------------+
|  1 | SIMPLE      | forum_posts | index_merge | thread_id,deleted | thread_id,deleted | 5,2     | NULL |   97 | Using intersect(thread_id,deleted); Using where; Using index |
+----+-------------+-------------+-------------+-------------------+-------------------+---------+------+------+--------------------------------------------------------------+
1 row in set (0.00 sec)

Answer 1

Based on the comment that using FORCE KEY alters the result from the query, it is very likely that we are dealing with the merge optimizer bug. EXPLAIN of the original query shows the optimization is done by selecting from the deleted key, then from the post_id key, then merging the results. When we force to bypass that code, the problem goes away.

The steps from the point:

• try it on the same data with the most recent 5.6 version of MySQL
• if the issue reproduces, try to isolate it to the most minimal test case, visit http://bugs.mysql.com/ and report the bug