diff options
| -rw-r--r-- | mysql-test/main/derived_view.result | 48 | ||||
| -rw-r--r-- | mysql-test/main/derived_view.test | 40 | ||||
| -rw-r--r-- | sql/sql_lex.cc | 6 | ||||
| -rw-r--r-- | sql/sql_lex.h | 2 | ||||
| -rw-r--r-- | sql/sql_view.cc | 5 | ||||
| -rw-r--r-- | sql/table.cc | 6 |
6 files changed, 98 insertions, 9 deletions
diff --git a/mysql-test/main/derived_view.result b/mysql-test/main/derived_view.result index 0bb934f5016..4cb567ccab0 100644 --- a/mysql-test/main/derived_view.result +++ b/mysql-test/main/derived_view.result @@ -4190,4 +4190,52 @@ Warnings: Warning 1287 '@@big_tables' is deprecated and will be removed in a future release drop view v; drop table t1, t2; +# +# MDEV-31162: multi-table mergeable view with ORDER BY used +# in query with rownum() in WHERE +# +create table t1 (a INT) engine=MyISAM; +insert into t1 values (1),(2); +create table t2 (b INT) engine=MyISAM; +insert into t2 values (3),(4); +create view v1 AS select * from t1 join t2 order by b; +explain select * from v1 where rownum() <= 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +select * from v1 where rownum() <= 2; +a b +1 3 +2 3 +prepare stmt from "select * from v1 where rownum() <= 2"; +execute stmt; +a b +1 3 +2 3 +execute stmt; +a b +1 3 +2 3 +deallocate prepare stmt; +create view v2 AS select * from t1 join t2 order by b/a; +explain select * from v2 where rownum() <= 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +select * from v2 where rownum() <= 2; +a b +2 3 +1 3 +prepare stmt from "select * from v2 where rownum() <= 2"; +execute stmt; +a b +2 3 +1 3 +execute stmt; +a b +2 3 +1 3 +deallocate prepare stmt; +drop view v1,v2; +drop table t1,t2; # End of 10.6 tests diff --git a/mysql-test/main/derived_view.test b/mysql-test/main/derived_view.test index d03fc37fe24..777389a9844 100644 --- a/mysql-test/main/derived_view.test +++ b/mysql-test/main/derived_view.test @@ -2773,4 +2773,44 @@ set big_tables=default; drop view v; drop table t1, t2; +--echo # +--echo # MDEV-31162: multi-table mergeable view with ORDER BY used +--echo # in query with rownum() in WHERE +--echo # + +create table t1 (a INT) engine=MyISAM; +insert into t1 values (1),(2); + +create table t2 (b INT) engine=MyISAM; +insert into t2 values (3),(4); + +create view v1 AS select * from t1 join t2 order by b; +let $q1= +select * from v1 where rownum() <= 2; + +eval explain $q1; +--sorted_result +eval $q1; + +eval prepare stmt from "$q1"; +--sorted_result +execute stmt; +--sorted_result +execute stmt; +deallocate prepare stmt; + +create view v2 AS select * from t1 join t2 order by b/a; +let $q2= +select * from v2 where rownum() <= 2; + +eval explain $q2; +eval $q2; +eval prepare stmt from "$q2"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +drop view v1,v2; +drop table t1,t2; + --echo # End of 10.6 tests diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 743488ab808..dd3d182784c 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -4090,7 +4090,7 @@ bool LEX::can_use_merged() TRUE - VIEWs with MERGE algorithms can be used */ -bool LEX::can_not_use_merged(bool no_update_or_delete) +bool LEX::can_not_use_merged() { switch (sql_command) { case SQLCOM_CREATE_VIEW: @@ -4103,10 +4103,6 @@ bool LEX::can_not_use_merged(bool no_update_or_delete) case SQLCOM_SHOW_FIELDS: return TRUE; - case SQLCOM_UPDATE_MULTI: - case SQLCOM_DELETE_MULTI: - return no_update_or_delete; - default: return FALSE; } diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 2076fdf21f4..d457e309014 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -3659,7 +3659,7 @@ public: bool can_be_merged(); bool can_use_merged(); - bool can_not_use_merged(bool no_update_or_delete); + bool can_not_use_merged(); bool only_view_structure(); bool need_correct_ident(); uint8 get_effective_with_check(TABLE_LIST *view); diff --git a/sql/sql_view.cc b/sql/sql_view.cc index e3bfe37b172..a4da5c48b6d 100644 --- a/sql/sql_view.cc +++ b/sql/sql_view.cc @@ -1808,7 +1808,7 @@ bool mysql_make_view(THD *thd, TABLE_SHARE *share, TABLE_LIST *table, if (view_is_mergeable && (table->select_lex->master_unit() != &old_lex->unit || old_lex->can_use_merged()) && - !old_lex->can_not_use_merged(0)) + !old_lex->can_not_use_merged()) { /* lex should contain at least one table */ DBUG_ASSERT(view_main_select_tables != 0); @@ -1841,8 +1841,11 @@ bool mysql_make_view(THD *thd, TABLE_SHARE *share, TABLE_LIST *table, */ if (!table->select_lex->master_unit()->is_unit_op() && table->select_lex->order_list.elements == 0) + { table->select_lex->order_list. push_back(&lex->first_select_lex()->order_list); + lex->first_select_lex()->order_list.empty(); + } else { if (old_lex->sql_command == SQLCOM_SELECT && diff --git a/sql/table.cc b/sql/table.cc index 1bc7b36775a..5bbecb88bb6 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -9551,8 +9551,10 @@ bool TABLE_LIST::init_derived(THD *thd, bool init_view) (!first_select->group_list.elements && !first_select->order_list.elements)) && (is_view() || - (optimizer_flag(thd, OPTIMIZER_SWITCH_DERIVED_MERGE) && - !thd->lex->can_not_use_merged(1))) && + optimizer_flag(thd, OPTIMIZER_SWITCH_DERIVED_MERGE)) && + !thd->lex->can_not_use_merged() && + !((thd->lex->sql_command == SQLCOM_UPDATE_MULTI || + thd->lex->sql_command == SQLCOM_DELETE_MULTI) && !is_view()) && !is_recursive_with_table()) set_merged_derived(); else |