Fixed test case, some quoting and separators were garbled:
CREATE TABLE t1 ( f1 int(11)) ; INSERT IGNORE INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41),(42),(43),(44),(45),(46),(47),(48),(49),(50),(51),(52),(53),(54),(55),(56),(57),(58),(59),(60),(61),(62),(63),(64),(65),(66),(67),(68),(69),(70),(71),(72),(73),(74),(75),(76),(77),(78),(79),(80),(81),(82),(83),(84),(85),(86),(87),(88),(89),(90),(91),(92),(93),(94),(95),(96),(97),(98),(99),(100);
CREATE TABLE t2 ( f11 varchar(1) ); INSERT IGNORE INTO t2 VALUES ('f'),('d');
CREATE TABLE t4 ( f1 int(11)) ;
set session optimizer_switch='materialization=on,in_to_exists=off';
SELECT * FROM t2 WHERE ( SELECT f1 FROM t4 ) NOT IN ( SELECT f1 FROM t1 ) ;
Fixed test case, some quoting and separators were garbled:
CREATE TABLE t1 ( f1 int(11)) ; ,(3),(4) ,(5),(6) ,(7),(8) ,(9),(10) ,(11),( 12),(13) ,(14),( 15),(16) ,(17),( 18),(19) ,(20),( 21),(22) ,(23),( 24),(25) ,(26),( 27),(28) ,(29),( 30),(31) ,(32),( 33),(34) ,(35),( 36),(37) ,(38),( 39),(40) ,(41),( 42),(43) ,(44),( 45),(46) ,(47),( 48),(49) ,(50),( 51),(52) ,(53),( 54),(55) ,(56),( 57),(58) ,(59),( 60),(61) ,(62),( 63),(64) ,(65),( 66),(67) ,(68),( 69),(70) ,(71),( 72),(73) ,(74),( 75),(76) ,(77),( 78),(79) ,(80),( 81),(82) ,(83),( 84),(85) ,(86),( 87),(88) ,(89),( 90),(91) ,(92),( 93),(94) ,(95),( 96),(97) ,(98),( 99),(100) ;
INSERT IGNORE INTO t1 VALUES (1),(2)
CREATE TABLE t2 ( f11 varchar(1) );
INSERT IGNORE INTO t2 VALUES ('f'),('d');
CREATE TABLE t4 ( f1 int(11)) ;
set session optimizer_ switch= 'materializatio n=on,in_ to_exists= off';
SELECT *
FROM t2
WHERE ( SELECT f1 FROM t4 ) NOT IN ( SELECT f1 FROM t1 ) ;