MDEV-31143 Crash for query using ROWNUM() over view with ORDER BY

When processing a query over a mergeable view at some conditions checked
at prepare stage it may be decided to materialize the view rather than
to merge it. Before this patch in such case the field 'derived' of the
TABLE_LIST structure created for the view remained set to 0. As a result
the guard condition preventing range analysis for materialized views did
not work properly. This led to a call of some handler method for the
temporary table created to contain the view's records that was supposed
to be used only for opened tables. However temporary tables created for
materialization of derived tables or views are not opened yet when range
analysis is performed.

Approved by Oleksandr Byelkin <sanja@mariadb.com>

4 files changed, 44 insertions, 2 deletions

diff --git a/mysql-test/main/derived_view.result b/mysql-test/main/derived_view.result
index c367b882e7f..0bb934f5016 100644
--- a/mysql-test/main/derived_view.result
+++ b/mysql-test/main/derived_view.result
@@ -4171,3 +4171,23 @@ deallocate prepare stmt;
 drop view v;
 drop table t1,t2,t3;
 # End of 10.4 tests
+#
+# MDEV-31143: view with ORDER BY used in query with rownum() in WHERE
+#
+create table t1 (id int primary key);
+insert into t1 values (3), (7), (1);
+create table t2 (a int);
+insert into t2 values (2), (4);
+create view v as select a from t2 order by a;
+set big_tables= 1;
+Warnings:
+Warning	1287	'@@big_tables' is deprecated and will be removed in a future release
+select t1.id from v, t1 where rownum() = 1 group by t1.id;
+id
+1
+set big_tables=default;
+Warnings:
+Warning	1287	'@@big_tables' is deprecated and will be removed in a future release
+drop view v;
+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 5422fbcfd1d..d03fc37fe24 100644
--- a/mysql-test/main/derived_view.test
+++ b/mysql-test/main/derived_view.test
@@ -2752,3 +2752,25 @@ drop view v;
 drop table t1,t2,t3;
 
 --echo # End of 10.4 tests
+
+--echo #
+--echo # MDEV-31143: view with ORDER BY used in query with rownum() in WHERE
+--echo #
+
+create table t1 (id int primary key);
+insert into t1 values (3), (7), (1);
+
+create table t2 (a int);
+insert into t2 values (2), (4);
+
+create view v as select a from t2 order by a;
+
+set big_tables= 1;
+select t1.id from v, t1 where rownum() = 1 group by t1.id;
+
+set big_tables=default;
+
+drop view v;
+drop table t1, t2;
+
+--echo # End of 10.6 tests
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index d502fafdfae..cff8b623d64 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -5814,7 +5814,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list,
       /*
         Perform range analysis if there are keys it could use (1).
         Don't do range analysis for materialized subqueries (2).
-        Don't do range analysis for materialized derived tables (3)
+        Don't do range analysis for materialized derived tables/views (3)
       */
       if ((!s->const_keys.is_clear_all() ||
            !bitmap_is_clear_all(&s->table->cond_set)) &&              // (1)
diff --git a/sql/table.cc b/sql/table.cc
index f5144357a1d..1bc7b36775a 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -6743,7 +6743,7 @@ void TABLE_LIST::set_check_materialized()
   DBUG_ENTER("TABLE_LIST::set_check_materialized");
   SELECT_LEX_UNIT *derived= this->derived;
   if (view)
-    derived= &view->unit;
+    derived= this->derived= &view->unit;
   DBUG_ASSERT(derived);
   DBUG_ASSERT(!derived->is_excluded());
   if (!derived->first_select()->exclude_from_table_unique_test)