Bug #111706 Incorrect result

Description:
mysql> set optimizer_switch='default';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t1 LEFT OUTER JOIN t0 ON t1.c1 = t0.c0 where (t1.c1 IN (SELECT min(tin.c0) FROM t0 as tin group by c0));
+------+------+-------+
| c0   | c1   | c0    |
+------+------+-------+
| NULL |    0 | z)]s  |
| NULL |    0 | IcK5A |
| NULL |    0 |       |
+------+------+-------+
3 rows in set (0.01 sec)

mysql> SET optimizer_switch='subquery_to_derived=on';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t1 LEFT OUTER JOIN t0 ON t1.c1 = t0.c0 where (t1.c1 IN (SELECT min(tin.c0) FROM t0 as tin group by c0));
+------+------+-------+
| c0   | c1   | c0    |
+------+------+-------+
| NULL |    0 | z)]s  |
| NULL |    0 | IcK5A |
| NULL |    0 |       |
| NULL |    0 | z)]s  |
| NULL |    0 | IcK5A |
| NULL |    0 |       |
| NULL |    0 | z)]s  |
| NULL |    0 | IcK5A |
| NULL |    0 |       |
+------+------+-------+
9 rows in set (0.00 sec)

When the optimizer_switch is changed the result of the same query is different.One of them must be incorrect.

How to repeat:
mysql> select * from t0;
+-----------+
| c0        |
+-----------+
| 292269042 |
|           |
| NULL      |
| IcK5A     |
| NULL      |
| z)]s      |
| 292269042 |
| 4f*T      |
+-----------+
8 rows in set (0.00 sec)

mysql> select * from t1;
+------+-----------+
| c0   | c1        |
+------+-----------+
|  255 |      NULL |
| NULL |         0 |
| NULL | 292269000 |
|  000 |      NULL |
| NULL |      NULL |
+------+-----------+
5 rows in set (0.00 sec)

mysql> explain t0;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| c0    | text | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> explain t1;
+-------+------------------------------+------+-----+---------+-------+
| Field | Type                         | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| c0    | tinyint(3) unsigned zerofill | YES  | UNI | NULL    |       |
| c1    | float                        | YES  |     | NULL    |       |
+-------+------------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Hello Doris Li,

Thank you for the report and test case.

regards,
Umesh

This problem still applies in 8.0.41, and is reproducible with an even simpler test case:

mysql > SET optimizer_switch='subquery_to_derived=off';
Query OK, 0 rows affected (0.00 sec)

mysql > SELECT * FROM t1 where (t1.c1 IN (SELECT min(tin.c0) FROM t0 as tin group by c0));
+------+------+
| c0   | c1   |
+------+------+
| NULL |    0 |
+------+------+
1 row in set (0.00 sec)

mysql > SET optimizer_switch='subquery_to_derived=on';
Query OK, 0 rows affected (0.00 sec)

mysql > SELECT * FROM t1 where (t1.c1 IN (SELECT min(tin.c0) FROM t0 as tin group by c0));
+------+------+
| c0   | c1   |
+------+------+
| NULL |    0 |
| NULL |    0 |
| NULL |    0 |
+------+------+
3 rows in set (0.00 sec)