revision-id: 883d9a66ccdef5b32876d24377d327b61e1a12c0 (mariadb-10.4.11-659-g883d9a6) parent(s): ce868cd89e352f7ce04d8db260f96893df2bf26c author: Igor Babaev committer: Igor Babaev timestamp: 2021-06-22 10:58:06 -0700 message: 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. --- mysql-test/main/derived_view.result | 21 +++++++++++++++++++++ mysql-test/main/derived_view.test | 17 +++++++++++++++++ sql/sql_derived.cc | 2 +- 3 files changed, 39 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/derived_view.result b/mysql-test/main/derived_view.result index 00940d8..040f291 100644 --- a/mysql-test/main/derived_view.result +++ b/mysql-test/main/derived_view.result @@ -3480,3 +3480,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/main/derived_view.test b/mysql-test/main/derived_view.test index 76b15fa..9ab1ddd 100644 --- a/mysql-test/main/derived_view.test +++ b/mysql-test/main/derived_view.test @@ -2273,3 +2273,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 fc01dcd..8e6c8db 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -1262,7 +1262,7 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived) res= derived->fill_recursive(thd); } } - else if (unit->is_unit_op()) + else if (unit->is_unit_op() || unit->fake_select_lex) { // execute union without clean up res= unit->exec();