Comment 2 for bug 860580

A simplified testcase:

SET SESSION optimizer_switch='semijoin=ON,materialization=ON,firstmatch=OFF,loosescan=OFF';

SELECT
   alias1.col_int_nokey AS field1 ,
   (SELECT
      MAX( SQ2_alias1.col_varchar_key) AS SQ2_field1
    FROM
      t2 AS SQ2_alias1
    WHERE
       (SQ2_alias1.pk , SQ2_alias1.pk) IN
       (
          SELECT
             C_SQ1_alias2.col_int_nokey AS C_SQ1_field1,
             C_SQ1_alias1.col_int_key AS C_SQ1_field2
          FROM
          (
             t3 AS C_SQ1_alias1,
             t3 AS C_SQ1_alias2,
             t1 AS C_SQ1_alias3
          )
          WHERE C_SQ1_alias3.col_int_key > 98 AND
                C_SQ1_alias3.pk = C_SQ1_alias2.pk AND
                C_SQ1_alias3.col_varchar_key = C_SQ1_alias2.col_varchar_key
          GROUP BY C_SQ1_field1 , C_SQ1_field2
       )
   ) AS field3
FROM
  t3 AS alias1,
  t4 AS alias2,
  t1 AS alias3

GROUP BY
  field1, field3
HAVING
  field3 < 'e' AND field1 < 242
ORDER BY
  alias1.col_datetime_key ASC , field3 ASC, field1, field3
LIMIT 100 OFFSET 9 ;

drop table t1,t2,t3,t4;