Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
11.7.2
-
None
-
None
-
docker pull mariabd: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 LIMIT 0 query (e.g., SELECT * FROM time_zone_transition CROSS JOIN help_topic 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]> (SELECT * FROM time_zone_transition CROSS JOIN help_topic LIMIT 0) EXCEPT SELECT * FROM time_zone_transition CROSS JOIN help_topic; |
Empty set (3 min 57.720 sec) -- wasting too much time |
 |
MariaDB [mysql]> explain format=json (SELECT * FROM time_zone_transition CROSS JOIN help_topic LIMIT 0) 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, |
"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": "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.002 sec) |