MySQL Bugs: #12122: Views: ORDER BY doesn't have to mean merge

Bug #12122	Views: ORDER BY doesn't have to mean merge
Submitted:	22 Jul 2005 19:59	Modified:	26 Feb 2007 20:35
Reporter:	Trudy Pelzer	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.0.11-beta-debug-log	OS:	Linux (SuSE 9.2)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
A view created with CREATE VIEW ... ORDER BY ... cannot
be resolved with the merge algorithm, even when no other
part of the CREATE VIEW statement would require the view
to be resolved using the temptable algorithm.

If a view definition without an ORDER BY clause would normally 
be resolved using the merge algorithm, then that same view 
should be resolved with the merge algorithm even when CREATE 
VIEW includes an ORDER BY clause.

How to repeat:
mysql> create table t (a int);
Query OK, 0 rows affected (0.01 sec)

mysql> create view v as select * from t;
Query OK, 0 rows affected (0.10 sec)

mysql> insert into t values (1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> explain select * from v where a = 1;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY     | t     | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.10 sec)

mysql> create view v1 as select * from t order by 1;
Query OK, 0 rows affected (0.01 sec)

mysql> explain select * from v1 where a = 1;
+----+-------------+------------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+------------+------+---------------+------+---------+------+------+----------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where    |
|  2 | DERIVED     | t          | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using filesort |
+----+-------------+------------+------+---------------+------+---------+------+------+----------------+
2 rows in set (0.03 sec)

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/27504

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/commits/19642

ChangeSet@1.2407, 2007-02-10 00:00:07+03:00, evgen@moonbone.local +3 -0
  Bug#12122: The MERGE algorithm isn't applicable if the ORDER BY clause is
  present.
  
  A view created with CREATE VIEW ... ORDER BY ... cannot be resolved with
  the MERGE algorithm, even when no other part of the CREATE VIEW statement
  would require the view to be resolved using the TEMPTABLE algorithm.
  
  The check for presence of the ORDER BY clause in the underlying select is 
  removed from the st_lex::can_be_merged() function.
  The ORDER BY list of the underlying select is appended to the ORDER BY list

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/commits/19661

ChangeSet@1.2411, 2007-02-11 22:52:12+03:00, evgen@moonbone.local +2 -0
  sql_view.cc:
    Post fix for bug#12122.
  information_schema.result:
    Corrected test case after fixing bug#12122.

The fix has been pushed to 5.0.36 and 5.1.16-beta.

Noted in 5.0.36, 5.1.16 changelogs.

The presence of ORDER BY in a view definition prevented the MERGE
algorithm from being used to resolve the view even if nothing else in
the definition required the TEMPTABLE algorithm.