Bug #14816 VIEW performs filesort when using ORDER BY but underlying table does not

Description:
Performance when selecting from a view is suffering because of the filesort.  No filesort occurs when selecting from the table.

mysql> use test;
Database changedmysql> DROP VIEW IF EXISTS v1;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id))ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (),(),(),(),(),(),(),(),(),(),(),(),(),(),();
Query OK, 15 rows affected (0.00 sec)
Records: 15  Duplicates: 0  Warnings: 0

mysql> CREATE VIEW v1 AS SELECT id FROM t1;
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN SELECT id FROM t1 ORDER BY id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 15
        Extra: Using index
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT id FROM v1 ORDER BY id\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 15
        Extra: Using index; Using filesort
1 row in set (0.00 sec)

mysql>

How to repeat:
DROP VIEW IF EXISTS v1;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id))ENGINE=MyISAM;
INSERT INTO t1 VALUES (),(),(),(),(),(),(),(),(),(),(),(),(),(),();
CREATE VIEW v1 AS SELECT id FROM t1;
EXPLAIN SELECT id FROM t1 ORDER BY id\G
EXPLAIN SELECT id FROM v1 ORDER BY id\G

Suggested fix:
not sure.

Thank you for a bug report. Verified just as described on 5.0.16-BK (ChangeSet@1.1972, 2005-11-05 22:45:54-08:00, igor@rurik.mysql.com).

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32252

test_if_order_by_key() expected only Item_fields to be in order->item, thus
failing to find available index on view's field, which results in reported
error.

Fixed in 5.0.17, cset 1.1952.18.1

Noted in 5.0.17 changelog.