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)
