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

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)

4 files changed, 87 insertions, 1 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.h b/sql/sql_lex.h
index 778c6105d6b..39c67ffa544 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_select() { return !master || master->master == NULL; };
   st_select_lex* next_select() { return (st_select_lex*) next; }
   st_select_lex* next_select_in_list() 
   {
diff --git a/sql/table.cc b/sql/table.cc
index 26b13debc95..4b30af8d06a 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_select()) &&
+           !is_view())) &&
+
         (!thd->lex->with_rownum ||
          (!first_select->group_list.elements &&
           !first_select->order_list.elements)) &&