Filesort is used on same match expression as where clause (while it is not supposed to)

CREATE TABLE wp( 

  FTS_DOC_ID BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, 

  title VARCHAR(255) NOT NULL DEFAULT '', 

  text MEDIUMTEXT NOT NULL, 

  dummy INTEGER, 

  PRIMARY KEY (FTS_DOC_ID), 

  UNIQUE KEY FTS_DOC_ID_INDEX (FTS_DOC_ID), 

  FULLTEXT KEY idx (title,text) 

) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

INSERT INTO wp (title, text) VALUES 

  ('MySQL Tutorial','DBMS stands for MySQL DataBase ...'), 

  ('How To Use MySQL Well','After you went through a ...'), 

  ('Optimizing MySQL','In this tutorial we will show ...'), 

  ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), 

  ('MySQL vs. YourSQL','In the following database to database comparison ...'), 

  ('MySQL Security','When configured properly, MySQL ...'); 

FLUSH STATUS; 

SELECT title, MATCH(title, text) AGAINST ('database') AS score 

FROM wp 

WHERE MATCH(title, text) AGAINST ('database') 

ORDER BY score DESC; 

SHOW SESSION STATUS LIKE 'Sort%'; 

SHOW SESSION STATUS LIKE 'Sort%';

Variable_name	Value

Sort_merge_passes	0

Sort_range	0

Sort_rows	0

Sort_scan	0

SHOW SESSION STATUS LIKE 'Sort%';

Variable_name	Value

Sort_merge_passes	0

Sort_range	0

Sort_rows	2

Sort_scan	1

+----+-------------+-------+----------+---------------+------+---------+------+------+----------+-----------------------------+

| id | select_type | table | type     | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |

+----+-------------+-------+----------+---------------+------+---------+------+------+----------+-----------------------------+

|  1 | SIMPLE      | wp    | fulltext | idx           | idx  | 0       | NULL |    1 |   100.00 | Using where; Using filesort |

+----+-------------+-------+----------+---------------+------+---------+------+------+----------+-----------------------------+

1 row in set, 1 warning (0.16 sec)

MySQL [test]> SHOW WARNINGS;

+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Level | Code | Message                                                                                                                                                                                                                                              |

+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Note  | 1003 | /* select#1 */ select `test`.`wp`.`title` AS `title`,(match `test`.`wp`.`title`,`test`.`wp`.`text` against ('database')) AS `score` from `test`.`wp` where (match `test`.`wp`.`title`,`test`.`wp`.`text` against ('database')) order by `score` desc |

+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

+------+-------------+-------+----------+---------------+------+---------+------+------+----------+----------------------------------------------+

| id   | select_type | table | type     | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                        |

+------+-------------+-------+----------+---------------+------+---------+------+------+----------+----------------------------------------------+

|    1 | SIMPLE      | wp    | fulltext | idx           | idx  | 0       |      |    1 |   100.00 | Using where; Using temporary; Using filesort |

+------+-------------+-------+----------+---------------+------+---------+------+------+----------+----------------------------------------------+

1 row in set, 1 warning (0.01 sec)

MariaDB [test]> SHOW WARNINGS;

+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Level | Code | Message                                                                                                                                                                                                                                                                                           |

+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Note  | 1003 | select `test`.`wp`.`title` AS `title`,(match `test`.`wp`.`title`,`test`.`wp`.`text` against ('database')) AS `score` from `test`.`wp` where (match `test`.`wp`.`title`,`test`.`wp`.`text` against ('database')) order by (match `test`.`wp`.`title`,`test`.`wp`.`text` against ('database')) desc |

+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

revision-id: sergii@pisem.net-20130717165112-i9klgxk4enpvc09a

revno: 3637

branch-nick: 10.0-monty