Bug #118017 A very serious performance bug
Submitted: 20 Apr 10:59 Modified: 19 May 14:53
Reporter: jinhui lai Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:9.2.0, 9.3.0 OS:Ubuntu (22.04)
Assigned to: CPU Architecture:Any

[20 Apr 10:59] jinhui lai
Description:
The following two queries will always return an empty result. However, the second query wastes lots of execution time. If there are a large number of queries connected through the INTERSECT keyword, the wasted computing time will be very very very large.

How to repeat:
mysql>  use information_schema;
mysql>  SELECT * from TABLES CROSS JOIN VIEWS ON TABLES.TABLE_NAME != VIEWS.TABLE_NAME INTERSECT SELECT * from TABLES CROSS JOIN VIEWS ON TABLES.TABLE_NAME LIKE VIEWS.TABLE_NAME LIMIT 0;
Empty set (0.00 sec)

mysql>  SELECT * from TABLES CROSS JOIN VIEWS ON TABLES.TABLE_NAME != VIEWS.TABLE_NAME INTERSECT (SELECT * from TABLES CROSS JOIN VIEWS ON TABLES.TABLE_NAME LIKE VIEWS.TABLE_NAME LIMIT 0);
Empty set (6 min 51.16 sec)
[20 Apr 10:59] jinhui lai
This problem also appears in mysql-9.3.0, but it is more obvious in mysql-9.2.0.
[22 Apr 11:14] jinhui lai
-- This is a supplementary information.
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 INTERSECT (SELECT * FROM TABLES CROSS JOIN VIEWS
ON TABLES.TABLE_NAME LIKE VIEWS.TABLE_NAME LIMIT 0);
Empty set (7 min 16.78 sec)
mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Handler_read_first    | 4       |
| Handler_read_key      | 1174105 |
| Handler_read_last     | 0       |
| Handler_read_next     | 1141702 |
| Handler_read_prev     | 0       |
| Handler_read_rnd      | 0       |
| Handler_read_rnd_next | 1160390 |
+-----------------------+---------+
[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