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, and q1 (the left branch of an EXCEPT operation) is an impossible HAVING query (e.g., HAVING FALSE), 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 [(none)]> use mysql; |
MariaDB [mysql]> SELECT * FROM time_zone_transition CROSS JOIN help_topic HAVING FALSE EXCEPT SELECT * FROM time_zone_transition CROSS JOIN help_topic; |
Empty set (4 min 23.874 sec) -- wasting too much time |
 |
MariaDB [mysql]> explain format=json SELECT * FROM time_zone_transition CROSS JOIN help_topic HAVING FALSE EXCEPT SELECT * FROM time_zone_transition CROSS JOIN help_topic \G |
*************************** 1. row ***************************
|
EXPLAIN: {
|
"query_block": { |
"union_result": { |
"table_name": "<except1,2>", |
"access_type": "ALL", |
"query_specifications": [ |
{
|
"query_block": { |
"select_id": 1, |
"table": { |
"message": "Impossible HAVING" |
}
|
}
|
},
|
{
|
"query_block": { |
"select_id": 2, |
"operation": "EXCEPT", |
"cost": 2443.928879, |
"nested_loop": [ |
{
|
"table": { |
"table_name": "help_topic", |
"access_type": "ALL", |
"loops": 1, |
"rows": 839, |
"cost": 0.255100898, |
"filtered": 100 |
}
|
},
|
{
|
"block-nl-join": { |
"table": { |
"table_name": "time_zone_transition", |
"access_type": "ALL", |
"loops": 839, |
"rows": 31306, |
"cost": 2443.673778, |
"filtered": 100 |
},
|
"buffer_type": "flat", |
"buffer_size": "255Kb", |
"join_type": "BNL" |
}
|
}
|
]
|
}
|
}
|
]
|
}
|
}
|
}
|
1 row in set (0.001 sec) |
Attachments
Issue Links
- duplicates
-
-
- Open
-