diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/subselect_cache.result | 28 | ||||
| -rw-r--r-- | mysql-test/t/subselect_cache.test | 34 |
2 files changed, 62 insertions, 0 deletions
diff --git a/mysql-test/r/subselect_cache.result b/mysql-test/r/subselect_cache.result index c4826eeb50f..8758497f7d7 100644 --- a/mysql-test/r/subselect_cache.result +++ b/mysql-test/r/subselect_cache.result @@ -1,3 +1,5 @@ +drop table if exists t1,t2,t3,t4,t5; +drop view if exists v1; set optimizer_switch='subquery_cache=on'; create table t1 (a int, b int); insert into t1 values (1,2),(3,4),(1,2),(3,4),(3,4),(4,5),(4,5),(5,6),(5,6),(4,5); @@ -3354,5 +3356,31 @@ f1 f2 f3 f3 7 0 0 0 7 0 0 0 drop tables t1, t2, t3; +# +# Test of LP BUG#872775 view with "outer references" bug +# +set @@optimizer_switch= default; +set optimizer_switch='subquery_cache=on'; +CREATE TABLE t1 (a int) ; +CREATE TABLE t2 (b int, c varchar(1) NOT NULL ) ; +INSERT INTO t2 VALUES (1,'x'),(2,'y'); +CREATE TABLE t3 (a int) ; +CREATE TABLE t4 ( pk int(11) NOT NULL , b int(11) NOT NULL ) ; +INSERT INTO t4 VALUES (26,9),(27,5),(28,0),(29,3); +CREATE OR REPLACE VIEW v1 AS +SELECT t2.b +FROM t1 +JOIN t2 +WHERE t2 .c > ( +SELECT t2.c FROM t3 +); +SELECT * FROM t4 WHERE b NOT IN ( SELECT * FROM v1 ); +pk b +26 9 +27 5 +28 0 +29 3 +drop view v1; +drop table t1,t2,t3,t4; # restore default set @@optimizer_switch= default; diff --git a/mysql-test/t/subselect_cache.test b/mysql-test/t/subselect_cache.test index 3d8de4e4808..6bf5028b6cf 100644 --- a/mysql-test/t/subselect_cache.test +++ b/mysql-test/t/subselect_cache.test @@ -1,4 +1,10 @@ +--disable_warnings +drop table if exists t1,t2,t3,t4,t5; +drop view if exists v1; +--enable_warnings + + set optimizer_switch='subquery_cache=on'; create table t1 (a int, b int); @@ -1664,5 +1670,33 @@ FROM t1 WHERE t2.f1 OR t2.f3 ); drop tables t1, t2, t3; +--echo # +--echo # Test of LP BUG#872775 view with "outer references" bug +--echo # +set @@optimizer_switch= default; +set optimizer_switch='subquery_cache=on'; +CREATE TABLE t1 (a int) ; + +CREATE TABLE t2 (b int, c varchar(1) NOT NULL ) ; +INSERT INTO t2 VALUES (1,'x'),(2,'y'); + +CREATE TABLE t3 (a int) ; + +CREATE TABLE t4 ( pk int(11) NOT NULL , b int(11) NOT NULL ) ; +INSERT INTO t4 VALUES (26,9),(27,5),(28,0),(29,3); + +CREATE OR REPLACE VIEW v1 AS +SELECT t2.b +FROM t1 +JOIN t2 +WHERE t2 .c > ( + SELECT t2.c FROM t3 + ); + +SELECT * FROM t4 WHERE b NOT IN ( SELECT * FROM v1 ); + +drop view v1; +drop table t1,t2,t3,t4; + --echo # restore default set @@optimizer_switch= default; |