=== modified file 'libmysqld/Makefile.am' --- libmysqld/Makefile.am 2010-03-20 12:01:47 +0000 +++ libmysqld/Makefile.am 2010-06-23 19:01:58 +0000 @@ -80,7 +80,8 @@ sql_tablespace.cc \ rpl_injector.cc my_user.c partition_info.cc \ sql_servers.cc event_parse_data.cc opt_table_elimination.cc \ - multi_range_read.cc opt_index_cond_pushdown.cc + multi_range_read.cc opt_index_cond_pushdown.cc \ + sql_expression_cache.cc libmysqld_int_a_SOURCES= $(libmysqld_sources) nodist_libmysqld_int_a_SOURCES= $(libmysqlsources) $(sqlsources) === modified file 'mysql-test/r/index_merge_myisam.result' --- mysql-test/r/index_merge_myisam.result 2010-03-20 12:01:47 +0000 +++ mysql-test/r/index_merge_myisam.result 2010-06-23 18:44:58 +0000 @@ -1419,19 +1419,19 @@ # select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='index_merge=off,index_merge_union=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='index_merge_union=on'; select @@optimizer_switch; @@optimizer_switch -index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='default,index_merge_sort_union=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch=4; ERROR 42000: Variable 'optimizer_switch' can't be set to the value of '4' set optimizer_switch=NULL; @@ -1458,21 +1458,21 @@ set optimizer_switch='index_merge=off,index_merge_union=off,default'; select @@optimizer_switch; @@optimizer_switch -index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch=default; select @@global.optimizer_switch; @@global.optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set @@global.optimizer_switch=default; select @@global.optimizer_switch; @@global.optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on # # Check index_merge's @@optimizer_switch flags # select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 (a int, b int, c int, filler char(100), @@ -1582,5 +1582,5 @@ set optimizer_switch=default; show variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on drop table t0, t1; === modified file 'mysql-test/r/myisam_mrr.result' --- mysql-test/r/myisam_mrr.result 2010-03-11 21:43:31 +0000 +++ mysql-test/r/myisam_mrr.result 2010-06-23 18:44:58 +0000 @@ -394,7 +394,7 @@ # - engine_condition_pushdown does not affect ICP select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 (a int, b int, key(a)); === modified file 'mysql-test/r/subselect3.result' --- mysql-test/r/subselect3.result 2010-03-29 14:04:35 +0000 +++ mysql-test/r/subselect3.result 2010-06-23 18:44:58 +0000 @@ -105,6 +105,7 @@ Handler_read_rnd_next 5 delete from t2; insert into t2 values (NULL, 0),(NULL, 0), (NULL, 0), (NULL, 0); +set optimizer_switch='subquery_cache=off'; flush status; select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; oref a Z @@ -123,6 +124,7 @@ select 'No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1.' Z; Z No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1. +set @@optimizer_switch=@save_optimizer_switch; drop table t1, t2; create table t1 (a int, b int, primary key (a)); insert into t1 values (1,1), (3,1),(100,1); === modified file 'mysql-test/r/subselect3_jcl6.result' --- mysql-test/r/subselect3_jcl6.result 2010-03-29 14:04:35 +0000 +++ mysql-test/r/subselect3_jcl6.result 2010-06-23 18:44:58 +0000 @@ -109,6 +109,7 @@ Handler_read_rnd_next 5 delete from t2; insert into t2 values (NULL, 0),(NULL, 0), (NULL, 0), (NULL, 0); +set optimizer_switch='subquery_cache=off'; flush status; select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; oref a Z @@ -127,6 +128,7 @@ select 'No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1.' Z; Z No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1. +set @@optimizer_switch=@save_optimizer_switch; drop table t1, t2; create table t1 (a int, b int, primary key (a)); insert into t1 values (1,1), (3,1),(100,1); === modified file 'mysql-test/r/subselect_no_mat.result' --- mysql-test/r/subselect_no_mat.result 2010-03-20 12:01:47 +0000 +++ mysql-test/r/subselect_no_mat.result 2010-06-23 18:44:58 +0000 @@ -1,6 +1,6 @@ show variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='materialization=off'; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; set @save_optimizer_switch=@@optimizer_switch; @@ -4826,4 +4826,4 @@ set optimizer_switch=default; show variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on === modified file 'mysql-test/r/subselect_no_opts.result' --- mysql-test/r/subselect_no_opts.result 2010-03-20 12:01:47 +0000 +++ mysql-test/r/subselect_no_opts.result 2010-06-23 18:44:58 +0000 @@ -1,6 +1,6 @@ show variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='materialization=off,semijoin=off'; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; set @save_optimizer_switch=@@optimizer_switch; @@ -4826,4 +4826,4 @@ set optimizer_switch=default; show variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on === modified file 'mysql-test/r/subselect_no_semijoin.result' --- mysql-test/r/subselect_no_semijoin.result 2010-03-20 12:01:47 +0000 +++ mysql-test/r/subselect_no_semijoin.result 2010-06-23 18:44:58 +0000 @@ -1,6 +1,6 @@ show variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='semijoin=off'; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; set @save_optimizer_switch=@@optimizer_switch; @@ -4826,4 +4826,4 @@ set optimizer_switch=default; show variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on === modified file 'mysql-test/r/subselect_sj.result' --- mysql-test/r/subselect_sj.result 2010-03-29 14:04:35 +0000 +++ mysql-test/r/subselect_sj.result 2010-06-23 18:44:58 +0000 @@ -202,39 +202,39 @@ select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='default,materialization=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='default,semijoin=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='default,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='default,semijoin=off,materialization=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='default,materialization=off,semijoin=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='default,semijoin=off,materialization=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='default,semijoin=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='default,materialization=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch=default; drop table t0, t1, t2; drop table t10, t11, t12; === modified file 'mysql-test/r/subselect_sj_jcl6.result' --- mysql-test/r/subselect_sj_jcl6.result 2010-03-29 14:04:35 +0000 +++ mysql-test/r/subselect_sj_jcl6.result 2010-06-23 18:44:58 +0000 @@ -206,39 +206,39 @@ select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='default,materialization=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='default,semijoin=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='default,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='default,semijoin=off,materialization=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='default,materialization=off,semijoin=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='default,semijoin=off,materialization=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='default,semijoin=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='default,materialization=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch=default; drop table t0, t1, t2; drop table t10, t11, t12; === modified file 'mysql-test/t/subselect3.test' --- mysql-test/t/subselect3.test 2010-03-20 12:01:47 +0000 +++ mysql-test/t/subselect3.test 2010-06-23 18:44:58 +0000 @@ -98,10 +98,12 @@ delete from t2; insert into t2 values (NULL, 0),(NULL, 0), (NULL, 0), (NULL, 0); +set optimizer_switch='subquery_cache=off'; flush status; select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; show status like '%Handler_read%'; select 'No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1.' Z; +set @@optimizer_switch=@save_optimizer_switch; drop table t1, t2; === modified file 'sql/CMakeLists.txt' --- sql/CMakeLists.txt 2010-03-20 12:01:47 +0000 +++ sql/CMakeLists.txt 2010-06-23 18:47:59 +0000 @@ -78,7 +78,7 @@ rpl_rli.cc rpl_mi.cc sql_servers.cc sql_connect.cc scheduler.cc sql_profile.cc event_parse_data.cc opt_table_elimination.cc - ds_mrr.cc + ds_mrr.cc sql_expression_cache.cc ${PROJECT_SOURCE_DIR}/sql/sql_yacc.cc ${PROJECT_SOURCE_DIR}/sql/sql_yacc.h ${PROJECT_SOURCE_DIR}/include/mysqld_error.h === modified file 'sql/Makefile.am' --- sql/Makefile.am 2010-03-20 12:01:47 +0000 +++ sql/Makefile.am 2010-06-23 18:47:59 +0000 @@ -80,7 +80,7 @@ event_data_objects.h event_scheduler.h \ sql_partition.h partition_info.h partition_element.h \ contributors.h sql_servers.h \ - multi_range_read.h + multi_range_read.h sql_expression_cache.h mysqld_SOURCES = sql_lex.cc sql_handler.cc sql_partition.cc \ item.cc item_sum.cc item_buff.cc item_func.cc \ @@ -130,7 +130,7 @@ sql_servers.cc event_parse_data.cc \ opt_table_elimination.cc \ multi_range_read.cc \ - opt_index_cond_pushdown.cc + opt_index_cond_pushdown.cc sql_expression_cache.cc nodist_mysqld_SOURCES = mini_client_errors.c pack.c client.c my_time.c my_user.c === modified file 'sql/item.cc' --- sql/item.cc 2010-03-20 12:01:47 +0000 +++ sql/item.cc 2010-06-23 21:34:31 +0000 @@ -28,6 +28,9 @@ const String my_null_string("NULL", 4, default_charset_info); +static int save_field_in_field(Field *from,my_bool * null_value, + Field *to, bool no_conversions); + /****************************************************************************/ /* Hybrid_type_traits {_real} */ @@ -540,6 +543,35 @@ return (this->*transformer)(arg); } +/** + Creates and sets expression cache for this item + + @param thd Thread handler + @param depends_on List of external dependences + + @details Creates expression cache item, prepare it and set expression + cache in it for this item with parameters in 'depends_on' list. If all + above finished with success reference to the new cache will be returned + otherwise - reference to this item. + + @note it is common part for all items which creates and set expression + cache +*/ + +Item* Item::set_ecache(THD *thd, List &depends_on) +{ + DBUG_ENTER("Item::set_ecache"); + Item_cache_wrapper *wrapper; + if ((wrapper= new Item_cache_wrapper(this)) && + !wrapper->fix_fields(thd, (Item**)&wrapper)) + { + if (wrapper->set_cache(thd, depends_on)) + DBUG_RETURN(this); + DBUG_RETURN(wrapper); + } + DBUG_RETURN(this); +} + Item_ident::Item_ident(Name_resolution_context *context_arg, const char *db_name_arg,const char *table_name_arg, @@ -2273,7 +2305,6 @@ str->append(str_value); } - Item_uint::Item_uint(const char *str_arg, uint length): Item_int(str_arg, length) { @@ -3646,12 +3677,20 @@ resolved_item->db_name : ""); const char *table_name= (resolved_item->table_name ? resolved_item->table_name : ""); + DBUG_ENTER("mark_as_dependent"); + DBUG_PRINT("enter", ("Field '%s.%s.%s in select %d resolved in %d", + db_name, table_name, + resolved_item->field_name, current->select_number, + last->select_number)); /* store pointer on SELECT_LEX from which item is dependent */ if (mark_item) + { + DBUG_PRINT("info", ("depended_from assigned: 0x%lx", (ulong) last)); mark_item->depended_from= last; + } if (current->mark_as_dependent(thd, last, /** resolved_item psergey-thu **/mark_item)) - return TRUE; + DBUG_RETURN(TRUE); if (thd->lex->describe & DESCRIBE_EXTENDED) { push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE, @@ -3661,7 +3700,7 @@ resolved_item->field_name, current->select_number, last->select_number); } - return FALSE; + DBUG_RETURN(FALSE); } @@ -4098,6 +4137,9 @@ ((ref_type == REF_ITEM || ref_type == FIELD_ITEM) ? (Item_ident*) (*reference) : 0)); + context->select_lex-> + register_dependency_item(last_checked_context->select_lex, + reference); return 0; } } @@ -4113,7 +4155,9 @@ ((ref_type == REF_ITEM || ref_type == FIELD_ITEM) ? (Item_ident*) (*reference) : 0)); - + context->select_lex-> + register_dependency_item(last_checked_context->select_lex, + reference); /* A reference to a view field had been found and we substituted it instead of this Item (find_field_in_tables @@ -4215,6 +4259,10 @@ mark_as_dependent(thd, last_checked_context->select_lex, context->select_lex, rf, rf); + context->select_lex-> + register_dependency_item(last_checked_context->select_lex, + reference); + return 0; } else @@ -4222,6 +4270,9 @@ mark_as_dependent(thd, last_checked_context->select_lex, context->select_lex, this, (Item_ident*)*reference); + context->select_lex-> + register_dependency_item(last_checked_context->select_lex, + reference); if (last_checked_context->select_lex->having_fix_field) { Item_ref *rf; @@ -5082,39 +5133,69 @@ /** + Saves one Fields (field or result_field) of an Item_ref or Item_field + in given Field + + @param from Field to copy value from + @param null_value reference on item null_value to set it if it is needed + @param to Field to cope value to + @param no_conversions how to deal with NULL value (see + set_field_to_null_with_conversions()) + + @details Saves 'from' field value in 'to' field and set null_value + reference in TRUE if the value is NULL. 'no_conversions' passed to + set_field_to_null_with_conversions to determinate what to do with NULL + value if it can't be accepted by 'to' field. + + @note Used by Item_field::save_in_field, Item_field::save_org_in_field and + Item_ref::save_in_field + + @retval FALSE OK + @retval TRUE Error +*/ + +static int save_field_in_field(Field *from, my_bool *null_value, + Field *to, bool no_conversions) +{ + int res; + DBUG_ENTER("save_field_in_field"); + if (from->is_null()) + { + (*null_value)= 1; + res= set_field_to_null_with_conversions(to, no_conversions); + } + else + { + to->set_notnull(); + res= field_conv(to, from); + (*null_value)= 0; + } + DBUG_RETURN(res); +} + +/** Set a field's value from a item. */ void Item_field::save_org_in_field(Field *to) { - if (field->is_null()) - { - null_value=1; - set_field_to_null_with_conversions(to, 1); - } - else - { - to->set_notnull(); - field_conv(to,field); - null_value=0; - } + save_field_in_field(field, &null_value, to, TRUE); } int Item_field::save_in_field(Field *to, bool no_conversions) { - int res; - if (result_field->is_null()) - { - null_value=1; - res= set_field_to_null_with_conversions(to, no_conversions); - } - else - { - to->set_notnull(); - res= field_conv(to,result_field); - null_value=0; - } - return res; + DBUG_ENTER("Item_field::save_in_field"); + /* if it is external field */ + if (unlikely(depended_from)) + { + DBUG_PRINT("info", ("field used, depended_from: 0x%lx", + (ulong) depended_from)); + DBUG_RETURN(save_field_in_field(field, &null_value, to, no_conversions)); + } + + DBUG_PRINT("info", ("result_field used")); + DBUG_RETURN(save_field_in_field(result_field, &null_value, to, + no_conversions)); } @@ -5973,6 +6054,9 @@ refer_type == FIELD_ITEM) ? (Item_ident*) (*reference) : 0)); + context->select_lex-> + register_dependency_item(last_checked_context->select_lex, + reference); /* view reference found, we substituted it instead of this Item, so can quit @@ -6023,6 +6107,9 @@ thd->change_item_tree(reference, fld); mark_as_dependent(thd, last_checked_context->select_lex, thd->lex->current_select, fld, fld); + context->select_lex-> + register_dependency_item(last_checked_context->select_lex, + reference); /* A reference is resolved to a nest level that's outer or the same as the nest level of the enclosing set function : adjust the value of @@ -6046,6 +6133,9 @@ DBUG_ASSERT(*ref && (*ref)->fixed); mark_as_dependent(thd, last_checked_context->select_lex, context->select_lex, this, this); + context->select_lex-> + register_dependency_item(last_checked_context->select_lex, + ref); /* A reference is resolved to a nest level that's outer or the same as the nest level of the enclosing set function : adjust the value of @@ -6312,7 +6402,8 @@ int Item_ref::save_in_field(Field *to, bool no_conversions) { int res; - DBUG_ASSERT(!result_field); + if (result_field && !depended_from) + return save_field_in_field(result_field, &null_value, to, no_conversions); res= (*ref)->save_in_field(to, no_conversions); null_value= (*ref)->null_value; return res; @@ -6421,6 +6512,297 @@ /** + Item_cache_wrapper destructor also destroys expression cache +*/ + +Item_cache_wrapper::~Item_cache_wrapper() +{ + delete ecache; + // expression_value is Item so it will be destroyed from list of Items +} + +/** + Prepare Item_cache_wrapper + + @details Creates Item_cache of the same type as result of Item we are + caching for. This cache will be used for value of the expression to avoid + its double evaluation + + @retval FALSE OK + @retval TRUE Error +*/ +bool Item_cache_wrapper::fix_fields(THD *thd __attribute__((unused)), + Item **it __attribute__((unused))) +{ + DBUG_ENTER("Item_cache_wrapper::fix_fields"); + DBUG_ASSERT(item->fixed); + if ((expression_value= Item_cache::get_cache(item))) + expression_value->setup(item); + fixed= 1; + DBUG_RETURN(expression_value == NULL); +} + +/** + Cleanup the cache before reusing or destruction by Query_arena. +*/ + +void Item_cache_wrapper::cleanup() +{ + delete ecache; + ecache= 0; + // expression_value is Item so it will be destroyed from list of Items + expression_value= 0; +} + + +/** + Set expression cache for the arguments list + + @details Creates Expression_cache_tmptable for given list of dependencies + ('depends_on' and result of the expression this item caching. + + @retval FALSE OK + @retval TRUE Error +*/ + +bool Item_cache_wrapper::set_cache(THD *thd, List &depends_on) +{ + DBUG_ENTER("Item_cache_wrapper::set_cache"); + ecache= new Expression_cache_tmptable(thd, depends_on, expression_value); + DBUG_RETURN(ecache == NULL); +} + +/** + Checks if current set of parameters (stored in cache during its creation) + represented in the cache and return result as Item if it is true. + + @retval NULL nothing was found in the cache + @retval result found +*/ + +Item *Item_cache_wrapper::check_cache() +{ + DBUG_ENTER("Item_cache_wrapper::check_cache"); + if (ecache) + { + Expression_cache_tmptable::result res; + Item *cached_value; + res= ecache->check_value(&cached_value); + if (res == Expression_cache_tmptable::HIT) + DBUG_RETURN(cached_value); + } + DBUG_RETURN(NULL); +} + + +/** + Evaluates integer value of this item via the cache or the cached item +*/ + +longlong Item_cache_wrapper::val_int() +{ + DBUG_ENTER("Item_cache_wrapper::val_int"); + if (!ecache) + { + longlong tmp= item->val_int(); + null_value= item->null_value; + DBUG_RETURN(tmp); + } + { + Item *cached_value; + if ((cached_value= check_cache())) + { + longlong tmp= cached_value->val_int(); + null_value= cached_value->null_value; + DBUG_RETURN(tmp); + } + // Get value in cache to avoid double evaluation + expression_value->store(item); + expression_value->cache_value(); + ecache->put_value(expression_value); // put in ecache + null_value= expression_value->null_value; + DBUG_RETURN(expression_value->val_int()); + } +} + +/** + Evaluates real value of this item via the cache or the cached item +*/ + +double Item_cache_wrapper::val_real() +{ + DBUG_ENTER("Item_cache_wrapper::val_real"); + if (!ecache) + { + double tmp= item->val_real(); + null_value= item->null_value; + DBUG_RETURN(tmp); + } + { + Item *cached_value; + if ((cached_value= check_cache())) + { + double tmp= cached_value->val_real(); + null_value= cached_value->null_value; + DBUG_RETURN(tmp); + } + // Get value in cache to avoid double evaluation + expression_value->store(item); + expression_value->cache_value(); + ecache->put_value(expression_value); // put in ecache + null_value= expression_value->null_value; + DBUG_RETURN(expression_value->val_real()); + } +} + + +/** + Evaluates string value of this item via the cache or the cached item +*/ + +String *Item_cache_wrapper::val_str(String* str) +{ + DBUG_ENTER("Item_cache_wrapper::val_str"); + if (!ecache) + { + String *tmp= item->val_str(str); + null_value= item->null_value; + DBUG_RETURN(tmp); + } + { + Item *cached_value; + if ((cached_value= check_cache())) + { + String *tmp= cached_value->val_str(str); + null_value= cached_value->null_value; + DBUG_RETURN(tmp); + } + // Get value in cache to avoid double evaluation + expression_value->store(item); + expression_value->cache_value(); + ecache->put_value(expression_value); // put in ecache + if ((null_value= expression_value->null_value)) + DBUG_RETURN(NULL); + DBUG_RETURN(expression_value->val_str(str)); + } +} + + +/** + Evaluates decimal value of this item via the cache or the cached item +*/ + +my_decimal *Item_cache_wrapper::val_decimal(my_decimal* decimal_value) +{ + DBUG_ENTER("Item_cache_wrapper::val_decimal"); + if (!ecache) + { + my_decimal *tmp= item->val_decimal(decimal_value); + null_value= item->null_value; + DBUG_RETURN(tmp); + } + { + Item *cached_value; + if ((cached_value= check_cache())) + { + my_decimal *tmp= cached_value->val_decimal(decimal_value); + null_value= cached_value->null_value; + DBUG_RETURN(tmp); + } + // Get value in cache to avoid double evaluation + expression_value->store(item); + expression_value->cache_value(); + ecache->put_value(expression_value); // put in ecache + if ((null_value= expression_value->null_value)) + DBUG_RETURN(NULL); + DBUG_RETURN(expression_value->val_decimal(decimal_value)); + } +} + + +/** + Evaluates boolean value of this item via the cache or the cached item +*/ + +bool Item_cache_wrapper::val_bool() +{ + DBUG_ENTER("Item_cache_wrapper::val_bool"); + if (!ecache) + { + bool tmp= item->val_bool(); + null_value= item->null_value; + DBUG_RETURN(tmp); + } + { + Item *cached_value; + if ((cached_value= check_cache())) + { + bool tmp= cached_value->val_bool(); + null_value= cached_value->null_value; + DBUG_RETURN(tmp); + } + // Get value in cache to avoid double evaluation + expression_value->store(item); + expression_value->cache_value(); + ecache->put_value(expression_value); // put in ecache + null_value= expression_value->null_value; + DBUG_RETURN(expression_value->val_bool()); + } +} + +/** + Checks value of this item for NULL via the cache or the cached item +*/ + +bool Item_cache_wrapper::is_null() +{ + DBUG_ENTER("Item_cache_wrapper::is_null"); + if (!ecache) + { + bool tmp= item->is_null(); + null_value= item->null_value; + DBUG_RETURN(tmp); + } + { + Item *cached_value; + if ((cached_value= check_cache())) + { + bool tmp= cached_value->is_null(); + null_value= cached_value->null_value; + DBUG_RETURN(tmp); + } + expression_value->store(item); + expression_value->cache_value(); + ecache->put_value(expression_value); // put in ecache + DBUG_RETURN((null_value= expression_value->null_value)); + } +} + + +/** + Evaluates date value of this item via the cache or the cached item +*/ + +bool Item_cache_wrapper::get_date(MYSQL_TIME *ltime, uint fuzzydate) +{ + DBUG_ENTER("Item_cache_wrapper::get_date"); + if (!ecache) + DBUG_RETURN((null_value= item->get_date(ltime, fuzzydate))); + + { + Item *cached_value; + if ((cached_value= check_cache())) + DBUG_RETURN((null_value= cached_value->get_date(ltime, fuzzydate))); + + // Get value in cache to avoid double evaluation + expression_value->store(item); + expression_value->cache_value(); + ecache->put_value(expression_value); // put in ecache + DBUG_RETURN((null_value= expression_value->get_date(ltime, fuzzydate))); + } +} + +/** Prepare referenced field then call usual Item_direct_ref::fix_fields . @param thd thread handler === modified file 'sql/item.h' --- sql/item.h 2010-03-20 12:01:47 +0000 +++ sql/item.h 2010-06-23 18:44:58 +0000 @@ -1090,6 +1090,7 @@ virtual Item *neg_transformer(THD *thd) { return NULL; } virtual Item *update_value_transformer(uchar *select_arg) { return this; } + virtual Item *cache_insert_transformer(uchar *thd_arg) { return this; } virtual Item *safe_charset_converter(CHARSET_INFO *tocs); void delete_self() { @@ -1143,6 +1144,9 @@ { return Field::GEOM_GEOMETRY; }; String *check_well_formed_result(String *str, bool send_error= 0); bool eq_by_collation(Item *item, bool binary_cmp, CHARSET_INFO *cs); + + /* Sets expression cache for this Item */ + Item* set_ecache(THD *thd, List &depends_on); }; @@ -1922,8 +1926,6 @@ virtual void print(String *str, enum_query_type query_type); Item_num *neg (); uint decimal_precision() const { return max_length; } - bool check_partition_func_processor(uchar *bool_arg) { return FALSE;} - bool check_vcol_func_processor(uchar *arg) { return FALSE;} }; @@ -2346,7 +2348,8 @@ protected: void set_properties(); public: - enum Ref_Type { REF, DIRECT_REF, VIEW_REF, OUTER_REF, AGGREGATE_REF }; + enum Ref_Type { REF, DIRECT_REF, VIEW_REF, OUTER_REF, AGGREGATE_REF, + CACHE_REF }; Field *result_field; /* Save result here */ Item **ref; Item_ref(Name_resolution_context *context_arg, @@ -2509,6 +2512,73 @@ virtual Ref_Type ref_type() { return DIRECT_REF; } }; +class Expression_cache; +class Item_cache; +/* + The same as Item_ref, but get value from val_* family of method to get + value of item on which it referred instead of result* family. Also it + uses Expression_cache for result cacheing if it is set +*/ +class Item_cache_wrapper :public Item_ref +{ +private: + Expression_cache *ecache; + Item_cache *expression_value; + Item *item; + Name_resolution_context cn; // Fake name resolution context + + + Item *check_cache(); + +public: + Item_cache_wrapper(Item *item_arg) + :Item_ref(&cn, 0, "", "", FALSE), + ecache(NULL), expression_value(NULL), item(item_arg) + { + cn.init(); // Fake name resolution context initialization; + ref= &item; + set_properties(); + name= item_arg->name; + name_length= item_arg->name_length; + } + + ~Item_cache_wrapper(); + + bool set_cache(THD *thd, List &depends_on); + + bool fix_fields(THD *thd, Item **it); + void cleanup(); + + /* Methods of getting value which should be cached */ + double val_real(); + longlong val_int(); + String *val_str(String* tmp); + my_decimal *val_decimal(my_decimal *); + bool val_bool(); + bool is_null(); + bool get_date(MYSQL_TIME *ltime,uint fuzzydate); + bool send(Protocol *protocol, String *buffer) + { return Item::send(protocol, buffer); } + + /* + Methods of getting value which should be cached in general case + int save_in_field(Field *field, bool no_conversions); + void save_org_in_field(Field *field); + */ + + // Should be invisible + virtual void print(String *str, enum_query_type query_type) + { + // TODO: maybe print something for EXPLAIN EXTENDED + return item->print(str, query_type); + } + virtual const char *full_name() const { return item->full_name(); } + virtual void make_field(Send_field *field) { item->make_field(field); } + + virtual Ref_Type ref_type() { return CACHE_REF; } +}; + + /* Class for view fields, the same as Item_direct_ref, but call fix_fields of reference if it is not called yet @@ -3146,7 +3216,8 @@ example(0), used_table_map(0), cached_field(0), cached_field_type(MYSQL_TYPE_STRING), value_cached(0) { - fixed= 1; + fixed= 1; + maybe_null= 1; null_value= 1; } Item_cache(enum_field_types field_type_arg): @@ -3154,6 +3225,7 @@ value_cached(0) { fixed= 1; + maybe_null= 1; null_value= 1; } === modified file 'sql/item_cmpfunc.cc' --- sql/item_cmpfunc.cc 2010-03-20 12:01:47 +0000 +++ sql/item_cmpfunc.cc 2010-06-23 21:39:51 +0000 @@ -1716,19 +1716,20 @@ bool Item_in_optimizer::fix_fields(THD *thd, Item **ref) { + DBUG_ENTER("Item_in_optimizer::fix_fields"); DBUG_ASSERT(fixed == 0); if (fix_left(thd, ref)) - return TRUE; + DBUG_RETURN(TRUE); if (args[0]->maybe_null) maybe_null=1; if (!args[1]->fixed && args[1]->fix_fields(thd, args+1)) - return TRUE; + DBUG_RETURN(TRUE); Item_in_subselect * sub= (Item_in_subselect *)args[1]; if (args[0]->cols() != sub->engine->cols()) { my_error(ER_OPERAND_COLUMNS, MYF(0), args[0]->cols()); - return TRUE; + DBUG_RETURN(TRUE); } if (args[1]->maybe_null) maybe_null=1; @@ -1737,17 +1738,54 @@ not_null_tables_cache|= args[1]->not_null_tables(); const_item_cache&= args[1]->const_item(); fixed= 1; - return FALSE; + DBUG_RETURN(FALSE); +} + + +/** + Adds expression cache for the subquery controlled by this item + if it is needed + + @param thd_arg Thread handler + + @details Adds left expression to the list of dependencies, checks if it is + possible to cache this IN subquery and add expression cache if it is + possible (scalar expression, cacheable subquery, not prohibited by switch). + + @note used from Item::transform() + + @return new cache of pointer to this item +*/ + +Item *Item_in_optimizer::cache_insert_transformer(uchar *thd_arg) +{ + THD *thd= (THD*) thd_arg; + DBUG_ENTER("Item_in_optimizer::cache_insert_transformer"); + List &depends_on= ((Item_subselect *)args[1])->depends_on; + + // We depends from left expression so adds it to the list + depends_on.push_front((Item**)args); + + if (args[0]->cols() == 1 && + thd->variables.optimizer_switch & OPTIMIZER_SWITCH_SUBQUERY_CACHE && + !(((Item_subselect *)args[1])->engine->uncacheable() & + (UNCACHEABLE_RAND | UNCACHEABLE_SIDEEFFECT))) + { + DBUG_RETURN(set_ecache(thd, depends_on)); + } + DBUG_RETURN(this); } longlong Item_in_optimizer::val_int() { bool tmp; + DBUG_ENTER("Item_in_optimizer::val_int"); + DBUG_ASSERT(fixed == 1); cache->store(args[0]); cache->cache_value(); - + if (cache->null_value) { /* @@ -1818,11 +1856,11 @@ for (uint i= 0; i < ncols; i++) item_subs->set_cond_guard_var(i, TRUE); } - return 0; + DBUG_RETURN(0); } tmp= args[1]->val_bool_result(); null_value= args[1]->null_value; - return tmp; + DBUG_RETURN(tmp); } === modified file 'sql/item_cmpfunc.h' --- sql/item_cmpfunc.h 2010-03-20 12:01:47 +0000 +++ sql/item_cmpfunc.h 2010-06-23 18:44:58 +0000 @@ -215,6 +215,7 @@ class Item_cache; +class Expression_cache; #define UNKNOWN ((my_bool)-1) @@ -259,6 +260,7 @@ Item_cache **get_cache() { return &cache; } void keep_top_level_cache(); Item *transform(Item_transformer transformer, uchar *arg); + virtual Item *cache_insert_transformer(uchar *thd_arg); }; class Comp_creator === modified file 'sql/item_subselect.cc' --- sql/item_subselect.cc 2010-03-29 14:04:35 +0000 +++ sql/item_subselect.cc 2010-06-23 21:45:48 +0000 @@ -35,9 +35,9 @@ Item_subselect::Item_subselect(): Item_result_field(), value_assigned(0), thd(0), substitution(0), engine(0), old_engine(0), used_tables_cache(0), have_to_be_excluded(0), - const_item_cache(1), + const_item_cache(1), inside_first_fix_fields(0), done_first_fix_fields(FALSE), - eliminated(FALSE), + eliminated(FALSE), engine_changed(0), changed(0), is_correlated(FALSE) { with_subselect= 1; @@ -93,6 +93,8 @@ SELECT_LEX *upper= unit->outer_select(); if (upper->parsing_place == IN_HAVING) upper->subquery_in_having= 1; + /* Subquery is expression cache candidate */ + upper->has_ecache_candidates[upper->parsing_place]= TRUE; } DBUG_VOID_RETURN; } @@ -116,6 +118,7 @@ } if (engine) engine->cleanup(); + depends_on.empty(); reset(); value_assigned= 0; DBUG_VOID_RETURN; @@ -746,8 +749,12 @@ void Item_singlerow_subselect::fix_length_and_dec() { + DBUG_ENTER("Item_singlerow_subselect::fix_length_and_dec"); if ((max_columns= engine->cols()) == 1) { + DBUG_PRINT("info", ("one, elements: %u flag %u", + (uint)depends_on.elements, + (uint)test(thd->variables.optimizer_switch & OPTIMIZER_SWITCH_SUBQUERY_CACHE))); engine->fix_length_and_dec(row= &value); } else @@ -765,7 +772,40 @@ */ if (engine->no_tables()) maybe_null= engine->may_be_null(); -} + DBUG_VOID_RETURN; +} + + +/** + Adds expression cache for this subquery if it is needed + + @param thd_arg Thread handler + + @details Checks if it is possible to cache this single row subquery and + add expression cache if it is possible (the subquery is dependent, scalar, + cacheable and caching is not prohibited by switch). + + @note used from Item::transform() + + @return new cache of pointer to this item +*/ + +Item* Item_singlerow_subselect::cache_insert_transformer(uchar *thd_arg) +{ + THD *thd= (THD*) thd_arg; + DBUG_ENTER("Item_singlerow_subselect::cache_insert_transformer"); + + if (depends_on.elements && + engine->cols() == 1 && + optimizer_flag(thd, OPTIMIZER_SWITCH_SUBQUERY_CACHE) && + !(engine->uncacheable() & (UNCACHEABLE_RAND | + UNCACHEABLE_SIDEEFFECT))) + { + DBUG_RETURN(set_ecache(thd, depends_on)); + } + DBUG_RETURN(this); +} + uint Item_singlerow_subselect::cols() { @@ -952,12 +992,44 @@ void Item_exists_subselect::fix_length_and_dec() { + DBUG_ENTER("Item_exists_subselect::fix_length_and_dec"); decimals= 0; max_length= 1; max_columns= engine->cols(); /* We need only 1 row to determine existence */ unit->global_parameters->select_limit= new Item_int((int32) 1); -} + + DBUG_VOID_RETURN; +} + + +/** + Adds expression cache for this subquery if it is needed + + @param thd_arg Thread handler + + @details Checks if it is possible to cache this EXISTS subquery and add + expression cache if it is possible (the subquery is really EXISTS, + dependent, scalar, cacheable and caching is not prohibited by switch). + @note used from Item::transform() + + @return new cache of pointer to this item +*/ + +Item* Item_exists_subselect::cache_insert_transformer(uchar *thd_arg) +{ + THD *thd= (THD*) thd_arg; + DBUG_ENTER("Item_exists_subselect::cache_insert_transformer"); + if (substype() == EXISTS_SUBS && depends_on.elements && + optimizer_flag(thd, OPTIMIZER_SWITCH_SUBQUERY_CACHE) && + !(engine->uncacheable() & (UNCACHEABLE_RAND | + UNCACHEABLE_SIDEEFFECT))) + { + DBUG_RETURN(set_ecache(thd, depends_on)); + } + DBUG_RETURN(this); +} + double Item_exists_subselect::val_real() { === modified file 'sql/item_subselect.h' --- sql/item_subselect.h 2010-03-29 14:04:35 +0000 +++ sql/item_subselect.h 2010-06-23 18:44:59 +0000 @@ -88,11 +88,19 @@ */ List upper_refs; st_select_lex *parent_select; - - /* + + /** + List of references on items subquery depends on (externally resolved); + + @note We can't store direct links on Items because it could be + substituted with other item (for example for grouping). + */ + List depends_on; + + /* TRUE<=>Table Elimination has made it redundant to evaluate this select (and so it is not part of QEP, etc) - */ + */ bool eliminated; /* changed engine indicator */ @@ -202,6 +210,7 @@ { protected: Item_cache *value, **row; + public: Item_singlerow_subselect(st_select_lex *select_lex); Item_singlerow_subselect() :Item_subselect(), value(0), row (0) {} @@ -242,6 +251,8 @@ */ st_select_lex* invalidate_and_restore_select_lex(); + Item* cache_insert_transformer(uchar *thd_arg); + friend class select_singlerow_subselect; }; @@ -289,6 +300,8 @@ void fix_length_and_dec(); virtual void print(String *str, enum_query_type query_type); + Item* cache_insert_transformer(uchar *thd_arg); + friend class select_exists_subselect; friend class subselect_uniquesubquery_engine; friend class subselect_indexsubquery_engine; @@ -812,7 +825,7 @@ { return materialize_engine->cols(); } - uint8 uncacheable() { return UNCACHEABLE_DEPENDENT; } + uint8 uncacheable() { return materialize_engine->uncacheable(); } table_map upper_select_const_tables() { return 0; } bool no_rows() { return !tmp_table->file->stats.records; } virtual enum_engine_type engine_type() { return HASH_SJ_ENGINE; } === modified file 'sql/item_sum.cc' --- sql/item_sum.cc 2010-03-20 12:01:47 +0000 +++ sql/item_sum.cc 2010-06-23 18:44:59 +0000 @@ -319,6 +319,7 @@ if (aggr_level >= 0) { ref_by= ref; + thd->lex->current_select->register_dependency_item(aggr_sel, ref); /* Add the object to the list of registered objects assigned to aggr_sel */ if (!aggr_sel->inner_sum_func_list) next= this; === modified file 'sql/mysql_priv.h' --- sql/mysql_priv.h 2010-03-20 12:01:47 +0000 +++ sql/mysql_priv.h 2010-06-23 18:47:59 +0000 @@ -568,12 +568,13 @@ #define OPTIMIZER_SWITCH_SEMIJOIN 256 #define OPTIMIZER_SWITCH_PARTIAL_MATCH_ROWID_MERGE 512 #define OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN 1024 +#define OPTIMIZER_SWITCH_SUBQUERY_CACHE (1<<11) #ifdef DBUG_OFF -# define OPTIMIZER_SWITCH_LAST 2048 +# define OPTIMIZER_SWITCH_LAST (1<<12) #else -# define OPTIMIZER_SWITCH_TABLE_ELIMINATION 2048 -# define OPTIMIZER_SWITCH_LAST 4096 +# define OPTIMIZER_SWITCH_TABLE_ELIMINATION (1<<12) +# define OPTIMIZER_SWITCH_LAST (1<<13) #endif #ifdef DBUG_OFF @@ -588,7 +589,8 @@ OPTIMIZER_SWITCH_MATERIALIZATION | \ OPTIMIZER_SWITCH_SEMIJOIN | \ OPTIMIZER_SWITCH_PARTIAL_MATCH_ROWID_MERGE|\ - OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN) + OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN|\ + OPTIMIZER_SWITCH_SUBQUERY_CACHE) #else # define OPTIMIZER_SWITCH_DEFAULT (OPTIMIZER_SWITCH_INDEX_MERGE | \ OPTIMIZER_SWITCH_INDEX_MERGE_UNION | \ @@ -601,7 +603,8 @@ OPTIMIZER_SWITCH_MATERIALIZATION | \ OPTIMIZER_SWITCH_SEMIJOIN | \ OPTIMIZER_SWITCH_PARTIAL_MATCH_ROWID_MERGE|\ - OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN) + OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN|\ + OPTIMIZER_SWITCH_SUBQUERY_CACHE) #endif /* @@ -694,7 +697,8 @@ IN_HAVING, SELECT_LIST, IN_WHERE, - IN_ON + IN_ON, + PARSING_PLACE_SIZE /* always should be the last */ }; struct st_table; @@ -936,6 +940,7 @@ #ifdef MYSQL_SERVER #include "sql_servers.h" #include "opt_range.h" +#include "sql_expression_cache.h" #ifdef HAVE_QUERY_CACHE struct Query_cache_query_flags @@ -1269,6 +1274,10 @@ Item *having, ORDER *proc_param, ulonglong select_type, select_result *result, SELECT_LEX_UNIT *unit, SELECT_LEX *select_lex); + +struct st_join_table *create_index_lookup_join_tab(TABLE *table, int key_no); +int join_read_key2(THD *thd, struct st_join_table *tab, TABLE *table, + struct st_table_ref *table_ref); void free_underlaid_joins(THD *thd, SELECT_LEX *select); bool mysql_explain_union(THD *thd, SELECT_LEX_UNIT *unit, select_result *result); @@ -1288,6 +1297,7 @@ bool table_cant_handle_bit_fields, bool make_copy_field, uint convert_blob_length); +bool open_tmp_table(TABLE *table); void sp_prepare_create_field(THD *thd, Create_field *sql_field); int prepare_create_field(Create_field *sql_field, uint *blob_columns, === modified file 'sql/mysqld.cc' --- sql/mysqld.cc 2010-03-20 12:01:47 +0000 +++ sql/mysqld.cc 2010-06-23 19:41:34 +0000 @@ -305,6 +305,7 @@ "firstmatch","loosescan","materialization", "semijoin", "partial_match_rowid_merge", "partial_match_table_scan", + "subquery_cache", #ifndef DBUG_OFF "table_elimination", #endif @@ -325,6 +326,7 @@ sizeof("semijoin") - 1, sizeof("partial_match_rowid_merge") - 1, sizeof("partial_match_table_scan") - 1, + sizeof("subquery_cache") - 1, #ifndef DBUG_OFF sizeof("table_elimination") - 1, #endif @@ -404,8 +406,9 @@ static const char *optimizer_switch_str="index_merge=on,index_merge_union=on," "index_merge_sort_union=on," "index_merge_intersection=on," - "index_condition_pushdown=on" -#ifndef DBUG_OFF + "index_condition_pushdown=on," + "subquery_cache=on" +#ifndef DBUG_OFF ",table_elimination=on"; #else ; @@ -5872,7 +5875,9 @@ OPT_RECORD_RND_BUFFER, OPT_DIV_PRECINCREMENT, OPT_RELAY_LOG_SPACE_LIMIT, OPT_RELAY_LOG_PURGE, OPT_SLAVE_NET_TIMEOUT, OPT_SLAVE_COMPRESSED_PROTOCOL, OPT_SLOW_LAUNCH_TIME, - OPT_SLAVE_TRANS_RETRIES, OPT_READONLY, OPT_ROWID_MERGE_BUFF_SIZE, + OPT_SLAVE_TRANS_RETRIES, + OPT_SUBQUERY_CACHE, + OPT_READONLY, OPT_ROWID_MERGE_BUFF_SIZE, OPT_DEBUGGING, OPT_DEBUG_FLUSH, OPT_SORT_BUFFER, OPT_TABLE_OPEN_CACHE, OPT_TABLE_DEF_CACHE, OPT_THREAD_CONCURRENCY, OPT_THREAD_CACHE_SIZE, @@ -7164,7 +7169,7 @@ {"optimizer_switch", OPT_OPTIMIZER_SWITCH, "optimizer_switch=option=val[,option=val...], where option={index_merge, " "index_merge_union, index_merge_sort_union, index_merge_intersection, " - "index_condition_pushdown" + "index_condition_pushdown, subquery_cache" #ifndef DBUG_OFF ", table_elimination" #endif @@ -7868,6 +7873,14 @@ {"Ssl_version", (char*) &show_ssl_get_version, SHOW_FUNC}, #endif /* HAVE_OPENSSL */ {"Syncs", (char*) &my_sync_count, SHOW_LONG_NOFLUSH}, + + /* + As long as subquery is the only thing which cached by the expression cache + we will call statistcs variables subquery_cache* to make meaning clear for + users. + */ + {"Subquery_cache_hit", (char*) &subquery_cache_hit, SHOW_LONG}, + {"Subquery_cache_miss", (char*) &subquery_cache_miss, SHOW_LONG}, {"Table_locks_immediate", (char*) &locks_immediate, SHOW_LONG}, {"Table_locks_waited", (char*) &locks_waited, SHOW_LONG}, #ifdef HAVE_MMAP @@ -8006,6 +8019,7 @@ abort_loop= select_thread_in_use= signal_thread_in_use= 0; ready_to_exit= shutdown_in_progress= grant_option= 0; aborted_threads= aborted_connects= 0; + subquery_cache_miss= subquery_cache_hit= 0; delayed_insert_threads= delayed_insert_writes= delayed_rows_in_use= 0; delayed_insert_errors= thread_created= 0; specialflag= 0; === modified file 'sql/sql_base.cc' --- sql/sql_base.cc 2010-03-20 12:01:47 +0000 +++ sql/sql_base.cc 2010-06-23 18:44:59 +0000 @@ -8062,6 +8062,10 @@ if (*conds) { thd->where="where clause"; + DBUG_EXECUTE("where", + print_where(*conds, + "WHERE in setup_conds", + QT_ORDINARY);); if ((!(*conds)->fixed && (*conds)->fix_fields(thd, conds)) || (*conds)->check_cols(1)) goto err_no_arena; === modified file 'sql/sql_class.cc' --- sql/sql_class.cc 2010-03-20 12:01:47 +0000 +++ sql/sql_class.cc 2010-06-23 18:44:59 +0000 @@ -3020,6 +3020,7 @@ table_charset= 0; precomputed_group_by= 0; bit_fields_as_long= 0; + skip_create_table= 0; DBUG_VOID_RETURN; } === modified file 'sql/sql_class.h' --- sql/sql_class.h 2010-03-20 12:01:47 +0000 +++ sql/sql_class.h 2010-06-23 18:44:59 +0000 @@ -2786,12 +2786,17 @@ that MEMORY tables cannot index BIT columns. */ bool bit_fields_as_long; + /* + Whether to create or postpone actual creation of this temporary table. + TRUE <=> create_tmp_table will create only the TABLE structure. + */ + bool skip_create_table; TMP_TABLE_PARAM() :copy_field(0), group_parts(0), group_length(0), group_null_parts(0), convert_blob_length(0), schema_table(0), precomputed_group_by(0), force_copy_fields(0), - bit_fields_as_long(0) + bit_fields_as_long(0), skip_create_table(0) {} ~TMP_TABLE_PARAM() { === added file 'sql/sql_expression_cache.cc' --- sql/sql_expression_cache.cc 1970-01-01 00:00:00 +0000 +++ sql/sql_expression_cache.cc 2010-06-23 21:49:48 +0000 @@ -0,0 +1,300 @@ + +#include "mysql_priv.h" +#include "sql_select.h" + +/* + As long as subquery is the only thing which cached by the expression cache + we will call statistc variables subquery_cache* to make meaning clear for + users. +*/ +ulonglong subquery_cache_miss, subquery_cache_hit; + +Expression_cache_tmptable::Expression_cache_tmptable(THD *thd, + List &dependance, + Item *value) + :cache_table(NULL), table_thd(thd), list(&dependance), val(value), + equalities(NULL), inited (0) +{ + DBUG_ENTER("Expression_cache_tmptable::Expression_cache_tmptable"); + DBUG_VOID_RETURN; +}; + + +/** + Creates equalities expression. + + @details For some type of fields index lookup do not return failure but set + pointer on the next record. To check exact match we use expression like: + field1=value1 and field2=value2 ... + + @retval FALSE OK + @retval TRUE Error +*/ + +bool Expression_cache_tmptable::make_equalities() +{ + List args; + List_iterator_fast li(*list); + Item **ref; + Name_resolution_context *cn= NULL; + DBUG_ENTER("Expression_cache_tmptable::make_equalities"); + + for (uint i= 1 /* skip result filed */; (ref= li++); i++) + { + Field *fld= cache_table->field[i]; + /* Only some field types should be checked after lookup */ + if (fld->type() == MYSQL_TYPE_VARCHAR || + fld->type() == MYSQL_TYPE_TINY_BLOB || + fld->type() == MYSQL_TYPE_MEDIUM_BLOB || + fld->type() == MYSQL_TYPE_LONG_BLOB || + fld->type() == MYSQL_TYPE_BLOB || + fld->type() == MYSQL_TYPE_VAR_STRING || + fld->type() == MYSQL_TYPE_STRING || + fld->type() == MYSQL_TYPE_NEWDECIMAL || + fld->type() == MYSQL_TYPE_DECIMAL) + { + if (!cn) + { + // dummy resolution context + cn= new Name_resolution_context(); + cn->init(); + } + args.push_front(new Item_func_eq(new Item_ref(cn, ref, "", "", FALSE), + new Item_field(fld))); + } + } + if (args.elements == 1) + equalities= args.head(); + else + equalities= new Item_cond_and(args); + + DBUG_RETURN(equalities->fix_fields(table_thd, &equalities)); +} + + +/** + Enumerates all fields in field number order. + + @param arg reference on current field number + + @note Used for temporary table key creation + + @return field number +*/ + +static uint field_enumerator(uchar *arg) +{ + return ((uint*)arg)[0]++; +} + + +/** + Initializes temporary table and index for this cache + + @details Creates temporary table, index and search structures. If any of + steps fails it just disable the cache by removing its temporary table. +*/ + +void Expression_cache_tmptable::init() +{ + List_iterator_fast li(*list); + List_iterator_fast li_items(items); + Item **item; + uint field_counter; + DBUG_ENTER("Expression_cache_tmptable::init"); + DBUG_ASSERT(!inited); + inited= TRUE; + + if (!(ULONGLONG_MAX >> (list->elements + 1))) + { + DBUG_PRINT("info", ("Too many dependencies")); + DBUG_VOID_RETURN; + } + + cache_table= NULL; + while ((item= li++)) + { + DBUG_ASSERT(item); + DBUG_ASSERT(*item); + DBUG_ASSERT((*item)->fixed); + items.push_back((*item)); + } + + cache_table_param.init(); + /* dependance items and result */ + cache_table_param.field_count= list->elements + 1; + /* postpone table creation to index description */ + cache_table_param.skip_create_table= 1; + + + items.push_front(val); + if (!(cache_table= create_tmp_table(table_thd, &cache_table_param, + items, (ORDER*) NULL, + FALSE, FALSE, + ((table_thd->options | + TMP_TABLE_ALL_COLUMNS) & + ~(OPTION_BIG_TABLES | + TMP_TABLE_FORCE_MYISAM)), + HA_POS_ERROR, + (char *)"subquery-cache-table"))) + { + DBUG_PRINT("error", ("create_tmp_table failed, caching switched off")); + DBUG_VOID_RETURN; + } + + if (cache_table->s->db_type() != heap_hton) + { + DBUG_PRINT("error", ("we need only heap table")); + goto error; + } + + /* first field in the table is result value, so we skip it */ + li_items++; + field_counter=1; + + if (cache_table->alloc_keys(1) || + (cache_table->add_tmp_key(0, items.elements - 1, + &field_enumerator, + (uchar*)&field_counter) < 0) || + cache_table->createtmp_table_search_structures(table_thd, li_items, + &tab_ref) || + !(tab= create_index_lookup_join_tab(cache_table, 0))) + { + DBUG_PRINT("error", ("creating index failed")); + goto error; + } + cache_table->s->keys= 1; + cache_table->s->uniques= 1; + + if (open_tmp_table(cache_table)) + { + DBUG_PRINT("error", ("Opening (creating) temporary table failed")); + goto error; + } + + if (!(cached_result= new Item_field(cache_table->field[0]))) + { + DBUG_PRINT("error", ("Creating Item_field failed")); + goto error; + } + + if (make_equalities()) + { + DBUG_PRINT("error", ("Creating equalities failed")); + goto error; + } + + DBUG_VOID_RETURN; + +error: + /* switch off cache */ + free_tmp_table(table_thd, cache_table); + cache_table= NULL; + DBUG_VOID_RETURN; +} + +/** + Destroys temporary table if it exists +*/ + +Expression_cache_tmptable::~Expression_cache_tmptable() +{ + if (cache_table) + free_tmp_table(table_thd, cache_table); +} + + +/** + Checks if current key present in the cache and returns value if it is true + + @param value assigned Item with value from the cache if key + is found + + @return result of the key lookup +*/ + +Expression_cache::result Expression_cache_tmptable::check_value(Item **value) +{ + int res; + DBUG_ENTER("Expression_cache_tmptable::check_value"); + + /* + We delay cache initialization to get item references which should be + used at the moment of query execution. I.e. we store reference on item + reference at the moment of class creation but for table creation and + index supply structures (join_tab) we need real Items which used at the + moment of execution so we can resolve reference only at this point. + */ + if (!inited) + init(); + + if (cache_table) + { + DBUG_PRINT("info", ("status: %u has_record %u", + (uint)cache_table->status, (uint)tab_ref->has_record)); + if ((res= join_read_key2(table_thd, tab, cache_table, tab_ref)) == 1) + DBUG_RETURN(ERROR); + if (res || (equalities && !equalities->val_int())) + { + subquery_cache_miss++; + DBUG_RETURN(MISS); + } + + subquery_cache_hit++; + *value= cached_result; + DBUG_RETURN(Expression_cache::HIT); + } + DBUG_RETURN(Expression_cache::MISS); +} + + +/** + Puts given value in the cache + + @param value Value to put in the cache + + @note As value of the key of the given value used values of parameter + items stored during initialization. + + @retval FALSE OK + @retval TRUE Error +*/ + +my_bool Expression_cache_tmptable::put_value(Item *value) +{ + int error; + DBUG_ENTER("Expression_cache_tmptable::put_value"); + DBUG_ASSERT(inited); + + if (!cache_table) + { + DBUG_PRINT("info", ("No table so behave as we successfully put value")); + DBUG_RETURN(FALSE); + } + + *(items.head_ref())= value; + fill_record(table_thd, cache_table->field, items, 1); + if (table_thd->is_error()) + goto err;; + + if ((error= cache_table->file->ha_write_row(cache_table->record[0]))) + { + /* create_myisam_from_heap will generate error if needed */ + if (cache_table->file->is_fatal_error(error, HA_CHECK_DUP) && + create_internal_tmp_table_from_heap(table_thd, cache_table, + cache_table_param.start_recinfo, + &cache_table_param.recinfo, + error, 1)) + goto err; + } + cache_table->status= 0; /* cache_table->record contains an existed record */ + tab_ref->has_record= TRUE; /* the same as above */ + DBUG_PRINT("info", ("has_record: TRUE status: 0")); + + DBUG_RETURN(FALSE); + +err: + free_tmp_table(table_thd, cache_table); + cache_table= NULL; + DBUG_RETURN(TRUE); +} === added file 'sql/sql_expression_cache.h' --- sql/sql_expression_cache.h 1970-01-01 00:00:00 +0000 +++ sql/sql_expression_cache.h 2010-06-23 20:28:44 +0000 @@ -0,0 +1,74 @@ +#ifndef _SQL_SUBQUERY_CACHE_H_ +#define _SQL_SUBQUERY_CACHE_H_ + +/** + Interface for expression cache works with Item_cache_wrapper + + @note Parameters of the cache passed on its creation and are not subject + of this interface. +*/ + +extern ulonglong subquery_cache_miss, subquery_cache_hit; + +class Expression_cache :public Sql_alloc +{ +public: + enum result {ERROR, HIT, MISS}; + + Expression_cache(){}; + virtual ~Expression_cache() {}; + /** + Checks presence of the key (taken from cache owner) and if found return + it via value parameter + */ + virtual result check_value(Item **value)= 0; + /** + Puts value into this cache (key should be taken from cache owner) + */ + virtual my_bool put_value(Item *value)= 0; +}; + +struct st_table_ref; +struct st_join_table; +class Item_field; + +/** + Implementation of expression cache over temporary table +*/ + +class Expression_cache_tmptable :public Expression_cache +{ +public: + Expression_cache_tmptable(THD *thd, List &dependance, Item *value); + virtual ~Expression_cache_tmptable(); + virtual result check_value(Item **value); + virtual my_bool put_value(Item *value); + +private: + void init(); + bool make_equalities(); + + /* tmp table parameters */ + TMP_TABLE_PARAM cache_table_param; + /* temporary table to store this cache */ + TABLE *cache_table; + /* Thread handler for the temporary table */ + THD *table_thd; + /* tab_ref for index search */ + struct st_table_ref *tab_ref; + /* JOIN_TAB for index lookup */ + st_join_table *tab; + /* Chached result */ + Item_field *cached_result; + /* List of references to items */ + List *list; + /* List of items */ + List items; + /* Value Item example */ + Item *val; + /* Expression to check after index lookup */ + Item *equalities; + /* set if structures are inited */ + bool inited; +}; +#endif === modified file 'sql/sql_lex.cc' --- sql/sql_lex.cc 2010-03-20 12:01:47 +0000 +++ sql/sql_lex.cc 2010-06-23 21:54:52 +0000 @@ -1632,6 +1632,8 @@ nest_level= 0; link_next= 0; lock_option= TL_READ_DEFAULT; + + bzero((char*) has_ecache_candidates, sizeof(has_ecache_candidates)); } void st_select_lex::init_select() @@ -1829,6 +1831,59 @@ } +/** + Registers reference on items on which the subqueries depends + + @param last pointer to last st_select_lex struct, before + which all st_select_lex have to be marked as + dependent + @param dependency reference on the item on which all this + subqueries depends + + @details Goes from this SELECT_LEX upper to 'last' and put 'dependency' + reference in their depends_on list if it is not present there already. + + TODO: check all entries of mark_as_dependant used without call of this + method, probably they do not need or even lead to incorrect marking during + prepared statement execution + +*/ + +void st_select_lex::register_dependency_item(st_select_lex *last, + Item **dependency) +{ + SELECT_LEX *s= this; + DBUG_ENTER("st_select_lex::register_dependency_item"); + DBUG_ASSERT(this != last); + DBUG_ASSERT(*dependency); + do + { + /* check duplicates */ + List_iterator_fast li(s->master_unit()->item->depends_on); + Item **dep; + while ((dep= li++)) + { + if ((*dep)->eq(*dependency, FALSE)) + { + DBUG_PRINT("info", ("dependency %s already present", + ((*dependency)->name ? + (*dependency)->name : + ""))); + DBUG_VOID_RETURN; + } + } + + s->master_unit()->item->depends_on.push_back(dependency); + DBUG_PRINT("info", ("depends_on: Select: %d added: %s", + s->select_number, + ((*dependency)->name ? + (*dependency)->name : + ""))); + } while ((s= s->outer_select()) != last && s != 0); + DBUG_VOID_RETURN; +} + + /* st_select_lex_node::mark_as_dependent mark all st_select_lex struct from this to 'last' as dependent @@ -1843,7 +1898,7 @@ bool st_select_lex::mark_as_dependent(THD *thd, st_select_lex *last, Item *dependency) { - + DBUG_ENTER("st_select_lex::mark_as_dependent"); DBUG_ASSERT(this != last); /* @@ -1872,11 +1927,11 @@ Item_subselect *subquery_expr= s->master_unit()->item; if (subquery_expr && subquery_expr->mark_as_dependent(thd, last, dependency)) - return TRUE; + DBUG_RETURN(TRUE); } while ((s= s->outer_select()) != last && s != 0); is_correlated= TRUE; this->master_unit()->item->is_correlated= TRUE; - return FALSE; + DBUG_RETURN(FALSE); } bool st_select_lex_node::set_braces(bool value) { return 1; } === modified file 'sql/sql_lex.h' --- sql/sql_lex.h 2010-03-20 12:01:47 +0000 +++ sql/sql_lex.h 2010-06-23 18:44:59 +0000 @@ -660,6 +660,9 @@ /* explicit LIMIT clause was used */ bool explicit_limit; + + /* where we have candidates for expression caching, see enum_parsing_place */ + bool has_ecache_candidates[PARSING_PLACE_SIZE]; /* there are subquery in HAVING clause => we can't close tables before query processing end even if we use temporary table @@ -748,6 +751,7 @@ } bool mark_as_dependent(THD *thd, st_select_lex *last, Item *dependency); + void register_dependency_item(st_select_lex *last, Item **dependency); bool set_braces(bool value); bool inc_in_sum_expr(); === modified file 'sql/sql_select.cc' --- sql/sql_select.cc 2010-05-10 13:46:08 +0000 +++ sql/sql_select.cc 2010-06-23 22:06:13 +0000 @@ -151,7 +151,6 @@ static int join_read_system(JOIN_TAB *tab); static int join_read_const(JOIN_TAB *tab); static int join_read_key(JOIN_TAB *tab); -static int join_read_key2(JOIN_TAB *tab, TABLE *table, TABLE_REF *table_ref); static void join_read_key_unlock_row(st_join_table *tab); static int join_read_always_key(JOIN_TAB *tab); static int join_read_last_key(JOIN_TAB *tab); @@ -1479,6 +1478,9 @@ DBUG_RETURN(-1); /* purecov: inspected */ } + if (setup_subquery_caches(thd)) + DBUG_RETURN(-1); + error= 0; DBUG_RETURN(0); @@ -1495,6 +1497,62 @@ DBUG_RETURN(0); } +/** + Setup expression caches if there is subqueries + + @param thd Thread handler + + @details Calls transformer which puts expression cache before subqueries + for expressions of this SELECT JOIN if subqueries was detected in them + + @note We call this method on late optimization phase so should check + WHERE, HAVING and ON together, because optimization moves expression + between them. + + @retval FALSE OK + @retval TRUE Error +*/ +bool JOIN::setup_subquery_caches(THD *thd) +{ + DBUG_ENTER("JOIN::setup_subquery_caches"); + if (select_lex->has_ecache_candidates[IN_WHERE] || + select_lex->has_ecache_candidates[IN_HAVING] || + select_lex->has_ecache_candidates[IN_ON]) + { + if (conds) + conds= conds->transform(&Item::cache_insert_transformer, + (uchar*) thd); + if (having) + having= having->transform(&Item::cache_insert_transformer, + (uchar*) thd); + for (TABLE_LIST *table= tables_list; table; table= table->next_local) + { + if (table->on_expr) + { + Item *new_on= + table->on_expr->transform(&Item::cache_insert_transformer, + (uchar*) thd); + if (new_on != table->on_expr) + thd->change_item_tree(&table->on_expr, new_on); + } + } + } + if (select_lex->has_ecache_candidates[SELECT_LIST]) + { + List_iterator li(all_fields); + Item *item; + while ((item= li++)) + { + Item *new_item= + item->transform(&Item::cache_insert_transformer, (uchar*) thd); + if (new_item != item) + { + thd->change_item_tree(li.ref(), new_item); + } + } + } + DBUG_RETURN(FALSE); +} /** Restore values in temporary join. @@ -1556,6 +1614,7 @@ DBUG_RETURN(0); } + /** @brief Save the original join layout @@ -5209,7 +5268,7 @@ 'join->best_positions' contains a complete optimal extension of the current partial QEP. */ - DBUG_EXECUTE("opt", print_plan(join, join->tables, + DBUG_EXECUTE("opt", print_plan(join, n_tables, record_count, read_time, read_time, "optimal");); DBUG_RETURN(FALSE); @@ -7625,6 +7684,43 @@ /** + Creates and fills JOIN_TAB for index look up in temporary table + + @param table The table where to look up + @param key_no Number of key + + @details Prepares this JOIN_TAB be used with 'key_no' key in the 'table'. + + @return JOIN_TAB object or NULL in case of error +*/ + +JOIN_TAB *create_index_lookup_join_tab(TABLE *table, int key_no) +{ + JOIN_TAB *tab; + DBUG_ENTER("create_index_lookup_join_tab"); + + if (!((tab= new JOIN_TAB))) + DBUG_RETURN(NULL); + tab->read_record.table= table; + tab->read_record.file=table->file; + tab->next_select=0; + tab->sorted= 1; + tab->ref.key= key_no; + + table->status= STATUS_NO_RECORD; + tab->read_first_record= join_read_key; + tab->read_record.read_record= join_no_more_records; + if (table->covering_keys.is_set(tab->ref.key) && + !table->no_keyread) + { + table->key_read=1; + table->file->extra(HA_EXTRA_KEYREAD); + } + DBUG_RETURN(tab); +} + + +/** Give error if we some tables are done with a full join. This is used by multi_table_update and multi_table_delete when running @@ -10778,6 +10874,7 @@ case Item::REF_ITEM: case Item::NULL_ITEM: case Item::VARBIN_ITEM: + case Item::CACHE_ITEM: if (make_copy_field) { DBUG_ASSERT(((Item_result_field*)item)->result_field); @@ -11552,7 +11649,8 @@ ¶m->recinfo, select_options)) goto err; } - if (open_tmp_table(table)) + DBUG_PRINT("info", ("skip_create_table: %d", (int)param->skip_create_table)); + if (!param->skip_create_table && open_tmp_table(table)) goto err; thd->mem_root= mem_root_save; @@ -11700,16 +11798,17 @@ bool open_tmp_table(TABLE *table) { int error; + DBUG_ENTER("open_tmp_table"); if ((error= table->file->ha_open(table, table->s->table_name.str, O_RDWR, HA_OPEN_TMP_TABLE | HA_OPEN_INTERNAL_TABLE))) { table->file->print_error(error,MYF(0)); /* purecov: inspected */ table->db_stat=0; - return(1); + DBUG_RETURN(1); } (void) table->file->extra(HA_EXTRA_QUICK); /* Faster */ - return(0); + DBUG_RETURN(0); } @@ -12540,7 +12639,8 @@ else { /* Do index lookup in the materialized table */ - if ((res= join_read_key2(join_tab, sjm->table, sjm->tab_ref)) == 1) + if ((res= join_read_key2(join_tab->join->thd, join_tab, + sjm->table, sjm->tab_ref)) == 1) DBUG_RETURN(NESTED_LOOP_ERROR); /* purecov: inspected */ if (res || !sjm->in_equality->val_int()) DBUG_RETURN(NESTED_LOOP_NO_MORE_ROWS); @@ -13323,61 +13423,62 @@ static int join_read_key(JOIN_TAB *tab) { - return join_read_key2(tab, tab->table, &tab->ref); + return join_read_key2(tab->join->thd, tab, tab->table, &tab->ref); } -/* +/* eq_ref access handler but generalized a bit to support TABLE and TABLE_REF not from the join_tab. See join_read_key for detailed synopsis. */ -static int -join_read_key2(JOIN_TAB *tab, TABLE *table, TABLE_REF *table_ref) +int join_read_key2(THD *thd, JOIN_TAB *tab, TABLE *table, TABLE_REF *table_ref) { int error; + DBUG_ENTER("join_read_key2"); if (!table->file->inited) { table->file->ha_index_init(table_ref->key, tab->sorted); } /* TODO: Why don't we do "Late NULLs Filtering" here? */ - if (cmp_buffer_with_ref(tab->join->thd, table, table_ref) || + if (cmp_buffer_with_ref(thd, table, table_ref) || (table->status & (STATUS_GARBAGE | STATUS_NO_PARENT | STATUS_NULL_ROW))) { if (table_ref->key_err) { table->status=STATUS_NOT_FOUND; - return -1; + DBUG_RETURN(-1); } /* Moving away from the current record. Unlock the row in the handler if it did not match the partial WHERE. */ - if (tab->ref.has_record && tab->ref.use_count == 0) + if (table_ref->has_record ) + if (table_ref->use_count == 0) { tab->read_record.file->unlock_row(); - tab->ref.has_record= FALSE; + table_ref->has_record= FALSE; } error=table->file->ha_index_read_map(table->record[0], table_ref->key_buff, make_prev_keypart_map(table_ref->key_parts), HA_READ_KEY_EXACT); if (error && error != HA_ERR_KEY_NOT_FOUND && error != HA_ERR_END_OF_FILE) - return report_error(table, error); + DBUG_RETURN(report_error(table, error)); if (! error) { - tab->ref.has_record= TRUE; - tab->ref.use_count= 1; + table_ref->has_record= TRUE; + table_ref->use_count= 1; } } else if (table->status == 0) { - DBUG_ASSERT(tab->ref.has_record); - tab->ref.use_count++; + DBUG_ASSERT(table_ref->has_record); + table_ref->use_count++; } table->null_row=0; - return table->status ? -1 : 0; + DBUG_RETURN(table->status ? -1 : 0); } === modified file 'sql/sql_select.h' --- sql/sql_select.h 2010-06-14 11:17:54 +0000 +++ sql/sql_select.h 2010-06-23 18:44:59 +0000 @@ -1711,6 +1711,7 @@ ((group || tmp_table_param.sum_func_count) && !group_list)) ? NULL : join_tab+const_tables; } + bool setup_subquery_caches(THD *thd); private: /** TRUE if the query contains an aggregate function but has no GROUP === modified file 'sql/table.cc' --- sql/table.cc 2010-03-20 12:01:47 +0000 +++ sql/table.cc 2010-06-23 22:06:13 +0000 @@ -20,6 +20,8 @@ #include "sql_trigger.h" #include #include "my_md5.h" +#include "my_bit.h" +#include "sql_select.h" /* INFORMATION_SCHEMA name */ LEX_STRING INFORMATION_SCHEMA_NAME= {C_STRING_WITH_LEN("information_schema")}; @@ -5096,6 +5098,210 @@ file->column_bitmaps_signal(); } + +/** + Allocates space for keys + + @param key_count number of keys to allocate. + + @details + Allocates space enough to fit 'key_count' keys for this table. + + @return FALSE space was successfully allocated. + @return TRUE an error occur. +*/ + +bool TABLE::alloc_keys(uint key_count) +{ + DBUG_ASSERT(!s->keys); + key_info= s->key_info= (KEY*) alloc_root(&mem_root, sizeof(KEY)*key_count); + max_keys= key_count; + return !(key_info); +} + + +/** + Adds one key to a temporary table. + + @param key key number. + @param key_parts number of fields in the key + @param next_field_no function which returns field numbers which + should be included in the key + @param arg above function argement + + @return <0 an error occur. + @return >=0 number of newly added key. +*/ + +bool TABLE::add_tmp_key(uint key, uint key_parts, + uint (*next_field_no) (uchar *), uchar *arg) +{ + DBUG_ASSERT(key < max_keys); + + char buf[NAME_CHAR_LEN]; + KEY* keyinfo; + Field **reg_field; + uint i; + bool key_start= TRUE; + KEY_PART_INFO* key_part_info= + (KEY_PART_INFO*) alloc_root(&mem_root, sizeof(KEY_PART_INFO)*key_parts); + if (!key_part_info) + return TRUE; + keyinfo= key_info + key; + keyinfo->key_part= key_part_info; + keyinfo->usable_key_parts= keyinfo->key_parts = key_parts; + keyinfo->key_length=0; + keyinfo->algorithm= HA_KEY_ALG_UNDEF; + keyinfo->flags= HA_GENERATED_KEY; + sprintf(buf, "key%i", key); + if (!(keyinfo->name= strdup_root(&mem_root, buf))) + return TRUE; + keyinfo->rec_per_key= (ulong*) alloc_root(&mem_root, + sizeof(ulong)*key_parts); + if (!keyinfo->rec_per_key) + return TRUE; + bzero(keyinfo->rec_per_key, sizeof(ulong)*key_parts); + for (i= 0; i < key_parts; i++) + { + reg_field= field + next_field_no(arg); + if (key_start) + (*reg_field)->key_start.set_bit(key); + key_start= FALSE; + (*reg_field)->part_of_key.set_bit(key); + (*reg_field)->flags|= PART_KEY_FLAG; + key_part_info->null_bit= (*reg_field)->null_bit; + key_part_info->null_offset= (uint) ((*reg_field)->null_ptr - + (uchar*) record[0]); + key_part_info->field= *reg_field; + key_part_info->offset= (*reg_field)->offset(record[0]); + key_part_info->length= (uint16) (*reg_field)->pack_length(); + keyinfo->key_length+= key_part_info->length; + key_part_info->key_part_flag= 0; + /* TODO: + The below method of computing the key format length of the + key part is a copy/paste from opt_range.cc, and table.cc. + This should be factored out, e.g. as a method of Field. + In addition it is not clear if any of the Field::*_length + methods is supposed to compute the same length. If so, it + might be reused. + */ + key_part_info->store_length= key_part_info->length; + + if ((*reg_field)->real_maybe_null()) + { + key_part_info->store_length+= HA_KEY_NULL_LENGTH; + keyinfo->key_length+= HA_KEY_NULL_LENGTH; + } + if ((*reg_field)->type() == MYSQL_TYPE_BLOB || + (*reg_field)->real_type() == MYSQL_TYPE_VARCHAR) + { + key_part_info->store_length+= HA_KEY_BLOB_LENGTH; + keyinfo->key_length+= HA_KEY_BLOB_LENGTH; // ??? + } + + key_part_info->type= (uint8) (*reg_field)->key_type(); + key_part_info->key_type = + ((ha_base_keytype) key_part_info->type == HA_KEYTYPE_TEXT || + (ha_base_keytype) key_part_info->type == HA_KEYTYPE_VARTEXT1 || + (ha_base_keytype) key_part_info->type == HA_KEYTYPE_VARTEXT2) ? + 0 : FIELDFLAG_BINARY; + key_part_info++; + } + set_if_bigger(s->max_key_length, keyinfo->key_length); + s->keys++; + return FALSE; +} + + +/** + Creates structures which we need for index look up + + @param thd Thread handler + @param li list of items representing the key + @param ref reference to write created TABLE_REF + + @details Creates TABLE_REF object for this (temporary) table and fill it + according with 'li' list to allow to search in the temporary table index + using items from the list 'li'. If everything OK created object will be + assigned to 'ref' and FALSE returned. + + @note should be carefully merged with code ported from 6.0, because mostly + taken from there + + @retval FALSE OK + @retval TRUE Error +*/ + +bool TABLE::createtmp_table_search_structures(THD *thd, + List_iterator_fast &li, + st_table_ref **ref) +{ + /* + Create/initialize everything we will need to index lookups into the + temptable. + */ + TABLE_REF *tab_ref; + KEY *tmp_key; /* The only index on the temporary table. */ + Item *item; + uint tmp_key_parts; + uint i; + + DBUG_ENTER("createtmp_table_search_structures"); + + tmp_key= this->key_info; + tmp_key_parts= tmp_key->key_parts; + + if (!(tab_ref= (TABLE_REF*) thd->alloc(sizeof(TABLE_REF)))) + DBUG_RETURN(TRUE); + + tab_ref->key= 0; /* The only temp table index. */ + tab_ref->key_length= tmp_key->key_length; + if (!(tab_ref->key_buff= + (uchar*) thd->calloc(ALIGN_SIZE(tmp_key->key_length) * 2)) || + !(tab_ref->key_copy= + (store_key**) thd->alloc((sizeof(store_key*) * + (tmp_key_parts + 1)))) || + !(tab_ref->items= + (Item**) thd->alloc(sizeof(Item*) * tmp_key_parts))) + DBUG_RETURN(TRUE); /* purecov: inspected */ + + tab_ref->key_buff2=tab_ref->key_buff+ALIGN_SIZE(tmp_key->key_length); + tab_ref->key_err=1; + tab_ref->null_rejecting= 1; + tab_ref->disable_cache= FALSE; + tab_ref->has_record= 0; + tab_ref->use_count= 0; + + KEY_PART_INFO *cur_key_part= tmp_key->key_part; + store_key **ref_key= tab_ref->key_copy; + uchar *cur_ref_buff= tab_ref->key_buff; + + for (i= 0; i < tmp_key_parts; i++, cur_key_part++, ref_key++) + { + item= li++; + DBUG_ASSERT(item); + tab_ref->items[i]= item; + int null_count= test(cur_key_part->field->real_maybe_null()); + *ref_key= new store_key_item(thd, cur_key_part->field, + /* TODO: + the NULL byte is taken into account in + cur_key_part->store_length, so instead of + cur_ref_buff + test(maybe_null), we could + use that information instead. + */ + cur_ref_buff + null_count, + null_count ? tab_ref->key_buff : 0, + cur_key_part->length, tab_ref->items[i]); + cur_ref_buff+= cur_key_part->store_length; + } + *ref_key= NULL; /* End marker. */ + tab_ref->key_parts= tmp_key_parts; + *ref= tab_ref; + + DBUG_RETURN(FALSE); +} + + /** @brief Check if this is part of a MERGE table with attached children. === modified file 'sql/table.h' --- sql/table.h 2010-03-20 12:01:47 +0000 +++ sql/table.h 2010-06-23 19:51:11 +0000 @@ -25,6 +25,7 @@ class partition_info; class COND_EQUAL; class Security_context; +struct st_table_ref; /*************************************************************************/ @@ -781,6 +782,7 @@ uint temp_pool_slot; /* Used by intern temp tables */ uint status; /* What's in record[0] */ uint db_stat; /* mode of file as in handler.h */ + uint max_keys; /* Size of allocated key_info array. */ /* number of select if it is derived table */ uint derived_select_number; int current_lock; /* Type of lock on table */ @@ -913,6 +915,12 @@ */ inline bool needs_reopen_or_name_lock() { return s->version != refresh_version; } + bool alloc_keys(uint key_count); + bool add_tmp_key(uint key, uint key_parts, + uint (*next_field_no) (uchar *), uchar *arg); + bool createtmp_table_search_structures(THD *thd, + List_iterator_fast &li, + st_table_ref **ref); bool is_children_attached(void); }; === modified file 'storage/maria/ha_maria.cc' --- storage/maria/ha_maria.cc 2010-03-20 12:01:47 +0000 +++ storage/maria/ha_maria.cc 2010-06-23 18:44:59 +0000 @@ -995,6 +995,8 @@ { MARIA_HA *tmp= file; file= 0; + if (!tmp) + return 0; return maria_close(tmp); }