diff options
author | Igor Babaev <igor@askmonty.org> | 2023-04-30 11:53:21 -0700 |
---|---|---|
committer | Igor Babaev <igor@askmonty.org> | 2023-04-30 11:53:21 -0700 |
commit | 7e2e96899711316aa99ef025e0d845f7430f2b32 (patch) | |
tree | d942e5a718fc1e0119486fb5adcf415015a7d197 /mysql-test | |
parent | 4329ec5d3b109cb0bcbee151b5800dc7b19d1945 (diff) | |
download | mariadb-git-7e2e96899711316aa99ef025e0d845f7430f2b32.tar.gz |
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>
Diffstat (limited to 'mysql-test')
-rw-r--r-- | mysql-test/main/derived_view.result | 20 | ||||
-rw-r--r-- | mysql-test/main/derived_view.test | 22 |
2 files changed, 42 insertions, 0 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 |