diff options
Diffstat (limited to 'mysql-test/main/derived_view.result')
| -rw-r--r-- | mysql-test/main/derived_view.result | 68 |
1 files changed, 68 insertions, 0 deletions
diff --git a/mysql-test/main/derived_view.result b/mysql-test/main/derived_view.result index e040aa7c217..6bc9b4246f0 100644 --- a/mysql-test/main/derived_view.result +++ b/mysql-test/main/derived_view.result @@ -4247,3 +4247,71 @@ 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; +# +# MDEV-31162: multi-table mergeable view with ORDER BY used +# in query with rownum() in WHERE +# +create table t1 (a INT) engine=MyISAM; +insert into t1 values (1),(2); +create table t2 (b INT) engine=MyISAM; +insert into t2 values (3),(4); +create view v1 AS select * from t1 join t2 order by b; +explain select * from v1 where rownum() <= 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +select * from v1 where rownum() <= 2; +a b +1 3 +2 3 +prepare stmt from "select * from v1 where rownum() <= 2"; +execute stmt; +a b +1 3 +2 3 +execute stmt; +a b +1 3 +2 3 +deallocate prepare stmt; +create view v2 AS select * from t1 join t2 order by b/a; +explain select * from v2 where rownum() <= 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +select * from v2 where rownum() <= 2; +a b +2 3 +1 3 +prepare stmt from "select * from v2 where rownum() <= 2"; +execute stmt; +a b +2 3 +1 3 +execute stmt; +a b +2 3 +1 3 +deallocate prepare stmt; +drop view v1,v2; +drop table t1,t2; +# End of 10.6 tests |