SELECT VERSION();
SET optimizer_switch='firstmatch=off';

CREATE TABLE t1 (
  pk int NOT NULL,
  col_int_key int NOT NULL,
  col_varchar_nokey varchar(1) NOT NULL,
  col_varchar_key varchar(1) NOT NULL,
  PRIMARY KEY(pk),
  KEY col_int_key(col_int_key),
  KEY col_varchar_key(col_varchar_key, col_int_key)
) charset utf8mb4 engine=INNODB;

INSERT INTO t1 VALUES (1,7,'a','a'), (2,0,'v','v'), (3,9,'c','c'), (4,3,'m','m'), (5,2,'a','a'),
(6,1,'d','d'), (7,8,'y','y'), (8,6,'t','t'), (11,7,'a','x'), (12,0,'v','v'), (13,9,'c','c'),
(14,3,'m','m'), (15,2,'a','x'), (16,1,'d','d'), (17,8,'y','y');
RENAME TABLE t1 TO t2;

CREATE TABLE t3 (
  pk int NOT NULL,
  col_int_nokey int NOT NULL,
  col_int_key int NOT NULL,
  PRIMARY KEY (pk),
  KEY col_int_key (col_int_key)
) ENGINE=MyIsam;

INSERT INTO t3 VALUES (10,1,7), (13,7,3), (18,0,1), (23,8,1);
CREATE TABLE t4 (
  pk int NOT NULL,
  col_int_key int NOT NULL,
  PRIMARY KEY (pk),
  KEY col_int_key (col_int_key)
) ENGINE=MyIsam;
INSERT INTO t4 VALUES (1,7);

let $query=
SELECT t1a.*
FROM t3 AS t1a
  JOIN t3 AS t1b USING ( col_int_nokey )
WHERE t1a.col_int_key IN (
  SELECT pk
  FROM t4
  WHERE col_int_key IN (
    SELECT col_int_nokey
    FROM t3
  )
);

eval EXPLAIN $query;
--sorted_result
eval $query;
ALTER TABLE t3 ENGINE=Innodb;
ALTER TABLE t4 ENGINE=Innodb;
eval $query;
DROP TABLE t2, t3, t4;