Bug #118018 A query expose a logic bug and performance bug

• Submitted: 20 Apr 13:49
• Modified: 19 May 14:53
• Reporter: jinhui lai
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 9.2.0, 9.3.0
• OS: Ubuntu (22.04)
• CPU Architecture: Any

[20 Apr 13:49] jinhui lai

Description:
A query q1 EXCEPT q1 should alway return an emepy result. However, in the following case, it return a non-emepy result and wasting lots of computing time (9 min 59.30 sec), when other DBMS like TiDB only comsume 0.06 sec.I think it is really a critical bug. This bug is obvious in mysql-9.2.0, but also appears in mysql-9.3.0. Notably, mysql-9.3.0 returns an emepy result correctly, but wastes some unnecessary computing time.

How to repeat:
sudo docker run -it --name mysql-9.2.0 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=1234 mysql:9.2.0
sudo docker exec -it mysql-9.2.0 mysql -uroot -p

mysql>  use information_schema;
mysql>  SELECT * FROM tables CROSS JOIN views ON tables.table_name != views.table_name EXCEPT (SELECT * FROM tables CROSS JOIN views ON tables.table_name != views.table_name);
...
200 rows in set (9 min 59.30 sec)

[22 Apr 7:10] jinhui lai

I execute the query in mysql-9.2.0 again. Then it costs more time and returns 500 row.
mysql>  use information_schema;
mysql> SELECT * FROM tables CROSS JOIN views ON tables.table_name != views.table_name EXCEPT (SELECT * FROM tables CROSS JOIN views ON tables.table_name != views.table_name);
...
500 rows in set (20 min 28.91 sec)

[22 Apr 7:15] jinhui lai

This is a supplementary information.

mysql>  use information_schema;
mysql> FLUSH STATUS;
Query OK, 0 rows affected (0.12 sec)

mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 0     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in set (0.01 sec)

mysql>  SELECT * FROM tables CROSS JOIN views ON tables.table_name != views.table_name EXCEPT (SELECT * FROM tables CROSS JOIN views ON tables.table_name != views.table_name);
...
500 rows in set (20 min 28.91 sec)

 SHOW SESSION STATUS LIKE 'Handler_read%';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Handler_read_first    | 7       |
| Handler_read_key      | 2411924 |
| Handler_read_last     | 0       |
| Handler_read_next     | 2314702 |
| Handler_read_prev     | 0       |
| Handler_read_rnd      | 0       |
| Handler_read_rnd_next | 1171862 |
+-----------------------+---------+
7 rows in set (0.00 sec)

[19 May 14:53] MySQL Verification Team

Hello jinhui lai,

Thank you for the report and feedback.
Verifying for now but I could not see the magnitude of slowness you had observed in your tests (sorry couldn't check on docker) in as much as 15+ attempts at my end. Development team may have follow up questions/request for details and if needed will request you to share. Thank you once again.

regards,
Umesh