| Bug #12122 | Views: ORDER BY doesn't have to mean merge | ||
|---|---|---|---|
| Submitted: | 22 Jul 2005 21:59 | Modified: | 26 Feb 2007 21:35 |
| Reporter: | Trudy Pelzer | ||
| Status: | Closed | ||
| Category: | Server: Views | Severity: | S3 (Non-critical) |
| Version: | 5.0.11-beta-debug-log | OS: | Linux (SuSE 9.2) |
| Assigned to: | Evgeny Potemkin | Target Version: | |
[23 Jul 2005 0: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 22: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 20: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 11:27]
Igor Babaev
The fix has been pushed to 5.0.36 and 5.1.16-beta.
[26 Feb 2007 21: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.
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)