MDEV-25679 Wrong result selecting from simple view with LIMIT and ORDER BY

This bug affected queries with views / derived_tables / CTEs whose
specifications were of the form
  (SELECT ... LIMIT <n>) ORDER BY ...
Units representing such specifications contains one SELECT_LEX structure
for (SELECT ... LIMIT <n>) and additionally SELECT_LEX structure for
fake_select_lex. This fact should have been taken into account in the
function mysql_derived_fill().

This patch has to be applied to 10.2 and 10.3 only.

3 files changed, 39 insertions, 1 deletions

diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index fe25feb5a38..d8ee508f5db 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -3436,3 +3436,24 @@ Warnings:
 Note	1003	select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a` AS `a`,3 AS `d`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`c` AS `c` from `test`.`t1` join `test`.`t2` join `test`.`t3` where `test`.`t1`.`a` = 3 and `test`.`t1`.`pk` <= 2
 drop view v1;
 drop table t1,t2,t3;
+#
+# MDEV-25679: view / derived table defined as ordered select with LIMIT
+#
+create table t1 (a int);
+insert into t1 values (3), (7), (1);
+create view v1 as (select a from t1 limit 2) order by a desc;
+(select a from t1 limit 2) order by a desc;
+a
+7
+3
+select * from v1;
+a
+7
+3
+select * from ((select a from t1 limit 2) order by a desc) dt;
+a
+7
+3
+drop view v1;
+drop table t1;
+# End of 10.2 tests
diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test
index 46d6a7805ca..89ada40e84c 100644
--- a/mysql-test/t/derived_view.test
+++ b/mysql-test/t/derived_view.test
@@ -2249,3 +2249,20 @@ eval explain extended $q;
 
 drop view v1;
 drop table t1,t2,t3;
+
+--echo #
+--echo # MDEV-25679: view / derived table defined as ordered select with LIMIT
+--echo #
+
+create table t1 (a int);
+insert into t1 values (3), (7), (1);
+
+create view v1 as (select a from t1 limit 2) order by a desc;
+(select a from t1 limit 2) order by a desc;
+select * from v1;
+select * from ((select a from t1 limit 2) order by a desc) dt;
+
+drop view v1;
+drop table t1;
+
+--echo # End of 10.2 tests
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index 3ab93840d80..632baf4bc5b 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -1091,7 +1091,7 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived)
       res= derived->fill_recursive(thd);
     }
   }
-  else if (unit->is_union())
+  else if (unit->is_union() || unit->fake_select_lex)
   {
     // execute union without clean up
     res= unit->exec();