diff options
Diffstat (limited to 'mysql-test/t')
| -rw-r--r-- | mysql-test/t/derived_view.test | 217 | ||||
| -rw-r--r-- | mysql-test/t/lock_multi_bug38499.test | 12 |
2 files changed, 224 insertions, 5 deletions
diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test new file mode 100644 index 00000000000..a8dd703d58c --- /dev/null +++ b/mysql-test/t/derived_view.test @@ -0,0 +1,217 @@ +--disable_warnings +drop table if exists t1,t2; +drop view if exists v1,v2,v3,v4; +--enable_warnings +create table t1(f1 int, f11 int); +create table t2(f2 int, f22 int); +insert into t1 values(1,1),(2,2),(3,3),(5,5),(9,9),(7,7); +insert into t1 values(17,17),(13,13),(11,11),(15,15),(19,19); +insert into t2 values(1,1),(3,3),(2,2),(4,4),(8,8),(6,6); +insert into t2 values(12,12),(14,14),(10,10),(18,18),(16,16); + +--echo Tests: + +--echo for merged derived tables +--echo explain for simple derived +explain select * from (select * from t1) tt; +select * from (select * from t1) tt; +--echo explain for multitable derived +explain extended select * from (select * from t1 join t2 on f1=f2) tt; +select * from (select * from t1 join t2 on f1=f2) tt; +--echo explain for derived with where +explain extended + select * from (select * from t1 where f1 in (2,3)) tt where f11=2; +select * from (select * from t1 where f1 in (2,3)) tt where f11=2; +--echo join of derived +explain extended + select * from (select * from t1 where f1 in (2,3)) tt join + (select * from t1 where f1 in (1,2)) aa on tt.f1=aa.f1; +select * from (select * from t1 where f1 in (2,3)) tt join + (select * from t1 where f1 in (1,2)) aa on tt.f1=aa.f1; + +flush status; +explain extended + select * from (select * from t1 where f1 in (2,3)) tt where f11=2; +show status like 'Handler_read%'; +flush status; +select * from (select * from t1 where f1 in (2,3)) tt where f11=2; +show status like 'Handler_read%'; + +--echo for merged views +create view v1 as select * from t1; +create view v2 as select * from t1 join t2 on f1=f2; +create view v3 as select * from t1 where f1 in (2,3); +create view v4 as select * from t2 where f2 in (2,3); +--echo explain for simple views +explain extended select * from v1; +select * from v1; +--echo explain for multitable views +explain extended select * from v2; +select * from v2; +--echo explain for views with where +explain extended select * from v3 where f11 in (1,3); +select * from v3 where f11 in (1,3); +--echo explain for joined views +explain extended + select * from v3 join v4 on f1=f2; +select * from v3 join v4 on f1=f2; + +flush status; +explain extended select * from v4 where f2 in (1,3); +show status like 'Handler_read%'; +flush status; +select * from v4 where f2 in (1,3); +show status like 'Handler_read%'; + +--echo for materialized derived tables +--echo explain for simple derived +explain extended select * from (select * from t1 group by f1) tt; +select * from (select * from t1 having f1=f1) tt; +--echo explain showing created indexes +explain extended + select * from t1 join (select * from t2 group by f2) tt on f1=f2; +select * from t1 join (select * from t2 group by f2) tt on f1=f2; +--echo explain showing late materialization +flush status; +explain select * from t1 join (select * from t2 group by f2) tt on f1=f2; +show status like 'Handler_read%'; +flush status; +select * from t1 join (select * from t2 group by f2) tt on f1=f2; +show status like 'Handler_read%'; + +--echo for materialized views +drop view v1,v2,v3; +create view v1 as select * from t1 group by f1; +create view v2 as select * from t2 group by f2; +create view v3 as select t1.f1,t1.f11 from t1 join t1 as t11 where t1.f1=t11.f1 + having t1.f1<100; +--echo explain for simple derived +explain extended select * from v1; +select * from v1; +--echo explain showing created indexes +explain extended select * from t1 join v2 on f1=f2; +select * from t1 join v2 on f1=f2; +explain extended + select * from t1,v3 as v31,v3 where t1.f1=v31.f1 and t1.f1=v3.f1; +flush status; +select * from t1,v3 as v31,v3 where t1.f1=v31.f1 and t1.f1=v3.f1; +show status like 'Handler_read%'; +--echo explain showing late materialization +flush status; +explain select * from t1 join v2 on f1=f2; +show status like 'Handler_read%'; +flush status; +select * from t1 join v2 on f1=f2; +show status like 'Handler_read%'; + +explain extended select * from v1 join v4 on f1=f2; +select * from v1 join v4 on f1=f2; + +--echo merged derived in merged derived +explain extended select * from (select * from + (select * from t1 where f1 < 7) tt where f1 > 2) zz; +select * from (select * from + (select * from t1 where f1 < 7) tt where f1 > 2) zz; + +--echo materialized derived in merged derived +explain extended select * from (select * from + (select * from t1 where f1 < 7 group by f1) tt where f1 > 2) zz; +select * from (select * from + (select * from t1 where f1 < 7 group by f1) tt where f1 > 2) zz; + +--echo merged derived in materialized derived +explain extended select * from (select * from + (select * from t1 where f1 < 7) tt where f1 > 2 group by f1) zz; +select * from (select * from + (select * from t1 where f1 < 7) tt where f1 > 2 group by f1) zz; + +--echo materialized derived in materialized derived +explain extended select * from (select * from + (select * from t1 where f1 < 7 group by f1) tt where f1 > 2 group by f1) zz; +select * from (select * from + (select * from t1 where f1 < 7 group by f1) tt where f1 > 2 group by f1) zz; + +--echo mat in merged derived join mat in merged derived +explain extended select * from + (select * from (select * from t1 where f1 < 7 group by f1) tt where f1 > 2) x +join + (select * from (select * from t1 where f1 < 7 group by f1) tt where f1 > 2) z + on x.f1 = z.f1; + +flush status; +select * from + (select * from (select * from t1 where f1 < 7 group by f1) tt where f1 > 2) x +join + (select * from (select * from t1 where f1 < 7 group by f1) tt where f1 > 2) z + on x.f1 = z.f1; +show status like 'Handler_read%'; +flush status; + +--echo merged in merged derived join merged in merged derived +explain extended select * from + (select * from + (select * from t1 where f1 < 7 ) tt where f1 > 2 ) x +join + (select * from + (select * from t1 where f1 < 7 ) tt where f1 > 2 ) z + on x.f1 = z.f1; + +select * from + (select * from + (select * from t1 where f1 < 7 ) tt where f1 > 2 ) x +join + (select * from + (select * from t1 where f1 < 7 ) tt where f1 > 2 ) z + on x.f1 = z.f1; + +--echo materialized in materialized derived join +--echo materialized in materialized derived +explain extended select * from + (select * from + (select * from t1 where f1 < 7 group by f1) tt where f1 > 2 group by f1) x +join + (select * from + (select * from t1 where f1 < 7 group by f1) tt where f1 > 2 group by f1) z + on x.f1 = z.f1; + +select * from + (select * from + (select * from t1 where f1 < 7 group by f1) tt where f1 > 2 group by f1) x +join + (select * from + (select * from t1 where f1 < 7 group by f1) tt where f1 > 2 group by f1) z + on x.f1 = z.f1; + +--echo merged view in materialized derived +explain extended +select * from (select * from v4 group by 1) tt; +select * from (select * from v4 group by 1) tt; + +--echo materialized view in merged derived +explain extended +select * from ( select * from v1 where f1 < 7) tt; +select * from ( select * from v1 where f1 < 7) tt; + +--echo merged view in a merged view in a merged derived +create view v6 as select * from v4 where f2 < 7; +explain extended select * from (select * from v6) tt; +select * from (select * from v6) tt; + +--echo materialized view in a merged view in a materialized derived +create view v7 as select * from v1; +explain extended select * from (select * from v7 group by 1) tt; +select * from (select * from v7 group by 1) tt; + +--echo join of above two +explain extended select * from v6 join v7 on f2=f1; +select * from v6 join v7 on f2=f1; + +--echo test two keys +explain select * from t1 join (select * from t2 group by f2) tt on t1.f1=tt.f2 join t1 xx on tt.f22=xx.f1; +select * from t1 join (select * from t2 group by f2) tt on t1.f1=tt.f2 join t1 xx on tt.f22=xx.f1; + + +--echo TODO: Add test with 64 tables mergeable view to test fall back to +--echo materialization on tables > MAX_TABLES merge +drop table t1,t2; +drop view v1,v2,v3,v4,v6,v7; diff --git a/mysql-test/t/lock_multi_bug38499.test b/mysql-test/t/lock_multi_bug38499.test index 3d3f084ba5f..b812984e516 100644 --- a/mysql-test/t/lock_multi_bug38499.test +++ b/mysql-test/t/lock_multi_bug38499.test @@ -16,7 +16,9 @@ connect (writer,localhost,root,,); DROP TABLE IF EXISTS t1; --enable_warnings CREATE TABLE t1( a INT, b INT ); +CREATE TABLE t2( a INT, b INT ); INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4); +INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4); --echo # 1. test regular tables --echo # 1.1. test altering of columns that multiupdate doesn't use @@ -28,7 +30,7 @@ while ($i) { --dec $i --connection writer - send UPDATE t1, (SELECT 1 FROM t1 t1i) d SET a = 0 WHERE 1=0; + send UPDATE t1, (SELECT 1 FROM t2 t1i) d SET a = 0 WHERE 1=0; --connection locker ALTER TABLE t1 ADD COLUMN (c INT); @@ -41,7 +43,7 @@ while ($i) { --echo # 1.1.2. PS mode --connection writer -PREPARE stmt FROM 'UPDATE t1, (SELECT 1 FROM t1 t1i) d SET a = 0 WHERE 1=0'; +PREPARE stmt FROM 'UPDATE t1, (SELECT 1 FROM t2 t1i) d SET a = 0 WHERE 1=0'; let $i = 100; while ($i) { @@ -75,7 +77,7 @@ while ($i) { UPDATE t1 SET a=b; --connection writer ---send UPDATE t1, (SELECT 1 FROM t1 t1i) d SET a = 0 WHERE 1=0; +--send UPDATE t1, (SELECT 1 FROM t2 t1i) d SET a = 0 WHERE 1=0; --connection locker --error 0,ER_CANT_DROP_FIELD_OR_KEY @@ -100,7 +102,7 @@ while ($i) { UPDATE t1 SET a=b; --connection writer - PREPARE stmt FROM 'UPDATE t1, (SELECT 1 FROM t1 t1i) d SET a = 0 WHERE 1=0'; + PREPARE stmt FROM 'UPDATE t1, (SELECT 1 FROM t2 t1i) d SET a = 0 WHERE 1=0'; --send EXECUTE stmt --connection locker @@ -210,7 +212,7 @@ while ($i) { } --enable_query_log --connection default -DROP TABLE t1; +DROP TABLE t1,t2; # Close connections |