Bug #118015 The query which always return a empty result should not comsume executing time

• Submitted: 20 Apr 7:18
• Modified: 22 Apr 7:28
• 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 7:18] jinhui lai

Description:
If you have a query of the form:
Q1 INTERSECT Q2 ... INTERSECT Qn, 
and you know that query Qn always returns an empty set(e.g., a query with WHERE 1=2), then the entire intersection will always be empty.
I think that the query should be eliminated during optimization, as it will always return an empty set and should never consume execution 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

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 != VIEWS.TABLE_NAME WHERE 1=2;
Empty set (1.53 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 > VIEWS.TABLE_NAME INTERSECT SELECT * from TABLES CROSS JOIN VIEWS ON TABLES.TABLE_NAME < VIEWS.TABLE_NAME WHERE 1=2;
Empty set (11.84 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 > VIEWS.TABLE_NAME INTERSECT SELECT * from TABLES CROSS JOIN VIEWS ON TABLES.TABLE_NAME < VIEWS.TABLE_NAME INTERSECT SELECT * from TABLES CROSS JOIN VIEWS ON TABLES.TABLE_NAME != VIEWS.TABLE_NAME WHERE 1=2;
Empty set (26.58 sec)

In the above example, if there are a large number of queries connected through the INTERSECT keyword, the wasted computing time will be very large. According to the query plan, the second query below obviously consumed unnecessary execution time：

mysql> EXPLAIN SELECT * from TABLES CROSS JOIN VIEWS ON TABLES.TABLE_NAME = VIEWS.TABLE_NAME WHERE 1=2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra            |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * from TABLES CROSS JOIN VIEWS ON TABLES.TABLE_NAME = VIEWS.TABLE_NAME INTERSECT SELECT * from TABLES CROSS JOIN VIEWS ON TABLES.TABLE_NAME != VIEWS.TABLE_NAME WHERE 1=2;
+----+------------------+----------------+------------+--------+-----------------------------------------------------------------------------+---------+---------+---------------------------------------+------+----------+--------------------------------------------+
| id | select_type      | table          | partitions | type   | possible_keys                                                               | key     | key_len | ref                                   | rows | filtered | Extra                                      |
+----+------------------+----------------+------------+--------+-----------------------------------------------------------------------------+---------+---------+---------------------------------------+------+----------+--------------------------------------------+
|  1 | PRIMARY          | cat            | NULL       | index  | PRIMARY                                                                     | name    | 194     | NULL                                  |    1 |   100.00 | Using index                                |
|  1 | PRIMARY          | cat            | NULL       | index  | PRIMARY                                                                     | name    | 194     | NULL                                  |    1 |   100.00 | Using index; Using join buffer (hash join) |
|  1 | PRIMARY          | tbl            | NULL       | ALL    | schema_id                                                                   | NULL    | NULL    | NULL                                  | 7768 |   100.00 | Using where; Using join buffer (hash join) |
|  1 | PRIMARY          | col            | NULL       | eq_ref | PRIMARY                                                                     | PRIMARY | 8       | mysql.tbl.collation_id                |    1 |   100.00 | NULL                                       |
|  1 | PRIMARY          | sch            | NULL       | eq_ref | PRIMARY,catalog_id                                                          | PRIMARY | 8       | mysql.tbl.schema_id                   |    1 |   100.00 | Using where                                |
|  1 | PRIMARY          | ts             | NULL       | eq_ref | PRIMARY                                                                     | PRIMARY | 8       | mysql.tbl.tablespace_id               |    1 |   100.00 | NULL                                       |
|  1 | PRIMARY          | stat           | NULL       | eq_ref | PRIMARY                                                                     | PRIMARY | 388     | mysql.sch.name,mysql.tbl.name         |    1 |   100.00 | NULL                                       |
|  1 | PRIMARY          | vw             | NULL       | ref    | schema_id,type,view_client_collation_id,view_connection_collation_id,type_2 | type    | 1       | const                                 |  100 |    10.00 | Using where                                |
|  1 | PRIMARY          | conn_coll      | NULL       | eq_ref | PRIMARY                                                                     | PRIMARY | 8       | mysql.vw.view_connection_collation_id |    1 |   100.00 | NULL                                       |
|  1 | PRIMARY          | client_coll    | NULL       | eq_ref | PRIMARY,character_set_id                                                    | PRIMARY | 8       | mysql.vw.view_client_collation_id     |    1 |   100.00 | NULL                                       |
|  1 | PRIMARY          | cs             | NULL       | eq_ref | PRIMARY                                                                     | PRIMARY | 8       | mysql.client_coll.character_set_id    |    1 |   100.00 | NULL                                       |
|  1 | PRIMARY          | sch            | NULL       | eq_ref | PRIMARY,catalog_id                                                          | PRIMARY | 8       | mysql.vw.schema_id                    |    1 |   100.00 | Using where                                |
|  2 | INTERSECT        | NULL           | NULL       | NULL   | NULL                                                                        | NULL    | NULL    | NULL                                  | NULL |     NULL | Impossible WHERE                           |
|  3 | INTERSECT RESULT | <intersect1,2> | NULL       | ALL    | NULL                                                                        | NULL    | NULL    | NULL                                  | NULL |     NULL | Using temporary                            |
+----+------------------+----------------+------------+--------+-----------------------------------------------------------------------------+---------+---------+---------------------------------------+------+----------+--------------------------------------------+
14 rows in set, 1 warning (0.00 sec)

[20 Apr 7:50] jinhui lai

This problem also appears in mysql-9.3.0, but it is more obvious in mysql-9.2.0.

[20 Apr 14:12] jinhui lai

The following sql is more obvious
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 != VIEWS.TABLE_NAME WHERE 1=2);
Empty set (2 min 20.52 sec)

[21 Apr 6:17] jinhui lai

I executed this sql again, and it consumed more time.
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 != VIEWS.TABLE_NAME WHERE 1=2);
Empty set (7 min 35.59 sec)

[22 Apr 7:28] 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 INTERSECT (SELECT * FROM TABLES CROSS JOIN VIEWS ON TABLES.TABLE_NAME != VIEWS.TABLE_NAME WHERE 1=2);
Empty set (7 min 26.01 sec)
mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Handler_read_first    | 4       |
| Handler_read_key      | 1175343 |
| Handler_read_last     | 0       |
| Handler_read_next     | 1143786 |
| Handler_read_prev     | 0       |
| Handler_read_rnd      | 0       |
| Handler_read_rnd_next | 1160390 |
+-----------------------+---------+
7 rows in set (0.00 sec)