Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
11.7.2
-
None
-
None
-
docker pull mariadb:latest
Description
If a query is structured as q1 INTERSECT q2, and q2 is an impossible HAVING query (e.g., HAVING FALSE), then the entire intersection 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.
MariaDB [(none)]> use mysql; |
MariaDB [mysql]> SELECT * FROM time_zone_transition CROSS JOIN help_topic INTERSECT SELECT * FROM time_zone_transition CROSS JOIN help_topic HAVING FALSE; |
Empty set (18 min 16.287 sec) -- wasting too much time |
 |
MariaDB [mysql]> explain format=json SELECT * FROM time_zone_transition CROSS JOIN help_topic INTERSECT SELECT * FROM time_zone_transition CROSS JOIN help_topic HAVING FALSE \G |
*************************** 1. row ***************************
|
EXPLAIN: {
|
"query_block": { |
"union_result": { |
"table_name": "<intersect1,2>", |
"access_type": "ALL", |
"query_specifications": [ |
{
|
"query_block": { |
"select_id": 1, |
"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" |
}
|
}
|
]
|
}
|
},
|
{
|
"query_block": { |
"select_id": 2, |
"operation": "INTERSECT", |
"table": { |
"message": "Impossible HAVING" |
}
|
}
|
}
|
]
|
}
|
}
|
}
|
1 row in set (0.001 sec) |
Attachments
Issue Links
- is duplicated by
-
-
- Open
-