Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Not a Bug
-
11.7.2
-
None
-
docker
Description
q1: SELECT /*+ JOIN_ORDER(tables, views) */ * FROM tables CROSS JOIN views ON tables.table_name != views.table_name
q2: SELECT * FROM tables CROSS JOIN views ON tables.table_name != views.table_name;
Since q1 is equivalent to q2, q1 EXCEPT q2 should always return an empty set and consume minimal execution time. However, the query returns a non-empty result and comsume 5.843 sec computation time.
Repeat it as following:
MariaDB > use information_schema;
MariaDB [information_schema]> SELECT /*+ JOIN_ORDER(tables, views) */ * FROM tables CROSS JOIN views ON tables.table_name != views.table_name EXCEPT (SELECT * FROM tables CROSS JOIN views ON tables.table_name != views.table_name);
...
8668 rows in set (5.843 sec)