Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
11.7.2, 10.11, 11.4
-
None
-
docker pull mariadb:latest
Description
Dear MariaDB Developer,
I think it is a common scenario in practice. When users need to quickly create an empty table from an existing views or tables, such as combining t0 and t1, users may execute a SQL statement like CREATE TABLE empty_table AS SELECT * FROM t0 CROSS JOIN t1 EXCEPT SELECT * FROM t0 CROSS JOIN t1. As q EXCEPT q always returns an empty set, the above SQL statement should create an empty table quickly. However, the fact is that the SQL statement consumes a lot of time.
It is a common case. Especially when there are many columns in t0 or t1, manually recreating the table structure can be very time-consuming. The above SQL statement is a quick way to create a new empty table.
Actual behavior:
MariaDB [(none)]> create database test; |
MariaDB [(none)]> use test; |
MariaDB [test]> CREATE TABLE t0 AS SELECT * FROM mysql.time_zone_transition; |
Query OK, 31306 rows affected (9.547 sec) |
Records: 31306 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> CREATE TABLE t1 AS SELECT * FROM mysql.help_topic |
Query OK, 839 rows affected (8.895 sec) |
Records: 839 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> CREATE TABLE empty_table0 AS SELECT * FROM t0 EXCEPT SELECT * FROM t0; |
Query OK, 0 rows affected (0.859 sec) |
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> CREATE TABLE empty_table1 AS SELECT * FROM t0 CROSS JOIN t1 EXCEPT SELECT * FROM t0 CROSS JOIN t1; -- buggy SQL statement which wastes too much time. |
Query OK, 0 rows affected (*49 min 35.189 sec*) |
Records: 0 Duplicates: 0 Warnings: 0
|
Expected behavior:
MariaDB [test]> CREATE TABLE empty_table1 AS SELECT * FROM t0 CROSS JOIN t1 EXCEPT SELECT * FROM t0 CROSS JOIN t1; |
Query OK, 0 rows affected (less time) |
Records: 0 Duplicates: 0 Warnings: 0
|
I believe that much unnecessary time (49 min 35.189 sec) is wasted, and the reason may be that mariadb performs physical reading when executes the query of the form q EXCEPT q, which always returns an empty set.
MariaDB [test]> SELECT * FROM t0 CROSS JOIN t1 EXCEPT SELECT * FROM t0 CROSS JOIN t1; |
Empty set (49 min 26.203 sec) |
Best regard,
Jinhui Lai