LIMIT ROWS EXAMINED: query with NOT IN subquery and materialization+partial_match_rowid_merge examines over 900K rows while the limit is 17K

SELECT * FROM t1 AS alias1, t1 AS alias2 

WHERE ( alias1.b, alias2.c ) NOT IN 

  ( SELECT d, a FROM t1, t2 ) 

LIMIT ROWS_EXAMINED 17000

id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra

1       PRIMARY alias1  ALL     NULL    NULL    NULL    NULL    500     100.00

1       PRIMARY alias2  ALL     NULL    NULL    NULL    NULL    500     100.00  Using where; Using join buffer (flat, BNL join)

2       MATERIALIZED    t2      index   PRIMARY PRIMARY 4       NULL    320     100.00  Using index

2       MATERIALIZED    t1      index   PRIMARY PRIMARY 4       NULL    500     100.00  Using index; Using join buffer (flat, BNL join)

Warnings:

Note    1003    select `test`.`alias1`.`a` AS `a`,`test`.`alias1`.`b` AS `b`,`test`.`alias1`.`c` AS `c`,`test`.`alias2`.`a` AS `a`,`test`.`alias2`.`b` AS `b`,`test`.`alias2`.`c` AS `c` from `test`.`t1` `alias1` join `test`.`t1` `alias2` where (not(<expr_cache><`test`.`alias1`.`b`,`test`.`alias2`.`c`>(<in_optimizer>((`test`.`alias1`.`b`,`test`.`alias2`.`c`),(`test`.`alias1`.`b`,`test`.`alias2`.`c`) in ( <materialize> (select `test`.`t2`.`d`,`test`.`t1`.`a` from `test`.`t1` join `test`.`t2` ), <primary_index_lookup>(`test`.`alias1`.`b` in <temporary table> on distinct_key where ((`test`.`alias1`.`b` = `<subquery2>`.`d`) and (`test`.`alias2`.`c` = `<subquery2>`.`a`))))))))

materialization=on,partial_match_rowid_merge=on,subquery_cache=on

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

SET optimizer_switch = 'materialization=on,partial_match_rowid_merge=on,subquery_cache=on';

CREATE TABLE t1 (

  a INT AUTO_INCREMENT PRIMARY KEY,

  b INT, c INT 

);

# 500 rows		

INSERT INTO t1 ( c, b ) VALUES 

(132,209),(2,1),(1,4),(0,5),(0,7),(7,NULL),(3,NULL),(0,1),

(8,7),(NULL,NULL),(7,6),(0,4),(NULL,2),(2,3),(6,3),

(3,NULL),(7,8),(1,7),(6,0),(134,51),(7,6),(9,2),(0,8),

(5,NULL),(NULL,2),(NULL,6),(NULL,5),(9,4),(1,4),(NULL,6),

(6,0),(0,6),(1,2),(4,NULL),(1,NULL),(239,135),(148,18),

(2,6),(4,6),(6,8),(2,4),(9,0),(6,NULL),(9,3),(3,3),(8,5),

(1,5),(2,1),(6,8),(4,2),(0,6),(4,6),(1,3),(NULL,2),(9,4),

(1,2),(0,1),(147,34),(5,2),(5,4),(8,3),(6,NULL),(3,2),

(6,NULL),(9,2),(7,6),(80,227),(0,7),(9,NULL),(5,6),(0,2),

(9,7),(0,NULL),(4,6),(6,5),(1,3),(0,5),(9,6),(1,7),(7,0),

(9,6),(6,3),(3,4),(5,1),(3,4),(2,3),(NULL,8),(5,0),

(81,187),(2,2),(6,NULL),(5,3),(0,NULL),(9,8),(9,2),

(NULL,7),(8,6),(NULL,3),(8,4),(7,9),(4,8),(0,1),(NULL,2),

(NULL,6),(4,4),(0,0),(3,9),(3,2),(243,17),(2,0),(2,0),

(0,NULL),(9,9),(186,4),(4,NULL),(7,3),(8,2),(7,6),(181,15),

(115,46),(9,1),(NULL,7),(9,2),(NULL,6),(5,3),(3,9),(9,4),

(NULL,8),(130,129),(6,2),(2,8),(5,6),(3,4),(0,NULL),(0,4),

(1,6),(NULL,5),(7,6),(NULL,9),(3,NULL),(2,5),(2,2),

(8,NULL),(5,1),(2,9),(1,9),(0,3),(2,4),(8,7),(NULL,6),

(0,8),(NULL,7),(7,1),(6,4),(8,8),(5,5),(8,5),(7,1),(0,8),

(3,4),(2,5),(157,54),(0,5),(NULL,2),(5,7),(5,1),(7,2),

(9,7),(212,78),(7,4),(NULL,0),(6,1),(1,0),(7,6),(6,7),

(8,4),(0,2),(9,3),(8,1),(4,4),(6,9),(7,3),(132,59),

(1,NULL),(0,5),(1,1),(4,2),(2,0),(2,5),(4,6),(5,4),(7,0),

(221,163),(2,9),(NULL,5),(7,NULL),(3,7),(9,NULL),(NULL,2),

