Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Duplicate
-
11.7.2
-
None
-
docker pull mariadb:latest
-
Not for Release Notes
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 WHERE query (e.g., WHERE 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 WHERE FALSE EXCEPT SELECT * FROM time_zone_transition CROSS JOIN help_topic; |
Empty set (4 min 23.941 sec) |
 |
MariaDB [mysql]> explain format=json SELECT * FROM time_zone_transition CROSS JOIN help_topic WHERE 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 WHERE" |
}
|
}
|
},
|
{
|
"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
-
-
- Confirmed
-