diff options
| -rw-r--r-- | mysql-test/r/derived_view.result | 28 | ||||
| -rw-r--r-- | mysql-test/r/subselect4.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/subselect_cache.result | 1 | ||||
| -rw-r--r-- | mysql-test/t/disabled.def | 1 | ||||
| -rw-r--r-- | mysql-test/t/subselect4.test | 2 | ||||
| -rw-r--r-- | mysql-test/t/subselect_cache.test | 3 | ||||
| -rw-r--r-- | sql/item_subselect.cc | 11 | ||||
| -rw-r--r-- | sql/sql_select.cc | 1 |
8 files changed, 34 insertions, 15 deletions
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index 1e6604d6435..37157162e61 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -74,6 +74,7 @@ show status like 'Handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 0 +Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 @@ -86,6 +87,7 @@ show status like 'Handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 0 +Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 @@ -157,6 +159,7 @@ show status like 'Handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 0 +Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 @@ -169,6 +172,7 @@ show status like 'Handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 0 +Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 @@ -219,6 +223,7 @@ show status like 'Handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 0 +Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 @@ -233,6 +238,7 @@ show status like 'Handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 11 +Handler_read_last 0 Handler_read_next 3 Handler_read_prev 0 Handler_read_rnd 11 @@ -306,6 +312,7 @@ show status like 'Handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 22 +Handler_read_last 0 Handler_read_next 22 Handler_read_prev 0 Handler_read_rnd 0 @@ -321,6 +328,7 @@ show status like 'Handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 0 +Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 @@ -335,6 +343,7 @@ show status like 'Handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 11 +Handler_read_last 0 Handler_read_next 3 Handler_read_prev 0 Handler_read_rnd 11 @@ -414,7 +423,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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)) +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 `t1`.`f1` AS `f1`,`t1`.`f11` AS `f11` from `test`.`t1` where (`t1`.`f1` < 7) group by `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 @@ -428,6 +437,7 @@ show status like 'Handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 2 +Handler_read_last 0 Handler_read_next 2 Handler_read_prev 0 Handler_read_rnd 8 @@ -445,7 +455,7 @@ 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)) +Note 1003 select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11`,`t1`.`f1` AS `f1`,`t1`.`f11` AS `f11` from `test`.`t1` join `test`.`t1` where ((`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)) select * from (select * from (select * from t1 where f1 < 7 ) tt where f1 > 2 ) x @@ -473,7 +483,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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`) +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 `t1`.`f1` AS `f1`,`t1`.`f11` AS `f11` from `test`.`t1` where (`t1`.`f1` < 7) group by `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 @@ -990,7 +1000,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where 3 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 1 100.00 Warnings: -Note 1003 select `test`.`t2`.`a` AS `a`,0 AS `a`,0 AS `b` from `test`.`t1` left join `test`.`t2` on((0 <> 0)) where <in_optimizer>(0,<exists>(select 0 from `test`.`t1` where (<cache>(0) = 0))) +Note 1003 select `test`.`t2`.`a` AS `a`,0 AS `a`,0 AS `b` from `test`.`t2` where <in_optimizer>(0,<exists>(select 0 from dual where (<cache>(0) = 0))) SELECT * FROM t2 RIGHT JOIN v1 AS t ON t.a != 0 WHERE t.a IN (SELECT b FROM t1); a a b @@ -1003,7 +1013,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where 2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 1 100.00 Warnings: -Note 1003 select `test`.`t2`.`a` AS `a`,0 AS `a`,0 AS `b` from `test`.`t1` left join `test`.`t2` on((0 <> 0)) where <in_optimizer>(0,<exists>(select 0 from `test`.`t1` where (<cache>(0) = 0))) +Note 1003 select `test`.`t2`.`a` AS `a`,0 AS `a`,0 AS `b` from `test`.`t2` where <in_optimizer>(0,<exists>(select 0 from dual where (<cache>(0) = 0))) DROP VIEW v1; DROP TABLE t1,t2; # @@ -1075,7 +1085,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 system NULL NULL NULL NULL 1 100.00 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t1` `t` join `test`.`t2` left join `test`.`t3` on((0 <> 0)) where (not(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5))))))) +Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5))))))) SELECT t.a,t.b FROM t3 RIGHT JOIN ((SELECT * FROM t1) AS t, t2) ON t2.b != 0 WHERE (t.a,t.b) NOT IN (SELECT 7, 5); a b @@ -1089,7 +1099,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 system NULL NULL NULL NULL 1 100.00 3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t1` join `test`.`t2` left join `test`.`t3` on((0 <> 0)) where (not(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5))))))) +Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5))))))) SELECT t.a,t.b FROM t3 RIGHT JOIN (v1 AS t, t2) ON t2.b != 0 WHERE (t.a,t.b) NOT IN (SELECT 7, 5); a b @@ -1103,7 +1113,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 system NULL NULL NULL NULL 1 100.00 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t1` join `test`.`t2` left join `test`.`t3` on((0 <> 0)) where (not(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5))))))) +Note 1003 select 6 AS `a`,5 AS `b` from `test`.`t3` where (not(<in_optimizer>((6,5),<exists>(select 7,5 having (trigcond(((<cache>(6) = 7) or isnull(7))) and trigcond(((<cache>(5) = 5) or isnull(5))) and trigcond(<is_not_null_test>(7)) and trigcond(<is_not_null_test>(5))))))) DROP VIEW v1; DROP TABLE t1,t2,t3; # @@ -1262,7 +1272,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1276 Field or reference 't.a' of SELECT #3 was resolved in SELECT #1 -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where exists(select 28 from `test`.`t2` left join `test`.`t3` on((`test`.`t3`.`a` = 28)) where ('j' < `test`.`t1`.`a`)) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where exists(select 28 from `test`.`t3` where ('j' < `test`.`t1`.`a`)) SELECT * FROM (SELECT * FROM t1) AS t WHERE EXISTS (SELECT t2.a FROM t3 RIGHT JOIN t2 ON (t3.a = t2.a) WHERE t2.b < t.a); diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index 23b56e35d66..84317beba32 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -1,4 +1,4 @@ -drop table if exists t1,t2,t3,t4,t5,t6; +drop table if exists t0,t1,t2,t3,t4,t5,t6; drop view if exists v1, v2; set @subselect4_tmp= @@optimizer_switch; set optimizer_switch='semijoin=on,firstmatch=on,loosescan=on'; diff --git a/mysql-test/r/subselect_cache.result b/mysql-test/r/subselect_cache.result index 9145bc3cc58..e57010b026c 100644 --- a/mysql-test/r/subselect_cache.result +++ b/mysql-test/r/subselect_cache.result @@ -1,3 +1,4 @@ +drop table if exists t0,t1,t2,t3,t4,t5,t6,t7,t8,t9; 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); diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index ba03fc73852..2cc734ce24f 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -20,4 +20,3 @@ archive-big : Bug#11817185 2011-03-10 Anitha Disabled since this le log_tables-big : Bug#11756699 2010-11-15 mattiasj report already exists mysql_embedded : Bug#12561297 2011-06-15 New test failing on all platforms tablespace : disabled in MariaDB (no TABLESPACE table attribute) -derived_view : SergeyP diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index 1e8dd73fec6..b31ba9b8f89 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -1,7 +1,7 @@ # General purpose bug fix tests go here : subselect.test too large --disable_warnings -drop table if exists t1,t2,t3,t4,t5,t6; +drop table if exists t0,t1,t2,t3,t4,t5,t6; drop view if exists v1, v2; --enable_warnings diff --git a/mysql-test/t/subselect_cache.test b/mysql-test/t/subselect_cache.test index 47cf50e63d2..e4671cf7b2a 100644 --- a/mysql-test/t/subselect_cache.test +++ b/mysql-test/t/subselect_cache.test @@ -1,3 +1,6 @@ +--disable_warnings +drop table if exists t0,t1,t2,t3,t4,t5,t6,t7,t8,t9; +--enable_warnings set optimizer_switch='subquery_cache=on'; create table t1 (a int, b int); diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index ee2cc861ae2..554052640da 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -2681,9 +2681,14 @@ bool subselect_union_engine::no_rows() void subselect_uniquesubquery_engine::cleanup() { DBUG_ENTER("subselect_uniquesubquery_engine::cleanup"); - /* Tell handler we don't need the index anymore */ - if (tab->table->file->inited) - tab->table->file->ha_index_end(); + /* + Note for mergers: we don't have to, and actually must not de-initialize + tab->table->file here. + - We don't have to, because free_tmp_table() will call ha_index_or_rnd_end + - We must not do it, because tab->table may be a derived table which + has been already dropped by close_thread_tables(), while we here are + called from cleanup_items() + */ DBUG_VOID_RETURN; } diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 313b1bb73ca..b514d95fca5 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -14793,6 +14793,7 @@ free_tmp_table(THD *thd, TABLE *entry) if (entry->file && entry->created) { + entry->file->ha_index_or_rnd_end(); if (entry->db_stat) entry->file->ha_drop_table(entry->s->table_name.str); else |