Bug #118017 A very serious performance bug

• Submitted: 20 Apr 10:59
• Modified: 22 Apr 11:14
• Reporter: jinhui lai
• Status: Analyzing
• Category: MySQL Server: Optimizer
• Severity: S5 (Performance)
• Version: 9.2.0, 9.3.0
• OS: Ubuntu (22.04)
• Assigned to: MySQL Verification Team
• 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 |
+-----------------------+---------+