Description:
I found a test case which may cause mysql server produce incorrect result, and the behavior is attached as follows:
```
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.29-debug-asan Source distribution
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> drop database mytest;
Query OK, 1 row affected (0.12 sec)
mysql> create database mytest;
Query OK, 1 row affected (0.02 sec)
mysql> use mytest;
Database changed
mysql> CREATE TABLE t0 ( c0 DECIMAL ( 10 , 0 ) NOT NULL , PRIMARY KEY ( c0 ) , UNIQUE KEY c0 ( c0 ) ) ;
Query OK, 0 rows affected (0.15 sec)
mysql> INSERT INTO t0 VALUES ( 123 );
Query OK, 1 row affected (0.03 sec)
mysql> SELECT ra0 . c0 FROM t0 AS ra0 WHERE ra0 . c0 NOT IN ( SELECT ra1 . c0 FROM t0 ra1 WHERE ( ra1 . c0 IN ( SELECT ra2 . c0 AS ca0 FROM t0 ra2 ) ) = ( ra1 . c0 ) ) ;
+-----+
| c0 |
+-----+
| 123 |
+-----+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT ra0 . c0 FROM t0 AS ra0 WHERE ra0 . c0 NOT IN ( SELECT ra1 . c0 FROM t0 ra1 WHERE ( ra1 . c0 IN ( SELECT ra2 . c0 AS ca0 FROM t0 ra2 ) ) = ( ra1 . c0 ) ) ;
+----+--------------------+-------+------------+-----------------+---------------+---------+---------+---------------+------+----------+--------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------------+-----------------+---------------+---------+---------+---------------+------+----------+--------------------------------------+
| 1 | PRIMARY | ra0 | NULL | index | NULL | PRIMARY | 5 | NULL | 1 | 100.00 | Using index |
| 1 | PRIMARY | ra1 | NULL | eq_ref | PRIMARY,c0 | PRIMARY | 5 | mytest.ra0.c0 | 1 | 100.00 | Using where; Not exists; Using index |
| 3 | DEPENDENT SUBQUERY | ra2 | NULL | unique_subquery | PRIMARY,c0 | PRIMARY | 5 | func | 1 | 100.00 | Using index |
+----+--------------------+-------+------------+-----------------+---------------+---------+---------+---------------+------+----------+--------------------------------------+
3 rows in set, 1 warning (0.01 sec)
mysql> SET SESSION optimizer_switch='loosescan=off,firstmatch=off,duplicateweedout=off';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT ra0 . c0 FROM t0 AS ra0 WHERE ra0 . c0 NOT IN ( SELECT ra1 . c0 FROM t0 ra1 WHERE ( ra1 . c0 IN ( SELECT ra2 . c0 AS ca0 FROM t0 ra2 ) ) = ( ra1 . c0 ) ) ;
Empty set (0.00 sec)
mysql> EXPLAIN SELECT ra0 . c0 FROM t0 AS ra0 WHERE ra0 . c0 NOT IN ( SELECT ra1 . c0 FROM t0 ra1 WHERE ( ra1 . c0 IN ( SELECT ra2 . c0 AS ca0 FROM t0 ra2 ) ) = ( ra1 . c0 ) ) ;
+----+--------------------+-------------+------------+-----------------+---------------------+---------------------+---------+---------------+------+----------+-------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------------+------------+-----------------+---------------------+---------------------+---------+---------------+------+----------+-------------------------+
| 1 | PRIMARY | ra0 | NULL | index | NULL | PRIMARY | 5 | NULL | 1 | 100.00 | Using index |
| 1 | PRIMARY | <subquery2> | NULL | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 6 | mytest.ra0.c0 | 1 | 100.00 | Using where; Not exists |
| 2 | MATERIALIZED | ra1 | NULL | index | PRIMARY,c0 | PRIMARY | 5 | NULL | 1 | 100.00 | Using index |
| 3 | DEPENDENT SUBQUERY | ra2 | NULL | unique_subquery | PRIMARY,c0 | PRIMARY | 5 | func | 1 | 100.00 | Using index |
+----+--------------------+-------------+------------+-----------------+---------------------+---------------------+---------+---------------+------+----------+-------------------------+
4 rows in set, 1 warning (0.00 sec)
```
When I close some optimization option to force the optimizer use materialization-lookup, I get inconsistent result. The server fails to fetch the row in the second query.
How to repeat:
Test case:
```
CREATE TABLE t0 ( c0 DECIMAL, PRIMARY KEY ( c0 ) ) ;
INSERT INTO t0 VALUES ( 123 );
SELECT ra0 . c0 FROM t0 AS ra0 WHERE ra0 . c0 NOT IN ( SELECT ra1 . c0 FROM t0 ra1 WHERE ( ra1 . c0 IN ( SELECT ra2 . c0 AS ca0 FROM t0 ra2 ) ) = ( ra1 . c0 ) ) ;
EXPLAIN SELECT ra0 . c0 FROM t0 AS ra0 WHERE ra0 . c0 NOT IN ( SELECT ra1 . c0 FROM t0 ra1 WHERE ( ra1 . c0 IN ( SELECT ra2 . c0 AS ca0 FROM t0 ra2 ) ) = ( ra1 . c0 ) ) ;
SET SESSION optimizer_switch='loosescan=off,firstmatch=off,duplicateweedout=off';
SELECT ra0 . c0 FROM t0 AS ra0 WHERE ra0 . c0 NOT IN ( SELECT ra1 . c0 FROM t0 ra1 WHERE ( ra1 . c0 IN ( SELECT ra2 . c0 AS ca0 FROM t0 ra2 ) ) = ( ra1 . c0 ) ) ;
EXPLAIN SELECT ra0 . c0 FROM t0 AS ra0 WHERE ra0 . c0 NOT IN ( SELECT ra1 . c0 FROM t0 ra1 WHERE ( ra1 . c0 IN ( SELECT ra2 . c0 AS ca0 FROM t0 ra2 ) ) = ( ra1 . c0 ) ) ;
```
