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

[22 Jul 2005 19:59] Trudy Pelzer
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)
[22 Jul 2005 22:07] Bugs System
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
[9 Feb 2007 21:01] Bugs System
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
[11 Feb 2007 19:54] Bugs System
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.
[14 Feb 2007 10:27] Igor Babaev
The fix has been pushed to 5.0.36 and 5.1.16-beta.
[26 Feb 2007 20:35] Paul Dubois
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.