diff options
43 files changed, 4255 insertions, 164 deletions
diff --git a/libmysqld/Makefile.am b/libmysqld/Makefile.am index 10fe8071f3f..3316c91ba80 100644 --- a/libmysqld/Makefile.am +++ b/libmysqld/Makefile.am @@ -80,7 +80,8 @@ sqlsources = derror.cc field.cc field_conv.cc strfunc.cc filesort.cc \ sql_tablespace.cc create_options.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) diff --git a/mysql-test/r/index_merge_myisam.result b/mysql-test/r/index_merge_myisam.result index e9a82b14556..2c4408f3ff3 100644 --- a/mysql-test/r/index_merge_myisam.result +++ b/mysql-test/r/index_merge_myisam.result @@ -1419,19 +1419,19 @@ drop table t1; # 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=default; 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 @@ id select_type table type possible_keys key key_len ref rows Extra 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; diff --git a/mysql-test/r/myisam_mrr.result b/mysql-test/r/myisam_mrr.result index 27ae694570f..70bc0cd36be 100644 --- a/mysql-test/r/myisam_mrr.result +++ b/mysql-test/r/myisam_mrr.result @@ -394,7 +394,7 @@ drop table t0, t1; # - 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)); diff --git a/mysql-test/r/subquery_cache.result b/mysql-test/r/subquery_cache.result new file mode 100644 index 00000000000..8af0dc88567 --- /dev/null +++ b/mysql-test/r/subquery_cache.result @@ -0,0 +1,1840 @@ +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); +create table t2 (c int, d int); +insert into t2 values (2,3),(3,4),(5,6),(4,1); +* +* Test subquery as top item in different clauses +* +#single value subquery test (SELECT list) +flush status; +select a, (select d from t2 where b=c) from t1; +a (select d from t2 where b=c) +1 3 +3 1 +1 3 +3 1 +3 1 +4 6 +4 6 +5 NULL +5 NULL +4 6 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 6 +Subquery_cache_miss 4 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 7 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 31 +set optimizer_switch='subquery_cache=off'; +flush status; +select a, (select d from t2 where b=c) from t1; +a (select d from t2 where b=c) +1 3 +3 1 +1 3 +3 1 +3 1 +4 6 +4 6 +5 NULL +5 NULL +4 6 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 0 +Subquery_cache_miss 0 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 61 +set optimizer_switch='subquery_cache=on'; +#single value subquery test (where) +flush status; +select a from t1 where (select d from t2 where b=c); +a +1 +3 +1 +3 +3 +4 +4 +4 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 6 +Subquery_cache_miss 4 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 7 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 31 +set optimizer_switch='subquery_cache=off'; +flush status; +select a from t1 where (select d from t2 where b=c); +a +1 +3 +1 +3 +3 +4 +4 +4 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 0 +Subquery_cache_miss 0 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 61 +set optimizer_switch='subquery_cache=on'; +#single value subquery test (having) +flush status; +select a from t1 where a > 0 having (select d from t2 where b=c); +a +1 +3 +1 +3 +3 +4 +4 +4 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 6 +Subquery_cache_miss 4 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 7 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 31 +set optimizer_switch='subquery_cache=off'; +flush status; +select a from t1 where a > 0 having (select d from t2 where b=c); +a +1 +3 +1 +3 +3 +4 +4 +4 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 0 +Subquery_cache_miss 0 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 61 +set optimizer_switch='subquery_cache=on'; +#single value subquery test (OUTER JOIN ON) +flush status; +select ta.a, tb.a from t1 ta join t1 tb on (select d from t2 where tb.b=c); +a a +1 1 +3 1 +1 1 +3 1 +3 1 +4 1 +4 1 +5 1 +5 1 +4 1 +1 3 +3 3 +1 3 +3 3 +3 3 +4 3 +4 3 +5 3 +5 3 +4 3 +1 1 +3 1 +1 1 +3 1 +3 1 +4 1 +4 1 +5 1 +5 1 +4 1 +1 3 +3 3 +1 3 +3 3 +3 3 +4 3 +4 3 +5 3 +5 3 +4 3 +1 3 +3 3 +1 3 +3 3 +3 3 +4 3 +4 3 +5 3 +5 3 +4 3 +1 4 +3 4 +1 4 +3 4 +3 4 +4 4 +4 4 +5 4 +5 4 +4 4 +1 4 +3 4 +1 4 +3 4 +3 4 +4 4 +4 4 +5 4 +5 4 +4 4 +1 4 +3 4 +1 4 +3 4 +3 4 +4 4 +4 4 +5 4 +5 4 +4 4 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 6 +Subquery_cache_miss 4 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 7 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 442 +set optimizer_switch='subquery_cache=off'; +flush status; +select ta.a, tb.a from t1 ta join t1 tb on (select d from t2 where tb.b=c); +a a +1 1 +3 1 +1 1 +3 1 +3 1 +4 1 +4 1 +5 1 +5 1 +4 1 +1 3 +3 3 +1 3 +3 3 +3 3 +4 3 +4 3 +5 3 +5 3 +4 3 +1 1 +3 1 +1 1 +3 1 +3 1 +4 1 +4 1 +5 1 +5 1 +4 1 +1 3 +3 3 +1 3 +3 3 +3 3 +4 3 +4 3 +5 3 +5 3 +4 3 +1 3 +3 3 +1 3 +3 3 +3 3 +4 3 +4 3 +5 3 +5 3 +4 3 +1 4 +3 4 +1 4 +3 4 +3 4 +4 4 +4 4 +5 4 +5 4 +4 4 +1 4 +3 4 +1 4 +3 4 +3 4 +4 4 +4 4 +5 4 +5 4 +4 4 +1 4 +3 4 +1 4 +3 4 +3 4 +4 4 +4 4 +5 4 +5 4 +4 4 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 0 +Subquery_cache_miss 0 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 472 +set optimizer_switch='subquery_cache=on'; +#single value subquery test (GROUP BY) +flush status; +select max(a) from t1 GROUP BY (select d from t2 where b=c); +max(a) +5 +3 +1 +4 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 10 +Subquery_cache_miss 4 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 17 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 4 +Handler_read_rnd_next 36 +set optimizer_switch='subquery_cache=off'; +flush status; +select max(a) from t1 GROUP BY (select d from t2 where b=c); +max(a) +5 +3 +1 +4 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 0 +Subquery_cache_miss 0 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 10 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 4 +Handler_read_rnd_next 86 +set optimizer_switch='subquery_cache=on'; +#single value subquery test (distinct GROUP BY) +flush status; +select distinct max(a) from t1 GROUP BY (select d from t2 where b=c); +max(a) +5 +3 +1 +4 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 10 +Subquery_cache_miss 4 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 17 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 4 +Handler_read_rnd_next 41 +set optimizer_switch='subquery_cache=off'; +flush status; +select distinct max(a) from t1 GROUP BY (select d from t2 where b=c); +max(a) +5 +3 +1 +4 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 0 +Subquery_cache_miss 0 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 10 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 4 +Handler_read_rnd_next 91 +set optimizer_switch='subquery_cache=on'; +#single value subquery test (ORDER BY) +flush status; +select a from t1 ORDER BY (select d from t2 where b=c); +a +5 +5 +3 +3 +3 +1 +1 +4 +4 +4 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 6 +Subquery_cache_miss 4 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 7 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 31 +set optimizer_switch='subquery_cache=off'; +flush status; +select a from t1 ORDER BY (select d from t2 where b=c); +a +5 +5 +3 +3 +3 +1 +1 +4 +4 +4 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 0 +Subquery_cache_miss 0 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 61 +set optimizer_switch='subquery_cache=on'; +#single value subquery test (distinct ORDER BY) +flush status; +select distinct a from t1 ORDER BY (select d from t2 where b=c); +a +5 +3 +1 +4 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 6 +Subquery_cache_miss 4 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 7 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 4 +Handler_read_rnd_next 37 +set optimizer_switch='subquery_cache=off'; +flush status; +select distinct a from t1 ORDER BY (select d from t2 where b=c); +a +5 +3 +1 +4 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 0 +Subquery_cache_miss 0 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 4 +Handler_read_rnd_next 67 +set optimizer_switch='subquery_cache=on'; +#single value subquery test (LEFT JOIN ON) +flush status; +select ta.a, tb.a from t1 ta left join t1 tb on (select d from t2 where tb.b=c); +a a +1 1 +1 3 +1 1 +1 3 +1 3 +1 4 +1 4 +1 4 +3 1 +3 3 +3 1 +3 3 +3 3 +3 4 +3 4 +3 4 +1 1 +1 3 +1 1 +1 3 +1 3 +1 4 +1 4 +1 4 +3 1 +3 3 +3 1 +3 3 +3 3 +3 4 +3 4 +3 4 +3 1 +3 3 +3 1 +3 3 +3 3 +3 4 +3 4 +3 4 +4 1 +4 3 +4 1 +4 3 +4 3 +4 4 +4 4 +4 4 +4 1 +4 3 +4 1 +4 3 +4 3 +4 4 +4 4 +4 4 +5 1 +5 3 +5 1 +5 3 +5 3 +5 4 +5 4 +5 4 +5 1 +5 3 +5 1 +5 3 +5 3 +5 4 +5 4 +5 4 +4 1 +4 3 +4 1 +4 3 +4 3 +4 4 +4 4 +4 4 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 106 +Subquery_cache_miss 4 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 70 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 141 +set optimizer_switch='subquery_cache=off'; +flush status; +select ta.a, tb.a from t1 ta left join t1 tb on (select d from t2 where tb.b=c); +a a +1 1 +1 3 +1 1 +1 3 +1 3 +1 4 +1 4 +1 4 +3 1 +3 3 +3 1 +3 3 +3 3 +3 4 +3 4 +3 4 +1 1 +1 3 +1 1 +1 3 +1 3 +1 4 +1 4 +1 4 +3 1 +3 3 +3 1 +3 3 +3 3 +3 4 +3 4 +3 4 +3 1 +3 3 +3 1 +3 3 +3 3 +3 4 +3 4 +3 4 +4 1 +4 3 +4 1 +4 3 +4 3 +4 4 +4 4 +4 4 +4 1 +4 3 +4 1 +4 3 +4 3 +4 4 +4 4 +4 4 +5 1 +5 3 +5 1 +5 3 +5 3 +5 4 +5 4 +5 4 +5 1 +5 3 +5 1 +5 3 +5 3 +5 4 +5 4 +5 4 +4 1 +4 3 +4 1 +4 3 +4 3 +4 4 +4 4 +4 4 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 0 +Subquery_cache_miss 0 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 671 +set optimizer_switch='subquery_cache=on'; +#single value subquery test (PS) +prepare stmt1 from 'select a, (select d from t2 where b=c) + 1 from t1'; +execute stmt1; +a (select d from t2 where b=c) + 1 +1 4 +3 2 +1 4 +3 2 +3 2 +4 7 +4 7 +5 NULL +5 NULL +4 7 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 6 +Subquery_cache_miss 4 +execute stmt1; +a (select d from t2 where b=c) + 1 +1 4 +3 2 +1 4 +3 2 +3 2 +4 7 +4 7 +5 NULL +5 NULL +4 7 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 12 +Subquery_cache_miss 8 +deallocate prepare stmt1; +#single value subquery test (SP) +CREATE PROCEDURE p1() select a, (select d from t2 where b=c) + 1 from t1; +call p1; +a (select d from t2 where b=c) + 1 +1 4 +3 2 +1 4 +3 2 +3 2 +4 7 +4 7 +5 NULL +5 NULL +4 7 +call p1; +a (select d from t2 where b=c) + 1 +1 4 +3 2 +1 4 +3 2 +3 2 +4 7 +4 7 +5 NULL +5 NULL +4 7 +drop procedure p1; +#IN subquery test +flush status; +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 0 +Subquery_cache_miss 0 +select a, b , b in (select d from t2) as SUBS from t1; +a b SUBS +1 2 0 +3 4 1 +1 2 0 +3 4 1 +3 4 1 +4 5 0 +4 5 0 +5 6 1 +5 6 1 +4 5 0 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 6 +Subquery_cache_miss 4 +insert into t1 values (7,8),(9,NULL); +select a, b , b in (select d from t2) as SUBS from t1; +a b SUBS +1 2 0 +3 4 1 +1 2 0 +3 4 1 +3 4 1 +4 5 0 +4 5 0 +5 6 1 +5 6 1 +4 5 0 +7 8 0 +9 NULL NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 12 +Subquery_cache_miss 10 +insert into t2 values (8,NULL); +select a, b , b in (select d from t2) as SUBS from t1; +a b SUBS +1 2 NULL +3 4 1 +1 2 NULL +3 4 1 +3 4 1 +4 5 NULL +4 5 NULL +5 6 1 +5 6 1 +4 5 NULL +7 8 NULL +9 NULL NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 18 +Subquery_cache_miss 16 +# multicolumn NOT IN with NULLs +flush status; +set optimizer_switch='subquery_cache=off'; +select a, b, (b, a) not in (select d, c from t2) as SUBS from t1; +a b SUBS +1 2 1 +3 4 0 +1 2 1 +3 4 0 +3 4 0 +4 5 1 +4 5 1 +5 6 0 +5 6 0 +4 5 1 +7 8 1 +9 NULL 1 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 0 +Subquery_cache_miss 0 +set optimizer_switch='subquery_cache=on'; +select a, b, (b, a) not in (select d, c from t2) as SUBS from t1; +a b SUBS +1 2 1 +3 4 0 +1 2 1 +3 4 0 +3 4 0 +4 5 1 +4 5 1 +5 6 0 +5 6 0 +4 5 1 +7 8 1 +9 NULL 1 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 6 +Subquery_cache_miss 6 +# multicolumn NOT IN with NULLs (other order) +flush status; +set optimizer_switch='subquery_cache=off'; +select a, b, (a, b) not in (select d, c from t2) as SUBS from t1; +a b SUBS +1 2 1 +3 4 1 +1 2 1 +3 4 1 +3 4 1 +4 5 1 +4 5 1 +5 6 1 +5 6 1 +4 5 1 +7 8 NULL +9 NULL NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 0 +Subquery_cache_miss 0 +set optimizer_switch='subquery_cache=on'; +select a, b, (a, b) not in (select d, c from t2) as SUBS from t1; +a b SUBS +1 2 1 +3 4 1 +1 2 1 +3 4 1 +3 4 1 +4 5 1 +4 5 1 +5 6 1 +5 6 1 +4 5 1 +7 8 NULL +9 NULL NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 6 +Subquery_cache_miss 6 +# multicolumn IN with NULLs +flush status; +set optimizer_switch='subquery_cache=off'; +select a, b, (b, a) in (select d, c from t2) as SUBS from t1; +a b SUBS +1 2 0 +3 4 1 +1 2 0 +3 4 1 +3 4 1 +4 5 0 +4 5 0 +5 6 1 +5 6 1 +4 5 0 +7 8 0 +9 NULL 0 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 0 +Subquery_cache_miss 0 +set optimizer_switch='subquery_cache=on'; +select a, b, (b, a) in (select d, c from t2) as SUBS from t1; +a b SUBS +1 2 0 +3 4 1 +1 2 0 +3 4 1 +3 4 1 +4 5 0 +4 5 0 +5 6 1 +5 6 1 +4 5 0 +7 8 0 +9 NULL 0 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 6 +Subquery_cache_miss 6 +# multicolumn IN with NULLs (other order) +flush status; +set optimizer_switch='subquery_cache=off'; +select a, b, (a, b) in (select d, c from t2) as SUBS from t1; +a b SUBS +1 2 0 +3 4 0 +1 2 0 +3 4 0 +3 4 0 +4 5 0 +4 5 0 +5 6 0 +5 6 0 +4 5 0 +7 8 NULL +9 NULL NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 0 +Subquery_cache_miss 0 +set optimizer_switch='subquery_cache=on'; +select a, b, (a, b) in (select d, c from t2) as SUBS from t1; +a b SUBS +1 2 0 +3 4 0 +1 2 0 +3 4 0 +3 4 0 +4 5 0 +4 5 0 +5 6 0 +5 6 0 +4 5 0 +7 8 NULL +9 NULL NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 6 +Subquery_cache_miss 6 +#IN subquery test (PS) +delete from t1 where a > 6; +delete from t2 where c > 6; +prepare stmt1 from 'select a, b , b in (select d from t2) as SUBS from t1'; +execute stmt1; +a b SUBS +1 2 0 +3 4 1 +1 2 0 +3 4 1 +3 4 1 +4 5 0 +4 5 0 +5 6 1 +5 6 1 +4 5 0 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 12 +Subquery_cache_miss 10 +execute stmt1; +a b SUBS +1 2 0 +3 4 1 +1 2 0 +3 4 1 +3 4 1 +4 5 0 +4 5 0 +5 6 1 +5 6 1 +4 5 0 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 18 +Subquery_cache_miss 14 +insert into t1 values (7,8),(9,NULL); +execute stmt1; +a b SUBS +1 2 0 +3 4 1 +1 2 0 +3 4 1 +3 4 1 +4 5 0 +4 5 0 +5 6 1 +5 6 1 +4 5 0 +9 NULL NULL +7 8 0 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 24 +Subquery_cache_miss 20 +execute stmt1; +a b SUBS +1 2 0 +3 4 1 +1 2 0 +3 4 1 +3 4 1 +4 5 0 +4 5 0 +5 6 1 +5 6 1 +4 5 0 +9 NULL NULL +7 8 0 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 30 +Subquery_cache_miss 26 +insert into t2 values (8,NULL); +execute stmt1; +a b SUBS +1 2 NULL +3 4 1 +1 2 NULL +3 4 1 +3 4 1 +4 5 NULL +4 5 NULL +5 6 1 +5 6 1 +4 5 NULL +9 NULL NULL +7 8 NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 36 +Subquery_cache_miss 32 +execute stmt1; +a b SUBS +1 2 NULL +3 4 1 +1 2 NULL +3 4 1 +3 4 1 +4 5 NULL +4 5 NULL +5 6 1 +5 6 1 +4 5 NULL +9 NULL NULL +7 8 NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 42 +Subquery_cache_miss 38 +deallocate prepare stmt1; +#IN subquery test (SP) +delete from t1 where a > 6; +delete from t2 where c > 6; +CREATE PROCEDURE p1() select a, b , b in (select d from t2) as SUBS from t1; +call p1(); +a b SUBS +1 2 0 +3 4 1 +1 2 0 +3 4 1 +3 4 1 +4 5 0 +4 5 0 +5 6 1 +5 6 1 +4 5 0 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 48 +Subquery_cache_miss 42 +call p1(); +a b SUBS +1 2 0 +3 4 1 +1 2 0 +3 4 1 +3 4 1 +4 5 0 +4 5 0 +5 6 1 +5 6 1 +4 5 0 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 54 +Subquery_cache_miss 46 +insert into t1 values (7,8),(9,NULL); +call p1(); +a b SUBS +1 2 0 +3 4 1 +1 2 0 +3 4 1 +3 4 1 +4 5 0 +4 5 0 +5 6 1 +5 6 1 +4 5 0 +9 NULL NULL +7 8 0 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 60 +Subquery_cache_miss 52 +call p1(); +a b SUBS +1 2 0 +3 4 1 +1 2 0 +3 4 1 +3 4 1 +4 5 0 +4 5 0 +5 6 1 +5 6 1 +4 5 0 +9 NULL NULL +7 8 0 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 66 +Subquery_cache_miss 58 +insert into t2 values (8,NULL); +call p1(); +a b SUBS +1 2 NULL +3 4 1 +1 2 NULL +3 4 1 +3 4 1 +4 5 NULL +4 5 NULL +5 6 1 +5 6 1 +4 5 NULL +9 NULL NULL +7 8 NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 72 +Subquery_cache_miss 64 +call p1(); +a b SUBS +1 2 NULL +3 4 1 +1 2 NULL +3 4 1 +3 4 1 +4 5 NULL +4 5 NULL +5 6 1 +5 6 1 +4 5 NULL +9 NULL NULL +7 8 NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 78 +Subquery_cache_miss 70 +drop procedure p1; +# test of simple exists +select a, b , exists (select * from t2 where b=d) as SUBS from t1; +a b SUBS +1 2 0 +3 4 1 +1 2 0 +3 4 1 +3 4 1 +4 5 0 +4 5 0 +5 6 1 +5 6 1 +4 5 0 +9 NULL 0 +7 8 0 +# test of prepared statement exists +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 84 +Subquery_cache_miss 76 +prepare stmt1 from 'select a, b , exists (select * from t2 where b=d) as SUBS from t1'; +execute stmt1; +a b SUBS +1 2 0 +3 4 1 +1 2 0 +3 4 1 +3 4 1 +4 5 0 +4 5 0 +5 6 1 +5 6 1 +4 5 0 +9 NULL 0 +7 8 0 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 90 +Subquery_cache_miss 82 +execute stmt1; +a b SUBS +1 2 0 +3 4 1 +1 2 0 +3 4 1 +3 4 1 +4 5 0 +4 5 0 +5 6 1 +5 6 1 +4 5 0 +9 NULL 0 +7 8 0 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 96 +Subquery_cache_miss 88 +deallocate prepare stmt1; +# test of stored procedure exists +CREATE PROCEDURE p1() select a, b , exists (select * from t2 where b=d) as SUBS from t1; +call p1; +a b SUBS +1 2 0 +3 4 1 +1 2 0 +3 4 1 +3 4 1 +4 5 0 +4 5 0 +5 6 1 +5 6 1 +4 5 0 +9 NULL 0 +7 8 0 +call p1; +a b SUBS +1 2 0 +3 4 1 +1 2 0 +3 4 1 +3 4 1 +4 5 0 +4 5 0 +5 6 1 +5 6 1 +4 5 0 +9 NULL 0 +7 8 0 +drop procedure p1; +#several subqueries +set optimizer_switch='subquery_cache=off'; +flush status; +select a, b , exists (select * from t2 where b=d) as SUBSE, b in (select d from t2) as SUBSI, (select d from t2 where b=c) SUBSR from t1; +a b SUBSE SUBSI SUBSR +1 2 0 NULL 3 +3 4 1 1 1 +1 2 0 NULL 3 +3 4 1 1 1 +3 4 1 1 1 +4 5 0 NULL 6 +4 5 0 NULL 6 +5 6 1 1 NULL +5 6 1 1 NULL +4 5 0 NULL 6 +9 NULL 0 NULL NULL +7 8 0 NULL NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 0 +Subquery_cache_miss 0 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 11 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 145 +set optimizer_switch='subquery_cache=on'; +flush status; +select a, b , exists (select * from t2 where b=d) as SUBSE, b in (select d from t2) as SUBSI, (select d from t2 where b=c) SUBSR from t1; +a b SUBSE SUBSI SUBSR +1 2 0 NULL 3 +3 4 1 1 1 +1 2 0 NULL 3 +3 4 1 1 1 +3 4 1 1 1 +4 5 0 NULL 6 +4 5 0 NULL 6 +5 6 1 1 NULL +5 6 1 1 NULL +4 5 0 NULL 6 +9 NULL 0 NULL NULL +7 8 0 NULL NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 18 +Subquery_cache_miss 18 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 32 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 84 +#several subqueries (several levels) +set optimizer_switch='subquery_cache=off'; +flush status; +set optimizer_switch='subquery_cache=off'; +flush status; +select a, b, (select exists (select * from t2 where b=d) from t2 where b=c) as SUNS1 from t1; +a b SUNS1 +1 2 0 +3 4 1 +1 2 0 +3 4 1 +3 4 1 +4 5 0 +4 5 0 +5 6 NULL +5 6 NULL +4 5 0 +9 NULL NULL +7 8 0 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 0 +Subquery_cache_miss 0 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 127 +set optimizer_switch='subquery_cache=on'; +flush status; +select a, b, (select exists (select * from t2 where b=d) from t2 where b=c) as SUNS1 from t1; +a b SUNS1 +1 2 0 +3 4 1 +1 2 0 +3 4 1 +3 4 1 +4 5 0 +4 5 0 +5 6 NULL +5 6 NULL +4 5 0 +9 NULL NULL +7 8 0 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 6 +Subquery_cache_miss 10 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 13 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 69 +#clean up +drop table t1,t2; +test different types +#int +CREATE TABLE t1 ( a int, b int); +INSERT INTO t1 VALUES(1,1),(2,2),(3,3); +SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = 2); +a +1 +3 +DROP TABLE t1; +#char +CREATE TABLE t1 ( a char(1), b char (1)); +INSERT INTO t1 VALUES('1','1'),('2','2'),('3','3'); +SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '2'); +a +1 +3 +DROP TABLE t1; +#decimal +CREATE TABLE t1 ( a decimal(3,1), b decimal(3,1)); +INSERT INTO t1 VALUES(1,1),(2,2),(3,3); +SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = 2); +a +1.0 +3.0 +DROP TABLE t1; +#date +CREATE TABLE t1 ( a date, b date); +INSERT INTO t1 VALUES('1000-01-01','1000-01-01'),('2000-02-01','2000-02-01'),('3000-03-03','3000-03-03'); +SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '2000-02-01'); +a +1000-01-01 +3000-03-03 +DROP TABLE t1; +#datetime +CREATE TABLE t1 ( a datetime, b datetime); +INSERT INTO t1 VALUES('1000-01-01 01:01:01','1000-01-01 01:01:01'),('2000-02-02 02:02:02','2000-02-02 02:02:02'),('3000-03-03 03:03:03','3000-03-03 03:03:03'); +SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '2000-02-02 02:02:02'); +a +1000-01-01 01:01:01 +3000-03-03 03:03:03 +DROP TABLE t1; +#time +CREATE TABLE t1 ( a time, b time); +INSERT INTO t1 VALUES('01:01:01','01:01:01'),('02:02:02','02:02:02'),('03:03:03','03:03:03'); +SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '02:02:02'); +a +01:01:01 +03:03:03 +DROP TABLE t1; +#timestamp +CREATE TABLE t1 ( a timestamp, b timestamp); +INSERT INTO t1 VALUES('2000-02-02 01:01:01','2000-02-02 01:01:01'),('2000-02-02 02:02:02','2000-02-02 02:02:02'),('2000-02-02 03:03:03','2000-02-02 03:03:03'); +SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '2000-02-02 02:02:02'); +a +2000-02-02 01:01:01 +2000-02-02 03:03:03 +DROP TABLE t1; +#bit +CREATE TABLE t1 ( a bit(20), b bit(20)); +INSERT INTO t1 VALUES(1,1),(2,2),(3,3); +SELECT a+0 FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = 2); +a+0 +1 +3 +DROP TABLE t1; +#enum +CREATE TABLE t1 ( a enum('1','2','3'), b enum('1','2','3')); +INSERT INTO t1 VALUES('1','1'),('2','2'),('3','3'); +SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '2'); +a +1 +3 +DROP TABLE t1; +#set +CREATE TABLE t1 ( a set('1','2','3'), b set('1','2','3')); +INSERT INTO t1 VALUES('1','1'),('2','2'),('3','3'); +SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '2'); +a +1 +3 +DROP TABLE t1; +#blob +CREATE TABLE t1 ( a blob, b blob); +INSERT INTO t1 VALUES('1','1'),('2','2'),('3','3'); +SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '2'); +a +1 +3 +DROP TABLE t1; +#geometry +CREATE TABLE t1 ( a geometry, b geometry); +INSERT INTO t1 VALUES(POINT(1,1),POINT(1,1)),(POINT(2,2),POINT(2,2)),(POINT(3,3),POINT(3,3)); +SELECT astext(a) FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = POINT(2,2)); +astext(a) +POINT(1 1) +POINT(3 3) +DROP TABLE t1; +#uncacheable queries test (random and side effect) +flush status; +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (2), (4), (1), (3); +select a, a in (select a from t1) from t1 as ext; +a a in (select a from t1) +2 1 +4 1 +1 1 +3 1 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 0 +Subquery_cache_miss 4 +select a, a in (select a from t1 where -1 < rand()) from t1 as ext; +a a in (select a from t1 where -1 < rand()) +2 1 +4 1 +1 1 +3 1 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 0 +Subquery_cache_miss 4 +select a, a in (select a from t1 where -1 < benchmark(a,100)) from t1 as ext; +a a in (select a from t1 where -1 < benchmark(a,100)) +2 1 +4 1 +1 1 +3 1 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 0 +Subquery_cache_miss 4 +drop table t1; +#test of sql_big_tables switch and outer table reference in subquery with grouping +set option sql_big_tables=1; +CREATE TABLE t1 (a INT PRIMARY KEY, b INT); +INSERT INTO t1 VALUES (1,1),(2,1),(3,2),(4,2),(5,3),(6,3); +SELECT (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1) FROM t1 AS t1_outer; +(SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1) +1 +2 +3 +4 +5 +6 +drop table t1; +set option sql_big_tables=0; +#test of function reference to outer query +set local group_concat_max_len=400; +create table t2 (a int, b int); +insert into t2 values (1,1), (2,2); +select b x, (select group_concat(x) from t2) from t2; +x (select group_concat(x) from t2) +1 1,1 +2 2,2 +drop table t2; +set local group_concat_max_len=default; +#aggregate functions +CREATE TABLE t1 (a int, b INT); +CREATE TABLE t2 (c int, d INT); +insert into t1 values (2,1), (3,1), (2,4), (3,4), (10,2), (20,2), (2,5), +(3,5), (100,3), (200,3), (10,6), (20,6), (20,7), (100,8), (200,8); +insert into t2 values (1,1),(3,3),(20,20); +aggregate function as parameter of subquery +set optimizer_switch='subquery_cache=off'; +flush status; +select max(a), (select max(a) from t2 where max(a)=c) from t1 group by b; +max(a) (select max(a) from t2 where max(a)=c) +3 3 +20 20 +200 NULL +3 3 +3 3 +20 20 +20 20 +200 NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 0 +Subquery_cache_miss 0 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 15 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 8 +Handler_read_rnd_next 57 +set optimizer_switch='subquery_cache=on'; +flush status; +select max(a), (select max(a) from t2 where max(a)=c) from t1 group by b; +max(a) (select max(a) from t2 where max(a)=c) +3 3 +20 20 +200 NULL +3 3 +3 3 +20 20 +20 20 +200 NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 5 +Subquery_cache_miss 3 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 21 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 8 +Handler_read_rnd_next 37 +argument of aggregate function as parameter of subquery (illegal use) +set optimizer_switch='subquery_cache=off'; +flush status; +select max(a), (select a from t2 where a=c) from t1 group by b; +max(a) (select a from t2 where a=c) +3 NULL +20 NULL +200 NULL +3 NULL +3 NULL +20 NULL +20 20 +200 NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 0 +Subquery_cache_miss 0 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 15 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 8 +Handler_read_rnd_next 57 +set optimizer_switch='subquery_cache=on'; +flush status; +select max(a), (select a from t2 where a=c) from t1 group by b; +max(a) (select a from t2 where a=c) +3 NULL +20 NULL +200 NULL +3 NULL +3 NULL +20 NULL +20 20 +200 NULL +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 4 +Subquery_cache_miss 4 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 22 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 8 +Handler_read_rnd_next 41 +drop table t1,t2; +#test of flattening subquery optimisations and cache +create table t0 (a int); +insert into t0 values (9),(8),(7),(6),(5),(4),(3),(2),(1),(0); +create table t1(a int, b int); +insert into t1 values +(0,0),(1,1),(2,2),(0,0),(1,1),(2,2),(0,0),(1,1),(2,2),(0,0),(1,1),(2,2),(0,0),(1,1),(2,2); +create table t2 (pk int, a int, primary key(pk)); +insert into t2 select a,a from t0; +set optimizer_switch='default,semijoin=on,materialization=on,subquery_cache=on'; +flush status; +select * from t1 where a in (select pk from t2); +a b +0 0 +1 1 +2 2 +0 0 +1 1 +2 2 +0 0 +1 1 +2 2 +0 0 +1 1 +2 2 +0 0 +1 1 +2 2 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 0 +Subquery_cache_miss 0 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 15 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 16 +alter table t2 drop primary key; +set optimizer_switch='default,semijoin=off,materialization=off,subquery_cache=off'; +explain select * from t1 where a in (select pk from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 15 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 10 Using where +flush status; +select * from t1 where a in (select pk from t2); +a b +0 0 +1 1 +2 2 +0 0 +1 1 +2 2 +0 0 +1 1 +2 2 +0 0 +1 1 +2 2 +0 0 +1 1 +2 2 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 0 +Subquery_cache_miss 0 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 0 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 151 +set optimizer_switch='default,semijoin=off,materialization=off,subquery_cache=on'; +explain select * from t1 where a in (select pk from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 15 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 10 Using where +flush status; +select * from t1 where a in (select pk from t2); +a b +0 0 +1 1 +2 2 +0 0 +1 1 +2 2 +0 0 +1 1 +2 2 +0 0 +1 1 +2 2 +0 0 +1 1 +2 2 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 12 +Subquery_cache_miss 3 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 15 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 43 +set optimizer_switch='default,semijoin=off,materialization=on,subquery_cache=on'; +explain select * from t1 where a in (select pk from t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 15 Using where +2 SUBQUERY t2 ALL NULL NULL NULL NULL 10 +flush status; +select * from t1 where a in (select pk from t2); +a b +0 0 +1 1 +2 2 +0 0 +1 1 +2 2 +0 0 +1 1 +2 2 +0 0 +1 1 +2 2 +0 0 +1 1 +2 2 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 12 +Subquery_cache_miss 3 +show status like '%Handler_read%'; +Variable_name Value +Handler_read_first 0 +Handler_read_key 18 +Handler_read_next 0 +Handler_read_prev 0 +Handler_read_rnd 0 +Handler_read_rnd_next 27 +drop table t0,t1,t2; +set optimizer_switch='default'; diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index 6d5654c9895..665bb9a2bde 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -105,6 +105,7 @@ Variable_name Value 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 @@ Handler_read_rnd_next 29 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); @@ -952,6 +954,8 @@ i1 i2 4 NULL 5 0 FLUSH STATUS; +set @save_optimizer_switch2=@@optimizer_switch; +set optimizer_switch='subquery_cache=off'; SELECT i1, i2 FROM t1 @@ -984,6 +988,7 @@ i1 i2 SHOW STATUS LIKE '%Handler_read_rnd_next'; Variable_name Value Handler_read_rnd_next 19 +set @@optimizer_switch=@save_optimizer_switch2; DROP TABLE t1,t2; End of 5.1 tests CREATE TABLE t1 ( diff --git a/mysql-test/r/subselect3_jcl6.result b/mysql-test/r/subselect3_jcl6.result index 3e91591d432..d25ca436311 100644 --- a/mysql-test/r/subselect3_jcl6.result +++ b/mysql-test/r/subselect3_jcl6.result @@ -109,6 +109,7 @@ Variable_name Value 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 @@ Handler_read_rnd_next 29 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); @@ -956,6 +958,8 @@ i1 i2 4 NULL 5 0 FLUSH STATUS; +set @save_optimizer_switch2=@@optimizer_switch; +set optimizer_switch='subquery_cache=off'; SELECT i1, i2 FROM t1 @@ -988,6 +992,7 @@ i1 i2 SHOW STATUS LIKE '%Handler_read_rnd_next'; Variable_name Value Handler_read_rnd_next 19 +set @@optimizer_switch=@save_optimizer_switch2; DROP TABLE t1,t2; End of 5.1 tests CREATE TABLE t1 ( diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 42f12f849bc..48205830e78 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -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; @@ -4927,4 +4927,4 @@ DROP TABLE t1; 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 diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index bb93ddf36fe..5ad1f2b714c 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -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; @@ -4927,4 +4927,4 @@ DROP TABLE t1; 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 diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index a8b675e8e8a..f372e3b7822 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -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; @@ -4927,4 +4927,4 @@ DROP TABLE t1; 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 diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result index f27d83d77fa..2a689719f52 100644 --- a/mysql-test/r/subselect_sj.result +++ b/mysql-test/r/subselect_sj.result @@ -202,39 +202,39 @@ BUG#37120 optimizer_switch allowable values not according to specification 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; diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index b593a8320fd..e6db7be4495 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -206,39 +206,39 @@ BUG#37120 optimizer_switch allowable values not according to specification 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; diff --git a/mysql-test/t/subquery_cache.test b/mysql-test/t/subquery_cache.test new file mode 100644 index 00000000000..af87b4fd287 --- /dev/null +++ b/mysql-test/t/subquery_cache.test @@ -0,0 +1,509 @@ + +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); +create table t2 (c int, d int); +insert into t2 values (2,3),(3,4),(5,6),(4,1); + +--echo * +--echo * Test subquery as top item in different clauses +--echo * +--echo #single value subquery test (SELECT list) +flush status; +select a, (select d from t2 where b=c) from t1; + +show status like "subquery_cache%"; +show status like '%Handler_read%'; + +set optimizer_switch='subquery_cache=off'; +flush status; + +select a, (select d from t2 where b=c) from t1; + +show status like "subquery_cache%"; +show status like '%Handler_read%'; +set optimizer_switch='subquery_cache=on'; + + +--echo #single value subquery test (where) +flush status; +select a from t1 where (select d from t2 where b=c); + +show status like "subquery_cache%"; +show status like '%Handler_read%'; + +set optimizer_switch='subquery_cache=off'; +flush status; + +select a from t1 where (select d from t2 where b=c); + +show status like "subquery_cache%"; +show status like '%Handler_read%'; +set optimizer_switch='subquery_cache=on'; + +--echo #single value subquery test (having) +flush status; +select a from t1 where a > 0 having (select d from t2 where b=c); + +show status like "subquery_cache%"; +show status like '%Handler_read%'; + +set optimizer_switch='subquery_cache=off'; +flush status; + +select a from t1 where a > 0 having (select d from t2 where b=c); + +show status like "subquery_cache%"; +show status like '%Handler_read%'; +set optimizer_switch='subquery_cache=on'; + +--echo #single value subquery test (OUTER JOIN ON) +flush status; +select ta.a, tb.a from t1 ta join t1 tb on (select d from t2 where tb.b=c); + +show status like "subquery_cache%"; +show status like '%Handler_read%'; + +set optimizer_switch='subquery_cache=off'; +flush status; + +select ta.a, tb.a from t1 ta join t1 tb on (select d from t2 where tb.b=c); + +show status like "subquery_cache%"; +show status like '%Handler_read%'; +set optimizer_switch='subquery_cache=on'; + +--echo #single value subquery test (GROUP BY) +flush status; +select max(a) from t1 GROUP BY (select d from t2 where b=c); + +show status like "subquery_cache%"; +show status like '%Handler_read%'; +set optimizer_switch='subquery_cache=off'; + +flush status; +select max(a) from t1 GROUP BY (select d from t2 where b=c); + +show status like "subquery_cache%"; +show status like '%Handler_read%'; +set optimizer_switch='subquery_cache=on'; + +--echo #single value subquery test (distinct GROUP BY) +flush status; +select distinct max(a) from t1 GROUP BY (select d from t2 where b=c); + +show status like "subquery_cache%"; +show status like '%Handler_read%'; +set optimizer_switch='subquery_cache=off'; + +flush status; +select distinct max(a) from t1 GROUP BY (select d from t2 where b=c); + +show status like "subquery_cache%"; +show status like '%Handler_read%'; +set optimizer_switch='subquery_cache=on'; + +--echo #single value subquery test (ORDER BY) +flush status; +select a from t1 ORDER BY (select d from t2 where b=c); + +show status like "subquery_cache%"; +show status like '%Handler_read%'; +set optimizer_switch='subquery_cache=off'; + +flush status; +select a from t1 ORDER BY (select d from t2 where b=c); + +show status like "subquery_cache%"; +show status like '%Handler_read%'; +set optimizer_switch='subquery_cache=on'; + +--echo #single value subquery test (distinct ORDER BY) +flush status; +select distinct a from t1 ORDER BY (select d from t2 where b=c); + +show status like "subquery_cache%"; +show status like '%Handler_read%'; +set optimizer_switch='subquery_cache=off'; + +flush status; +select distinct a from t1 ORDER BY (select d from t2 where b=c); + +show status like "subquery_cache%"; +show status like '%Handler_read%'; +set optimizer_switch='subquery_cache=on'; + +--echo #single value subquery test (LEFT JOIN ON) +flush status; +select ta.a, tb.a from t1 ta left join t1 tb on (select d from t2 where tb.b=c); + +show status like "subquery_cache%"; +show status like '%Handler_read%'; + +set optimizer_switch='subquery_cache=off'; +flush status; + +select ta.a, tb.a from t1 ta left join t1 tb on (select d from t2 where tb.b=c); + +show status like "subquery_cache%"; +show status like '%Handler_read%'; +set optimizer_switch='subquery_cache=on'; + +--echo #single value subquery test (PS) +prepare stmt1 from 'select a, (select d from t2 where b=c) + 1 from t1'; +execute stmt1; +show status like "subquery_cache%"; +execute stmt1; +show status like "subquery_cache%"; +deallocate prepare stmt1; + +--echo #single value subquery test (SP) +CREATE PROCEDURE p1() select a, (select d from t2 where b=c) + 1 from t1; + +call p1; +call p1; + +drop procedure p1; + +--echo #IN subquery test +flush status; + +show status like "subquery_cache%"; +select a, b , b in (select d from t2) as SUBS from t1; +show status like "subquery_cache%"; + +insert into t1 values (7,8),(9,NULL); +select a, b , b in (select d from t2) as SUBS from t1; +show status like "subquery_cache%"; + +insert into t2 values (8,NULL); +select a, b , b in (select d from t2) as SUBS from t1; +show status like "subquery_cache%"; + +--echo # multicolumn NOT IN with NULLs +flush status; +set optimizer_switch='subquery_cache=off'; +select a, b, (b, a) not in (select d, c from t2) as SUBS from t1; +show status like "subquery_cache%"; + +set optimizer_switch='subquery_cache=on'; +select a, b, (b, a) not in (select d, c from t2) as SUBS from t1; +show status like "subquery_cache%"; + +--echo # multicolumn NOT IN with NULLs (other order) +flush status; +set optimizer_switch='subquery_cache=off'; +select a, b, (a, b) not in (select d, c from t2) as SUBS from t1; +show status like "subquery_cache%"; + +set optimizer_switch='subquery_cache=on'; +select a, b, (a, b) not in (select d, c from t2) as SUBS from t1; +show status like "subquery_cache%"; + +--echo # multicolumn IN with NULLs +flush status; +set optimizer_switch='subquery_cache=off'; +select a, b, (b, a) in (select d, c from t2) as SUBS from t1; +show status like "subquery_cache%"; + +set optimizer_switch='subquery_cache=on'; +select a, b, (b, a) in (select d, c from t2) as SUBS from t1; +show status like "subquery_cache%"; + +--echo # multicolumn IN with NULLs (other order) +flush status; +set optimizer_switch='subquery_cache=off'; +select a, b, (a, b) in (select d, c from t2) as SUBS from t1; +show status like "subquery_cache%"; + +set optimizer_switch='subquery_cache=on'; +select a, b, (a, b) in (select d, c from t2) as SUBS from t1; +show status like "subquery_cache%"; + +--echo #IN subquery test (PS) +delete from t1 where a > 6; +delete from t2 where c > 6; + +prepare stmt1 from 'select a, b , b in (select d from t2) as SUBS from t1'; +execute stmt1; +show status like "subquery_cache%"; +execute stmt1; +show status like "subquery_cache%"; + +insert into t1 values (7,8),(9,NULL); +execute stmt1; +show status like "subquery_cache%"; +execute stmt1; +show status like "subquery_cache%"; + +insert into t2 values (8,NULL); +execute stmt1; +show status like "subquery_cache%"; +execute stmt1; +show status like "subquery_cache%"; + +deallocate prepare stmt1; + + +--echo #IN subquery test (SP) +delete from t1 where a > 6; +delete from t2 where c > 6; + +CREATE PROCEDURE p1() select a, b , b in (select d from t2) as SUBS from t1; + +call p1(); +show status like "subquery_cache%"; +call p1(); +show status like "subquery_cache%"; + +insert into t1 values (7,8),(9,NULL); +call p1(); +show status like "subquery_cache%"; +call p1(); +show status like "subquery_cache%"; + +insert into t2 values (8,NULL); +call p1(); +show status like "subquery_cache%"; +call p1(); +show status like "subquery_cache%"; + +drop procedure p1; + + +--echo # test of simple exists +select a, b , exists (select * from t2 where b=d) as SUBS from t1; + +--echo # test of prepared statement exists +show status like "subquery_cache%"; +prepare stmt1 from 'select a, b , exists (select * from t2 where b=d) as SUBS from t1'; +execute stmt1; +show status like "subquery_cache%"; +execute stmt1; +show status like "subquery_cache%"; +deallocate prepare stmt1; + +--echo # test of stored procedure exists +CREATE PROCEDURE p1() select a, b , exists (select * from t2 where b=d) as SUBS from t1; +call p1; +call p1; +drop procedure p1; + +--echo #several subqueries +set optimizer_switch='subquery_cache=off'; +flush status; +select a, b , exists (select * from t2 where b=d) as SUBSE, b in (select d from t2) as SUBSI, (select d from t2 where b=c) SUBSR from t1; +show status like "subquery_cache%"; +show status like '%Handler_read%'; + +set optimizer_switch='subquery_cache=on'; +flush status; +select a, b , exists (select * from t2 where b=d) as SUBSE, b in (select d from t2) as SUBSI, (select d from t2 where b=c) SUBSR from t1; +show status like "subquery_cache%"; +show status like '%Handler_read%'; + +--echo #several subqueries (several levels) +set optimizer_switch='subquery_cache=off'; +flush status; + +set optimizer_switch='subquery_cache=off'; +flush status; +select a, b, (select exists (select * from t2 where b=d) from t2 where b=c) as SUNS1 from t1; +show status like "subquery_cache%"; +show status like '%Handler_read%'; + + +set optimizer_switch='subquery_cache=on'; +flush status; +select a, b, (select exists (select * from t2 where b=d) from t2 where b=c) as SUNS1 from t1; +show status like "subquery_cache%"; +show status like '%Handler_read%'; + + +--echo #clean up +drop table t1,t2; + +--echo test different types +--echo #int +CREATE TABLE t1 ( a int, b int); +INSERT INTO t1 VALUES(1,1),(2,2),(3,3); +SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = 2); +DROP TABLE t1; + +--echo #char +CREATE TABLE t1 ( a char(1), b char (1)); +INSERT INTO t1 VALUES('1','1'),('2','2'),('3','3'); +SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '2'); +DROP TABLE t1; + +--echo #decimal +CREATE TABLE t1 ( a decimal(3,1), b decimal(3,1)); +INSERT INTO t1 VALUES(1,1),(2,2),(3,3); +SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = 2); +DROP TABLE t1; + +--echo #date +CREATE TABLE t1 ( a date, b date); +INSERT INTO t1 VALUES('1000-01-01','1000-01-01'),('2000-02-01','2000-02-01'),('3000-03-03','3000-03-03'); +SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '2000-02-01'); +DROP TABLE t1; + +--echo #datetime +CREATE TABLE t1 ( a datetime, b datetime); +INSERT INTO t1 VALUES('1000-01-01 01:01:01','1000-01-01 01:01:01'),('2000-02-02 02:02:02','2000-02-02 02:02:02'),('3000-03-03 03:03:03','3000-03-03 03:03:03'); +SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '2000-02-02 02:02:02'); +DROP TABLE t1; + +--echo #time +CREATE TABLE t1 ( a time, b time); +INSERT INTO t1 VALUES('01:01:01','01:01:01'),('02:02:02','02:02:02'),('03:03:03','03:03:03'); +SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '02:02:02'); +DROP TABLE t1; + +--echo #timestamp +CREATE TABLE t1 ( a timestamp, b timestamp); +INSERT INTO t1 VALUES('2000-02-02 01:01:01','2000-02-02 01:01:01'),('2000-02-02 02:02:02','2000-02-02 02:02:02'),('2000-02-02 03:03:03','2000-02-02 03:03:03'); +SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '2000-02-02 02:02:02'); +DROP TABLE t1; + +--echo #bit +CREATE TABLE t1 ( a bit(20), b bit(20)); +INSERT INTO t1 VALUES(1,1),(2,2),(3,3); +SELECT a+0 FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = 2); +DROP TABLE t1; + +--echo #enum +CREATE TABLE t1 ( a enum('1','2','3'), b enum('1','2','3')); +INSERT INTO t1 VALUES('1','1'),('2','2'),('3','3'); +SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '2'); +DROP TABLE t1; + +--echo #set +CREATE TABLE t1 ( a set('1','2','3'), b set('1','2','3')); +INSERT INTO t1 VALUES('1','1'),('2','2'),('3','3'); +SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '2'); +DROP TABLE t1; + +--echo #blob +CREATE TABLE t1 ( a blob, b blob); +INSERT INTO t1 VALUES('1','1'),('2','2'),('3','3'); +SELECT a FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = '2'); +DROP TABLE t1; + +--echo #geometry +CREATE TABLE t1 ( a geometry, b geometry); +INSERT INTO t1 VALUES(POINT(1,1),POINT(1,1)),(POINT(2,2),POINT(2,2)),(POINT(3,3),POINT(3,3)); +SELECT astext(a) FROM t1 WHERE NOT a IN (SELECT a FROM t1 WHERE b = POINT(2,2)); +DROP TABLE t1; + + +--echo #uncacheable queries test (random and side effect) +flush status; +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (2), (4), (1), (3); +select a, a in (select a from t1) from t1 as ext; +show status like "subquery_cache%"; +select a, a in (select a from t1 where -1 < rand()) from t1 as ext; +show status like "subquery_cache%"; +select a, a in (select a from t1 where -1 < benchmark(a,100)) from t1 as ext; +show status like "subquery_cache%"; +drop table t1; + +--echo #test of sql_big_tables switch and outer table reference in subquery with grouping +set option sql_big_tables=1; +CREATE TABLE t1 (a INT PRIMARY KEY, b INT); +INSERT INTO t1 VALUES (1,1),(2,1),(3,2),(4,2),(5,3),(6,3); +SELECT (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1) FROM t1 AS t1_outer; +drop table t1; +set option sql_big_tables=0; + +--echo #test of function reference to outer query +set local group_concat_max_len=400; +create table t2 (a int, b int); +insert into t2 values (1,1), (2,2); +select b x, (select group_concat(x) from t2) from t2; +drop table t2; +set local group_concat_max_len=default; + +--echo #aggregate functions +CREATE TABLE t1 (a int, b INT); +CREATE TABLE t2 (c int, d INT); + +insert into t1 values (2,1), (3,1), (2,4), (3,4), (10,2), (20,2), (2,5), +(3,5), (100,3), (200,3), (10,6), (20,6), (20,7), (100,8), (200,8); +insert into t2 values (1,1),(3,3),(20,20); + +--echo aggregate function as parameter of subquery +set optimizer_switch='subquery_cache=off'; +flush status; +select max(a), (select max(a) from t2 where max(a)=c) from t1 group by b; +show status like "subquery_cache%"; +show status like '%Handler_read%'; +set optimizer_switch='subquery_cache=on'; +flush status; +select max(a), (select max(a) from t2 where max(a)=c) from t1 group by b; +show status like "subquery_cache%"; +show status like '%Handler_read%'; + +--echo argument of aggregate function as parameter of subquery (illegal use) +set optimizer_switch='subquery_cache=off'; +flush status; +select max(a), (select a from t2 where a=c) from t1 group by b; +show status like "subquery_cache%"; +show status like '%Handler_read%'; + +set optimizer_switch='subquery_cache=on'; +flush status; +select max(a), (select a from t2 where a=c) from t1 group by b; +show status like "subquery_cache%"; +show status like '%Handler_read%'; + +drop table t1,t2; + +--echo #test of flattening subquery optimisations and cache +create table t0 (a int); +insert into t0 values (9),(8),(7),(6),(5),(4),(3),(2),(1),(0); + +create table t1(a int, b int); +insert into t1 values +(0,0),(1,1),(2,2),(0,0),(1,1),(2,2),(0,0),(1,1),(2,2),(0,0),(1,1),(2,2),(0,0),(1,1),(2,2); + +create table t2 (pk int, a int, primary key(pk)); +insert into t2 select a,a from t0; + +set optimizer_switch='default,semijoin=on,materialization=on,subquery_cache=on'; +flush status; +select * from t1 where a in (select pk from t2); +show status like "subquery_cache%"; +show status like '%Handler_read%'; + +alter table t2 drop primary key; +set optimizer_switch='default,semijoin=off,materialization=off,subquery_cache=off'; + +explain select * from t1 where a in (select pk from t2); +flush status; +select * from t1 where a in (select pk from t2); +show status like "subquery_cache%"; +show status like '%Handler_read%'; + +set optimizer_switch='default,semijoin=off,materialization=off,subquery_cache=on'; + +explain select * from t1 where a in (select pk from t2); +flush status; +select * from t1 where a in (select pk from t2); +show status like "subquery_cache%"; +show status like '%Handler_read%'; + +#TODO: switch off cache if materialization used +set optimizer_switch='default,semijoin=off,materialization=on,subquery_cache=on'; + +explain select * from t1 where a in (select pk from t2); +flush status; +select * from t1 where a in (select pk from t2); +show status like "subquery_cache%"; +show status like '%Handler_read%'; + +drop table t0,t1,t2; + +set optimizer_switch='default'; diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test index fa1f916261c..a621c4c776c 100644 --- a/mysql-test/t/subselect3.test +++ b/mysql-test/t/subselect3.test @@ -98,10 +98,12 @@ show status like '%Handler_read_rnd_next'; 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; @@ -794,6 +796,8 @@ SELECT i1, i2 FROM t1; SELECT i1, i2 FROM t2 ORDER BY i1; FLUSH STATUS; +set @save_optimizer_switch2=@@optimizer_switch; +set optimizer_switch='subquery_cache=off'; --echo SELECT i1, i2 @@ -822,7 +826,7 @@ WHERE (i1, i2) --echo # (read record from t1, but do not read from t2) SHOW STATUS LIKE '%Handler_read_rnd_next'; - +set @@optimizer_switch=@save_optimizer_switch2; DROP TABLE t1,t2; --echo End of 5.1 tests diff --git a/sql/CMakeLists.txt b/sql/CMakeLists.txt index b59080adfb7..bae77f80b22 100755 --- a/sql/CMakeLists.txt +++ b/sql/CMakeLists.txt @@ -82,6 +82,7 @@ SET (SQL_SOURCE opt_subselect.cc opt_index_cond_pushdown.cc create_options.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 diff --git a/sql/Makefile.am b/sql/Makefile.am index 6f5f03f8637..13a60ba5c79 100644 --- a/sql/Makefile.am +++ b/sql/Makefile.am @@ -81,7 +81,8 @@ noinst_HEADERS = item.h item_func.h item_sum.h item_cmpfunc.h \ sql_partition.h partition_info.h partition_element.h \ contributors.h sql_servers.h \ multi_range_read.h \ - create_options.h + create_options.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 \ @@ -131,7 +132,7 @@ mysqld_SOURCES = sql_lex.cc sql_handler.cc sql_partition.cc \ sql_servers.cc event_parse_data.cc \ opt_table_elimination.cc create_options.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 client_plugin.c diff --git a/sql/item.cc b/sql/item.cc index dd303c93844..274a467f4e3 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -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} */ @@ -541,6 +544,36 @@ Item* Item::transform(Item_transformer transformer, uchar *arg) } +/** + Create and set up an expression cache for this item + + @param thd Thread handle + @param depends_on List of the expression parameters + + @details + The function creates an expression cache for an item and its parameters + specified by the 'depends_on' list. Then the expression cache is placed + into a cache wrapper that is returned as the result of the function. + + @returns + A pointer to created wrapper item if successful, NULL - otherwise +*/ + +Item* Item::set_expr_cache(THD *thd, List<Item *> &depends_on) +{ + DBUG_ENTER("Item::set_expr_cache"); + 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, const char *field_name_arg) @@ -549,7 +582,7 @@ Item_ident::Item_ident(Name_resolution_context *context_arg, db_name(db_name_arg), table_name(table_name_arg), field_name(field_name_arg), alias_name_used(FALSE), cached_field_index(NO_CACHED_FIELD_INDEX), - cached_table(0), depended_from(0) + cached_table(0), depended_from(0), can_be_depended(TRUE) { name = (char*) field_name_arg; } @@ -561,7 +594,7 @@ Item_ident::Item_ident(TABLE_LIST *view_arg, const char *field_name_arg) db_name(NullS), table_name(view_arg->alias), field_name(field_name_arg), alias_name_used(FALSE), cached_field_index(NO_CACHED_FIELD_INDEX), - cached_table(NULL), depended_from(NULL) + cached_table(NULL), depended_from(NULL), can_be_depended(TRUE) { name = (char*) field_name_arg; } @@ -583,7 +616,8 @@ Item_ident::Item_ident(THD *thd, Item_ident *item) alias_name_used(item->alias_name_used), cached_field_index(item->cached_field_index), cached_table(item->cached_table), - depended_from(item->depended_from) + depended_from(item->depended_from), + can_be_depended(item->can_be_depended) {} void Item_ident::cleanup() @@ -601,7 +635,8 @@ void Item_ident::cleanup() db_name= orig_db_name; table_name= orig_table_name; field_name= orig_field_name; - depended_from= 0; + /* Store if this Item was depended */ + can_be_depended= test(depended_from); DBUG_VOID_RETURN; } @@ -1424,7 +1459,7 @@ void Item::split_sum_func2(THD *thd, Item **ref_pointer_array, /* An item of type Item_sum is registered <=> ref_by != 0 */ if (type() == SUM_FUNC_ITEM && skip_registered && ((Item_sum *) this)->ref_by) - return; + return; if ((type() != SUM_FUNC_ITEM && with_sum_func) || (type() == FUNC_ITEM && (((Item_func *) this)->functype() == Item_func::ISNOTNULLTEST_FUNC || @@ -1745,7 +1780,10 @@ bool agg_item_set_converter(DTCollation &coll, const char *fname, subselect transformation does not happen in view_prepare_mode and thus val_...() methods can not be called for const items. */ - bool resolve_const= ((*arg)->type() == Item::SUBSELECT_ITEM && + bool resolve_const= (((*arg)->type() == Item::SUBSELECT_ITEM || + ((*arg)->get_cached_item() && + (*arg)->get_cached_item()->type() == + Item::SUBSELECT_ITEM)) && thd->lex->view_prepare_mode) ? FALSE : TRUE; conv= new Item_func_conv_charset(*arg, coll.collation, resolve_const); } @@ -2131,6 +2169,12 @@ bool Item_field::get_time(MYSQL_TIME *ltime) return 0; } +void Item_field::save_result(Field *to) +{ + save_field_in_field(result_field, &null_value, to, TRUE); +} + + double Item_field::val_result() { if ((null_value=result_field->is_null())) @@ -3596,6 +3640,15 @@ bool Item::fix_fields(THD *thd, Item **ref) return FALSE; } + +void Item_ref_null_helper::save_val(Field *to) +{ + DBUG_ASSERT(fixed == 1); + (*ref)->save_val(to); + owner->was_null|= null_value= (*ref)->null_value; +} + + double Item_ref_null_helper::val_real() { DBUG_ASSERT(fixed == 1); @@ -3668,7 +3721,7 @@ static bool mark_as_dependent(THD *thd, SELECT_LEX *last, SELECT_LEX *current, const char *table_name= (resolved_item->table_name ? resolved_item->table_name : ""); /* store pointer on SELECT_LEX from which item is dependent */ - if (mark_item) + if (mark_item && mark_item->can_be_depended) mark_item->depended_from= last; if (current->mark_as_dependent(thd, last, /** resolved_item psergey-thu **/mark_item)) @@ -4134,7 +4187,9 @@ Item_field::fix_outer_field(THD *thd, Field **from_field, Item **reference) ((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 @@ -4236,6 +4291,10 @@ Item_field::fix_outer_field(THD *thd, Field **from_field, Item **reference) 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 @@ -4243,6 +4302,9 @@ Item_field::fix_outer_field(THD *thd, Field **from_field, Item **reference) 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; @@ -4549,6 +4611,7 @@ void Item_field::cleanup() { DBUG_ENTER("Item_field::cleanup"); Item_ident::cleanup(); + depended_from= NULL; /* Even if this object was created by direct link to field in setup_wild() it will be linked correctly next time by name of field and table alias. @@ -5107,47 +5170,69 @@ void Item_field::make_field(Send_field *tmp_field) /** - Set a field's value from a item. -*/ + Save a field value in another field -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; - } -} + @param from Field to take the value from + @param [out] null_value Pointer to the null_value flag to set + @param to Field to save the value in + @param no_conversions How to deal with NULL value -int Item_field::save_in_field(Field *to, bool no_conversions) + @details + The function takes the value of the field 'from' and, if this value + is not null, it saves in the field 'to' setting off the flag referenced + by 'null_value'. Otherwise this flag is set on and field 'to' is + also set to null possibly with conversion. + + @note + This function is used by the functions 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; - if (result_field->is_null()) + DBUG_ENTER("save_field_in_field"); + if (from->is_null()) { - null_value=1; - return set_field_to_null_with_conversions(to, no_conversions); + (*null_value)= 1; + DBUG_RETURN(set_field_to_null_with_conversions(to, no_conversions)); } to->set_notnull(); /* If we're setting the same field as the one we're reading from there's nothing to do. This can happen in 'SET x = x' type of scenarios. - */ - if (to == result_field) + */ + if (to == from) { - null_value=0; - return 0; + (*null_value)= 0; + DBUG_RETURN(0); } - res= field_conv(to,result_field); - null_value=0; - return res; + 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) +{ + save_field_in_field(field, &null_value, to, TRUE); +} + + +int Item_field::save_in_field(Field *to, bool no_conversions) +{ + return save_field_in_field(result_field, &null_value, to, no_conversions); } @@ -6037,6 +6122,9 @@ bool Item_ref::fix_fields(THD *thd, Item **reference) 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 @@ -6087,6 +6175,9 @@ bool Item_ref::fix_fields(THD *thd, Item **reference) 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 @@ -6110,6 +6201,9 @@ bool Item_ref::fix_fields(THD *thd, Item **reference) 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, + 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 @@ -6124,7 +6218,11 @@ bool Item_ref::fix_fields(THD *thd, Item **reference) } else { - ; + if (depended_from && reference) + { + DBUG_ASSERT(context->select_lex != depended_from); + context->select_lex->register_dependency_item(depended_from, reference); + } /* It could be that we're referring to something that's in ancestor selects. We must make an appropriate mark_as_dependent() call for each such @@ -6315,6 +6413,25 @@ bool Item_ref::val_bool_result() } +void Item_ref::save_result(Field *to) +{ + if (result_field) + { + save_field_in_field(result_field, &null_value, to, TRUE); + return; + } + (*ref)->save_result(to); + null_value= (*ref)->null_value; +} + + +void Item_ref::save_val(Field *to) +{ + (*ref)->save_result(to); + null_value= (*ref)->null_value; +} + + double Item_ref::val_real() { DBUG_ASSERT(fixed); @@ -6432,6 +6549,13 @@ void Item_ref_null_helper::print(String *str, enum_query_type query_type) } +void Item_direct_ref::save_val(Field *to) +{ + (*ref)->save_val(to); + null_value=(*ref)->null_value; +} + + double Item_direct_ref::val_real() { double tmp=(*ref)->val_real(); @@ -6484,6 +6608,364 @@ bool Item_direct_ref::get_date(MYSQL_TIME *ltime,uint fuzzydate) } +Item_cache_wrapper::~Item_cache_wrapper() +{ + delete expr_cache; + /* expr_value is Item so it will be destroyed from list of Items */ +} + + +Item_cache_wrapper::Item_cache_wrapper(Item *item_arg) +:orig_item(item_arg), expr_cache(NULL), expr_value(NULL) +{ + DBUG_ASSERT(orig_item->fixed); + max_length= orig_item->max_length; + maybe_null= orig_item->maybe_null; + decimals= orig_item->decimals; + collation.set(orig_item->collation); + with_sum_func= orig_item->with_sum_func; + unsigned_flag= orig_item->unsigned_flag; + name= item_arg->name; + name_length= item_arg->name_length; + + if ((expr_value= Item_cache::get_cache(orig_item))) + expr_value->setup(orig_item); + + fixed= 1; +} + + +/** + Prepare the expression cache wrapper (do nothing) + + @retval FALSE OK +*/ + +bool Item_cache_wrapper::fix_fields(THD *thd __attribute__((unused)), + Item **it __attribute__((unused))) +{ + DBUG_ASSERT(orig_item->fixed); + DBUG_ASSERT(fixed); + return FALSE; +} + + +/** + Clean the expression cache wrapper up before reusing it. +*/ + +void Item_cache_wrapper::cleanup() +{ + delete expr_cache; + expr_cache= 0; + // expr_value is Item so it will be destroyed from list of Items + expr_value= 0; +} + + +/** + Create an expression cache that uses a temporary table + + @param thd Thread handle + @param depends_on Parameters of the expression to create cache for + + @details + The function takes 'depends_on' as the list of all parameters for + the expression wrapped into this object and creates an expression + cache in a temporary table containing the field for the parameters + and the result of the expression. + + @retval FALSE OK + @retval TRUE Error +*/ + +bool Item_cache_wrapper::set_cache(THD *thd, List<Item*> &depends_on) +{ + DBUG_ENTER("Item_cache_wrapper::set_cache"); + expr_cache= new Expression_cache_tmptable(thd, depends_on, expr_value); + DBUG_RETURN(expr_cache == NULL); +} + + +/** + Check if the current values of the parameters are in the expression cache + + @details + The function checks whether the current set of the parameters of the + referenced item can be found in the expression cache. If so the function + returns the item by which the result of the expression can be easily + extracted from the cache with the corresponding val_* method. + + @retval NULL - parameters are not in the cache + @retval <item*> - item providing the result of the expression found in cache +*/ + +Item *Item_cache_wrapper::check_cache() +{ + DBUG_ENTER("Item_cache_wrapper::check_cache"); + if (expr_cache) + { + Expression_cache_tmptable::result res; + Item *cached_value; + res= expr_cache->check_value(&cached_value); + if (res == Expression_cache_tmptable::HIT) + DBUG_RETURN(cached_value); + } + DBUG_RETURN(NULL); +} + + +/** + Get the value of the cached expression and put it in the cache +*/ + +inline void Item_cache_wrapper::cache() +{ + expr_value->store(orig_item); + expr_value->cache_value(); + expr_cache->put_value(expr_value); // put in expr_cache +} + + +/** + Get the value of the possibly cached item into the field. +*/ + +void Item_cache_wrapper::save_val(Field *to) +{ + Item *cached_value; + DBUG_ENTER("Item_cache_wrapper::val_int"); + if (!expr_cache) + { + orig_item->save_val(to); + null_value= orig_item->null_value; + DBUG_VOID_RETURN; + } + + if ((cached_value= check_cache())) + { + cached_value->save_val(to); + null_value= cached_value->null_value; + DBUG_VOID_RETURN; + } + cache(); + null_value= expr_value->null_value; + expr_value->save_val(to); + DBUG_VOID_RETURN; +} + + +/** + Get the integer value of the possibly cached item. +*/ + +longlong Item_cache_wrapper::val_int() +{ + Item *cached_value; + DBUG_ENTER("Item_cache_wrapper::val_int"); + if (!expr_cache) + { + longlong tmp= orig_item->val_int(); + null_value= orig_item->null_value; + DBUG_RETURN(tmp); + } + + if ((cached_value= check_cache())) + { + longlong tmp= cached_value->val_int(); + null_value= cached_value->null_value; + DBUG_RETURN(tmp); + } + cache(); + null_value= expr_value->null_value; + DBUG_RETURN(expr_value->val_int()); +} + + +/** + Get the real value of the possibly cached item +*/ + +double Item_cache_wrapper::val_real() +{ + Item *cached_value; + DBUG_ENTER("Item_cache_wrapper::val_real"); + if (!expr_cache) + { + double tmp= orig_item->val_real(); + null_value= orig_item->null_value; + DBUG_RETURN(tmp); + } + + if ((cached_value= check_cache())) + { + double tmp= cached_value->val_real(); + null_value= cached_value->null_value; + DBUG_RETURN(tmp); + } + cache(); + null_value= expr_value->null_value; + DBUG_RETURN(expr_value->val_real()); +} + + +/** + Get the string value of the possibly cached item +*/ + +String *Item_cache_wrapper::val_str(String* str) +{ + Item *cached_value; + DBUG_ENTER("Item_cache_wrapper::val_str"); + if (!expr_cache) + { + String *tmp= orig_item->val_str(str); + null_value= orig_item->null_value; + DBUG_RETURN(tmp); + } + + if ((cached_value= check_cache())) + { + String *tmp= cached_value->val_str(str); + null_value= cached_value->null_value; + DBUG_RETURN(tmp); + } + cache(); + if ((null_value= expr_value->null_value)) + DBUG_RETURN(NULL); + DBUG_RETURN(expr_value->val_str(str)); +} + + +/** + Get the decimal value of the possibly cached item +*/ + +my_decimal *Item_cache_wrapper::val_decimal(my_decimal* decimal_value) +{ + Item *cached_value; + DBUG_ENTER("Item_cache_wrapper::val_decimal"); + if (!expr_cache) + { + my_decimal *tmp= orig_item->val_decimal(decimal_value); + null_value= orig_item->null_value; + DBUG_RETURN(tmp); + } + + if ((cached_value= check_cache())) + { + my_decimal *tmp= cached_value->val_decimal(decimal_value); + null_value= cached_value->null_value; + DBUG_RETURN(tmp); + } + cache(); + if ((null_value= expr_value->null_value)) + DBUG_RETURN(NULL); + DBUG_RETURN(expr_value->val_decimal(decimal_value)); +} + + +/** + Get the boolean value of the possibly cached item +*/ + +bool Item_cache_wrapper::val_bool() +{ + Item *cached_value; + DBUG_ENTER("Item_cache_wrapper::val_bool"); + if (!expr_cache) + { + bool tmp= orig_item->val_bool(); + null_value= orig_item->null_value; + DBUG_RETURN(tmp); + } + + if ((cached_value= check_cache())) + { + bool tmp= cached_value->val_bool(); + null_value= cached_value->null_value; + DBUG_RETURN(tmp); + } + cache(); + null_value= expr_value->null_value; + DBUG_RETURN(expr_value->val_bool()); +} + + +/** + Check for NULL the value of the possibly cached item +*/ + +bool Item_cache_wrapper::is_null() +{ + Item *cached_value; + DBUG_ENTER("Item_cache_wrapper::is_null"); + if (!expr_cache) + { + bool tmp= orig_item->is_null(); + null_value= orig_item->null_value; + DBUG_RETURN(tmp); + } + + if ((cached_value= check_cache())) + { + bool tmp= cached_value->is_null(); + null_value= cached_value->null_value; + DBUG_RETURN(tmp); + } + cache(); + DBUG_RETURN((null_value= expr_value->null_value)); +} + + +/** + Get the date value of the possibly cached item +*/ + +bool Item_cache_wrapper::get_date(MYSQL_TIME *ltime, uint fuzzydate) +{ + Item *cached_value; + DBUG_ENTER("Item_cache_wrapper::get_date"); + if (!expr_cache) + DBUG_RETURN((null_value= orig_item->get_date(ltime, fuzzydate))); + + if ((cached_value= check_cache())) + DBUG_RETURN((null_value= cached_value->get_date(ltime, fuzzydate))); + + cache(); + DBUG_RETURN((null_value= expr_value->get_date(ltime, fuzzydate))); +} + + +/** + Get the time value of the possibly cached item +*/ + +bool Item_cache_wrapper::get_time(MYSQL_TIME *ltime) +{ + Item *cached_value; + DBUG_ENTER("Item_cache_wrapper::get_time"); + if (!expr_cache) + DBUG_RETURN((null_value= orig_item->get_time(ltime))); + + if ((cached_value= check_cache())) + DBUG_RETURN((null_value= cached_value->get_time(ltime))); + + cache(); + DBUG_RETURN((null_value= expr_value->get_time(ltime))); +} + + +int Item_cache_wrapper::save_in_field(Field *to, bool no_conversions) +{ + int res; + DBUG_ASSERT(!result_field); + res= orig_item->save_in_field(to, no_conversions); + null_value= orig_item->null_value; + return res; +} + + /** Prepare referenced field then call usual Item_direct_ref::fix_fields . diff --git a/sql/item.h b/sql/item.h index 8cab1c523f2..b627729374d 100644 --- a/sql/item.h +++ b/sql/item.h @@ -507,7 +507,7 @@ public: SUBSELECT_ITEM, ROW_ITEM, CACHE_ITEM, TYPE_HOLDER, PARAM_ITEM, TRIGGER_FIELD_ITEM, DECIMAL_ITEM, XPATH_NODESET, XPATH_NODESET_CMP, - VIEW_FIXER_ITEM}; + VIEW_FIXER_ITEM, EXPR_CACHE_ITEM}; enum cond_result { COND_UNDEF,COND_OK,COND_TRUE,COND_FALSE }; @@ -737,6 +737,17 @@ public: */ virtual bool val_bool(); virtual String *val_nodeset(String*) { return 0; } + + /* + save_val() is method of val_* family which stores value in the given + field. + */ + virtual void save_val(Field *to) { save_org_in_field(to); } + /* + save_result() is method of val*result() family which stores value in + the given field. + */ + virtual void save_result(Field *to) { save_val(to); } /* Helper functions, see item_sum.cc */ String *val_string_from_real(String *str); String *val_string_from_int(String *str); @@ -1092,6 +1103,8 @@ public: virtual Item *neg_transformer(THD *thd) { return NULL; } virtual Item *update_value_transformer(uchar *select_arg) { return this; } + virtual Item *expr_cache_insert_transformer(uchar *thd_arg) { return this; } + virtual bool expr_cache_is_needed(THD *) { return FALSE; } virtual Item *safe_charset_converter(CHARSET_INFO *tocs); void delete_self() { @@ -1145,6 +1158,9 @@ public: { 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); + + Item* set_expr_cache(THD *thd, List<Item*> &depends_on); + virtual Item *get_cached_item() { return NULL; } }; @@ -1524,6 +1540,19 @@ public: */ TABLE_LIST *cached_table; st_select_lex *depended_from; + /* + Some Items resolved in another select should not be marked as dependency + of the subquery where they are. During normal name resolution, we check + this. Stored procedures and prepared statements first try to resolve an + ident item using a cached table reference and field position from the + previous query execution (cached_table/cached_field_index). If the + tables were not changed, the ident matches the table/field, and we have + faster resolution of the ident without looking through all tables and + fields in the query. But in this case, we can not check all conditions + about this ident item dependency, so we should cache the condition in + this variable. + */ + bool can_be_depended; Item_ident(Name_resolution_context *context_arg, const char *db_name_arg, const char *table_name_arg, const char *field_name_arg); @@ -1606,6 +1635,7 @@ public: longlong val_int(); my_decimal *val_decimal(my_decimal *); String *val_str(String*); + void save_result(Field *to); double val_result(); longlong val_int_result(); String *str_result(String* tmp); @@ -1926,8 +1956,6 @@ public: 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;} }; @@ -2389,6 +2417,8 @@ public: Item *it= ((Item *) item)->real_item(); return ref && (*ref)->eq(it, binary_cmp); } + void save_val(Field *to); + void save_result(Field *to); double val_real(); longlong val_int(); my_decimal *val_decimal(my_decimal *); @@ -2519,6 +2549,7 @@ public: alias_name_used_arg) {} + void save_val(Field *to); double val_real(); longlong val_int(); String *val_str(String* tmp); @@ -2529,6 +2560,134 @@ public: virtual Ref_Type ref_type() { return DIRECT_REF; } }; +class Expression_cache; +class Item_cache; + + +/** + The objects of this class can store its values in an expression cache. +*/ + +class Item_cache_wrapper :public Item_result_field +{ +private: + /* Pointer on the cached expression */ + Item *orig_item; + Expression_cache *expr_cache; + /* + In order to put the expression into the expression cache and return + value of val_*() method, we will need to get the expression value twice + (probably in different types). In order to avoid making two + (potentially costly) orig_item->val_*() calls, we store expression value + in this Item_cache object. + */ + Item_cache *expr_value; + + Item *check_cache(); + inline void cache(); + +public: + Item_cache_wrapper(Item *item_arg); + ~Item_cache_wrapper(); + + const char *func_name() const { return "<expr_cache>"; } + enum Type type() const { return EXPR_CACHE_ITEM; } + virtual Item *get_cached_item() { return orig_item; } + + bool set_cache(THD *thd, List<Item*> &depends_on); + + bool fix_fields(THD *thd, Item **it); + void fix_length_and_dec() {} + void cleanup(); + + /* Methods of getting value which should be cached in the cache */ + void save_val(Field *to); + 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 get_time(MYSQL_TIME *ltime); + bool send(Protocol *protocol, String *buffer) + { + if (result_field) + return protocol->store(result_field); + return Item::send(protocol, buffer); + } + void save_org_in_field(Field *field) + { + save_val(field); + } + void save_in_result_field(bool no_conversions) + { + save_val(result_field); + } + + /* Following methods make this item transparent as much as possible */ + + virtual void print(String *str, enum_query_type query_type) + { + /* TODO: maybe print something for EXPLAIN EXTENDED */ + return orig_item->print(str, query_type); + } + virtual const char *full_name() const { return orig_item->full_name(); } + virtual void make_field(Send_field *field) { orig_item->make_field(field); } + bool eq(const Item *item, bool binary_cmp) const + { + Item *it= ((Item *) item)->real_item(); + return orig_item->eq(it, binary_cmp); + } + void fix_after_pullout(st_select_lex *new_parent, Item **refptr) + { + orig_item->fix_after_pullout(new_parent, &orig_item); + } + int save_in_field(Field *to, bool no_conversions); + enum Item_result result_type () const { return orig_item->result_type(); } + enum_field_types field_type() const { return orig_item->field_type(); } + table_map used_tables() const { return orig_item->used_tables(); } + void update_used_tables() { orig_item->update_used_tables(); } + bool const_item() const { return orig_item->const_item(); } + table_map not_null_tables() const { return orig_item->not_null_tables(); } + bool walk(Item_processor processor, bool walk_subquery, uchar *arg) + { + return orig_item->walk(processor, walk_subquery, arg) || + (this->*processor)(arg); + } + bool enumerate_field_refs_processor(uchar *arg) + { return orig_item->enumerate_field_refs_processor(arg); } + bool result_as_longlong() { return orig_item->result_as_longlong(); } + Item_field *filed_for_view_update() + { return orig_item->filed_for_view_update(); } + + /* Row emulation: forwarding of ROW-related calls to orig_item */ + uint cols() + { return result_type() == ROW_RESULT ? orig_item->cols() : 1; } + Item* element_index(uint i) + { return result_type() == ROW_RESULT ? orig_item->element_index(i) : this; } + Item** addr(uint i) + { return result_type() == ROW_RESULT ? orig_item->addr(i) : 0; } + bool check_cols(uint c) + { + return (result_type() == ROW_RESULT ? + orig_item->check_cols(c) : + Item::check_cols(c)); + } + bool null_inside() + { return result_type() == ROW_RESULT ? orig_item->null_inside() : 0; } + void bring_value() + { + if (result_type() == ROW_RESULT) + orig_item->bring_value(); + } + bool check_vcol_func_processor(uchar *arg) + { + return trace_unsupported_by_check_vcol_func_processor("cache"); + } +}; + + /* Class for view fields, the same as Item_direct_ref, but call fix_fields of reference if it is not called yet @@ -2638,6 +2797,7 @@ public: const char *table_name_arg, const char *field_name_arg) :Item_ref(context_arg, item, table_name_arg, field_name_arg), owner(master) {} + void save_val(Field *to); double val_real(); longlong val_int(); String* val_str(String* s); @@ -3172,7 +3332,8 @@ public: 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): @@ -3180,6 +3341,7 @@ public: value_cached(0) { fixed= 1; + maybe_null= 1; null_value= 1; } diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index f4a814b7aee..d382b3b7a4e 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -1750,6 +1750,45 @@ bool Item_in_optimizer::fix_fields(THD *thd, Item **ref) } +/** + Add an expression cache for this subquery if it is needed + + @param thd_arg Thread handle + + @details + The function checks whether an expression cache is needed for this item + and if if so wraps the item into an item of the class + Item_exp_cache_wrapper with an appropriate expression cache set up there. + + @note + used from Item::transform() + + @return + new wrapper item if an expression cache is needed, + this item - otherwise +*/ + +Item *Item_in_optimizer::expr_cache_insert_transformer(uchar *thd_arg) +{ + THD *thd= (THD*) thd_arg; + DBUG_ENTER("Item_in_optimizer::expr_cache_insert_transformer"); + List<Item*> &depends_on= ((Item_subselect *)args[1])->depends_on; + + /* Add left expression to the list of the parameters of the subquery */ + if (args[0]->cols() == 1) + depends_on.push_front((Item**)args); + else + for (int i= 0; i < args[0]->cols(); i++) + { + depends_on.push_front(args[0]->addr(i)); + } + + if (args[1]->expr_cache_is_needed(thd)) + DBUG_RETURN(set_expr_cache(thd, depends_on)); + DBUG_RETURN(this); +} + + longlong Item_in_optimizer::val_int() { bool tmp; diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index e431116a70d..053a54349c2 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -264,6 +264,7 @@ public: Item_cache **get_cache() { return &cache; } void keep_top_level_cache(); Item *transform(Item_transformer transformer, uchar *arg); + virtual Item *expr_cache_insert_transformer(uchar *thd_arg); }; class Comp_creator diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 0d0acdb29e4..454a3929f99 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -93,6 +93,8 @@ void Item_subselect::init(st_select_lex *select_lex, SELECT_LEX *upper= unit->outer_select(); if (upper->parsing_place == IN_HAVING) upper->subquery_in_having= 1; + /* The subquery is an expression cache candidate */ + upper->expr_cache_may_be_used[upper->parsing_place]= TRUE; } DBUG_VOID_RETURN; } @@ -116,6 +118,7 @@ void Item_subselect::cleanup() } if (engine) engine->cleanup(); + depends_on.empty(); reset(); value_assigned= 0; DBUG_VOID_RETURN; @@ -486,6 +489,56 @@ bool Item_subselect::exec() } +/** + Check if an expression cache is needed for this subquery + + @param thd Thread handle + + @details + The function checks whether a cache is needed for a subquery and whether + the result of the subquery can be put in cache. + + @retval TRUE cache is needed + @retval FALSE otherwise +*/ + +bool Item_subselect::expr_cache_is_needed(THD *thd) +{ + return (depends_on.elements && + engine->cols() == 1 && + optimizer_flag(thd, OPTIMIZER_SWITCH_SUBQUERY_CACHE) && + !(engine->uncacheable() & (UNCACHEABLE_RAND | + UNCACHEABLE_SIDEEFFECT))); +} + + +/** + Check if an expression cache is needed for this subquery + + @param thd Thread handle + + @details + The function checks whether a cache is needed for a subquery and whether + the result of the subquery can be put in cache. + + @note + This method allows many columns in the subquery because it is supported by + Item_in optimizer and result of the IN subquery will be scalar in this + case. + + @retval TRUE cache is needed + @retval FALSE otherwise +*/ + +bool Item_in_subselect::expr_cache_is_needed(THD *thd) +{ + return (depends_on.elements && + optimizer_flag(thd, OPTIMIZER_SWITCH_SUBQUERY_CACHE) && + !(engine->uncacheable() & (UNCACHEABLE_RAND | + UNCACHEABLE_SIDEEFFECT))); +} + + /* Compute the IN predicate if the left operand's cache changed. */ @@ -784,6 +837,36 @@ void Item_singlerow_subselect::fix_length_and_dec() maybe_null= engine->may_be_null(); } + +/** + Add an expression cache for this subquery if it is needed + + @param thd_arg Thread handle + + @details + The function checks whether an expression cache is needed for this item + and if if so wraps the item into an item of the class + Item_exp_cache_wrapper with an appropriate expression cache set up there. + + @note + used from Item::transform() + + @return + new wrapper item if an expression cache is needed, + this item - otherwise +*/ + +Item* Item_singlerow_subselect::expr_cache_insert_transformer(uchar *thd_arg) +{ + THD *thd= (THD*) thd_arg; + DBUG_ENTER("Item_singlerow_subselect::expr_cache_insert_transformer"); + + if (expr_cache_is_needed(thd)) + DBUG_RETURN(set_expr_cache(thd, depends_on)); + DBUG_RETURN(this); +} + + uint Item_singlerow_subselect::cols() { return engine->cols(); @@ -976,6 +1059,36 @@ void Item_exists_subselect::fix_length_and_dec() unit->global_parameters->select_limit= new Item_int((int32) 1); } + +/** + Add an expression cache for this subquery if it is needed + + @param thd_arg Thread handle + + @details + The function checks whether an expression cache is needed for this item + and if if so wraps the item into an item of the class + Item_exp_cache_wrapper with an appropriate expression cache set up there. + + @note + used from Item::transform() + + @return + new wrapper item if an expression cache is needed, + this item - otherwise +*/ + +Item* Item_exists_subselect::expr_cache_insert_transformer(uchar *thd_arg) +{ + THD *thd= (THD*) thd_arg; + DBUG_ENTER("Item_exists_subselect::expr_cache_insert_transformer"); + + if (substype() == EXISTS_SUBS && expr_cache_is_needed(thd)) + DBUG_RETURN(set_expr_cache(thd, depends_on)); + DBUG_RETURN(this); +} + + double Item_exists_subselect::val_real() { DBUG_ASSERT(fixed == 1); @@ -3841,6 +3954,7 @@ subselect_uniquesubquery_engine* subselect_hash_sj_engine::make_unique_engine() { Item_in_subselect *item_in= (Item_in_subselect *) item; + Item_iterator_row it(item_in->left_expr); /* The only index on the temporary table. */ KEY *tmp_key= tmp_table->key_info; /* Number of keyparts in tmp_key. */ @@ -3858,41 +3972,9 @@ subselect_hash_sj_engine::make_unique_engine() */ if (!(tab= (JOIN_TAB*) thd->alloc(sizeof(JOIN_TAB)))) DBUG_RETURN(NULL); - tab->table= tmp_table; - 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(NULL); - 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 (uint i= 0; i < tmp_key_parts; i++, cur_key_part++, ref_key++) - { - tab->ref.items[i]= item_in->left_expr->element_index(i); - int null_count= test(cur_key_part->field->real_maybe_null()); - *ref_key= new store_key_item(thd, cur_key_part->field, - /* TIMOUR: - 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_err= 1; - tab->ref.key_parts= tmp_key_parts; + tab->table= tmp_table; + tab->ref.tmp_table_index_lookup_init(thd, tmp_key, it, FALSE); DBUG_RETURN(new subselect_uniquesubquery_engine(thd, tab, item, semi_join_conds)); diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 2e7e117096e..72f73d58867 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -88,11 +88,19 @@ public: */ List<Ref_to_outside> 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<Item*> 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 */ @@ -185,6 +193,7 @@ public: */ st_select_lex* get_select_lex(); const char *func_name() const { DBUG_ASSERT(0); return "subselect"; } + virtual bool expr_cache_is_needed(THD *); friend class select_result_interceptor; friend class Item_in_optimizer; @@ -243,6 +252,8 @@ public: */ st_select_lex* invalidate_and_restore_select_lex(); + Item* expr_cache_insert_transformer(uchar *thd_arg); + friend class select_singlerow_subselect; }; @@ -290,6 +301,8 @@ public: void fix_length_and_dec(); virtual void print(String *str, enum_query_type query_type); + Item* expr_cache_insert_transformer(uchar *thd_arg); + friend class select_exists_subselect; friend class subselect_uniquesubquery_engine; friend class subselect_indexsubquery_engine; @@ -434,6 +447,7 @@ public: /* Inform 'this' that it was computed, and contains a valid result. */ void set_first_execution() { if (first_execution) first_execution= FALSE; } bool is_expensive_processor(uchar *arg); + bool expr_cache_is_needed(THD *thd); /* Return the identifier that we could use to identify the subquery for the @@ -813,7 +827,7 @@ public: { 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; } diff --git a/sql/item_sum.cc b/sql/item_sum.cc index 89441a29a76..75d98bc6d2a 100644 --- a/sql/item_sum.cc +++ b/sql/item_sum.cc @@ -319,6 +319,7 @@ bool Item_sum::register_sum_func(THD *thd, Item **ref) 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; diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h index 024d20472c3..ba60bab9b50 100644 --- a/sql/mysql_priv.h +++ b/sql/mysql_priv.h @@ -570,12 +570,13 @@ protected: #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 @@ -590,7 +591,8 @@ protected: 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 | \ @@ -603,7 +605,8 @@ protected: 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 /* @@ -683,7 +686,8 @@ enum enum_parsing_place SELECT_LIST, IN_WHERE, IN_ON, - IN_GROUP_BY + IN_GROUP_BY, + PARSING_PLACE_SIZE /* always should be the last */ }; struct st_table; @@ -925,6 +929,7 @@ bool general_log_write(THD *thd, enum enum_server_command command, #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 @@ -1258,6 +1263,9 @@ bool mysql_select(THD *thd, Item ***rref_pointer_array, Item *having, ORDER *proc_param, ulonglong select_type, select_result *result, SELECT_LEX_UNIT *unit, SELECT_LEX *select_lex); + +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); @@ -1277,6 +1285,7 @@ Field *create_tmp_field(THD *thd, TABLE *table,Item *item, Item::Type type, 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, @@ -1832,7 +1841,7 @@ bool close_cached_connection_tables(THD *thd, bool wait_for_refresh, bool have_lock = FALSE); void copy_field_from_tmp_record(Field *field,int offset); bool fill_record(THD *thd, Field **field, List<Item> &values, - bool ignore_errors); + bool ignore_errors, bool use_value); bool fill_record_n_invoke_before_triggers(THD *thd, List<Item> &fields, List<Item> &values, bool ignore_errors, diff --git a/sql/mysqld.cc b/sql/mysqld.cc index 55cb1b3b6fd..5bc26154790 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -307,6 +307,7 @@ static const char *optimizer_switch_names[]= "firstmatch","loosescan","materialization", "semijoin", "partial_match_rowid_merge", "partial_match_table_scan", + "subquery_cache", #ifndef DBUG_OFF "table_elimination", #endif @@ -327,6 +328,7 @@ static const unsigned int optimizer_switch_names_len[]= 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 @@ -406,8 +408,9 @@ static const char *sql_mode_str= "OFF"; 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 ; @@ -5890,7 +5893,9 @@ enum options_mysqld 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, @@ -7222,7 +7227,7 @@ The minimum value for this variable is 4096.", {"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 @@ -7936,6 +7941,12 @@ SHOW_VAR status_vars[]= { {"Ssl_version", (char*) &show_ssl_get_version, SHOW_FUNC}, #endif /* HAVE_OPENSSL */ {"Syncs", (char*) &my_sync_count, SHOW_LONG_NOFLUSH}, + /* + Expression cache used only for caching subqueries now, so its statistic + variables we call subquery_cache*. + */ + {"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 @@ -8075,6 +8086,7 @@ static int mysql_init_variables(void) 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; diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 0d8a0a6d73e..83abf3beac8 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -9569,7 +9569,9 @@ check_group_min_max_predicates(COND *cond, Item_field *min_max_arg_item, the MIN/MAX argument field, and disallow the optimization only if this is so. */ - if (cond_type == Item::SUBSELECT_ITEM) + if (cond_type == Item::SUBSELECT_ITEM || + (cond->get_cached_item() && + cond->get_cached_item()->type() == Item::SUBSELECT_ITEM)) DBUG_RETURN(FALSE); /* diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 1855224440c..8567d291341 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -2297,7 +2297,8 @@ bool setup_sj_materialization(JOIN_TAB *tab) */ cur_ref_buff + null_count, null_count ? tab_ref->key_buff : 0, - cur_key_part->length, tab_ref->items[i]); + cur_key_part->length, tab_ref->items[i], + FALSE); cur_ref_buff+= cur_key_part->store_length; } *ref_key= NULL; /* End marker. */ diff --git a/sql/procedure.h b/sql/procedure.h index 30a8a0efccb..488d461905e 100644 --- a/sql/procedure.h +++ b/sql/procedure.h @@ -13,6 +13,8 @@ along with this program; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */ +#ifndef PROCEDURE_INCLUDED +#define PROCEDURE_INCLUDED /* When using sql procedures */ @@ -153,3 +155,5 @@ public: Procedure *setup_procedure(THD *thd,ORDER *proc_param,select_result *result, List<Item> &field_list,int *error); + +#endif /* PROCEDURE_INCLUDED */ diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 89a3ce7a5fc..a93b48bfe94 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -1375,10 +1375,10 @@ bool close_thread_table(THD *thd, TABLE **table_ptr) bool found_old_table= 0; TABLE *table= *table_ptr; DBUG_ENTER("close_thread_table"); - DBUG_ASSERT(table->key_read == 0); - DBUG_ASSERT(!table->file || table->file->inited == handler::NONE); DBUG_PRINT("tcache", ("table: '%s'.'%s' 0x%lx", table->s->db.str, table->s->table_name.str, (long) table)); + DBUG_ASSERT(table->key_read == 0); + DBUG_ASSERT(!table->file || table->file->inited == handler::NONE); if (table->file) { @@ -6328,12 +6328,21 @@ find_field_in_tables(THD *thd, Item_ident *item, sub query as dependent on the outer query */ if (current_sel != last_select) + { mark_select_range_as_dependent(thd, last_select, current_sel, found, *ref, item); + if (item->can_be_depended) + { + DBUG_ASSERT((*ref) == (Item*)item); + current_sel->register_dependency_item(last_select, ref); + } + } } return found; } } + else + item->can_be_depended= TRUE; if (db && lower_case_table_names) { @@ -8083,6 +8092,10 @@ int setup_conds(THD *thd, TABLE_LIST *tables, TABLE_LIST *leaves, 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; @@ -8341,6 +8354,7 @@ fill_record_n_invoke_before_triggers(THD *thd, List<Item> &fields, ptr pointer on pointer to record values list of fields ignore_errors TRUE if we should ignore errors + use_value forces usage of value of the items instead of result NOTE fill_record() may set table->auto_increment_field_not_null and a @@ -8353,7 +8367,8 @@ fill_record_n_invoke_before_triggers(THD *thd, List<Item> &fields, */ bool -fill_record(THD *thd, Field **ptr, List<Item> &values, bool ignore_errors) +fill_record(THD *thd, Field **ptr, List<Item> &values, bool ignore_errors, + bool use_value) { List_iterator_fast<Item> v(values); List<TABLE> tbl_list; @@ -8395,8 +8410,11 @@ fill_record(THD *thd, Field **ptr, List<Item> &values, bool ignore_errors) field->field_name, table->s->table_name.str); thd->abort_on_warning= abort_on_warning_saved; } - if (value->save_in_field(field, 0) < 0) - goto err; + if (use_value) + value->save_val(field); + else + if (value->save_in_field(field, 0) < 0) + goto err; tbl_list.push_back(table); } /* Update virtual fields*/ @@ -8465,7 +8483,7 @@ fill_record_n_invoke_before_triggers(THD *thd, Field **ptr, enum trg_event_type event) { bool result; - result= (fill_record(thd, ptr, values, ignore_errors) || + result= (fill_record(thd, ptr, values, ignore_errors, FALSE) || (triggers && triggers->process_triggers(thd, event, TRG_ACTION_BEFORE, TRUE))); /* diff --git a/sql/sql_class.cc b/sql/sql_class.cc index 9cc66d4d8be..c85ea76c70d 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -3052,6 +3052,7 @@ void TMP_TABLE_PARAM::init() table_charset= 0; precomputed_group_by= 0; bit_fields_as_long= 0; + skip_create_table= 0; DBUG_VOID_RETURN; } diff --git a/sql/sql_class.h b/sql/sql_class.h index 932f2234766..6ef330e8ee6 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -23,6 +23,76 @@ #include "log.h" #include "rpl_tblmap.h" + +/** + Interface for Item iterator +*/ + +class Item_iterator +{ +public: + /** + Shall set this iterator to the position before the first item + + @note + This method also may perform some other initialization actions like + allocation of certain resources. + */ + virtual void open()= 0; + /** + Shall return the next Item (or NULL if there is no next item) and + move pointer to position after it. + */ + virtual Item *next()= 0; + /** + Shall force iterator to free resources (if it holds them) + + @note + One should not use the iterator without open() call after close() + */ + virtual void close()= 0; + + virtual ~Item_iterator() {} +}; + + +/** + Item iterator over List_iterator_fast for Item references +*/ + +class Item_iterator_ref_list: public Item_iterator +{ + List_iterator_fast<Item*> list; +public: + Item_iterator_ref_list(List_iterator_fast<Item*> &arg_list): + list(arg_list) {} + void open() { list.rewind(); } + Item *next() { return *(list++); } + void close() {} +}; + + +/** + Item iterator over Item interface for rows +*/ + +class Item_iterator_row: public Item_iterator +{ + Item *base_item; + uint current; +public: + Item_iterator_row(Item *base) : base_item(base), current(0) {} + void open() { current= 0; } + Item *next() + { + if (current >= base_item->cols()) + return NULL; + return base_item->element_index(current++); + } + void close() {} +}; + + /** An interface that is used to take an action when the locking module notices that a table version has changed @@ -2796,12 +2866,17 @@ public: 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() { diff --git a/sql/sql_expression_cache.cc b/sql/sql_expression_cache.cc new file mode 100644 index 00000000000..70c36b141b6 --- /dev/null +++ b/sql/sql_expression_cache.cc @@ -0,0 +1,314 @@ + +#include "mysql_priv.h" +#include "sql_select.h" + +/* + Expression cache is used only for caching subqueries now, so its statistic + variables we call subquery_cache*. +*/ +ulonglong subquery_cache_miss, subquery_cache_hit; + +Expression_cache_tmptable::Expression_cache_tmptable(THD *thd, + List<Item*> &dependants, + Item *value) + :cache_table(NULL), table_thd(thd), list(&dependants), val(value), + equalities(NULL), inited (0) +{ + DBUG_ENTER("Expression_cache_tmptable::Expression_cache_tmptable"); + DBUG_VOID_RETURN; +}; + + +/** + Build and of equalities for the expression's parameters of certain types + + @details + If the temporary table used as an expression cache contains fields of + certain types then it's not enough to perform a lookup into the table to + verify that there is no row in the table for a given set of parameters. + Additionally for those fields we have to check equalities of the form + fld=val, where val is the value of the parameter stored in the column + fld. + The function generates a conjunction of all such equality predicates + and saves a pointer to it in the field 'equalities'. + + @retval FALSE OK + @retval TRUE Error +*/ + +bool Expression_cache_tmptable::make_equalities() +{ + List<Item> args; + List_iterator_fast<Item*> 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)); +} + + +/** + Field enumerator for TABLE::add_tmp_key + + @param arg reference variable with current field number + + @return field number +*/ + +static uint field_enumerator(uchar *arg) +{ + return ((uint*)arg)[0]++; +} + + +/** + Initialize temporary table and auxiliary structures for the expression + cache + + @details + The function creates a temporary table for the expression cache, defines + the search index and initializes auxiliary search structures used to check + whether a given set of of values of the expression parameters is in some + cache entry. +*/ + +void Expression_cache_tmptable::init() +{ + List_iterator_fast<Item*> li(*list); + Item_iterator_ref_list it(li); + 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; + + cache_table_param.init(); + /* dependent items and result */ + cache_table_param.field_count= list->elements + 1; + /* postpone table creation to index description */ + cache_table_param.skip_create_table= 1; + cache_table= NULL; + + while ((item= li++)) + { + DBUG_ASSERT(item); + DBUG_ASSERT(*item); + DBUG_ASSERT((*item)->fixed); + items.push_back((*item)); + } + 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; + } + + /* This list do not contain result field */ + it.open(); + + field_counter=1; + + if (cache_table->alloc_keys(1) || + (cache_table->add_tmp_key(0, items.elements - 1, + &field_enumerator, + (uchar*)&field_counter) < 0) || + ref.tmp_table_index_lookup_init(table_thd, cache_table->key_info, it, + TRUE)) + { + DBUG_PRINT("error", ("creating index failed")); + goto error; + } + cache_table->s->keys= 1; + cache_table->s->uniques= 1; + ref.null_rejecting= 1; + ref.disable_cache= FALSE; + ref.has_record= 0; + ref.use_count= 0; + + + 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; +} + + +Expression_cache_tmptable::~Expression_cache_tmptable() +{ + if (cache_table) + free_tmp_table(table_thd, cache_table); +} + + +/** + Check if a given set of parameters of the expression is in the cache + + @param [out] value the expression value found in the cache if any + + @details + For a given set of the parameters of the expression the function + checks whether it can be found in some entry of the cache. If so + the function returns the result of the expression extracted from + the cache. + + @retval Expression_cache::HIT if the set of parameters is in the cache + @retval Expression_cache::MISS - otherwise +*/ + +Expression_cache::result Expression_cache_tmptable::check_value(Item **value) +{ + int res; + DBUG_ENTER("Expression_cache_tmptable::check_value"); + + /* + We defer cache initialization to get item references that are + used at the execution phase. + */ + if (!inited) + init(); + + if (cache_table) + { + DBUG_PRINT("info", ("status: %u has_record %u", + (uint)cache_table->status, (uint)ref.has_record)); + if ((res= join_read_key2(table_thd, NULL, cache_table, &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); +} + + +/** + Put a new entry into the expression cache + + @param value the result of the expression to be put into the cache + + @details + The function evaluates 'value' and puts the result into the cache as the + result of the expression for the current set of parameters. + + @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, TRUE, TRUE); + 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 */ + 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); +} diff --git a/sql/sql_expression_cache.h b/sql/sql_expression_cache.h new file mode 100644 index 00000000000..bb5dc7a76a2 --- /dev/null +++ b/sql/sql_expression_cache.h @@ -0,0 +1,80 @@ +#ifndef SQL_EXPRESSION_CACHE_INCLUDED +#define SQL_EXPRESSION_CACHE_INCLUDED + +#include "sql_select.h" + +/** + Interface for expression cache + + @note + Parameters of an expression cache interface are set on the creation of the + cache. They are passed when a cache object of the implementation class is + constructed. That's why they are not visible in 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() {}; + /** + Shall check the presence of expression value in the cache for a given + set of values of the expression parameters. Return the result of the + expression if it's found in the cache. + */ + virtual result check_value(Item **value)= 0; + /** + Shall put the value of an expression for given set of its parameters + into the expression cache + */ + virtual my_bool put_value(Item *value)= 0; +}; + +struct st_table_ref; +struct st_join_table; +class Item_field; + + +/** + Implementation of expression cache over a temporary table +*/ + +class Expression_cache_tmptable :public Expression_cache +{ +public: + Expression_cache_tmptable(THD *thd, List<Item*> &dependants, 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 handle for the temporary table */ + THD *table_thd; + /* TABLE_REF for index lookup */ + struct st_table_ref ref; + /* Cached result */ + Item_field *cached_result; + /* List of references to the parameters of the expression */ + List<Item*> *list; + /* List of items */ + List<Item> items; + /* Value Item example */ + Item *val; + /* Expression to check after index lookup */ + Item *equalities; + /* Set on if the object has been succesfully initialized with init() */ + bool inited; +}; + +#endif /* SQL_EXPRESSION_CACHE_INCLUDED */ diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 0c4a6210231..7b19b4874b1 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -1632,6 +1632,8 @@ void st_select_lex::init_query() nest_level= 0; link_next= 0; lock_option= TL_READ_DEFAULT; + + bzero((char*) expr_cache_may_be_used, sizeof(expr_cache_may_be_used)); } void st_select_lex::init_select() @@ -1829,6 +1831,55 @@ void st_select_lex_unit::exclude_tree() } +/** + Register reference to an item which the subqueries depends on + + @param def_sel select against which the item is resolved + @param dependency reference to the item + + @details + This function puts the reference dependency to an item that is either an + outer field or an aggregate function resolved against an outer select into + the list 'depends_on'. It adds it to the 'depends_on' lists for each + subquery between this one and 'def_sel' - the subquery against which the + item is resolved. +*/ + +void st_select_lex::register_dependency_item(st_select_lex *def_sel, + Item **dependency) +{ + SELECT_LEX *s= this; + DBUG_ENTER("st_select_lex::register_dependency_item"); + DBUG_ASSERT(this != def_sel); + DBUG_ASSERT(*dependency); + do + { + /* check duplicates */ + List_iterator_fast<Item*> 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 : + "<no 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 : + "<no name>"))); + } while ((s= s->outer_select()) != def_sel); + DBUG_VOID_RETURN; +} + + /* st_select_lex_node::mark_as_dependent mark all st_select_lex struct from this to 'last' as dependent diff --git a/sql/sql_lex.h b/sql/sql_lex.h index af007b713ca..335aa7f4ed4 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -661,6 +661,11 @@ public: /* explicit LIMIT clause was used */ bool explicit_limit; /* + This array is used to note whether we have any candidates for + expression caching in the corresponding clauses + */ + bool expr_cache_may_be_used[PARSING_PLACE_SIZE]; + /* there are subquery in HAVING clause => we can't close tables before query processing end even if we use temporary table */ @@ -749,6 +754,7 @@ public: inline bool is_subquery_function() { return master_unit()->item != 0; } 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(); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 731f4a5901f..887196fed9b 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -152,7 +152,6 @@ static int join_read_const_table(JOIN_TAB *tab, POSITION *pos); 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); @@ -1293,6 +1292,9 @@ JOIN::optimize() int res; if ((res= rewrite_to_index_subquery_engine(this)) != -1) DBUG_RETURN(res); + if (setup_subquery_caches()) + DBUG_RETURN(-1); + /* Need to tell handlers that to play it safe, it should fetch all columns of the primary key of the tables: this is because MySQL may @@ -1515,6 +1517,152 @@ setup_subq_exit: /** + Transform given item with changing it in all_fields if it is needed. + + @param expr Reference on expression to transform + @param transformer Transformer to apply to the expression + + @details + The function transforms the expression expr and, if the top item of the + expression has changed, the function looks for the item in + JOIN::all_fields and replaces it with the result of transformation. +*/ + +void JOIN::transform_and_change_in_all_fields(Item** expr, + Item_transformer transformer) +{ + DBUG_ENTER("JOIN::transform_and_change_in_all_fields"); + Item *new_item= (*expr)->transform(transformer, + (uchar*) thd); + if (new_item != (*expr)) + { + List_iterator<Item> li(all_fields); + Item *it; + + /* + Check if this item already has expression cache and if it has then use + that cache instead of the cache we have just created + */ + while ((it= li++)) + { + if (((*expr) == it->get_cached_item())) + { + /* + We have to forget about the created cache, but this situation is + really rare. + */ + new_item->cleanup(); + new_item= it; + DBUG_PRINT("info", ("Other cache found")); + break; + } + } + + li.rewind(); + while ((it= li++)) + { + if (it == (*expr)) + { + li.replace(new_item); + DBUG_PRINT("info", ("Cache Added")); + } + } + *expr= new_item; + } + DBUG_VOID_RETURN; +} + + +/** + Setup expression caches for subqueries that need them + + @details + The function wraps correlated subquery expressions that return one value + into objects of the class Item_cache_wrapper setting up an expression + cache for each of them. The result values of the subqueries are to be + cached together with the corresponding sets of the parameters - outer + references of the subqueries. + + @retval FALSE OK + @retval TRUE Error +*/ + +bool JOIN::setup_subquery_caches() +{ + DBUG_ENTER("JOIN::setup_subquery_caches"); + + /* + We have to check all this condition together because items created in + one of this clauses can be moved to another one by optimizer + */ + if (select_lex->expr_cache_may_be_used[IN_WHERE] || + select_lex->expr_cache_may_be_used[IN_HAVING] || + select_lex->expr_cache_may_be_used[IN_ON] || + select_lex->expr_cache_may_be_used[NO_MATTER]) + { + if (conds) + conds= conds->transform(&Item::expr_cache_insert_transformer, + (uchar*) thd); + for (JOIN_TAB *tab= join_tab + const_tables; + tab < join_tab + tables ; + tab++) + { + if (tab->select_cond) + tab->select_cond= + tab->select_cond->transform(&Item::expr_cache_insert_transformer, + (uchar*) thd); + if (tab->cache_select && tab->cache_select->cond) + tab->cache_select->cond= + tab->cache_select-> + cond->transform(&Item::expr_cache_insert_transformer, + (uchar*) thd); + + } + + if (having) + having= having->transform(&Item::expr_cache_insert_transformer, + (uchar*) thd); + if (tmp_having) + { + DBUG_ASSERT(having == NULL); + tmp_having= tmp_having->transform(&Item::expr_cache_insert_transformer, + (uchar*) thd); + } + } + if (select_lex->expr_cache_may_be_used[SELECT_LIST] || + select_lex->expr_cache_may_be_used[IN_GROUP_BY] || + select_lex->expr_cache_may_be_used[NO_MATTER]) + { + List_iterator<Item> li(fields_list); + Item *item; + while ((item= li++)) + { + Item *new_item= + item->transform(&Item::expr_cache_insert_transformer, (uchar*) thd); + if (new_item != item) + { + thd->change_item_tree(li.ref(), new_item); + } + } + for (ORDER *group= group_list; group ; group= group->next) + { + transform_and_change_in_all_fields(group->item, + &Item::expr_cache_insert_transformer); + } + } + if (select_lex->expr_cache_may_be_used[NO_MATTER]) + { + for (ORDER *ord= order; ord; ord= ord->next) + { + transform_and_change_in_all_fields(ord->item, + &Item::expr_cache_insert_transformer); + } + } + DBUG_RETURN(FALSE); +} + + +/** Restore values in temporary join. */ void JOIN::restore_tmp() @@ -5244,7 +5392,7 @@ greedy_search(JOIN *join, '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); @@ -6035,7 +6183,8 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse, store_key_item tmp(thd, keyinfo->key_part[i].field, key_buff + maybe_null, maybe_null ? key_buff : 0, - keyinfo->key_part[i].length, keyuse->val); + keyinfo->key_part[i].length, keyuse->val, + FALSE); if (thd->is_fatal_error) DBUG_RETURN(TRUE); tmp.copy(); @@ -6117,7 +6266,7 @@ get_store_key(THD *thd, KEYUSE *keyuse, table_map used_tables, key_buff + maybe_null, maybe_null ? key_buff : 0, key_part->length, - keyuse->val); + keyuse->val, FALSE); } /** @@ -7156,7 +7305,7 @@ end_sj_materialize(JOIN *join, JOIN_TAB *join_tab, bool end_of_records) if (item->is_null()) DBUG_RETURN(NESTED_LOOP_OK); } - fill_record(thd, table->field, sjm->sjm_table_cols, 1); + fill_record(thd, table->field, sjm->sjm_table_cols, TRUE, FALSE); if (thd->is_error()) DBUG_RETURN(NESTED_LOOP_ERROR); /* purecov: inspected */ if ((error= table->file->ha_write_row(table->record[0]))) @@ -7722,6 +7871,72 @@ void JOIN_TAB::cleanup() /** + Build a TABLE_REF structure for index lookup in the temporary table + + @param thd Thread handle + @param tmp_key The temporary table key + @param it The iterator of items for lookup in the key + + @details + Build TABLE_REF object for lookup in the key 'tmp_key' using items + accessible via item iterator 'it'. + + @retval TRUE Error + @retval FALSE OK +*/ + +bool TABLE_REF::tmp_table_index_lookup_init(THD *thd, + KEY *tmp_key, + Item_iterator &it, + bool value) +{ + uint tmp_key_parts= tmp_key->key_parts; + DBUG_ENTER("TABLE_REF::tmp_table_index_lookup_init"); + + key= 0; /* The only temp table index. */ + key_length= tmp_key->key_length; + if (!(key_buff= + (uchar*) thd->calloc(ALIGN_SIZE(tmp_key->key_length) * 2)) || + !(key_copy= + (store_key**) thd->alloc((sizeof(store_key*) * + (tmp_key_parts + 1)))) || + !(items= + (Item**) thd->alloc(sizeof(Item*) * tmp_key_parts))) + DBUG_RETURN(TRUE); + + key_buff2= key_buff + ALIGN_SIZE(tmp_key->key_length); + + KEY_PART_INFO *cur_key_part= tmp_key->key_part; + store_key **ref_key= key_copy; + uchar *cur_ref_buff= key_buff; + + it.open(); + for (uint i= 0; i < tmp_key_parts; i++, cur_key_part++, ref_key++) + { + Item *item= it.next(); + DBUG_ASSERT(item); + 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, + /* TIMOUR: + 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 ? key_buff : 0, + cur_key_part->length, items[i], value); + cur_ref_buff+= cur_key_part->store_length; + } + *ref_key= NULL; /* End marker. */ + key_err= 1; + key_parts= tmp_key_parts; + DBUG_RETURN(FALSE); +} + + +/** Partially cleanup JOIN after it has executed: close index or rnd read (table cursors), free quick selects. @@ -10829,6 +11044,8 @@ Field *create_tmp_field(THD *thd, TABLE *table,Item *item, Item::Type type, case Item::REF_ITEM: case Item::NULL_ITEM: case Item::VARBIN_ITEM: + case Item::CACHE_ITEM: + case Item::EXPR_CACHE_ITEM: if (make_copy_field) { DBUG_ASSERT(((Item_result_field*)item)->result_field); @@ -11089,6 +11306,8 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields, if (item->used_tables() & OUTER_REF_TABLE_BIT) item->update_used_tables(); if (type == Item::SUBSELECT_ITEM || + (item->get_cached_item() && + item->get_cached_item()->type() == Item::SUBSELECT_ITEM ) || (item->used_tables() & ~OUTER_REF_TABLE_BIT)) { /* @@ -11616,7 +11835,8 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields, ¶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; @@ -12609,7 +12829,8 @@ sub_select_sjm(JOIN *join, JOIN_TAB *join_tab, bool end_of_records) 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); @@ -13409,25 +13630,24 @@ join_read_const(JOIN_TAB *tab) 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; if (!table->file->inited) { - table->file->ha_index_init(table_ref->key, tab->sorted); + table->file->ha_index_init(table_ref->key, (tab ? tab->sorted : TRUE)); } /* 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) @@ -13439,10 +13659,10 @@ join_read_key2(JOIN_TAB *tab, TABLE *table, TABLE_REF *table_ref) 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 (tab && tab->ref.has_record && tab->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, @@ -13453,14 +13673,14 @@ join_read_key2(JOIN_TAB *tab, TABLE *table, TABLE_REF *table_ref) 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; @@ -16960,6 +17180,8 @@ setup_copy_fields(THD *thd, TMP_TABLE_PARAM *param, } else if ((real_pos->type() == Item::FUNC_ITEM || real_pos->type() == Item::SUBSELECT_ITEM || + (real_pos->get_cached_item() && + real_pos->get_cached_item()->type() == Item::SUBSELECT_ITEM) || real_pos->type() == Item::CACHE_ITEM || real_pos->type() == Item::COND_ITEM) && !real_pos->with_sum_func) diff --git a/sql/sql_select.h b/sql/sql_select.h index b75813fcbcb..2b7fc8fd47c 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -14,6 +14,8 @@ Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */ +#ifndef SQL_SELECT_INCLUDED +#define SQL_SELECT_INCLUDED /** @file @@ -114,6 +116,9 @@ typedef struct st_table_ref */ bool disable_cache; + + bool tmp_table_index_lookup_init(THD *thd, KEY *tmp_key, Item_iterator &it, + bool value); } TABLE_REF; @@ -1729,6 +1734,7 @@ public: ((group || tmp_table_param.sum_func_count) && !group_list)) ? NULL : join_tab+const_tables; } + bool setup_subquery_caches(); private: /** TRUE if the query contains an aggregate function but has no GROUP @@ -1736,6 +1742,8 @@ private: */ bool implicit_grouping; bool make_simple_join(JOIN *join, TABLE *tmp_table); + void transform_and_change_in_all_fields(Item** item, + Item_transformer transformer); }; @@ -1867,12 +1875,17 @@ class store_key_item :public store_key { protected: Item *item; + /* + Flag that forces usage of save_val() method which save value of the + item instead of save_in_field() method which saves result. + */ + bool use_value; public: store_key_item(THD *thd, Field *to_field_arg, uchar *ptr, - uchar *null_ptr_arg, uint length, Item *item_arg) + uchar *null_ptr_arg, uint length, Item *item_arg, bool val) :store_key(thd, to_field_arg, ptr, null_ptr_arg ? null_ptr_arg : item_arg->maybe_null ? - &err : (uchar*) 0, length), item(item_arg) + &err : (uchar*) 0, length), item(item_arg), use_value(val) {} const char *name() const { return "func"; } @@ -1882,7 +1895,11 @@ public: TABLE *table= to_field->table; my_bitmap_map *old_map= dbug_tmp_use_all_columns(table, table->write_set); - int res= item->save_in_field(to_field, 1); + int res= FALSE; + if (use_value) + item->save_val(to_field); + else + res= item->save_in_field(to_field, 1); /* Item::save_in_field() may call Item::val_xxx(). And if this is a subquery we need to check for errors executing it and react accordingly @@ -1906,7 +1923,7 @@ public: Item *item_arg) :store_key_item(thd, to_field_arg,ptr, null_ptr_arg ? null_ptr_arg : item_arg->maybe_null ? - &err : (uchar*) 0, length, item_arg), inited(0) + &err : (uchar*) 0, length, item_arg, FALSE), inited(0) { } const char *name() const { return "const"; } @@ -1997,6 +2014,4 @@ bool create_internal_tmp_table(TABLE *table, KEY *keyinfo, bool open_tmp_table(TABLE *table); void setup_tmp_table_column_bitmaps(TABLE *table, uchar *bitmaps); - - - +#endif /* SQL_SELECT_INCLUDED */ diff --git a/sql/sql_union.cc b/sql/sql_union.cc index ee9ff833726..21fe3de4c17 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -57,7 +57,7 @@ bool select_union::send_data(List<Item> &values) unit->offset_limit_cnt--; return 0; } - fill_record(thd, table->field, values, 1); + fill_record(thd, table->field, values, TRUE, FALSE); if (thd->is_error()) return 1; diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 3b3063063af..a7d12ce6efd 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -1866,7 +1866,7 @@ bool multi_update::send_data(List<Item> ¬_used_values) /* Store regular updated fields in the row. */ fill_record(thd, tmp_table->field + 1 + unupdated_check_opt_tables.elements, - *values_for_table[offset], 1); + *values_for_table[offset], TRUE, FALSE); /* Write row, ignoring duplicated updates to a row */ error= tmp_table->file->ha_write_row(tmp_table->record[0]); diff --git a/sql/table.cc b/sql/table.cc index 177dfc6fec1..3a4ea3064be 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -21,6 +21,8 @@ #include "create_options.h" #include <m_ctype.h> #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")}; @@ -5130,6 +5132,126 @@ void st_table::mark_virtual_columns_for_write(void) file->column_bitmaps_signal(); } + +/** + Allocate 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); +} + + +/** + Add a key to a temporary table + + @param key the number of the key + @param key_parts number of components of the key + @param next_field_no the call-back function that returns the number of + the field used as the next component of the key + @param arg the argument for the above function + + @details + The function adds a new key to the table that is assumed to be + temprary table. The call-back function must at each call must return + the number of the field that used as next component of this key + + @return FALSE is a success + @return TRUE if a failure +*/ + +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; +} + + /** @brief Check if this is part of a MERGE table with attached children. diff --git a/sql/table.h b/sql/table.h index be15c2059a7..5102955e2ad 100644 --- a/sql/table.h +++ b/sql/table.h @@ -785,6 +785,7 @@ struct st_table { 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 */ @@ -917,6 +918,9 @@ struct st_table { */ 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 is_children_attached(void); inline void enable_keyread() { diff --git a/storage/maria/ha_maria.cc b/storage/maria/ha_maria.cc index 09b52ffb4a4..6375f01237e 100644 --- a/storage/maria/ha_maria.cc +++ b/storage/maria/ha_maria.cc @@ -994,6 +994,8 @@ int ha_maria::open(const char *name, int mode, uint test_if_locked) int ha_maria::close(void) { MARIA_HA *tmp= file; + if (!tmp) + return 0; file= 0; return maria_close(tmp); } diff --git a/storage/maria/ha_maria.h b/storage/maria/ha_maria.h index c2ff99fab0e..6b9ea150ef1 100644 --- a/storage/maria/ha_maria.h +++ b/storage/maria/ha_maria.h @@ -13,6 +13,8 @@ along with this program; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */ +#ifndef HA_MARIA_INCLUDED +#define HA_MARIA_INCLUDED #ifdef USE_PRAGMA_INTERFACE #pragma interface /* gcc class implementation */ @@ -189,3 +191,5 @@ private: DsMrr_impl ds_mrr; friend ICP_RESULT index_cond_func_maria(void *arg); }; + +#endif /* HA_MARIA_INCLUDED */ diff --git a/storage/myisam/ha_myisam.cc b/storage/myisam/ha_myisam.cc index b8c612e0731..8f30f31f0db 100644 --- a/storage/myisam/ha_myisam.cc +++ b/storage/myisam/ha_myisam.cc @@ -770,6 +770,8 @@ int ha_myisam::open(const char *name, int mode, uint test_if_locked) int ha_myisam::close(void) { MI_INFO *tmp=file; + if (!tmp) + return 0; file=0; return mi_close(tmp); } |