diff options
| -rw-r--r-- | mysql-test/main/rownum.result | 33 | ||||
| -rw-r--r-- | mysql-test/main/rownum.test | 42 | ||||
| -rw-r--r-- | sql/sql_lex.h | 1 | ||||
| -rw-r--r-- | sql/table.cc | 12 |
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)) && |