MariaDB Fails to Handle INTERSECT Operation Involving Empty Table

Hi, MariaDB Developers,

Please considering such a query: empty_table INTERSECT 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 `INTERSECT` 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 INTERSECT 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 regard,
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 INTERSECT SELECT * FROM time_zone_transition CROSS JOIN help_topic;

Empty set (4 min 45.983 sec)

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

Empty set (13 min 44.527 sec)