MDEV-31073 Server crash, assertion `table != 0 && view->field_translation != 0' failure with ROWNUM and view

Now the same rule applied to vews and derived tables. So we should
allow merge of views (and derived) in queries with rownum, because
it do not change results, only makes query plans better.

6 files changed, 95 insertions, 3 deletions

diff --git a/mysql-test/main/rownum.result b/mysql-test/main/rownum.result
index 3ad51c93260..b61269b1b47 100644
--- a/mysql-test/main/rownum.result
+++ b/mysql-test/main/rownum.result
@@ -139,6 +139,13 @@ select * from t1,t2 where t1.a=t2.a and rownum()<=2 order by t1.a,t2.a;
 a	b	a	b
 2	20	2	21
 3	30	3	31
+create view v1 as
+select * from (select * from t1 order by a desc) as t where rownum() <= 2;
+select * from v1;
+a	b
+3	30
+2	20
+drop view v1;
 #
 # Having
 #
@@ -984,3 +991,29 @@ next row is 3
 3
 next row is 5
 5
+#
+# MDEV-31073: Server crash, assertion `table != 0 &&
+# view->field_translation != 0' failure with ROWNUM and view
+#
+CREATE TABLE t (f INT);
+INSERT INTO t VALUES (1),(2);
+CREATE VIEW v AS SELECT * FROM t;
+UPDATE v SET f = 10 WHERE ROWNUM() > 42 LIMIT 1;
+DROP VIEW v;
+DROP TABLE t;
+CREATE TABLE t (f INT);
+INSERT INTO t VALUES (1),(2);
+CREATE VIEW v AS SELECT f, 3 as e FROM t;
+UPDATE v SET f = 10 WHERE e > 42 LIMIT 1;
+DROP VIEW v;
+DROP TABLE t;
+CREATE TABLE t (f INT);
+INSERT INTO t VALUES (1),(2);
+CREATE VIEW v AS SELECT f, ROWNUM() as e FROM t;
+UPDATE v SET f = 10 WHERE e > 42 LIMIT 1;
+ERROR HY000: The target table v of the UPDATE is not updatable
+DROP VIEW v;
+DROP TABLE t;
+#
+# End of 10.6 tests
+#
diff --git a/mysql-test/main/rownum.test b/mysql-test/main/rownum.test
index bdd0bfa4f41..291bd9bd993 100644
--- a/mysql-test/main/rownum.test
+++ b/mysql-test/main/rownum.test
@@ -58,6 +58,11 @@ select *,rownum() from t1,t2 order by t2.a desc, t1.a desc;
 select * from (select * from t1 order by a desc) as t where rownum() <= 2;
 select * from t1,t2 where t1.a=t2.a and rownum()<=2 order by t1.a,t2.a;
 
+create view v1 as
+select * from (select * from t1 order by a desc) as t where rownum() <= 2;
+select * from v1;
+drop view v1;
+
 --echo #
 --echo # Having
 --echo #
@@ -568,3 +573,40 @@ drop table t1;
 --echo # Table value constructors
 --echo #
 values ("first row"),("next row is 3"),(rownum()),("next row is 5"),(rownum());
+
+--echo #
+--echo # MDEV-31073: Server crash, assertion `table != 0 &&
+--echo # view->field_translation != 0' failure with ROWNUM and view
+--echo #
+
+CREATE TABLE t (f INT);
+INSERT INTO t VALUES (1),(2);
+CREATE VIEW v AS SELECT * FROM t;
+UPDATE v SET f = 10 WHERE ROWNUM() > 42 LIMIT 1;
+
+# Cleanup
+DROP VIEW v;
+DROP TABLE t;
+
+CREATE TABLE t (f INT);
+INSERT INTO t VALUES (1),(2);
+CREATE VIEW v AS SELECT f, 3 as e FROM t;
+UPDATE v SET f = 10 WHERE e > 42 LIMIT 1;
+
+# Cleanup
+DROP VIEW v;
+DROP TABLE t;
+
+CREATE TABLE t (f INT);
+INSERT INTO t VALUES (1),(2);
+CREATE VIEW v AS SELECT f, ROWNUM() as e FROM t;
+--error ER_NON_UPDATABLE_TABLE
+UPDATE v SET f = 10 WHERE e > 42 LIMIT 1;
+
+# Cleanup
+DROP VIEW v;
+DROP TABLE t;
+
+--echo #
+--echo # End of 10.6 tests
+--echo #
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 61df2f153db..743488ab808 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -11847,3 +11847,9 @@ bool SELECT_LEX_UNIT::explainable() const
                derived->is_materialized_derived() :   // (3)
                false;
 }
+
+
+bool st_select_lex::is_query_topmost(THD *thd)
+{
+  return get_master() == &thd->lex->unit;
+}
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 778c6105d6b..2076fdf21f4 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -1384,6 +1384,7 @@ public:
     return (st_select_lex_unit*) slave; 
   }
   st_select_lex* outer_select();
+  bool is_query_topmost(THD *thd);
   st_select_lex* next_select() { return (st_select_lex*) next; }
   st_select_lex* next_select_in_list() 
   {
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index eda833b89b0..65e36ac68db 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -28739,7 +28739,7 @@ void st_select_lex::print_item_list(THD *thd, String *str,
     outer_select() can not be used here because it is for name resolution
     and will return NULL at any end of name resolution chain (view/derived)
   */
-  bool top_level= (get_master() == &thd->lex->unit);
+  bool top_level= is_query_topmost(thd);
   List_iterator_fast<Item> it(item_list);
   Item *item;
   while ((item= it++))
@@ -28846,7 +28846,7 @@ void st_select_lex::print(THD *thd, String *str, enum_query_type query_type)
     return;
   }
 
-  bool top_level= (get_master() == &thd->lex->unit);
+  bool top_level= is_query_topmost(thd);
   enum explainable_cmd_type sel_type= SELECT_CMD;
   if (top_level)
     sel_type= get_explainable_cmd_type(thd);
diff --git a/sql/table.cc b/sql/table.cc
index 26b13debc95..f5144357a1d 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -9536,7 +9536,17 @@ bool TABLE_LIST::init_derived(THD *thd, bool init_view)
   {
     /* A subquery might be forced to be materialized due to a side-effect. */
     if (!is_materialized_derived() && unit->can_be_merged() &&
-        (unit->outer_select() && !unit->outer_select()->with_rownum) &&
+        /*
+          Following is special case of
+          SELECT * FROM (<limited-select>) WHERE ROWNUM() <= nnn
+        */
+        (unit->outer_select() &&
+         !(unit->outer_select()->with_rownum &&
+           unit->outer_select()->table_list.elements == 1 &&
+           (thd->lex->sql_command == SQLCOM_SELECT ||
+            !unit->outer_select()->is_query_topmost(thd)) &&
+           !is_view())) &&
+
         (!thd->lex->with_rownum ||
          (!first_select->group_list.elements &&
           !first_select->order_list.elements)) &&