Description:
I found a test case that causes mysql server to produce incorrect results, the behavior is listed as follows:
```
mysql> CREATE TABLE t0 ( c0 DOUBLE ) ;
Query OK, 0 rows affected (0.15 sec)
mysql> CREATE TABLE t1 ( c1 DOUBLE ) ;
Query OK, 0 rows affected (0.13 sec)
mysql> INSERT INTO t0 VALUES ( 0 );
Query OK, 1 row affected (0.02 sec)
mysql> INSERT INTO t1 VALUES ( '-0' );
Query OK, 1 row affected (0.03 sec)
mysql> SELECT t0.c0 FROM t0 WHERE t0.c0 IN (SELECT t1.c1 FROM t1 WHERE t0.c0 = t1.c1);
+------+
| c0 |
+------+
| 0 |
+------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT t0.c0 FROM t0 WHERE t0.c0 IN (SELECT t1.c1 FROM t1 WHERE t0.c0 = t1.c1);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------------------------------------+
| 1 | SIMPLE | t0 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where; FirstMatch(t0); Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------------------------------------+
2 rows in set, 2 warnings (0.00 sec)
mysql> SET SESSION optimizer_switch='loosescan=off,firstmatch=off,duplicateweedout=off';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT t0.c0 FROM t0 WHERE t0.c0 IN (SELECT t1.c1 FROM t1 WHERE t0.c0 = t1.c1);
Empty set (0.00 sec)
mysql> EXPLAIN SELECT t0.c0 FROM t0 WHERE t0.c0 IN (SELECT t1.c1 FROM t1 WHERE t0.c0 = t1.c1);
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------------+------+----------+-------------+
| 1 | SIMPLE | t0 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
| 1 | SIMPLE | <subquery2> | NULL | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 18 | mytest.t0.c0,mytest.t0.c0 | 1 | 100.00 | NULL |
| 2 | MATERIALIZED | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------------+------+----------+-------------+
3 rows in set, 2 warnings (0.00 sec)
```
In this test case, when I disabled loosescan, firstmatch and duplicateweedout, and forced the optimizer to use materialization-lookup, the result is incorrect, which failed to fetch the row.
How to repeat:
Just run the test case.
