Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.7.2
-
None
-
docker
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.
You can repeat it as follow queries:
MariaDB [information_schema]> SELECT * from TABLES CROSS JOIN VIEWS ON TABLES.TABLE_NAME != VIEWS.TABLE_NAME WHERE 1=2;
|
Empty set (0.002 sec)
|
|
|
MariaDB [information_schema]> 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 (6.598 sec)
|
|
|
MariaDB [information_schema]> SELECT * from TABLES CROSS JOIN VIEWS ON TABLES.TABLE_NAME != VIEWS.TABLE_NAME INTERSECT SELECT * from TABLES CROSS JOIN VIEWS ON TABLES.TABLE_NAME NOT IN (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 (8.737 sec)
|
|
|
MariaDB [information_schema]> SELECT * from TABLES CROSS JOIN VIEWS ON TABLES.TABLE_NAME != VIEWS.TABLE_NAME INTERSECT SELECT * from TABLES CROSS JOIN VIEWS ON TABLES.TABLE_NAME NOT IN (VIEWS.TABLE_NAME) INTERSECT SELECT * from TABLES CROSS JOIN VIEWS ON TABLES.TABLE_NAME NOT LIKE VIEWS.TABLE_NAME INTERSECT SELECT * from TABLES CROSS JOIN VIEWS ON TABLES.TABLE_NAME = VIEWS.TABLE_NAME WHERE 1=2;
|
Empty set (9.759 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 following query plan, the second query below obviously consumed unnecessary execution time：
MariaDB [information_schema]> EXPLAIN SELECT * from TABLES CROSS JOIN VIEWS ON TABLES.TABLE_NAME != VIEWS.TABLE_NAME WHERE 1=2;
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------+
|
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
|
+------+-------------+-------+------+---------------+------+---------+------+------+------------------+
|
1 row in set (0.002 sec)
|
|
|
MariaDB [information_schema]> EXPLAIN SELECT * from TABLES CROSS JOIN VIEWS ON TABLES.TABLE_NAME != VIEWS.TABLE_NAME INTERSECT SELECT * f
|
rom TABLES CROSS JOIN VIEWS ON TABLES.TABLE_NAME NOT IN (VIEWS.TABLE_NAME) INTERSECT SELECT * from TABLES CROSS JOIN VIEWS ON TABLES.TA
|
BLE_NAME NOT LIKE VIEWS.TABLE_NAME INTERSECT SELECT * from TABLES CROSS JOIN VIEWS ON TABLES.TABLE_NAME = VIEWS.TABLE_NAME WHERE 1=2;
|
+------+------------------+--------------------+------+---------------+------+---------+------+------+-----------------------------------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+------------------+--------------------+------+---------------+------+---------+------+------+-----------------------------------------------------------------------------------------+
|
| 1 | PRIMARY | TABLES | ALL | NULL | NULL | NULL | NULL | NULL | Open_full_table; Scanned all databases |
|
| 1 | PRIMARY | VIEWS | ALL | NULL | NULL | NULL | NULL | NULL | Using where; Open_full_table; Scanned all databases; Using join buffer (flat, BNL join) |
|
| 2 | INTERSECT | TABLES | ALL | NULL | NULL | NULL | NULL | NULL | Open_full_table; Scanned all databases |
|
| 2 | INTERSECT | VIEWS | ALL | NULL | NULL | NULL | NULL | NULL | Using where; Open_full_table; Scanned all databases; Using join buffer (flat, BNL join) |
|
| 3 | INTERSECT | TABLES | ALL | NULL | NULL | NULL | NULL | NULL | Open_full_table; Scanned all databases |
|
| 3 | INTERSECT | VIEWS | ALL | NULL | NULL | NULL | NULL | NULL | Using where; Open_full_table; Scanned all databases; Using join buffer (flat, BNL join) |
|
| 4 | INTERSECT | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
|
| NULL | INTERSECT RESULT | <intersect1,2,3,4> | ALL | NULL | NULL | NULL | NULL | NULL | |
|
+------+------------------+--------------------+------+---------------+------+---------+------+------+-----------------------------------------------------------------------------------------+
|
8 rows in set (0.003 sec)
|
Attachments
Issue Links
- relates to
-
-
- Confirmed
-
-
-
- Confirmed
-