MariaDB Fails to Handle EXCEPT Operation Involving Empty Table

Hi, MariaDB Developers,

Please considering such a query: empty_table EXCEPT not_empty_table.

Obviously, the above query always returns an empty set. I think it should return an empty set quickly. However, it waste much time.

MariaDB fails to optimize queries containing EXCEPT operations when one branch includes an empty table. This leads to unnecessary memory consumption and query cancellation, even though the result should be deterministically empty.

I think this is a common case in actual production scenarios. It's important to clarify that users might not intentionally perform EXCEPT operations on empty tables. Rather, they may be unaware that a table is empty. For example, when data has been deleted by another user or process. If MariaDB can address this performance bug, it would significantly improve query efficiency and save users valuable time in such cases.

Thank you for your valuable time, looking forward to your reply!

Best regards,
Jinhui Lai

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

MariaDB [mysql]> SELECT * FROM empty_table EXCEPT SELECT * FROM time_zone_transition CROSS JOIN help_topic; --quickly way to reproduce this bug

Empty set (4 min 38.267 sec)

MariaDB [mysql]> SELECT * FROM empty_table EXCEPT SELECT * FROM  not_empty_table;

Empty set (13 min 41.030 sec)