Bug #111706 Incorrect result

• Submitted: 10 Jul 2023 8:24
• Modified: 10 Jul 2023 10:33
• Reporter: Doris Li
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.28, 8.0.33
• OS: Any
• CPU Architecture: Any

[10 Jul 2023 8:24] Doris Li

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)

[10 Jul 2023 10:33] MySQL Verification Team

Hello Doris Li,

Thank you for the report and test case.

regards,
Umesh

[17 Mar 9:33] Przemyslaw Malkowski

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)