From 94032c61282b301f5a1ff837397531916f71b7f1 Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 10 Feb 2007 00:00:07 +0300 Subject: 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 mysql-test/t/view.test: Added a test case for bug#12122: Views with ORDER BY can't be resolved using MERGE algorithm. mysql-test/r/view.result: Added a test case for bug#12122: Views with ORDER BY can't be resolved using MERGE algorithm. sql/sql_lex.cc: Bug#12122: Views with ORDER BY can't be resolved using MERGE algorithm. The st_lex::can_be_merged() function now allows views with the ORDER BY clause to be resolved using MERGE algorithm. The ORDER BY list of the view is appended to the ORDER BY list of the embedding select. --- mysql-test/r/view.result | 39 +++++++++++++++++++++++++++++++++++++++ mysql-test/t/view.test | 14 ++++++++++++++ sql/sql_lex.cc | 1 - 3 files changed, 53 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index a52882fef2f..462916ec09b 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -3078,4 +3078,43 @@ b 2 DROP VIEW v1; DROP TABLE t1,t2; +create table t1(f1 int, f2 int); +insert into t1 values(1,2),(1,3),(1,1),(2,3),(2,1),(2,2); +select * from t1; +f1 f2 +1 2 +1 3 +1 1 +2 3 +2 1 +2 2 +create view v1 as select * from t1 order by f2; +select * from v1; +f1 f2 +1 1 +2 1 +1 2 +2 2 +1 3 +2 3 +explain extended select * from v1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort +Warnings: +Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` order by `test`.`t1`.`f2` +select * from v1 order by f1; +f1 f2 +1 1 +1 2 +1 3 +2 1 +2 2 +2 3 +explain extended select * from v1 order by f1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort +Warnings: +Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` order by `test`.`t1`.`f1`,`test`.`t1`.`f2` +drop view v1; +drop table t1; End of 5.0 tests. diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 0fa5765bb64..7fdca1ff7e0 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -3024,4 +3024,18 @@ SELECT * FROM t2; DROP VIEW v1; DROP TABLE t1,t2; +# +# Bug#12122: Views with ORDER BY can't be resolved using MERGE algorithm. +# +create table t1(f1 int, f2 int); +insert into t1 values(1,2),(1,3),(1,1),(2,3),(2,1),(2,2); +select * from t1; +create view v1 as select * from t1 order by f2; +select * from v1; +explain extended select * from v1; +select * from v1 order by f1; +explain extended select * from v1 order by f1; +drop view v1; +drop table t1; + --echo End of 5.0 tests. diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index a362e6c7fd2..ce76c35b33c 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -1751,7 +1751,6 @@ bool st_lex::can_be_merged() } return (selects_allow_merge && - select_lex.order_list.elements == 0 && select_lex.group_list.elements == 0 && select_lex.having == 0 && select_lex.with_sum_func == 0 && -- cgit v1.2.1 From 56baa89b82e16dfec5ad153c47d781e82c1ae14d Mon Sep 17 00:00:00 2001 From: unknown Date: Sun, 11 Feb 2007 22:52:12 +0300 Subject: sql_view.cc: Post fix for bug#12122. information_schema.result: Corrected test case after fixing bug#12122. sql/sql_view.cc: Post fix for bug#12122. mysql-test/r/information_schema.result: Corrected test case after fixing bug#12122. --- mysql-test/r/information_schema.result | 3 +-- sql/sql_view.cc | 1 + 2 files changed, 2 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index b93a4c28849..3986dce2c29 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -1262,8 +1262,7 @@ from information_schema.tables order by object_schema; explain select * from v1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY system NULL NULL NULL NULL 0 const row not found -2 DERIVED tables ALL NULL NULL NULL NULL 2 Using filesort +1 SIMPLE tables ALL NULL NULL NULL NULL 2 Using filesort explain select * from (select table_name from information_schema.tables) as a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY system NULL NULL NULL NULL 0 const row not found diff --git a/sql/sql_view.cc b/sql/sql_view.cc index 18ef3eaf29c..fd2d6c89785 100644 --- a/sql/sql_view.cc +++ b/sql/sql_view.cc @@ -1263,6 +1263,7 @@ bool mysql_make_view(THD *thd, File_parser *parser, TABLE_LIST *table, but it will not be included to SELECT_LEX tree, because it will not be executed */ + table->select_lex->order_list.push_back(&lex->select_lex.order_list); goto ok; } -- cgit v1.2.1