(45,127),(1,2),(2,6),(8,7),(8,5),(NULL,4),(3,2),(7,5),

(4,7),(4,2),(130,35),(3,0),(4,1),(1,6),(6,8),(9,2),(8,3),

(9,7),(7,NULL),(6,1),(5,8),(3,4),(9,5),(4,0),(NULL,6),

(6,9),(8,5),(1,0),(3,9),(1,1),(NULL,7),(8,9),(3,NULL),

(1,2),(6,5),(3,5),(6,NULL),(3,7),(0,8),(4,9),(7,5),(6,5),

(0,5),(2,3),(4,1),(0,4),(2,NULL),(4,1),(0,5),(0,5),(2,9),

(5,6),(224,112),(9,5),(5,1),(6,NULL),(3,0),(2,2),(3,2),

(3,5),(2,6),(6,4),(4,9),(NULL,9),(20,132),(8,8),(7,0),

(6,5),(2,4),(104,101),(2,6),(7,2),(2,NULL),(7,6),(3,9),

(8,9),(2,7),(0,NULL),(2,2),(3,6),(2,5),(2,6),(NULL,3),

(6,6),(9,1),(21,NULL),(3,NULL),(1,3),(0,NULL),(9,7),(4,5),

(6,1),(7,4),(NULL,6),(8,3),(180,89),(NULL,9),(8,NULL),

(6,5),(2,0),(6,2),(3,0),(5,8),(2,1),(NULL,NULL),(0,NULL),

(6,4),(4,4),(2,3),(9,2),(2,NULL),(5,0),(7,4),(0,8),(5,6),

(8,4),(8,5),(6,6),(7,2),(243,71),(7,4),(4,1),(250,100),

(1,4),(NULL,NULL),(5,9),(8,0),(2,2),(8,4),(5,NULL),

(NULL,8),(NULL,NULL),(7,5),(2,0),(8,8),(5,8),(0,8),(9,3),

(9,7),(1,6),(1,3),(1,4),(79,137),(3,5),(6,5),(130,29),

(5,7),(5,3),(9,NULL),(9,8),(7,3),(8,7),(9,9),(9,NULL),

(7,9),(8,9),(0,8),(1,2),(6,6),(0,8),(8,2),(NULL,7),

(NULL,0),(2,2),(8,2),(9,0),(8,5),(6,8),(7,NULL),(NULL,2),

(2,1),(1,8),(5,2),(NULL,2),(NULL,3),(9,9),(5,1),(9,5),

(NULL,5),(NULL,6),(9,123),(5,0),(0,6),(NULL,0),(98,75),

(5,9),(242,69),(7,8),(1,NULL),(25,51),(4,8),(8,5),(9,5),

(NULL,3),(3,NULL),(5,NULL),(4,4),(6,8),(3,5),(NULL,5),

(0,3),(4,8),(9,1),(1,9),(4,9),(3,5),(4,NULL),(0,NULL),

(0,7),(4,5),(0,2),(9,8),(7,NULL),(152,245),(4,NULL),(7,7),

(6,NULL),(NULL,9),(8,NULL),(5,6),(3,5),(3,3),(3,6),(4,3),

(4,4),(5,2),(5,5),(9,4),(6,6),(5,4),(2,5),(5,5),(63,44),

(4,9),(9,5),(2,6),(3,6),(5,3),(1,4),(5,0),(2,7),(1,8),

(4,8),(2,1),(8,7),(NULL,NULL),(5,4),(4,9),(7,6),(9,4),

(4,NULL),(4,3),(4,2),(6,7),(5,8),(2,8),(7,4),(NULL,9),

(7,3),(32,51),(7,7),(8,7),(8,NULL),(7,8),(1,1),(7,9),(9,4),

(NULL,3),(1,2),(9,NULL),(2,2),(9,NULL),(2,6),(4,7),(0,2),

(4,5),(8,7),(NULL,6),(NULL,6),(0,2),(NULL,9),(8,6),(8,0),

(6,3),(2,8),(3,8),(1,6),(NULL,0),(1,NULL),(10,158),(2,5),

(103,163),(3,2),(6,8),(2,0),(7,9),(2,3),(2,1),(4,9),

(6,NULL),(6,5);

CREATE TABLE t2 ( d INT AUTO_INCREMENT PRIMARY KEY );

# 320 rows

INSERT INTO t2 VALUES 

  (NULL),(NULL),(NULL),(NULL),(NULL),

  (NULL),(NULL),(NULL),(NULL),(NULL);

INSERT INTO t2 SELECT NULL FROM t2;

INSERT INTO t2 SELECT NULL FROM t2;

INSERT INTO t2 SELECT NULL FROM t2;

INSERT INTO t2 SELECT NULL FROM t2;

INSERT INTO t2 SELECT NULL FROM t2;

SELECT * FROM t1 AS alias1, t1 AS alias2 

WHERE ( alias1.b, alias2.c ) NOT IN 

  ( SELECT d, a FROM t1, t2 ) 

LIMIT ROWS_EXAMINED 17000;