[Maria-developers] Rev 2789: Subquery cache for pre-review (MWL#66) in file:///home/bell/maria/bzr/work-maria-5.3-scache2/

At file:///home/bell/maria/bzr/work-maria-5.3-scache2/ ------------------------------------------------------------ revno: 2789 revision-id: sanja@askmonty.org-20100531212554-oal32d5v360l6cul parent: sergii@pisem.net-20100510134608-oyi2vznyghgcrt0x committer: sanja@askmonty.org branch nick: work-maria-5.3-scache2 timestamp: Tue 2010-06-01 00:25:54 +0300 message: Subquery cache for pre-review (MWL#66) === modified file 'libmysqld/Makefile.am' --- a/libmysqld/Makefile.am 2010-03-20 12:01:47 +0000 +++ b/libmysqld/Makefile.am 2010-05-31 21:25:54 +0000 @@ -80,7 +80,8 @@ sql_tablespace.cc \ rpl_injector.cc my_user.c partition_info.cc \ sql_servers.cc event_parse_data.cc opt_table_elimination.cc \ - multi_range_read.cc opt_index_cond_pushdown.cc + multi_range_read.cc opt_index_cond_pushdown.cc \ + sql_subquery_cache.cc libmysqld_int_a_SOURCES= $(libmysqld_sources) nodist_libmysqld_int_a_SOURCES= $(libmysqlsources) $(sqlsources) === modified file 'mysql-test/r/index_merge_myisam.result' --- a/mysql-test/r/index_merge_myisam.result 2010-03-20 12:01:47 +0000 +++ b/mysql-test/r/index_merge_myisam.result 2010-05-31 21:25:54 +0000 @@ -1419,19 +1419,19 @@ # select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='index_merge=off,index_merge_union=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='index_merge_union=on'; select @@optimizer_switch; @@optimizer_switch -index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='default,index_merge_sort_union=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch=4; ERROR 42000: Variable 'optimizer_switch' can't be set to the value of '4' set optimizer_switch=NULL; @@ -1458,21 +1458,21 @@ set optimizer_switch='index_merge=off,index_merge_union=off,default'; select @@optimizer_switch; @@optimizer_switch -index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch=default; select @@global.optimizer_switch; @@global.optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set @@global.optimizer_switch=default; select @@global.optimizer_switch; @@global.optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on # # Check index_merge's @@optimizer_switch flags # select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 (a int, b int, c int, filler char(100), @@ -1582,5 +1582,5 @@ set optimizer_switch=default; show variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on drop table t0, t1; === modified file 'mysql-test/r/myisam_mrr.result' --- a/mysql-test/r/myisam_mrr.result 2010-03-11 21:43:31 +0000 +++ b/mysql-test/r/myisam_mrr.result 2010-05-31 21:25:54 +0000 @@ -394,7 +394,7 @@ # - engine_condition_pushdown does not affect ICP select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 (a int, b int, key(a)); === added file 'mysql-test/r/subquery_cache.result' --- a/mysql-test/r/subquery_cache.result 1970-01-01 00:00:00 +0000 +++ b/mysql-test/r/subquery_cache.result 2010-05-31 21:25:54 +0000 @@ -0,0 +1,591 @@ +set optimizer_switch='subquery_cache=on'; +flush status; +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); +#single value subquery test +select a, (select d from t2 where b=c) + 1 from t1; +a (select d from t2 where b=c) + 1 +1 4 +3 NULL +1 4 +3 NULL +3 NULL +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 +#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 NULL +1 4 +3 NULL +3 NULL +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 +execute stmt1; +a (select d from t2 where b=c) + 1 +1 4 +3 NULL +1 4 +3 NULL +3 NULL +4 7 +4 7 +5 NULL +5 NULL +4 7 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 18 +Subquery_cache_miss 12 +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 NULL +1 4 +3 NULL +3 NULL +4 7 +4 7 +5 NULL +5 NULL +4 7 +call p1; +a (select d from t2 where b=c) + 1 +1 4 +3 NULL +1 4 +3 NULL +3 NULL +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 +#IN subquery tesy (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 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 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 30 +Subquery_cache_miss 24 +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 36 +Subquery_cache_miss 30 +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 42 +Subquery_cache_miss 36 +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 48 +Subquery_cache_miss 42 +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 54 +Subquery_cache_miss 48 +deallocate prepare stmt1; +#IN subquery tesy (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 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 +show status like "subquery_cache%"; +Variable_name Value +Subquery_cache_hit 66 +Subquery_cache_miss 56 +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 72 +Subquery_cache_miss 62 +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 78 +Subquery_cache_miss 68 +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 84 +Subquery_cache_miss 74 +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 90 +Subquery_cache_miss 80 +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 96 +Subquery_cache_miss 86 +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 102 +Subquery_cache_miss 92 +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 108 +Subquery_cache_miss 98 +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; +#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; +set optimizer_switch='subquery_cache=default'; === modified file 'mysql-test/r/subselect3.result' --- a/mysql-test/r/subselect3.result 2010-03-29 14:04:35 +0000 +++ b/mysql-test/r/subselect3.result 2010-05-31 21:25:54 +0000 @@ -105,6 +105,7 @@ Handler_read_rnd_next 5 delete from t2; insert into t2 values (NULL, 0),(NULL, 0), (NULL, 0), (NULL, 0); +set optimizer_switch='subquery_cache=off'; flush status; select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; oref a Z @@ -123,6 +124,7 @@ select 'No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1.' Z; Z No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1. +set @@optimizer_switch=@save_optimizer_switch; drop table t1, t2; create table t1 (a int, b int, primary key (a)); insert into t1 values (1,1), (3,1),(100,1); === modified file 'mysql-test/r/subselect3_jcl6.result' --- a/mysql-test/r/subselect3_jcl6.result 2010-03-29 14:04:35 +0000 +++ b/mysql-test/r/subselect3_jcl6.result 2010-05-31 21:25:54 +0000 @@ -109,6 +109,7 @@ Handler_read_rnd_next 5 delete from t2; insert into t2 values (NULL, 0),(NULL, 0), (NULL, 0), (NULL, 0); +set optimizer_switch='subquery_cache=off'; flush status; select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; oref a Z @@ -127,6 +128,7 @@ select 'No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1.' Z; Z No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1. +set @@optimizer_switch=@save_optimizer_switch; drop table t1, t2; create table t1 (a int, b int, primary key (a)); insert into t1 values (1,1), (3,1),(100,1); === modified file 'mysql-test/r/subselect_no_mat.result' --- a/mysql-test/r/subselect_no_mat.result 2010-03-20 12:01:47 +0000 +++ b/mysql-test/r/subselect_no_mat.result 2010-05-31 21:25:54 +0000 @@ -1,6 +1,6 @@ show variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='materialization=off'; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; set @save_optimizer_switch=@@optimizer_switch; @@ -4826,4 +4826,4 @@ set optimizer_switch=default; show variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on === modified file 'mysql-test/r/subselect_no_opts.result' --- a/mysql-test/r/subselect_no_opts.result 2010-03-20 12:01:47 +0000 +++ b/mysql-test/r/subselect_no_opts.result 2010-05-31 21:25:54 +0000 @@ -1,6 +1,6 @@ show variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='materialization=off,semijoin=off'; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; set @save_optimizer_switch=@@optimizer_switch; @@ -4826,4 +4826,4 @@ set optimizer_switch=default; show variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on === modified file 'mysql-test/r/subselect_no_semijoin.result' --- a/mysql-test/r/subselect_no_semijoin.result 2010-03-20 12:01:47 +0000 +++ b/mysql-test/r/subselect_no_semijoin.result 2010-05-31 21:25:54 +0000 @@ -1,6 +1,6 @@ show variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='semijoin=off'; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t11,t12; set @save_optimizer_switch=@@optimizer_switch; @@ -4826,4 +4826,4 @@ set optimizer_switch=default; show variables like 'optimizer_switch'; Variable_name Value -optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on === modified file 'mysql-test/r/subselect_sj.result' --- a/mysql-test/r/subselect_sj.result 2010-03-29 14:04:35 +0000 +++ b/mysql-test/r/subselect_sj.result 2010-05-31 21:25:54 +0000 @@ -202,39 +202,39 @@ select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='default,materialization=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='default,semijoin=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='default,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='default,semijoin=off,materialization=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='default,materialization=off,semijoin=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='default,semijoin=off,materialization=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='default,semijoin=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='default,materialization=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch=default; drop table t0, t1, t2; drop table t10, t11, t12; === modified file 'mysql-test/r/subselect_sj_jcl6.result' --- a/mysql-test/r/subselect_sj_jcl6.result 2010-03-29 14:04:35 +0000 +++ b/mysql-test/r/subselect_sj_jcl6.result 2010-05-31 21:25:54 +0000 @@ -206,39 +206,39 @@ select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='default,materialization=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='default,semijoin=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='default,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='default,semijoin=off,materialization=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='default,materialization=off,semijoin=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='default,semijoin=off,materialization=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='default,semijoin=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch='default,materialization=off,loosescan=off'; select @@optimizer_switch; @@optimizer_switch -index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=off,materialization=off,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on set optimizer_switch=default; drop table t0, t1, t2; drop table t10, t11, t12; === added file 'mysql-test/t/subquery_cache.test' --- a/mysql-test/t/subquery_cache.test 1970-01-01 00:00:00 +0000 +++ b/mysql-test/t/subquery_cache.test 2010-05-31 21:25:54 +0000 @@ -0,0 +1,204 @@ + +set optimizer_switch='subquery_cache=on'; +flush status; + +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); + +--echo #single value subquery test +select a, (select d from t2 where b=c) + 1 from t1; + +show status like "subquery_cache%"; + +--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 #IN subquery tesy (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 tesy (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 #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; + +set optimizer_switch='subquery_cache=default'; === modified file 'mysql-test/t/subselect3.test' --- a/mysql-test/t/subselect3.test 2010-03-20 12:01:47 +0000 +++ b/mysql-test/t/subselect3.test 2010-05-31 21:25:54 +0000 @@ -98,10 +98,12 @@ delete from t2; insert into t2 values (NULL, 0),(NULL, 0), (NULL, 0), (NULL, 0); +set optimizer_switch='subquery_cache=off'; flush status; select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2; show status like '%Handler_read%'; select 'No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1.' Z; +set @@optimizer_switch=@save_optimizer_switch; drop table t1, t2; === modified file 'sql/CMakeLists.txt' --- a/sql/CMakeLists.txt 2010-03-20 12:01:47 +0000 +++ b/sql/CMakeLists.txt 2010-05-31 21:25:54 +0000 @@ -78,7 +78,7 @@ rpl_rli.cc rpl_mi.cc sql_servers.cc sql_connect.cc scheduler.cc sql_profile.cc event_parse_data.cc opt_table_elimination.cc - ds_mrr.cc + ds_mrr.cc sql_subquery_cache.cc ${PROJECT_SOURCE_DIR}/sql/sql_yacc.cc ${PROJECT_SOURCE_DIR}/sql/sql_yacc.h ${PROJECT_SOURCE_DIR}/include/mysqld_error.h === modified file 'sql/Makefile.am' --- a/sql/Makefile.am 2010-03-20 12:01:47 +0000 +++ b/sql/Makefile.am 2010-05-31 21:25:54 +0000 @@ -80,7 +80,7 @@ event_data_objects.h event_scheduler.h \ sql_partition.h partition_info.h partition_element.h \ contributors.h sql_servers.h \ - multi_range_read.h + multi_range_read.h sql_subquery_cache.h mysqld_SOURCES = sql_lex.cc sql_handler.cc sql_partition.cc \ item.cc item_sum.cc item_buff.cc item_func.cc \ @@ -130,7 +130,7 @@ sql_servers.cc event_parse_data.cc \ opt_table_elimination.cc \ multi_range_read.cc \ - opt_index_cond_pushdown.cc + opt_index_cond_pushdown.cc sql_subquery_cache.cc nodist_mysqld_SOURCES = mini_client_errors.c pack.c client.c my_time.c my_user.c === modified file 'sql/item.cc' --- a/sql/item.cc 2010-03-20 12:01:47 +0000 +++ b/sql/item.cc 2010-05-31 21:25:54 +0000 @@ -28,6 +28,9 @@ const String my_null_string("NULL", 4, default_charset_info); +static int save_field_in_field(Field *from,my_bool * null_value, + Field *to, bool no_conversions); + /****************************************************************************/ /* Hybrid_type_traits {_real} */ @@ -2273,6 +2276,13 @@ str->append(str_value); } +void Item_bool_cache::print(String *str, enum_query_type query_type) +{ + if (null_value) + str->append("NULL", 4); + else + Item_int::print(str, query_type); +} Item_uint::Item_uint(const char *str_arg, uint length): Item_int(str_arg, length) @@ -3646,12 +3656,17 @@ resolved_item->db_name : ""); const char *table_name= (resolved_item->table_name ? resolved_item->table_name : ""); + DBUG_ENTER("mark_as_dependent"); + DBUG_PRINT("enter", ("Field '%s.%s.%s in select %d resolved in %d", + db_name, table_name, + resolved_item->field_name, current->select_number, + last->select_number)); /* store pointer on SELECT_LEX from which item is dependent */ if (mark_item) mark_item->depended_from= last; if (current->mark_as_dependent(thd, last, /** resolved_item psergey-thu **/mark_item)) - return TRUE; + DBUG_RETURN(TRUE); if (thd->lex->describe & DESCRIBE_EXTENDED) { push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE, @@ -3661,7 +3676,7 @@ resolved_item->field_name, current->select_number, last->select_number); } - return FALSE; + DBUG_RETURN(FALSE); } @@ -3698,6 +3713,7 @@ resolving) */ SELECT_LEX *previous_select= current_sel; + for (; previous_select->outer_select() != last_select; previous_select= previous_select->outer_select()) { @@ -3726,6 +3742,7 @@ mark_as_dependent(thd, last_select, current_sel, resolved_item, dependent); } + return; } @@ -4098,6 +4115,9 @@ ((ref_type == REF_ITEM || ref_type == FIELD_ITEM) ? (Item_ident*) (*reference) : 0)); + context->select_lex-> + register_dependency_item(last_checked_context->select_lex, + reference); return 0; } } @@ -4113,7 +4133,9 @@ ((ref_type == REF_ITEM || ref_type == FIELD_ITEM) ? (Item_ident*) (*reference) : 0)); - + context->select_lex-> + register_dependency_item(last_checked_context->select_lex, + reference); /* A reference to a view field had been found and we substituted it instead of this Item (find_field_in_tables @@ -4215,6 +4237,10 @@ mark_as_dependent(thd, last_checked_context->select_lex, context->select_lex, rf, rf); + context->select_lex-> + register_dependency_item(last_checked_context->select_lex, + reference); + return 0; } else @@ -4222,6 +4248,9 @@ mark_as_dependent(thd, last_checked_context->select_lex, context->select_lex, this, (Item_ident*)*reference); + context->select_lex-> + register_dependency_item(last_checked_context->select_lex, + reference); if (last_checked_context->select_lex->having_fix_field) { Item_ref *rf; @@ -5082,39 +5111,48 @@ /** + Saves one Fields of an Item of in other Field + + @param from Field to copy value from + @param null_value reference on item null_value to set it if it is needed + @param to Field to cope value to + @param no_conversions how to deal with NULL value (see + set_field_to_null_with_conversions()) + + @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 (from->is_null()) + { + (*null_value)= 1; + res= set_field_to_null_with_conversions(to, no_conversions); + } + else + { + to->set_notnull(); + res= field_conv(to, from); + (*null_value)= 0; + } + return res; +} + +/** Set a field's value from a item. */ void Item_field::save_org_in_field(Field *to) { - if (field->is_null()) - { - null_value=1; - set_field_to_null_with_conversions(to, 1); - } - else - { - to->set_notnull(); - field_conv(to,field); - null_value=0; - } + save_field_in_field(field, &null_value, to, TRUE); } int Item_field::save_in_field(Field *to, bool no_conversions) { - int res; - if (result_field->is_null()) - { - null_value=1; - res= set_field_to_null_with_conversions(to, no_conversions); - } - else - { - to->set_notnull(); - res= field_conv(to,result_field); - null_value=0; - } - return res; + return save_field_in_field(result_field, &null_value, to, no_conversions); } @@ -5973,6 +6011,9 @@ refer_type == FIELD_ITEM) ? (Item_ident*) (*reference) : 0)); + context->select_lex-> + register_dependency_item(last_checked_context->select_lex, + reference); /* view reference found, we substituted it instead of this Item, so can quit @@ -6023,6 +6064,9 @@ thd->change_item_tree(reference, fld); mark_as_dependent(thd, last_checked_context->select_lex, thd->lex->current_select, fld, fld); + context->select_lex-> + register_dependency_item(last_checked_context->select_lex, + reference); /* A reference is resolved to a nest level that's outer or the same as the nest level of the enclosing set function : adjust the value of @@ -6046,6 +6090,9 @@ DBUG_ASSERT(*ref && (*ref)->fixed); mark_as_dependent(thd, last_checked_context->select_lex, context->select_lex, this, this); + context->select_lex-> + register_dependency_item(last_checked_context->select_lex, + ref); /* A reference is resolved to a nest level that's outer or the same as the nest level of the enclosing set function : adjust the value of @@ -6312,7 +6359,8 @@ int Item_ref::save_in_field(Field *to, bool no_conversions) { int res; - DBUG_ASSERT(!result_field); + if (result_field) + return save_field_in_field(result_field, &null_value, to, no_conversions); res= (*ref)->save_in_field(to, no_conversions); null_value= (*ref)->null_value; return res; === modified file 'sql/item.h' --- a/sql/item.h 2010-03-20 12:01:47 +0000 +++ b/sql/item.h 2010-05-31 21:25:54 +0000 @@ -1922,8 +1922,31 @@ 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;} +}; + + +/** + Item represent TRUE/FALSE/NULL for subquery values +*/ + +class Item_bool_cache: public Item_int +{ +public: + Item_bool_cache(): Item_int(0, 1) + { + unsigned_flag= maybe_null= null_value= TRUE; + name= (char *)"bool chache"; + } + Item_bool_cache(my_bool val, my_bool null): Item_int(val, 1) + { + unsigned_flag= maybe_null= TRUE; + null_value= null; + name= (char *)"bool chache"; + } + Item *clone_item() { return new Item_bool_cache(value, null_value); } + uint decimal_precision() const { return 1; } + virtual void print(String *str, enum_query_type query_type); + void set(my_bool val, my_bool null) {value= test(val); null_value= null;} }; @@ -3146,7 +3169,8 @@ example(0), used_table_map(0), cached_field(0), cached_field_type(MYSQL_TYPE_STRING), value_cached(0) { - fixed= 1; + fixed= 1; + maybe_null= 1; null_value= 1; } Item_cache(enum_field_types field_type_arg): @@ -3154,6 +3178,7 @@ value_cached(0) { fixed= 1; + maybe_null= 1; null_value= 1; } === modified file 'sql/item_cmpfunc.cc' --- a/sql/item_cmpfunc.cc 2010-03-20 12:01:47 +0000 +++ b/sql/item_cmpfunc.cc 2010-05-31 21:25:54 +0000 @@ -1736,6 +1736,15 @@ used_tables_cache|= args[1]->used_tables(); not_null_tables_cache|= args[1]->not_null_tables(); const_item_cache&= args[1]->const_item(); + DBUG_ASSERT(scache == NULL); + if (args[0]->cols() ==1 && + thd->variables.optimizer_switch & OPTIMIZER_SWITCH_SUBQUERY_CACHE && + !(sub->engine->uncacheable() & (UNCACHEABLE_RAND | + UNCACHEABLE_SIDEEFFECT))) + { + sub->depends_on.push_front((Item**)&cache); + scache= new Subquery_cache_tmptable(thd, sub->depends_on, &result); + } fixed= 1; return FALSE; } @@ -1744,10 +1753,26 @@ longlong Item_in_optimizer::val_int() { bool tmp; + DBUG_ENTER("Item_in_optimizer::val_int"); + DBUG_ASSERT(fixed == 1); cache->store(args[0]); cache->cache_value(); - + + /* check if result is in the cache */ + if (scache) + { + Subquery_cache_tmptable::result res; + Item *cached_value; + res= scache->check_value(&cached_value); + if (res == Subquery_cache_tmptable::HIT) + { + tmp= cached_value->val_int(); + null_value= cached_value->null_value; + DBUG_RETURN(tmp); + } + } + if (cache->null_value) { /* @@ -1818,11 +1843,18 @@ for (uint i= 0; i < ncols; i++) item_subs->set_cond_guard_var(i, TRUE); } - return 0; + DBUG_RETURN(0); } tmp= args[1]->val_bool_result(); null_value= args[1]->null_value; - return tmp; + + /* put result in the cache */ + if (scache) + { + result.set(tmp, null_value); + scache->put_value(&result); + } + DBUG_RETURN(tmp); } @@ -1839,6 +1871,11 @@ Item_bool_func::cleanup(); if (!save_cache) cache= 0; + if (scache) + { + delete scache; + scache= 0; + } DBUG_VOID_RETURN; } === modified file 'sql/item_cmpfunc.h' --- a/sql/item_cmpfunc.h 2010-03-20 12:01:47 +0000 +++ b/sql/item_cmpfunc.h 2010-05-31 21:25:54 +0000 @@ -215,6 +215,7 @@ class Item_cache; +class Subquery_cache; #define UNKNOWN ((my_bool)-1) @@ -237,6 +238,10 @@ { protected: Item_cache *cache; + /* Subquery cache */ + Subquery_cache *scache; + /* result representation for the subquery cache */ + Item_bool_cache result; bool save_cache; /* Stores the value of "NULL IN (SELECT ...)" for uncorrelated subqueries: @@ -247,7 +252,7 @@ my_bool result_for_null_param; public: Item_in_optimizer(Item *a, Item_in_subselect *b): - Item_bool_func(a, my_reinterpret_cast(Item *)(b)), cache(0), + Item_bool_func(a, my_reinterpret_cast(Item *)(b)), cache(0), scache(NULL), save_cache(0), result_for_null_param(UNKNOWN) {} bool fix_fields(THD *, Item **); === modified file 'sql/item_subselect.cc' --- a/sql/item_subselect.cc 2010-03-29 14:04:35 +0000 +++ b/sql/item_subselect.cc 2010-05-31 21:25:54 +0000 @@ -34,11 +34,10 @@ Item_subselect::Item_subselect(): Item_result_field(), value_assigned(0), thd(0), substitution(0), - engine(0), old_engine(0), used_tables_cache(0), have_to_be_excluded(0), - const_item_cache(1), - inside_first_fix_fields(0), done_first_fix_fields(FALSE), - eliminated(FALSE), - engine_changed(0), changed(0), is_correlated(FALSE) + engine(0), old_engine(0), scache(0), used_tables_cache(0), + have_to_be_excluded(0), const_item_cache(1), inside_first_fix_fields(0), + done_first_fix_fields(FALSE), eliminated(FALSE), engine_changed(0), + changed(0), is_correlated(FALSE) { with_subselect= 1; reset(); @@ -116,6 +115,12 @@ } if (engine) engine->cleanup(); + depends_on.empty(); + if (scache) + { + delete scache; + scache= 0; + } reset(); value_assigned= 0; DBUG_VOID_RETURN; @@ -148,6 +153,8 @@ Item_subselect::~Item_subselect() { delete engine; + if (scache) + delete scache; } Item_subselect::trans_res @@ -746,9 +753,22 @@ void Item_singlerow_subselect::fix_length_and_dec() { + DBUG_ENTER("Item_singlerow_subselect::fix_length_and_dec"); if ((max_columns= engine->cols()) == 1) { + DBUG_PRINT("info", ("one, elements: %u flag %u", + (uint)depends_on.elements, + (uint)test(thd->variables.optimizer_switch & OPTIMIZER_SWITCH_SUBQUERY_CACHE))); engine->fix_length_and_dec(row= &value); + if (depends_on.elements && + optimizer_flag(thd, OPTIMIZER_SWITCH_SUBQUERY_CACHE) && + !(engine->uncacheable() & (UNCACHEABLE_RAND | + UNCACHEABLE_SIDEEFFECT))) + { + DBUG_ASSERT(scache == NULL); + scache= new Subquery_cache_tmptable(thd, depends_on, value); + DBUG_PRINT("info", ("cache: 0x%lx", (ulong) scache)); + } } else { @@ -765,6 +785,7 @@ */ if (engine->no_tables()) maybe_null= engine->may_be_null(); + DBUG_VOID_RETURN; } uint Item_singlerow_subselect::cols() @@ -797,77 +818,206 @@ exec(); } +/** + Checks subquery cache for value + + @retval NULL nothing found + @retval reference on item representing value found in the cache +*/ + +Item *Item_subselect::check_cache() +{ + DBUG_ENTER("Item_subselect::check_cache"); + if (scache) + { + Subquery_cache_tmptable::result res; + Item *cached_value; + res= scache->check_value(&cached_value); + if (res == Subquery_cache_tmptable::HIT) + DBUG_RETURN(cached_value); + } + DBUG_RETURN(NULL); +} + double Item_singlerow_subselect::val_real() { + Item *cached_value; + bool err; + DBUG_ENTER("Item_singlerow_subselect::val_real"); DBUG_ASSERT(fixed == 1); - if (!exec() && !value->null_value) + + if ((cached_value = check_cache())) + { + double res= cached_value->val_real(); + if ((null_value= cached_value->null_value)) + { + reset(); + DBUG_RETURN(0); + } + else + DBUG_RETURN(res); + } + + if (!(err= exec()) && !value->null_value) { null_value= 0; - return value->val_real(); + if (scache) + scache->put_value(value); + DBUG_RETURN(value->val_real()); } else { reset(); - return 0; + DBUG_PRINT("info", ("error: %u", (uint)err)); + if (scache && !err) + scache->put_value(&const_null_value); + DBUG_RETURN(0); } } longlong Item_singlerow_subselect::val_int() { + Item *cached_value; + bool err; + DBUG_ENTER("Item_singlerow_subselect::val_int"); DBUG_ASSERT(fixed == 1); - if (!exec() && !value->null_value) + + if ((cached_value = check_cache())) + { + longlong res= cached_value->val_int(); + if ((null_value= cached_value->null_value)) + { + reset(); + DBUG_RETURN(0); + } + else + DBUG_RETURN(res); + } + + if (!(err= exec()) && !value->null_value) { null_value= 0; - return value->val_int(); + if (scache) + scache->put_value(value); + DBUG_RETURN(value->val_int()); } else { reset(); - return 0; + DBUG_PRINT("info", ("error: %u", (uint)err)); + if (scache && !err) + scache->put_value(&const_null_value); + DBUG_RETURN(0); } } String *Item_singlerow_subselect::val_str(String *str) { - if (!exec() && !value->null_value) + Item *cached_value; + bool err; + DBUG_ENTER("Item_singlerow_subselect::val_str"); + DBUG_ASSERT(fixed == 1); + + if ((cached_value = check_cache())) + { + String *res= cached_value->val_str(str); + if ((null_value= cached_value->null_value)) + { + reset(); + DBUG_RETURN(0); + } + else + DBUG_RETURN(res); + } + + if (!(err= exec()) && !value->null_value) { null_value= 0; - return value->val_str(str); + if (scache) + scache->put_value(value); + DBUG_RETURN(value->val_str(str)); } else { reset(); - return 0; + DBUG_PRINT("info", ("error: %u", (uint)err)); + if (scache && !err) + scache->put_value(&const_null_value); + DBUG_RETURN(0); } } my_decimal *Item_singlerow_subselect::val_decimal(my_decimal *decimal_value) { - if (!exec() && !value->null_value) + Item *cached_value; + bool err; + DBUG_ENTER("Item_singlerow_subselect::val_decimal"); + DBUG_ASSERT(fixed == 1); + + if ((cached_value = check_cache())) + { + my_decimal *res= cached_value->val_decimal(decimal_value); + if ((null_value= cached_value->null_value)) + { + reset(); + DBUG_RETURN(0); + } + else + DBUG_RETURN(res); + } + + if (!(err= exec()) && !value->null_value) { null_value= 0; - return value->val_decimal(decimal_value); + if (scache) + scache->put_value(value); + DBUG_RETURN(value->val_decimal(decimal_value)); } else { reset(); - return 0; + DBUG_PRINT("info", ("error: %u", (uint)err)); + if (scache && !err) + scache->put_value(&const_null_value); + DBUG_RETURN(0); } } bool Item_singlerow_subselect::val_bool() { - if (!exec() && !value->null_value) + Item *cached_value; + bool err; + DBUG_ENTER("Item_singlerow_subselect::val_bool"); + DBUG_ASSERT(fixed == 1); + + if ((cached_value = check_cache())) + { + bool res= cached_value->val_bool(); + if ((null_value= cached_value->null_value)) + { + reset(); + DBUG_RETURN(0); + } + else + DBUG_RETURN(res); + } + + if (!(err= exec()) && !value->null_value) { null_value= 0; - return value->val_bool(); + if (scache) + scache->put_value(value); + DBUG_RETURN(value->val_bool()); } else { reset(); - return 0; + DBUG_PRINT("info", ("error: %u", (uint)err)); + if (scache && !err) + scache->put_value(&const_null_value); + DBUG_RETURN(0); } } @@ -952,33 +1102,79 @@ void Item_exists_subselect::fix_length_and_dec() { + DBUG_ENTER("Item_exists_subselect::fix_length_and_dec"); decimals= 0; max_length= 1; max_columns= engine->cols(); /* We need only 1 row to determine existence */ unit->global_parameters->select_limit= new Item_int((int32) 1); + if (substype() == EXISTS_SUBS && depends_on.elements && + optimizer_flag(thd, OPTIMIZER_SWITCH_SUBQUERY_CACHE) && + !(engine->uncacheable() & (UNCACHEABLE_RAND | + UNCACHEABLE_SIDEEFFECT))) + { + DBUG_ASSERT(scache == NULL); + scache= new Subquery_cache_tmptable(thd, depends_on, &result); + DBUG_PRINT("info", ("cache: 0x%lx", (ulong) scache)); + } + DBUG_VOID_RETURN; } double Item_exists_subselect::val_real() { + Item *cached_value; + DBUG_ENTER("Item_exists_subselect::val_int"); DBUG_ASSERT(fixed == 1); + + if ((cached_value = check_cache())) + { + double res= cached_value->val_real(); + DBUG_ASSERT(!cached_value->null_value); + DBUG_RETURN(res); + } + if (exec()) { reset(); - return 0; - } - return (double) value; + DBUG_RETURN(0); + } + + if (scache) + { + result.set(value, FALSE); + scache->put_value(&result); + } + + DBUG_RETURN((double) value); } longlong Item_exists_subselect::val_int() { + Item *cached_value; + DBUG_ENTER("Item_exists_subselect::val_real"); + DBUG_ASSERT(fixed == 1); + + if ((cached_value = check_cache())) + { + longlong res= cached_value->val_int(); + DBUG_ASSERT(!cached_value->null_value); + DBUG_RETURN(res); + } + DBUG_ASSERT(fixed == 1); if (exec()) { reset(); - return 0; - } - return value; + DBUG_RETURN(0); + } + + if (scache) + { + result.set(value, FALSE); + scache->put_value(&result); + } + + DBUG_RETURN(value); } @@ -997,11 +1193,32 @@ String *Item_exists_subselect::val_str(String *str) { + Item *cached_value; + DBUG_ENTER("Item_exists_subselect::val_str"); DBUG_ASSERT(fixed == 1); + + if ((cached_value = check_cache())) + { + String *res= cached_value->val_str(str); + DBUG_ASSERT(!cached_value->null_value); + DBUG_RETURN(res); + } + if (exec()) + { reset(); + str->set((ulonglong)0,&my_charset_bin); + DBUG_RETURN(str); + } + + if (scache) + { + result.set(value, FALSE); + scache->put_value(&result); + } + str->set((ulonglong)value,&my_charset_bin); - return str; + DBUG_RETURN(str); } @@ -1020,23 +1237,61 @@ my_decimal *Item_exists_subselect::val_decimal(my_decimal *decimal_value) { + Item *cached_value; + DBUG_ENTER("Item_exists_subselect::val_decvimal"); DBUG_ASSERT(fixed == 1); + + if ((cached_value = check_cache())) + { + my_decimal *res= cached_value->val_decimal(decimal_value); + DBUG_ASSERT(!cached_value->null_value); + DBUG_RETURN(res); + } + if (exec()) + { reset(); + int2my_decimal(E_DEC_FATAL_ERROR, 0, 0, decimal_value); + DBUG_RETURN(decimal_value); + } + + if (scache) + { + result.set(value, FALSE); + scache->put_value(&result); + } + int2my_decimal(E_DEC_FATAL_ERROR, value, 0, decimal_value); - return decimal_value; + DBUG_RETURN(decimal_value); } bool Item_exists_subselect::val_bool() { + Item *cached_value; + DBUG_ENTER("Item_exists_subselect::val_real"); DBUG_ASSERT(fixed == 1); + + if ((cached_value = check_cache())) + { + my_bool res= cached_value->val_bool(); + DBUG_ASSERT(!cached_value->null_value); + DBUG_RETURN(res); + } + if (exec()) { reset(); - return 0; - } - return value != 0; + DBUG_RETURN(0); + } + + if (scache) + { + result.set(value, FALSE); + scache->put_value(&result); + } + + DBUG_RETURN(value != 0); } === modified file 'sql/item_subselect.h' --- a/sql/item_subselect.h 2010-03-29 14:04:35 +0000 +++ b/sql/item_subselect.h 2010-05-31 21:25:54 +0000 @@ -27,6 +27,7 @@ class subselect_hash_sj_engine; class Item_bool_func2; class Cached_item; +class Subquery_cache; /* base class for subselects */ @@ -57,6 +58,10 @@ subselect_engine *engine; /* old engine if engine was changed */ subselect_engine *old_engine; + /* subquery cache */ + Subquery_cache *scache; + /* null consrtant for caching */ + Item_null const_null_value; /* cache of used external tables */ table_map used_tables_cache; /* allowed number of columns (1 for single value subqueries) */ @@ -67,7 +72,7 @@ bool have_to_be_excluded; /* cache of constant state */ bool const_item_cache; - + bool inside_first_fix_fields; bool done_first_fix_fields; public: @@ -88,13 +93,21 @@ */ List upper_refs; st_select_lex *parent_select; - - /* + + /** + List of references on items subquery depends on (externally resolved); + + @note We can't store direct links on Items because it could be + substituted with other item (for example for grouping). + */ + List depends_on; + + /* TRUE<=>Table Elimination has made it redundant to evaluate this select (and so it is not part of QEP, etc) - */ + */ bool eliminated; - + /* changed engine indicator */ bool engine_changed; /* subquery is transformed */ @@ -178,6 +191,8 @@ return trace_unsupported_by_check_vcol_func_processor("subselect"); } + Item *check_cache(); + /** Get the SELECT_LEX structure associated with this Item. @return the SELECT_LEX structure associated with this Item @@ -202,6 +217,7 @@ { protected: Item_cache *value, **row; + public: Item_singlerow_subselect(st_select_lex *select_lex); Item_singlerow_subselect() :Item_subselect(), value(0), row (0) {} @@ -268,6 +284,8 @@ { protected: bool value; /* value of this item (boolean: exists/not-exists) */ + /* result representation for the subquery cache */ + Item_bool_cache result; public: Item_exists_subselect(st_select_lex *select_lex); === modified file 'sql/item_sum.cc' --- a/sql/item_sum.cc 2010-03-20 12:01:47 +0000 +++ b/sql/item_sum.cc 2010-05-31 21:25:54 +0000 @@ -319,6 +319,7 @@ if (aggr_level >= 0) { ref_by= ref; + thd->lex->current_select->register_dependency_item(aggr_sel, ref); /* Add the object to the list of registered objects assigned to aggr_sel */ if (!aggr_sel->inner_sum_func_list) next= this; === modified file 'sql/mysql_priv.h' --- a/sql/mysql_priv.h 2010-03-20 12:01:47 +0000 +++ b/sql/mysql_priv.h 2010-05-31 21:25:54 +0000 @@ -568,12 +568,13 @@ #define OPTIMIZER_SWITCH_SEMIJOIN 256 #define OPTIMIZER_SWITCH_PARTIAL_MATCH_ROWID_MERGE 512 #define OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN 1024 +#define OPTIMIZER_SWITCH_SUBQUERY_CACHE (1<<11) #ifdef DBUG_OFF -# define OPTIMIZER_SWITCH_LAST 2048 +# define OPTIMIZER_SWITCH_LAST (1<<12) #else -# define OPTIMIZER_SWITCH_TABLE_ELIMINATION 2048 -# define OPTIMIZER_SWITCH_LAST 4096 +# define OPTIMIZER_SWITCH_TABLE_ELIMINATION (1<<12) +# define OPTIMIZER_SWITCH_LAST (1<<13) #endif #ifdef DBUG_OFF @@ -588,7 +589,8 @@ OPTIMIZER_SWITCH_MATERIALIZATION | \ OPTIMIZER_SWITCH_SEMIJOIN | \ OPTIMIZER_SWITCH_PARTIAL_MATCH_ROWID_MERGE|\ - OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN) + OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN|\ + OPTIMIZER_SWITCH_SUBQUERY_CACHE) #else # define OPTIMIZER_SWITCH_DEFAULT (OPTIMIZER_SWITCH_INDEX_MERGE | \ OPTIMIZER_SWITCH_INDEX_MERGE_UNION | \ @@ -601,7 +603,8 @@ OPTIMIZER_SWITCH_MATERIALIZATION | \ OPTIMIZER_SWITCH_SEMIJOIN | \ OPTIMIZER_SWITCH_PARTIAL_MATCH_ROWID_MERGE|\ - OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN) + OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN|\ + OPTIMIZER_SWITCH_SUBQUERY_CACHE) #endif /* @@ -936,6 +939,7 @@ #ifdef MYSQL_SERVER #include "sql_servers.h" #include "opt_range.h" +#include "sql_subquery_cache.h" #ifdef HAVE_QUERY_CACHE struct Query_cache_query_flags @@ -1269,6 +1273,10 @@ Item *having, ORDER *proc_param, ulonglong select_type, select_result *result, SELECT_LEX_UNIT *unit, SELECT_LEX *select_lex); + +struct st_join_table *create_index_lookup_join_tab(TABLE *table); +int join_read_key2(THD *thd, struct st_join_table *tab, TABLE *table, + struct st_table_ref *table_ref); void free_underlaid_joins(THD *thd, SELECT_LEX *select); bool mysql_explain_union(THD *thd, SELECT_LEX_UNIT *unit, select_result *result); @@ -1288,6 +1296,7 @@ bool table_cant_handle_bit_fields, bool make_copy_field, uint convert_blob_length); +bool open_tmp_table(TABLE *table); void sp_prepare_create_field(THD *thd, Create_field *sql_field); int prepare_create_field(Create_field *sql_field, uint *blob_columns, === modified file 'sql/mysqld.cc' --- a/sql/mysqld.cc 2010-03-20 12:01:47 +0000 +++ b/sql/mysqld.cc 2010-05-31 21:25:54 +0000 @@ -305,6 +305,7 @@ "firstmatch","loosescan","materialization", "semijoin", "partial_match_rowid_merge", "partial_match_table_scan", + "subquery_cache", #ifndef DBUG_OFF "table_elimination", #endif @@ -325,6 +326,7 @@ sizeof("semijoin") - 1, sizeof("partial_match_rowid_merge") - 1, sizeof("partial_match_table_scan") - 1, + sizeof("subquery_cache") - 1, #ifndef DBUG_OFF sizeof("table_elimination") - 1, #endif @@ -404,8 +406,9 @@ static const char *optimizer_switch_str="index_merge=on,index_merge_union=on," "index_merge_sort_union=on," "index_merge_intersection=on," - "index_condition_pushdown=on" -#ifndef DBUG_OFF + "index_condition_pushdown=on," + "subquery_cache=on" +#ifndef DBUG_OFF ",table_elimination=on"; #else ; @@ -5872,7 +5875,9 @@ OPT_RECORD_RND_BUFFER, OPT_DIV_PRECINCREMENT, OPT_RELAY_LOG_SPACE_LIMIT, OPT_RELAY_LOG_PURGE, OPT_SLAVE_NET_TIMEOUT, OPT_SLAVE_COMPRESSED_PROTOCOL, OPT_SLOW_LAUNCH_TIME, - OPT_SLAVE_TRANS_RETRIES, OPT_READONLY, OPT_ROWID_MERGE_BUFF_SIZE, + OPT_SLAVE_TRANS_RETRIES, + OPT_SUBQUERY_CACHE, + OPT_READONLY, OPT_ROWID_MERGE_BUFF_SIZE, OPT_DEBUGGING, OPT_DEBUG_FLUSH, OPT_SORT_BUFFER, OPT_TABLE_OPEN_CACHE, OPT_TABLE_DEF_CACHE, OPT_THREAD_CONCURRENCY, OPT_THREAD_CACHE_SIZE, @@ -7164,7 +7169,7 @@ {"optimizer_switch", OPT_OPTIMIZER_SWITCH, "optimizer_switch=option=val[,option=val...], where option={index_merge, " "index_merge_union, index_merge_sort_union, index_merge_intersection, " - "index_condition_pushdown" + "index_condition_pushdown, subquery_cache" #ifndef DBUG_OFF ", table_elimination" #endif @@ -7868,6 +7873,8 @@ {"Ssl_version", (char*) &show_ssl_get_version, SHOW_FUNC}, #endif /* HAVE_OPENSSL */ {"Syncs", (char*) &my_sync_count, SHOW_LONG_NOFLUSH}, + {"Subquery_cache_hit", (char*) &subquery_cache_hit, SHOW_LONG}, + {"Subquery_cache_miss", (char*) &subquery_cache_miss, SHOW_LONG}, {"Table_locks_immediate", (char*) &locks_immediate, SHOW_LONG}, {"Table_locks_waited", (char*) &locks_waited, SHOW_LONG}, #ifdef HAVE_MMAP @@ -8006,6 +8013,7 @@ abort_loop= select_thread_in_use= signal_thread_in_use= 0; ready_to_exit= shutdown_in_progress= grant_option= 0; aborted_threads= aborted_connects= 0; + subquery_cache_miss= subquery_cache_hit= 0; delayed_insert_threads= delayed_insert_writes= delayed_rows_in_use= 0; delayed_insert_errors= thread_created= 0; specialflag= 0; === modified file 'sql/sql_base.cc' --- a/sql/sql_base.cc 2010-03-20 12:01:47 +0000 +++ b/sql/sql_base.cc 2010-05-31 21:25:54 +0000 @@ -8062,6 +8062,10 @@ if (*conds) { thd->where="where clause"; + DBUG_EXECUTE("where", + print_where(*conds, + "WHERE in setup_conds", + QT_ORDINARY);); if ((!(*conds)->fixed && (*conds)->fix_fields(thd, conds)) || (*conds)->check_cols(1)) goto err_no_arena; === modified file 'sql/sql_class.cc' --- a/sql/sql_class.cc 2010-03-20 12:01:47 +0000 +++ b/sql/sql_class.cc 2010-05-31 21:25:54 +0000 @@ -3020,6 +3020,7 @@ table_charset= 0; precomputed_group_by= 0; bit_fields_as_long= 0; + skip_create_table= 0; DBUG_VOID_RETURN; } === modified file 'sql/sql_class.h' --- a/sql/sql_class.h 2010-03-20 12:01:47 +0000 +++ b/sql/sql_class.h 2010-05-31 21:25:54 +0000 @@ -2786,12 +2786,17 @@ that MEMORY tables cannot index BIT columns. */ bool bit_fields_as_long; + /* + Whether to create or postpone actual creation of this temporary table. + TRUE <=> create_tmp_table will create only the TABLE structure. + */ + bool skip_create_table; TMP_TABLE_PARAM() :copy_field(0), group_parts(0), group_length(0), group_null_parts(0), convert_blob_length(0), schema_table(0), precomputed_group_by(0), force_copy_fields(0), - bit_fields_as_long(0) + bit_fields_as_long(0), skip_create_table(0) {} ~TMP_TABLE_PARAM() { === modified file 'sql/sql_lex.cc' --- a/sql/sql_lex.cc 2010-03-20 12:01:47 +0000 +++ b/sql/sql_lex.cc 2010-05-31 21:25:54 +0000 @@ -1829,6 +1829,52 @@ } +/** + Registers reference on items on which the subqueries depends + + @param last pointer to last st_select_lex struct, before + which all st_select_lex have to be marked as + dependent + @param dependency reference on the item on which all this + subqueries depends + +*/ + +void st_select_lex::register_dependency_item(st_select_lex *last, + Item **dependency) +{ + SELECT_LEX *s= this; + DBUG_ENTER("st_select_lex::register_dependency_item"); + DBUG_ASSERT(this != last); + DBUG_ASSERT(*dependency); + do + { + /* check duplicates */ + List_iterator_fast li(s->master_unit()->item->depends_on); + Item **dep; + while ((dep= li++)) + { + if ((*dep)->eq(*dependency, FALSE)) + { + DBUG_PRINT("info", ("dependency %s already present", + ((*dependency)->name ? + (*dependency)->name : + "<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()) != last && s != 0); + DBUG_VOID_RETURN; +} + + /* st_select_lex_node::mark_as_dependent mark all st_select_lex struct from this to 'last' as dependent @@ -1843,7 +1889,7 @@ bool st_select_lex::mark_as_dependent(THD *thd, st_select_lex *last, Item *dependency) { - + DBUG_ENTER("st_select_lex::mark_as_dependent"); DBUG_ASSERT(this != last); /* @@ -1872,11 +1918,11 @@ Item_subselect *subquery_expr= s->master_unit()->item; if (subquery_expr && subquery_expr->mark_as_dependent(thd, last, dependency)) - return TRUE; + DBUG_RETURN(TRUE); } while ((s= s->outer_select()) != last && s != 0); is_correlated= TRUE; this->master_unit()->item->is_correlated= TRUE; - return FALSE; + DBUG_RETURN(FALSE); } bool st_select_lex_node::set_braces(bool value) { return 1; } === modified file 'sql/sql_lex.h' --- a/sql/sql_lex.h 2010-03-20 12:01:47 +0000 +++ b/sql/sql_lex.h 2010-05-31 21:25:54 +0000 @@ -748,6 +748,7 @@ } bool mark_as_dependent(THD *thd, st_select_lex *last, Item *dependency); + void register_dependency_item(st_select_lex *last, Item **dependency); bool set_braces(bool value); bool inc_in_sum_expr(); === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-05-10 13:46:08 +0000 +++ b/sql/sql_select.cc 2010-05-31 21:25:54 +0000 @@ -151,7 +151,6 @@ static int join_read_system(JOIN_TAB *tab); static int join_read_const(JOIN_TAB *tab); static int join_read_key(JOIN_TAB *tab); -static int join_read_key2(JOIN_TAB *tab, TABLE *table, TABLE_REF *table_ref); static void join_read_key_unlock_row(st_join_table *tab); static int join_read_always_key(JOIN_TAB *tab); static int join_read_last_key(JOIN_TAB *tab); @@ -5209,7 +5208,7 @@ 'join->best_positions' contains a complete optimal extension of the current partial QEP. */ - DBUG_EXECUTE("opt", print_plan(join, join->tables, + DBUG_EXECUTE("opt", print_plan(join, n_tables, record_count, read_time, read_time, "optimal");); DBUG_RETURN(FALSE); @@ -7625,6 +7624,40 @@ /** + Creates and fills JOIN_TAB for index look up in temporary table + + @param table The table where to look up + + @return JOIN_TAB object or NULL in case of error +*/ + +JOIN_TAB *create_index_lookup_join_tab(TABLE *table) +{ + JOIN_TAB *tab; + DBUG_ENTER("create_index_lookup_join_tab"); + + if (!((tab= new JOIN_TAB))) + DBUG_RETURN(NULL); + tab->read_record.table= table; + tab->read_record.file=table->file; + /*tab->read_record.unlock_row= rr_unlock_row;*/ + tab->next_select=0; + tab->sorted= 1; + + table->status= STATUS_NO_RECORD; + tab->read_first_record= join_read_key; + /*tab->read_record.unlock_row= join_read_key_unlock_row;*/ + tab->read_record.read_record= join_no_more_records; + if (table->covering_keys.is_set(tab->ref.key) && + !table->no_keyread) + { + table->key_read=1; + table->file->extra(HA_EXTRA_KEYREAD); + } + DBUG_RETURN(tab); +} + +/** Give error if we some tables are done with a full join. This is used by multi_table_update and multi_table_delete when running @@ -10778,6 +10811,7 @@ case Item::REF_ITEM: case Item::NULL_ITEM: case Item::VARBIN_ITEM: + case Item::CACHE_ITEM: if (make_copy_field) { DBUG_ASSERT(((Item_result_field*)item)->result_field); @@ -11552,7 +11586,8 @@ ¶m->recinfo, select_options)) goto err; } - if (open_tmp_table(table)) + DBUG_PRINT("info", ("skip_create_table: %d", (int)param->skip_create_table)); + if (!param->skip_create_table && open_tmp_table(table)) goto err; thd->mem_root= mem_root_save; @@ -11700,16 +11735,17 @@ bool open_tmp_table(TABLE *table) { int error; + DBUG_ENTER("open_tmp_table"); if ((error= table->file->ha_open(table, table->s->table_name.str, O_RDWR, HA_OPEN_TMP_TABLE | HA_OPEN_INTERNAL_TABLE))) { table->file->print_error(error,MYF(0)); /* purecov: inspected */ table->db_stat=0; - return(1); + DBUG_RETURN(1); } (void) table->file->extra(HA_EXTRA_QUICK); /* Faster */ - return(0); + DBUG_RETURN(0); } @@ -12540,7 +12576,8 @@ else { /* Do index lookup in the materialized table */ - if ((res= join_read_key2(join_tab, sjm->table, sjm->tab_ref)) == 1) + if ((res= join_read_key2(join_tab->join->thd, join_tab, + sjm->table, sjm->tab_ref)) == 1) DBUG_RETURN(NESTED_LOOP_ERROR); /* purecov: inspected */ if (res || !sjm->in_equality->val_int()) DBUG_RETURN(NESTED_LOOP_NO_MORE_ROWS); @@ -13323,61 +13360,62 @@ static int join_read_key(JOIN_TAB *tab) { - return join_read_key2(tab, tab->table, &tab->ref); + return join_read_key2(tab->join->thd, tab, tab->table, &tab->ref); } -/* +/* eq_ref access handler but generalized a bit to support TABLE and TABLE_REF not from the join_tab. See join_read_key for detailed synopsis. */ -static int -join_read_key2(JOIN_TAB *tab, TABLE *table, TABLE_REF *table_ref) +int join_read_key2(THD *thd, JOIN_TAB *tab, TABLE *table, TABLE_REF *table_ref) { int error; + DBUG_ENTER("join_read_key2"); if (!table->file->inited) { table->file->ha_index_init(table_ref->key, tab->sorted); } /* TODO: Why don't we do "Late NULLs Filtering" here? */ - if (cmp_buffer_with_ref(tab->join->thd, table, table_ref) || + if (cmp_buffer_with_ref(thd, table, table_ref) || (table->status & (STATUS_GARBAGE | STATUS_NO_PARENT | STATUS_NULL_ROW))) { if (table_ref->key_err) { table->status=STATUS_NOT_FOUND; - return -1; + DBUG_RETURN(-1); } /* Moving away from the current record. Unlock the row in the handler if it did not match the partial WHERE. */ - if (tab->ref.has_record && tab->ref.use_count == 0) + if (table_ref->has_record ) + if (table_ref->use_count == 0) { tab->read_record.file->unlock_row(); - tab->ref.has_record= FALSE; + table_ref->has_record= FALSE; } error=table->file->ha_index_read_map(table->record[0], table_ref->key_buff, make_prev_keypart_map(table_ref->key_parts), HA_READ_KEY_EXACT); if (error && error != HA_ERR_KEY_NOT_FOUND && error != HA_ERR_END_OF_FILE) - return report_error(table, error); + DBUG_RETURN(report_error(table, error)); if (! error) { - tab->ref.has_record= TRUE; - tab->ref.use_count= 1; + table_ref->has_record= TRUE; + table_ref->use_count= 1; } } else if (table->status == 0) { - DBUG_ASSERT(tab->ref.has_record); - tab->ref.use_count++; + DBUG_ASSERT(table_ref->has_record); + table_ref->use_count++; } table->null_row=0; - return table->status ? -1 : 0; + DBUG_RETURN(table->status ? -1 : 0); } === added file 'sql/sql_subquery_cache.cc' --- a/sql/sql_subquery_cache.cc 1970-01-01 00:00:00 +0000 +++ b/sql/sql_subquery_cache.cc 2010-05-31 21:25:54 +0000 @@ -0,0 +1,360 @@ + +#include "mysql_priv.h" +#include "sql_select.h" + +ulonglong subquery_cache_miss, subquery_cache_hit; + +/** + Creates structures which we need for index look up + + @retval FALSE OK + @retval TRUE Error +*/ + +static my_bool createtmp_table_search_structures(THD *thd, + TABLE *table, + List_iterator_fast<Item> &li, + TABLE_REF **ref) +{ + /* + Create/initialize everything we will need to index lookups into the + temptable. + */ + TABLE_REF *tab_ref; + KEY *tmp_key; /* The only index on the temporary table. */ + Item *item; + uint tmp_key_parts; /* Number of keyparts in tmp_key. */ + uint i; + + DBUG_ENTER("createtmp_table_search_structures"); + + tmp_key= table->key_info; + tmp_key_parts= tmp_key->key_parts; + + if (!(tab_ref= (TABLE_REF*) thd->alloc(sizeof(TABLE_REF)))) + DBUG_RETURN(TRUE); + + tab_ref->key= 0; /* The only temp table index. */ + tab_ref->key_length= tmp_key->key_length; + if (!(tab_ref->key_buff= + (uchar*) thd->calloc(ALIGN_SIZE(tmp_key->key_length) * 2)) || + !(tab_ref->key_copy= + (store_key**) thd->alloc((sizeof(store_key*) * + (tmp_key_parts + 1)))) || + !(tab_ref->items= + (Item**) thd->alloc(sizeof(Item*) * tmp_key_parts))) + DBUG_RETURN(TRUE); /* purecov: inspected */ + + tab_ref->key_buff2=tab_ref->key_buff+ALIGN_SIZE(tmp_key->key_length); + tab_ref->key_err=1; + tab_ref->null_rejecting= 1; + tab_ref->disable_cache= FALSE; + tab_ref->has_record= 0; + + KEY_PART_INFO *cur_key_part= tmp_key->key_part; + store_key **ref_key= tab_ref->key_copy; + uchar *cur_ref_buff= tab_ref->key_buff; + + for (i= 0; i < tmp_key_parts; i++, cur_key_part++, ref_key++) + { + item= li++; + DBUG_ASSERT(item); + tab_ref->items[i]= item; + int null_count= test(cur_key_part->field->real_maybe_null()); + *ref_key= new store_key_item(thd, cur_key_part->field, + /* TODO: + the NULL byte is taken into account in + cur_key_part->store_length, so instead of + cur_ref_buff + test(maybe_null), we could + use that information instead. + */ + cur_ref_buff + null_count, + null_count ? tab_ref->key_buff : 0, + cur_key_part->length, tab_ref->items[i]); + cur_ref_buff+= cur_key_part->store_length; + } + *ref_key= NULL; /* End marker. */ + tab_ref->key_err= 1; + tab_ref->key_parts= tmp_key_parts; + *ref= tab_ref; + + DBUG_RETURN(FALSE); +} + + +Subquery_cache_tmptable::Subquery_cache_tmptable(THD *thd, + List &dependance, + Item *value) + :cache_table(NULL), table_thd(thd), list(&dependance), val(value), + equalities(NULL), inited (0) +{ + DBUG_ENTER("Subquery_cache_tmptable::Subquery_cache_tmptable"); + DBUG_VOID_RETURN; +}; + + +/** + Creates equalities expression. + + @note For some type of fields index lookup do not return failure but set + pointer on the next record. To check exact match we use expression like: + field1=value1 and field2=value2 ... + + @retval FALSE OK + @retval TRUE Error +*/ + +bool Subquery_cache_tmptable::make_equalities() +{ + List<Item> args; + List_iterator_fast li(*list); + Item **ref; + Name_resolution_context *cn= NULL; + DBUG_ENTER("Subquery_cache_tmptable::make_equalities"); + + for (uint i= 1 /* skip result filed */; (ref= li++); i++) + { + Field *fld= cache_table->field[i]; + /* Only some field types should be checked after lookup */ + if (fld->type() == MYSQL_TYPE_VARCHAR || + fld->type() == MYSQL_TYPE_TINY_BLOB || + fld->type() == MYSQL_TYPE_MEDIUM_BLOB || + fld->type() == MYSQL_TYPE_LONG_BLOB || + fld->type() == MYSQL_TYPE_BLOB || + fld->type() == MYSQL_TYPE_VAR_STRING || + fld->type() == MYSQL_TYPE_STRING || + fld->type() == MYSQL_TYPE_NEWDECIMAL || + fld->type() == MYSQL_TYPE_DECIMAL) + { + if (!cn) + { + // dummy resolution context + cn= new Name_resolution_context(); + cn->init(); + } + args.push_front(new Item_func_eq(new Item_ref(cn, ref, "", "", FALSE), + new Item_field(fld))); + } + } + if (args.elements == 1) + equalities= args.head(); + else + equalities= new Item_cond_and(args); + + DBUG_RETURN(equalities->fix_fields(table_thd, &equalities)); +} + + +/** + Enumerates all fields in field number order. + + @param arg reference on current field number + + @return field number +*/ + +static uint field_enumerator(uchar *arg) +{ + return ((uint*)arg)[0]++; +} + +/** + Initializes temporary table and index for this cache +*/ + +void Subquery_cache_tmptable::init() +{ + List_iterator_fast li(*list); + List_iterator_fast<Item> li_items(items); + Item **item; + uint field_counter; + DBUG_ENTER("Subquery_cache_tmptable::init"); + DBUG_ASSERT(!inited); + inited= TRUE; + + if (!(ULONGLONG_MAX >> (list->elements + 1))) + { + DBUG_PRINT("info", ("Too many dependencies")); + DBUG_VOID_RETURN; + } + + cache_table= NULL; + while ((item= li++)) + { + DBUG_ASSERT(item); + DBUG_ASSERT(*item); + DBUG_ASSERT((*item)->fixed); + items.push_back((*item)); + } + + cache_table_param.init(); + /* dependance items and result */ + cache_table_param.field_count= list->elements + 1; + /* postpone table creation to index description */ + cache_table_param.skip_create_table= 1; + + + items.push_front(val); + if (!(cache_table= create_tmp_table(table_thd, &cache_table_param, + items, (ORDER*) NULL, + FALSE, FALSE, + ((table_thd->options | + TMP_TABLE_ALL_COLUMNS) & + ~(OPTION_BIG_TABLES | + TMP_TABLE_FORCE_MYISAM)), + HA_POS_ERROR, + (char *)"subquery-cache-table"))) + { + DBUG_PRINT("error", ("create_tmp_table failed, caching switched off")); + DBUG_VOID_RETURN; + } + + if (cache_table->s->db_type() != heap_hton) + { + DBUG_PRINT("error", ("we need only heap table")); + goto error; + } + + /* first field in the table is result value, so we skip it */ + li_items++; + field_counter=1; + + if (cache_table->alloc_keys(1) || + (cache_table->add_tmp_key(0, items.elements - 1, + &field_enumerator, + (uchar*)&field_counter) < 0) || + createtmp_table_search_structures(table_thd, cache_table, li_items, + &tab_ref) || + !(tab= create_index_lookup_join_tab(cache_table))) + { + DBUG_PRINT("error", ("creating index failed")); + goto error; + } + cache_table->s->keys= 1; + cache_table->s->uniques= 1; + + if (open_tmp_table(cache_table)) + { + DBUG_PRINT("error", ("Opening (creating) temporary table failed")); + goto error; + } + + if (!(chached_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; +} + + +Subquery_cache_tmptable::~Subquery_cache_tmptable() +{ + if (cache_table) + free_tmp_table(table_thd, cache_table); +} + + +/** + Checks if current key present in the cache and returns value if it is true + + @param value assigned Item with value from the cache if key + is found + @return result of the key lookup +*/ + +Subquery_cache::result Subquery_cache_tmptable::check_value(Item **value) +{ + int res; + DBUG_ENTER("Subquery_cache_tmptable::check_value"); + + /* + We delay cache initialization to get item references which should be + used at the moment of query execution. I.e. we store reference on item + reference at the moment of class creation but for table creation and + index supply structures (join_tab) we need real Items which used at the + moment of execution so we can resolve reference only at this point. + */ + if (!inited) + init(); + + if (cache_table) + { + DBUG_PRINT("info", ("status: %u has_record %u", + (uint)cache_table->status, (uint)tab_ref->has_record)); + if ((res= join_read_key2(table_thd, tab, cache_table, tab_ref)) == 1) + DBUG_RETURN(ERROR); + if (res || (equalities && !equalities->val_int())) + { + subquery_cache_miss++; + DBUG_RETURN(MISS); + } + + subquery_cache_hit++; + *value= chached_result; + DBUG_RETURN(Subquery_cache::HIT); + } + DBUG_RETURN(Subquery_cache::MISS); +} + + +/** + Puts given value in the cache + + @param value Value to put in the cache + + @retval FALSE OK + @retval TRUE Error +*/ + +my_bool Subquery_cache_tmptable::put_value(Item *value) +{ + int error; + DBUG_ENTER("Subquery_cache_tmptable::put_value"); + DBUG_ASSERT(inited); + + if (!cache_table) + { + DBUG_PRINT("info", ("No table so behave as we successfully put value")); + DBUG_RETURN(FALSE); + } + + *(items.head_ref())= value; + fill_record(table_thd, cache_table->field, items, 1); + if (table_thd->is_error()) + goto err;; + + if ((error= cache_table->file->ha_write_row(cache_table->record[0]))) + { + /* create_myisam_from_heap will generate error if needed */ + if (cache_table->file->is_fatal_error(error, HA_CHECK_DUP) && + create_internal_tmp_table_from_heap(table_thd, cache_table, + cache_table_param.start_recinfo, + &cache_table_param.recinfo, + error, 1)) + goto err; + } + cache_table->status= 0; /* cache_table->record contains an existed record */ + tab_ref->has_record= TRUE; /* the same as above */ + DBUG_PRINT("info", ("has_record: TRUE status: 0")); + + DBUG_RETURN(FALSE); + +err: + free_tmp_table(table_thd, cache_table); + cache_table= NULL; + DBUG_RETURN(TRUE); +} === added file 'sql/sql_subquery_cache.h' --- a/sql/sql_subquery_cache.h 1970-01-01 00:00:00 +0000 +++ b/sql/sql_subquery_cache.h 2010-05-31 21:25:54 +0000 @@ -0,0 +1,74 @@ +#ifndef _SQL_SUBQUERY_CACHE_H_ +#define _SQL_SUBQUERY_CACHE_H_ + +/** + Interface for subquery cache +*/ + +extern ulonglong subquery_cache_miss, subquery_cache_hit; + +class Subquery_cache :public Sql_alloc +{ +public: + enum result {ERROR, HIT, MISS}; + + Subquery_cache(){}; + virtual ~Subquery_cache() {}; + /** + Checks presence of the key (taken from cache owner) and if found return + it via value parameter + */ + virtual result check_value(Item **value)= 0; + /** + Puts value into this cache (key should be taken from cache owner) + */ + virtual my_bool put_value(Item *value)= 0; +}; + +struct st_table_ref; +struct st_join_table; +//class Item_cache; +class Item_field; + +/** + Implementation of subquery cache over temporary table +*/ + +class Subquery_cache_tmptable :public Subquery_cache +{ +public: + Subquery_cache_tmptable(THD *thd, List &dependance, Item *value); + virtual ~Subquery_cache_tmptable(); + virtual result check_value(Item **value); + virtual my_bool put_value(Item *value); + +private: + void init(); + bool make_equalities(); + + /* tmp table parameters */ + TMP_TABLE_PARAM cache_table_param; + /* temporary table to store this cache */ + TABLE *cache_table; + /* Thread handler for the temporary table */ + THD *table_thd; + /* tab_ref for index search */ + struct st_table_ref *tab_ref; + /* cache of subquery value to avoid evaluating it twice */ + //Item_cache *value_cache; + /* JOIN_TAB for index lookup */ + st_join_table *tab; + /* Chached result */ + Item_field *chached_result; + /* List of references to items */ + List *list; + /* List of items */ + List<Item> items; + /* Value Item example */ + Item *val; + /* Expression to check after index lookup */ + Item *equalities; + /* set if structures are inited */ + bool inited; +}; +#endif === modified file 'sql/table.cc' --- a/sql/table.cc 2010-03-20 12:01:47 +0000 +++ b/sql/table.cc 2010-05-31 21:25:54 +0000 @@ -20,6 +20,7 @@ #include "sql_trigger.h" #include #include "my_md5.h" +#include "my_bit.h" /* INFORMATION_SCHEMA name */ LEX_STRING INFORMATION_SCHEMA_NAME= {C_STRING_WITH_LEN("information_schema")}; @@ -5096,6 +5097,115 @@ file->column_bitmaps_signal(); } + +/** + @brief + Allocate space for keys + + @param key_count number of keys to allocate. + + @details + Allocate 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); +} + + +/** + @brief Adds one key to a temporary table. + + @param key key number. + @param key_parts number of fields in the key + @param next_field_no function which returns field numbers which + should be included in the key + @param arg above function argement + + @return <0 an error occur. + @return >=0 number of newly added key. +*/ + +bool TABLE::add_tmp_key(uint key, uint key_parts, + uint (*next_field_no) (uchar *), uchar *arg) +{ + DBUG_ASSERT(key < max_keys); + + char buf[NAME_CHAR_LEN]; + KEY* keyinfo; + Field **reg_field; + uint i; + bool key_start= TRUE; + KEY_PART_INFO* key_part_info= + (KEY_PART_INFO*) alloc_root(&mem_root, sizeof(KEY_PART_INFO)*key_parts); + if (!key_part_info) + return TRUE; + keyinfo= key_info + key; + keyinfo->key_part= key_part_info; + keyinfo->usable_key_parts= keyinfo->key_parts = key_parts; + keyinfo->key_length=0; + keyinfo->algorithm= HA_KEY_ALG_UNDEF; + keyinfo->flags= HA_GENERATED_KEY; + sprintf(buf, "key%i", key); + if (!(keyinfo->name= strdup_root(&mem_root, buf))) + return TRUE; + keyinfo->rec_per_key= (ulong*) alloc_root(&mem_root, + sizeof(ulong)*key_parts); + if (!keyinfo->rec_per_key) + return TRUE; + bzero(keyinfo->rec_per_key, sizeof(ulong)*key_parts); + for (i= 0; i < key_parts; i++) + { + reg_field= field + next_field_no(arg); + if (key_start) + (*reg_field)->key_start.set_bit(key); + key_start= FALSE; + (*reg_field)->part_of_key.set_bit(key); + (*reg_field)->flags|= PART_KEY_FLAG; + key_part_info->null_bit= (*reg_field)->null_bit; + key_part_info->null_offset= (uint) ((*reg_field)->null_ptr - + (uchar*) record[0]); + key_part_info->field= *reg_field; + key_part_info->offset= (*reg_field)->offset(record[0]); + key_part_info->length= (uint16) (*reg_field)->pack_length(); + keyinfo->key_length+= key_part_info->length; + /* 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; + if ((*reg_field)->type() == MYSQL_TYPE_BLOB || + (*reg_field)->real_type() == MYSQL_TYPE_VARCHAR) + key_part_info->store_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. === modified file 'sql/table.h' --- a/sql/table.h 2010-03-20 12:01:47 +0000 +++ b/sql/table.h 2010-05-31 21:25:54 +0000 @@ -781,6 +781,7 @@ uint temp_pool_slot; /* Used by intern temp tables */ uint status; /* What's in record[0] */ uint db_stat; /* mode of file as in handler.h */ + uint max_keys; /* Size of allocated key_info array. */ /* number of select if it is derived table */ uint derived_select_number; int current_lock; /* Type of lock on table */ @@ -913,6 +914,9 @@ */ 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); }; === modified file 'storage/maria/ha_maria.cc' --- a/storage/maria/ha_maria.cc 2010-03-20 12:01:47 +0000 +++ b/storage/maria/ha_maria.cc 2010-05-31 21:25:54 +0000 @@ -995,6 +995,8 @@ { MARIA_HA *tmp= file; file= 0; + if (!tmp) + return 0; return maria_close(tmp); }