SQL Statement:
"SELECT SQL_NO_CACHE a.project_number FROM projects a WHERE ( SELECT z.country FROM projects_history z WHERE z.project_number = a.project_number AND z.history_date <= '2014-09-01' ORDER BY z.id DESC LIMIT 1 ) IN ( SELECT r.country FROM region r WHERE r.region = 'eame' );"

Expected Result:

+----------------+
| project_number |
+----------------+
| aaa            |
+----------------+

-- --------------------------------------------------------



-- --------------------------------------------------------

EXPLAIN from MariaDB (10.0.13):
+------+--------------------+-------------+--------+---------------+--------------+---------+------+------+-------------+
| id   | select_type        | table       | type   | possible_keys | key          | key_len | ref  | rows | Extra       |
+------+--------------------+-------------+--------+---------------+--------------+---------+------+------+-------------+
|    1 | PRIMARY            | a           | index  | NULL          | PRIMARY      | 52      | NULL |    2 | Using index |
|    1 | PRIMARY            | <subquery3> | eq_ref | distinct_key  | distinct_key | 53      | func |    1 | Using where |
|    3 | MATERIALIZED       | r           | ALL    | NULL          | NULL         | NULL    | NULL |    2 | Using where |
|    2 | DEPENDENT SUBQUERY | z           | index  | NULL          | PRIMARY      | 4       | NULL |    1 | Using where |
+------+--------------------+-------------+--------+---------------+--------------+---------+------+------+-------------+

Actual Result from MariaDB (10.0.13):
<Empty set>



-- --------------------------------------------------------

EXPLAIN from MySQL (5.6.17):
+------+--------------------+-------------+--------+---------------+--------------+---------+------+------+-------------------------------------------------------------------+
| id   | select_type        | table       | type   | possible_keys | key          | key_len | ref  | rows | Extra                                                             |
+------+--------------------+-------------+--------+---------------+--------------+---------+------+------+-------------------------------------------------------------------+
|    1 | PRIMARY            | a           | index  | NULL          | PRIMARY      | 52      | NULL |    2 | Using index                                                       |
|    1 | PRIMARY            | r           | ALL    | NULL          | NULL         | NULL    | NULL |    2 | Using where; FirstMatch(a); Using join buffer (Block Nested Loop) |
|    2 | DEPENDENT SUBQUERY | z           | index  | NULL          | PRIMARY      | 4       | NULL |    1 | Using where                                                       |
+------+--------------------+-------------+--------+---------------+--------------+---------+------+------+-------------------------------------------------------------------+

Actual Result from MySQL (5.6.17):
+----------------+
| project_number |
+----------------+
| aaa            |
| bbb            |
+----------------+



-- --------------------------------------------------------

EXPLAIN from MySQL (5.0.96):
+----+--------------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type        | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+--------------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | PRIMARY            | a     | index | NULL          | PRIMARY | 52      | NULL |    2 | Using where; Using index |
|  3 | DEPENDENT SUBQUERY | r     | ALL   | NULL          | NULL    | NULL    | NULL |    2 | Using where              |
|  2 | DEPENDENT SUBQUERY | z     | index | NULL          | PRIMARY | 4       | NULL |    2 | Using where              |
+----+--------------------+-------+-------+---------------+---------+---------+------+------+--------------------------+

Actual Result from MariaDB (10.0.13):
+----------------+
| project_number |
+----------------+
| aaa            |
+----------------+