Return to Answer

added 1351 characters in body

edited Dec 27, 2019 at 23:12

2.7k
1
8
13

But Oracle did catch up at some point:

mysql> select cname from (select s.CName from (select 'A' as CNAME) s) t; +-------+ | CName | +-------+ | A | +-------+ 1 row in set (0.00 sec) mysql> explain -> select cname from (select s.CName from (select 'A' as CNAME) s) t; +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+ | 1 | PRIMARY | <derived3> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | | 3 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+ 2 rows in set, 1 warning (0.00 sec) mysql> select @@version; +-----------+ | @@version | +-----------+ | 8.0.17 | +-----------+ 1 row in set (0.00 sec)

But Oracle did catch up at some point:

mysql> select cname from (select s.CName from (select 'A' as CNAME) s) t; +-------+ | CName | +-------+ | A | +-------+ 1 row in set (0.00 sec) mysql> explain -> select cname from (select s.CName from (select 'A' as CNAME) s) t; +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+ | 1 | PRIMARY | <derived3> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | | 3 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+ 2 rows in set, 1 warning (0.00 sec) mysql> select @@version; +-----------+ | @@version | +-----------+ | 8.0.17 | +-----------+ 1 row in set (0.00 sec)

Source Link

answered Dec 27, 2019 at 23:07

Rick James

2.7k
1
8
13

(Thanks for the complete test case.)

Different Optimizers:

mysql> explain -> select cname from (select s.CName from (select 'A' as CNAME) s) t; +----+-------------+------------+--------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+------+---------+------+------+----------------+ | 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | NULL | | 2 | DERIVED | <derived3> | system | NULL | NULL | NULL | NULL | 1 | NULL | | 3 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+------------+--------+---------------+------+---------+------+------+----------------+ 3 rows in set (0.01 sec) mysql> select @@version; +-----------------+ | @@version | +-----------------+ | 5.6.22-71.0-log | +-----------------+

versus

mysql> explain -> select cname from (select s.CName from (select 'A' as CNAME) s) t; +------+-------------+------------+--------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+--------+---------------+------+---------+------+------+----------------+ | 1 | PRIMARY | <derived3> | system | NULL | NULL | NULL | NULL | 1 | | | 3 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +------+-------------+------------+--------+---------------+------+---------+------+------+----------------+ 2 rows in set (0.00 sec) mysql> select @@version; +--------------------------------------+ | @@version | +--------------------------------------+ | 10.4.1-MariaDB-1:10.4.1+maria~bionic | +--------------------------------------+

Notice how MariaDB was smart enough to throw away one layer of your query.

(MySQL/MariaDB questions are better handled on stackoverflow.com or dba.stackexchange.com .)