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 UNION q2 LIMIT 0, then the entire UNION 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]> 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
|
|
|
-- The following three queries are negative cases
|
MariaDB [mysql]> SELECT * FROM empty_table UNION SELECT * FROM time_zone_transition CROSS JOIN help_topic LIMIT 0; |
Empty set (10 min 27.314 sec) --wasting too much time |
|
|
MariaDB [mysql]> SELECT * FROM empty_table UNION SELECT * FROM not_empty_table LIMIT 0; |
Empty set (22 min 38.176 sec) --wasting too much time |
|
|
MariaDB [mysql]> SELECT * FROM not_empty_table UNION SELECT * FROM not_empty_table LIMIT 0; |
Empty set (40 min 19.430 sec) --wasting too much time |
|
-- The following queries are positive cases |
MariaDB [mysql]> SELECT * FROM empty_table UNION (SELECT * FROM time_zone_transition CROSS JOIN help_topic LIMIT 0); |
Empty set (0.001 sec) |
|
|
MariaDB [mysql]> SELECT * FROM empty_table UNION (SELECT * FROM not_empty_table LIMIT 0); |
Empty set (0.001 sec) |
|
|
MariaDB [mysql]> SELECT * FROM empty_table UNION SELECT * FROM time_zone_transition CROSS JOIN help_topic HAVING false; |
Empty set (0.001 sec) |
|
|
MariaDB [mysql]> SELECT * FROM empty_table UNION SELECT * FROM time_zone_transition CROSS JOIN help_topic WHERE false; |
Empty set (0.001 sec) |
|
|
MariaDB [mysql]> SELECT * FROM empty_table UNION SELECT * FROM not_empty_table HAVING false; |
Empty set (0.002 sec) |
|
|
MariaDB [mysql]> SELECT * FROM empty_table UNION SELECT * FROM not_empty_table WHERE false; |
Empty set (0.001 sec) |
|
|
MariaDB [mysql]> SELECT * FROM not_empty_table WHERE false UNION SELECT * FROM not_empty_table WHERE false; |
Empty set (0.002 sec) |
|
|
MariaDB [mysql]> SELECT * FROM not_empty_table HAVING false UNION SELECT * FROM not_empty_table HAVING false; |
Empty set (0.001 sec) |
|
|
MariaDB [mysql]> SELECT * FROM time_zone_transition CROSS JOIN help_topic WHERE false UNION SELECT * FROM time_zone_transition CROSS JOIN help_topic WHERE false; |
Empty set (0.001 sec) |
|
|
MariaDB [mysql]> SELECT * FROM time_zone_transition CROSS JOIN help_topic HAVING false UNION SELECT * FROM time_zone_transition CROSS JOIN help_topic HAVING false; |
Empty set (0.001 sec) |
|
|
MariaDB [mysql]> (SELECT * FROM not_empty_table LIMIT 0) UNION (SELECT * FROM not_empty_table LIMIT 0); |
Empty set (0.002 sec) |
Attachments
Issue Links
- is duplicated by
-
-
- Open
-