Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
11.7.2
-
None
-
None
-
docker pull mariadb:latest
Description
Hi, MariaDB developers,
If a query is structured as q1 EXCEPT q2 LIMIT 0, then the entire EXCEPT will always yield an empty result set. Since the outcome is guaranteed to be empty, such a query should be eliminated during the optimization phase to avoid unnecessary computation and resource usage. You can reproduce it as the follow queries.
MariaDB > use mysql; |
|
|
MariaDB [mysql]> CREATE TABLE empty_table AS SELECT * FROM time_zone_transition CROSS JOIN help_topic LIMIT 0; |
|
|
MariaDB [mysql]> CREATE TABLE not_empty_table AS SELECT * FROM time_zone_transition CROSS JOIN help_topic; |
Query OK, 26265734 rows affected (2 hours 20.347 sec) |
Records: 26265734 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [mysql]> SELECT * FROM not_empty_table EXCEPT SELECT * FROM empty_table LIMIT 0; |
Empty set (20 min 49.084 sec) -- wasting too much time |
|
|
MariaDB [mysql]> (SELECT * FROM not_empty_table EXCEPT SELECT * FROM empty_table) LIMIT 0; |
Empty set (21 min 26.446 sec) -- wasting too much time |
|
|
MariaDB [mysql]> explain format=json SELECT * FROM not_empty_table EXCEPT SELECT * FROM empty_table LIMIT 0 \G |
*************************** 1. row ***************************
|
EXPLAIN: {
|
"query_block": { |
"union_result": { |
"table_name": "<except1,2>", |
"access_type": "ALL", |
"query_specifications": [ |
{
|
"query_block": { |
"select_id": 1, |
"cost": 8441.997988, |
"nested_loop": [ |
{
|
"table": { |
"table_name": "not_empty_table", |
"access_type": "ALL", |
"loops": 1, |
"rows": 25830925, |
"cost": 8441.997988, |
"filtered": 100 |
}
|
}
|
]
|
}
|
},
|
{
|
"query_block": { |
"select_id": 2, |
"operation": "EXCEPT", |
"cost": 0.0110178, |
"nested_loop": [ |
{
|
"table": { |
"table_name": "empty_table", |
"access_type": "ALL", |
"loops": 1, |
"rows": 1, |
"cost": 0.0110178, |
"filtered": 100 |
}
|
}
|
]
|
}
|
}
|
]
|
}
|
}
|
}
|
1 row in set (0.001 sec) |
Attachments
Issue Links
- duplicates
-
-
- Open
-