MariaDB 에서 정렬후 limit 하는 쿼리에 대한 이상한 경우

아래와 같은 이상한 경우를 확인 했다. 이러면 항상 결과값을 확인을 해야 한다는 이야긴데… 그러나 MySQL 5.7.15 에서는 발생 안한다. 이럼 MySQL 아니면 Percona 인가 보다.

/**
 Test
 
 Ubuntu
 dbadmin@localhost:test 16:04:46>select version();
+--------------------------+
| version()                |
+--------------------------+
| 10.0.25-MariaDB-1~trusty |
+--------------------------+
 
+-----------------+
| @@version       |
+-----------------+
| 10.1.16-MariaDB |
+-----------------+

*/
 
--
-- 01. Test Data Table Create
--

create table test_tbl ( today datetime default current_timestamp);
 
insert into test_tbl(sysdate());
insert into test_tbl(sysdate());
insert into test_tbl(sysdate());
insert into test_tbl(sysdate());
 
dbadmin@localhost:test 16:02:06>select * from test_tbl;

+---------------------+
| today               |
+---------------------+
| 2016-10-11 16:02:00 |
| 2016-10-11 16:02:03 |
| 2016-10-11 16:02:05 |
| 2016-10-11 16:02:06 |
+---------------------+
4 rows in set (0.00 sec)
 
--
-- 02. Check SQL
--
 
dbadmin@localhost:test 16:02:39>select * from test_tbl order by today desc limit 1;
+---------------------+
| today               |
+---------------------+
| 2016-10-11 16:02:06 |
+---------------------+
1 row in set (0.00 sec)
 
+------+-------------+----------+------+---------------+------+---------+------+------+----------+----------------+
| id   | select_type | table    | type | possible_keys | key  | key_len |  ref | rows | filtered | Extra          |
+------+-------------+----------+------+---------------+------+---------+------+------+----------+----------------+
| 1    | SIMPLE      | test_tbl | ALL  | NULL          | NULL | NULL    | NULL |    4 | 100.00   | Using filesort |
+------+-------------+----------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
 
Note (Code 1003): select `test`.`test_tbl`.`today` AS `today` from `test`.`test_tbl` order by `test`.`test_tbl`.`today` desc limit 1
 
--
-- 03. Check View SQL
--
 
dbadmin@localhost:test 16:02:51>select * from (select * from test_tbl order by today) aa limit 1;
 
+---------------------+
| today               |
+---------------------+
| 2016-10-11 16:02:00 |
+---------------------+
1 row in set (0.00 sec)
 
+------+-------------+----------+------+---------------+------+---------+------+------+----------+-------+
| id   | select_type | table    | type | possible_keys | key  | key_len |  ref | rows | filtered | Extra |
+------+-------------+----------+------+---------------+------+---------+------+------+----------+-------+
| 1    | SIMPLE      | test_tbl | ALL  | NULL          | NULL | NULL    | NULL | 4    | 100.00   |       |
+------+-------------+----------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
 
Note (Code 1003): select `test`.`test_tbl`.`today` AS `today` from `test`.`test_tbl` limit 1
-- 이렇게 뷰로 감싸면 안에 있는 order by 가 쿼리 옵티마이징 이후 사라져 버린다.