diff options
Diffstat (limited to 'mysql-test/r/derived_view.result')
| -rw-r--r-- | mysql-test/r/derived_view.result | 529 |
1 files changed, 529 insertions, 0 deletions
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result new file mode 100644 index 00000000000..d1b6d4f7308 --- /dev/null +++ b/mysql-test/r/derived_view.result @@ -0,0 +1,529 @@ +drop table if exists t1,t2; +drop view if exists v1,v2,v3,v4; +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); +Tests: +for merged derived tables +explain for simple derived +explain select * from (select * from t1) tt; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 11 +select * from (select * from t1) tt; +f1 f11 +1 1 +2 2 +3 3 +5 5 +9 9 +7 7 +17 17 +13 13 +11 11 +15 15 +19 19 +explain for multitable derived +explain extended select * from (select * from t1 join t2 on f1=f2) tt; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 11 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 11 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11`,`test`.`t2`.`f2` AS `f2`,`test`.`t2`.`f22` AS `f22` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`f2` = `test`.`t1`.`f1`) +select * from (select * from t1 join t2 on f1=f2) tt; +f1 f11 f2 f22 +1 1 1 1 +3 3 3 3 +2 2 2 2 +explain for derived with where +explain extended +select * from (select * from t1 where f1 in (2,3)) tt where f11=2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 11 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` where ((`test`.`t1`.`f11` = 2) and (`test`.`t1`.`f1` in (2,3))) +select * from (select * from t1 where f1 in (2,3)) tt where f11=2; +f1 f11 +2 2 +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; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 11 100.00 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 11 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11`,`test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` join `test`.`t1` where ((`test`.`t1`.`f1` = `test`.`t1`.`f1`) and (`test`.`t1`.`f1` in (1,2)) and (`test`.`t1`.`f1` in (2,3))) +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; +f1 f11 f1 f11 +2 2 2 2 +flush status; +explain extended +select * from (select * from t1 where f1 in (2,3)) tt where f11=2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 11 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` where ((`test`.`t1`.`f11` = 2) and (`test`.`t1`.`f1` in (2,3))) +show status like 'Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 0 +flush status; +select * from (select * from t1 where f1 in (2,3)) tt where f11=2; +f1 f11 +2 2 +show status like 'Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 12 +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); +explain for simple views +explain extended select * from v1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 11 100.00 +Warnings: +Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` +select * from v1; +f1 f11 +1 1 +2 2 +3 3 +5 5 +9 9 +7 7 +17 17 +13 13 +11 11 +15 15 +19 19 +explain for multitable views +explain extended select * from v2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 11 100.00 +1 SIMPLE t2 ALL NULL NULL NULL NULL 11 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11`,`test`.`t2`.`f2` AS `f2`,`test`.`t2`.`f22` AS `f22` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`f2` = `test`.`t1`.`f1`) +select * from v2; +f1 f11 f2 f22 +1 1 1 1 +3 3 3 3 +2 2 2 2 +explain for views with where +explain extended select * from v3 where f11 in (1,3); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 11 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` where ((`test`.`t1`.`f11` in (1,3)) and (`test`.`t1`.`f1` in (2,3))) +select * from v3 where f11 in (1,3); +f1 f11 +3 3 +explain for joined views +explain extended +select * from v3 join v4 on f1=f2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 11 100.00 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 11 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11`,`test`.`t2`.`f2` AS `f2`,`test`.`t2`.`f22` AS `f22` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`f2` = `test`.`t1`.`f1`) and (`test`.`t1`.`f1` in (2,3)) and (`test`.`t1`.`f1` in (2,3))) +select * from v3 join v4 on f1=f2; +f1 f11 f2 f22 +3 3 3 3 +2 2 2 2 +flush status; +explain extended select * from v4 where f2 in (1,3); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 11 100.00 Using where +Warnings: +Note 1003 select `test`.`t2`.`f2` AS `f2`,`test`.`t2`.`f22` AS `f22` from `test`.`t2` where ((`test`.`t2`.`f2` in (1,3)) and (`test`.`t2`.`f2` in (2,3))) +show status like 'Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 0 +flush status; +select * from v4 where f2 in (1,3); +f2 f22 +3 3 +show status like 'Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 12 +for materialized derived tables +explain for simple derived +explain extended select * from (select * from t1 group by f1) tt; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 11 100.00 +2 DERIVED t1 ALL NULL NULL NULL NULL 11 100.00 Using temporary; Using filesort +Warnings: +Note 1003 select `tt`.`f1` AS `f1`,`tt`.`f11` AS `f11` from (select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` group by `test`.`t1`.`f1`) `tt` +select * from (select * from t1 having f1=f1) tt; +f1 f11 +1 1 +2 2 +3 3 +5 5 +9 9 +7 7 +17 17 +13 13 +11 11 +15 15 +19 19 +explain showing created indexes +explain extended +select * from t1 join (select * from t2 group by f2) tt on f1=f2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 11 100.00 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t1.f1 2 100.00 +2 DERIVED t2 ALL NULL NULL NULL NULL 11 100.00 Using temporary; Using filesort +Warnings: +Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11`,`tt`.`f2` AS `f2`,`tt`.`f22` AS `f22` from `test`.`t1` join (select `test`.`t2`.`f2` AS `f2`,`test`.`t2`.`f22` AS `f22` from `test`.`t2` group by `test`.`t2`.`f2`) `tt` where (`tt`.`f2` = `test`.`t1`.`f1`) +select * from t1 join (select * from t2 group by f2) tt on f1=f2; +f1 f11 f2 f22 +1 1 1 1 +2 2 2 2 +3 3 3 3 +explain showing late materialization +flush status; +explain select * from t1 join (select * from t2 group by f2) tt on f1=f2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 11 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t1.f1 2 +2 DERIVED t2 ALL NULL NULL NULL NULL 11 Using temporary; Using filesort +show status like 'Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 0 +flush status; +select * from t1 join (select * from t2 group by f2) tt on f1=f2; +f1 f11 f2 f22 +1 1 1 1 +2 2 2 2 +3 3 3 3 +show status like 'Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 11 +Handler_read_next 3 +Handler_read_prev 0 +Handler_read_rnd 11 +Handler_read_rnd_next 36 +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; +explain for simple derived +explain extended select * from v1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 11 100.00 +2 DERIVED t1 ALL NULL NULL NULL NULL 11 100.00 Using temporary; Using filesort +Warnings: +Note 1003 select `v1`.`f1` AS `f1`,`v1`.`f11` AS `f11` from `test`.`v1` +select * from v1; +f1 f11 +1 1 +2 2 +3 3 +5 5 +7 7 +9 9 +11 11 +13 13 +15 15 +17 17 +19 19 +explain showing created indexes +explain extended select * from t1 join v2 on f1=f2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 11 100.00 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t1.f1 2 100.00 +2 DERIVED t2 ALL NULL NULL NULL NULL 11 100.00 Using temporary; Using filesort +Warnings: +Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11`,`v2`.`f2` AS `f2`,`v2`.`f22` AS `f22` from `test`.`t1` join `test`.`v2` where (`v2`.`f2` = `test`.`t1`.`f1`) +select * from t1 join v2 on f1=f2; +f1 f11 f2 f22 +1 1 1 1 +2 2 2 2 +3 3 3 3 +explain extended +select * from t1,v3 as v31,v3 where t1.f1=v31.f1 and t1.f1=v3.f1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 11 100.00 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t1.f1 10 100.00 +1 PRIMARY <derived3> ref key0 key0 5 test.t1.f1 10 100.00 +3 DERIVED t1 ALL NULL NULL NULL NULL 11 100.00 +3 DERIVED t11 ALL NULL NULL NULL NULL 11 100.00 Using where; Using join buffer (flat, BNL join) +2 DERIVED t1 ALL NULL NULL NULL NULL 11 100.00 +2 DERIVED t11 ALL NULL NULL NULL NULL 11 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11`,`v31`.`f1` AS `f1`,`v31`.`f11` AS `f11`,`v3`.`f1` AS `f1`,`v3`.`f11` AS `f11` from `test`.`t1` join `test`.`v3` `v31` join `test`.`v3` where ((`v31`.`f1` = `test`.`t1`.`f1`) and (`v3`.`f1` = `test`.`t1`.`f1`)) +flush status; +select * from t1,v3 as v31,v3 where t1.f1=v31.f1 and t1.f1=v3.f1; +f1 f11 f1 f11 f1 f11 +1 1 1 1 1 1 +2 2 2 2 2 2 +3 3 3 3 3 3 +5 5 5 5 5 5 +9 9 9 9 9 9 +7 7 7 7 7 7 +17 17 17 17 17 17 +13 13 13 13 13 13 +11 11 11 11 11 11 +15 15 15 15 15 15 +19 19 19 19 19 19 +show status like 'Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 22 +Handler_read_next 22 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 60 +explain showing late materialization +flush status; +explain select * from t1 join v2 on f1=f2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 11 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t1.f1 2 +2 DERIVED t2 ALL NULL NULL NULL NULL 11 Using temporary; Using filesort +show status like 'Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 0 +flush status; +select * from t1 join v2 on f1=f2; +f1 f11 f2 f22 +1 1 1 1 +2 2 2 2 +3 3 3 3 +show status like 'Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 11 +Handler_read_next 3 +Handler_read_prev 0 +Handler_read_rnd 11 +Handler_read_rnd_next 36 +explain extended select * from v1 join v4 on f1=f2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 11 100.00 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.f2 2 100.00 +2 DERIVED t1 ALL NULL NULL NULL NULL 11 100.00 Using temporary; Using filesort +Warnings: +Note 1003 select `v1`.`f1` AS `f1`,`v1`.`f11` AS `f11`,`test`.`t2`.`f2` AS `f2`,`test`.`t2`.`f22` AS `f22` from `test`.`v1` join `test`.`t2` where ((`v1`.`f1` = `test`.`t2`.`f2`) and (`test`.`t2`.`f2` in (2,3))) +select * from v1 join v4 on f1=f2; +f1 f11 f2 f22 +3 3 3 3 +2 2 2 2 +merged derived in merged derived +explain extended select * from (select * from +(select * from t1 where f1 < 7) tt where f1 > 2) zz; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 11 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` where ((`test`.`t1`.`f1` > 2) and (`test`.`t1`.`f1` < 7)) +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; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE <derived3> ALL NULL NULL NULL NULL 11 100.00 Using where +3 DERIVED t1 ALL NULL NULL NULL NULL 11 100.00 Using where; Using temporary; Using filesort +Warnings: +Note 1003 select `tt`.`f1` AS `f1`,`tt`.`f11` AS `f11` from (select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` where (`test`.`t1`.`f1` < 7) group by `test`.`t1`.`f1`) `tt` where (`tt`.`f1` > 2) +select * from (select * from +(select * from t1 where f1 < 7 group by f1) tt where f1 > 2) zz; +f1 f11 +3 3 +5 5 +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; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 11 100.00 +2 DERIVED t1 ALL NULL NULL NULL NULL 11 100.00 Using where; Using temporary; Using filesort +Warnings: +Note 1003 select `zz`.`f1` AS `f1`,`zz`.`f11` AS `f11` from (select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` where ((`test`.`t1`.`f1` > 2) and (`test`.`t1`.`f1` < 7)) group by `test`.`t1`.`f1`) `zz` +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; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 11 100.00 +2 DERIVED <derived3> ALL NULL NULL NULL NULL 11 100.00 Using where; Using temporary; Using filesort +3 DERIVED t1 ALL NULL NULL NULL NULL 11 100.00 Using where; Using temporary; Using filesort +Warnings: +Note 1003 select `zz`.`f1` AS `f1`,`zz`.`f11` AS `f11` from (select `tt`.`f1` AS `f1`,`tt`.`f11` AS `f11` from (select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` where (`test`.`t1`.`f1` < 7) group by `test`.`t1`.`f1`) `tt` where (`tt`.`f1` > 2) group by `tt`.`f1`) `zz` +select * from (select * from +(select * from t1 where f1 < 7 group by f1) tt where f1 > 2 group by f1) zz; +f1 f11 +3 3 +5 5 +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; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE <derived3> ALL key0 NULL NULL NULL 11 100.00 Using where +1 SIMPLE <derived5> ref key0 key0 5 tt.f1 2 100.00 +5 DERIVED t1 ALL NULL NULL NULL NULL 11 100.00 Using where; Using temporary; Using filesort +3 DERIVED t1 ALL NULL NULL NULL NULL 11 100.00 Using where; Using temporary; Using filesort +Warnings: +Note 1003 select `tt`.`f1` AS `f1`,`tt`.`f11` AS `f11`,`tt`.`f1` AS `f1`,`tt`.`f11` AS `f11` from (select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` where (`test`.`t1`.`f1` < 7) group by `test`.`t1`.`f1`) `tt` join (select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` where (`test`.`t1`.`f1` < 7) group by `test`.`t1`.`f1`) `tt` where ((`tt`.`f1` = `tt`.`f1`) and (`tt`.`f1` > 2) and (`tt`.`f1` > 2)) +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; +f1 f11 f1 f11 +3 3 3 3 +5 5 5 5 +show status like 'Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 2 +Handler_read_next 2 +Handler_read_prev 0 +Handler_read_rnd 8 +Handler_read_rnd_next 39 +flush status; +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; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 11 100.00 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 11 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11`,`test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` join `test`.`t1` where ((`test`.`t1`.`f1` = `test`.`t1`.`f1`) and (`test`.`t1`.`f1` > 2) and (`test`.`t1`.`f1` < 7) and (`test`.`t1`.`f1` > 2) and (`test`.`t1`.`f1` < 7)) +materialized in materialized derived join +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; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL key0 NULL NULL NULL 11 100.00 Using where +1 PRIMARY <derived4> ref key0 key0 5 x.f1 2 100.00 +4 DERIVED <derived5> ALL NULL NULL NULL NULL 11 100.00 Using where; Using temporary; Using filesort +5 DERIVED t1 ALL NULL NULL NULL NULL 11 100.00 Using where; Using temporary; Using filesort +2 DERIVED <derived3> ALL NULL NULL NULL NULL 11 100.00 Using where; Using temporary; Using filesort +3 DERIVED t1 ALL NULL NULL NULL NULL 11 100.00 Using where; Using temporary; Using filesort +Warnings: +Note 1003 select `x`.`f1` AS `f1`,`x`.`f11` AS `f11`,`z`.`f1` AS `f1`,`z`.`f11` AS `f11` from (select `tt`.`f1` AS `f1`,`tt`.`f11` AS `f11` from (select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` where (`test`.`t1`.`f1` < 7) group by `test`.`t1`.`f1`) `tt` where (`tt`.`f1` > 2) group by `tt`.`f1`) `x` join (select `tt`.`f1` AS `f1`,`tt`.`f11` AS `f11` from (select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11` from `test`.`t1` where (`test`.`t1`.`f1` < 7) group by `test`.`t1`.`f1`) `tt` where (`tt`.`f1` > 2) group by `tt`.`f1`) `z` where (`z`.`f1` = `x`.`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; +f1 f11 f1 f11 +3 3 3 3 +5 5 5 5 +merged view in materialized derived +explain extended +select * from (select * from v4 group by 1) tt; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 11 100.00 +2 DERIVED t2 ALL NULL NULL NULL NULL 11 100.00 Using where; Using temporary; Using filesort +Warnings: +Note 1003 select `tt`.`f2` AS `f2`,`tt`.`f22` AS `f22` from (select `test`.`t2`.`f2` AS `f2`,`test`.`t2`.`f22` AS `f22` from `test`.`t2` where (`test`.`t2`.`f2` in (2,3)) group by 1) `tt` +materialized view in merged derived +explain extended +select * from ( select * from v1 where f1 < 7) tt; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE <derived3> ALL NULL NULL NULL NULL 11 100.00 Using where +3 DERIVED t1 ALL NULL NULL NULL NULL 11 100.00 Using temporary; Using filesort +Warnings: +Note 1003 select `v1`.`f1` AS `f1`,`v1`.`f11` AS `f11` from `test`.`v1` where (`v1`.`f1` < 7) +select * from ( select * from v1 where f1 < 7) tt; +f1 f11 +1 1 +2 2 +3 3 +5 5 +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; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 11 100.00 Using where +Warnings: +Note 1003 select `test`.`t2`.`f2` AS `f2`,`test`.`t2`.`f22` AS `f22` from `test`.`t2` where ((`test`.`t2`.`f2` < 7) and (`test`.`t2`.`f2` in (2,3))) +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; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 11 100.00 +2 DERIVED <derived4> ALL NULL NULL NULL NULL 11 100.00 Using temporary; Using filesort +4 DERIVED t1 ALL NULL NULL NULL NULL 11 100.00 Using temporary; Using filesort +Warnings: +Note 1003 select `tt`.`f1` AS `f1`,`tt`.`f11` AS `f11` from (select `v1`.`f1` AS `f1`,`v1`.`f11` AS `f11` from `test`.`v1` group by 1) `tt` +join of above two +explain extended select * from v6 join v7 on f2=f1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 11 100.00 Using where +1 SIMPLE <derived5> ref key0 key0 5 test.t2.f2 2 100.00 +5 DERIVED t1 ALL NULL NULL NULL NULL 11 100.00 Using temporary; Using filesort +Warnings: +Note 1003 select `test`.`t2`.`f2` AS `f2`,`test`.`t2`.`f22` AS `f22`,`v1`.`f1` AS `f1`,`v1`.`f11` AS `f11` from `test`.`t2` join `test`.`v1` where ((`v1`.`f1` = `test`.`t2`.`f2`) and (`test`.`t2`.`f2` < 7) and (`test`.`t2`.`f2` in (2,3))) +select * from v6 join v7 on f2=f1; +f2 f22 f1 f11 +3 3 3 3 +2 2 2 2 +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; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 11 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t1.f1 2 +1 PRIMARY xx ALL NULL NULL NULL NULL 11 Using where; Using join buffer (flat, BNL join) +2 DERIVED t2 ALL NULL NULL NULL NULL 11 Using temporary; Using filesort +select * from t1 join (select * from t2 group by f2) tt on t1.f1=tt.f2 join t1 xx on tt.f22=xx.f1; +f1 f11 f2 f22 f1 f11 +1 1 1 1 1 1 +2 2 2 2 2 2 +3 3 3 3 3 3 +TODO: Add test with 64 tables mergeable view to test fall back to +materialization on tables > MAX_TABLES merge +drop table t1,t2; +drop view v1,v2,v3,v4,v6,v7; |