mysql> explain -> select bug.id1, -> (select 0 -> from dual -> where bug.id1=bug.id1 -> ) as amount -> from t1 as bug -> #ignore index (t1_id2_index) -> where bug.id2 = 1 -> having amount > 0 -> order by bug.id1; +----+--------------------+-------+------+---------------+--------------+---------+-------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+------+---------------+--------------+---------+-------+------+-----------------------------+ | 1 | PRIMARY | bug | ref | t1_id2_index | t1_id2_index | 8 | const | 2 | Using where; Using filesort | | 2 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+--------------------+-------+------+---------------+--------------+---------+-------+------+-----------------------------+ 2 rows in set (0.00 sec) mysql> select bug.id1, -> (select 0 -> from dual -> where bug.id1=bug.id1 -> ) as amount -> from t1 as bug -> #ignore index (t1_id2_index) -> where bug.id2 = 1 -> having amount > 0 -> order by bug.id1; +-----+--------+ | id1 | amount | +-----+--------+ | 1 | 0 | | 2 | 0 | | 3 | 0 | +-----+--------+ 3 rows in set (0.01 sec) If we ignore the index, we get the correct results: mysql> explain -> select bug.id1, -> (select 0 -> from dual -> where bug.id1=bug.id1 -> ) as amount -> from t1 as bug -> ignore index (t1_id2_index) -> where bug.id2 = 1 -> having amount > 0 -> order by bug.id1; +----+--------------------+-------+------+---------------+------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+------+---------------+------+---------+------+------+-----------------------------+ | 1 | PRIMARY | bug | ALL | NULL | NULL | NULL | NULL | 3 | Using where; Using filesort | | 2 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+--------------------+-------+------+---------------+------+---------+------+------+-----------------------------+ 2 rows in set (0.00 sec) mysql> select bug.id1, -> (select 0 -> from dual -> where bug.id1=bug.id1 -> ) as amount -> from t1 as bug -> ignore index (t1_id2_index) -> where bug.id2 = 1 -> having amount > 0 -> order by bug.id1; Empty set (0.01 sec) Also, if a group by is added, or the order by is removed, we get correct results: mysql> explain -> select bug.id1, -> (select 0 -> from dual -> where bug.id1=bug.id1 -> ) as amount -> from t1 as bug -> #ignore index (t1_id2_index) -> where bug.id2 = 1 -> having amount > 0; +----+--------------------+-------+------+---------------+--------------+---------+-------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+------+---------------+--------------+---------+-------+------+----------------+ | 1 | PRIMARY | bug | ref | t1_id2_index | t1_id2_index | 8 | const | 2 | | | 2 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+--------------------+-------+------+---------------+--------------+---------+-------+------+----------------+ 2 rows in set (0.00 sec) mysql> select bug.id1, -> (select 0 -> from dual -> where bug.id1=bug.id1 -> ) as amount -> from t1 as bug -> #ignore index (t1_id2_index) -> where bug.id2 = 1 -> having amount > 0; Empty set (0.00 sec) mysql> explain -> select bug.id1, -> (select 0 -> from dual -> where bug.id1=bug.id1 -> ) as amount -> from t1 as bug -> #ignore index (t1_id2_index) -> where bug.id2 = 1 -> group by bug.id1 -> having amount > 0 -> order by bug.id1; +----+--------------------+-------+------+---------------+--------------+---------+-------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+------+---------------+--------------+---------+-------+------+----------------------------------------------+ | 1 | PRIMARY | bug | ref | t1_id2_index | t1_id2_index | 8 | const | 2 | Using where; Using temporary; Using filesort | | 2 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+--------------------+-------+------+---------------+--------------+---------+-------+------+----------------------------------------------+ 2 rows in set (0.00 sec) mysql> select bug.id1, -> (select 0 -> from dual -> where bug.id1=bug.id1 -> ) as amount -> from t1 as bug -> #ignore index (t1_id2_index) -> where bug.id2 = 1 -> group by bug.id1 -> having amount > 0 -> order by bug.id1; Empty set (0.00 sec) Finally, if we run the original query again, but, the data types on the temp table are char, we get correct results. (I have tested, bigint, int, tinyint, and decimal types for the fields, they are all broken). With a char data type, the issue seems to be fixed only because a temporary table is being used to resolve the query, similar to adding a group by clause. drop temporary table if exists t1; create temporary table t1 ( id1 char not null, id2 char not null, index t1_id2_index(id2) ); insert into t1 set id1=1, id2=1; insert into t1 set id1=2, id2=1; insert into t1 set id1=3, id2=1; mysql> explain -> select bug.id1, -> (select 0 -> from dual -> where bug.id1=bug.id1 -> ) as amount -> from t1 as bug -> #ignore index (t1_id2_index) -> where bug.id2 = 1 -> group by bug.id1 -> having amount > 0 -> order by bug.id1; +----+--------------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+ | 1 | PRIMARY | bug | ALL | t1_id2_index | NULL | NULL | NULL | 3 | Using where; Using temporary; Using filesort | | 2 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+--------------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+ 2 rows in set (0.00 sec) mysql> select bug.id1, -> (select 0 -> from dual -> where bug.id1=bug.id1 -> ) as amount -> from t1 as bug -> #ignore index (t1_id2_index) -> where bug.id2 = 1 -> group by bug.id1 -> having amount > 0 -> order by bug.id1; Empty set (0.00 sec)