create table ten(a int);

insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

select a from ten order by a limit 5;

+------+

| a    |

+------+

|    0 |

|    1 |

|    2 |

|    3 |

|    4 |

+------+

set optimizer_switch='condition_pushdown_for_derived=off';

Query OK, 0 rows affected (0.00 sec)

select * from (select a from ten order by a limit 5) TBL where TBL.a not in (1,2,3);

+------+

| a    |

+------+

|    0 |

|    4 |

+------+

set optimizer_switch='condition_pushdown_for_derived=on';

Query OK, 0 rows affected (0.00 sec)

select * from (select a from ten order by a limit 5) TBL where TBL.a not in (1,2,3);

+------+

| a    |

+------+

|    0 |

|    4 |

|    5 |

|    6 |

|    7 |

+------+