MariaDB [test]> select * from t1 where (rand() < 0) and i in (select i from t2);

+------+

| i    |

+------+

|    1 |

|    2 |

|    3 |

|    4 |

|    5 |

|    6 |

|    7 |

|    8 |

|    9 |

|   10 |

+------+

10 rows in set (0.00 sec)

MariaDB [test]> set session optimizer_switch='materialization=off';                                                                                                                                                                                                                                                         

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select * from t1 where (rand() < 0) and i in (select i from t2);

Empty set (0.00 sec)

create table t1(i int);

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

create table t2(i int);

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

set session optimizer_switch='materialization=on';

select * from t1 where (rand() < 0) and i in (select i from t2);

set session optimizer_switch='materialization=off';

select * from t1 where (rand() < 0) and i in (select i from t2);