A combination of outer join, join buffering, and order by causes invalid query plans to be generated.
Query and EXPLAIN:
The query plan uses both "Using filesort" and "Using join buffer". It will produce the results in the wrong order.
I'll elaborate why:
- The query has ORDER BY and so must produce rows in order.
"Using filesort" means that the first table (t1 in the example) is read by filesort(), which produces the sorted sequence, which is then joined to table t2.
- However, table t2 uses join buffer, which will break the ordering created by filesort.