[Maria-developers] Rev 2789: Subquery cache for pre-review (MWL#66) in file:///home/bell/maria/bzr/work-maria-5.3-scache2/
![](https://secure.gravatar.com/avatar/ac32c9d693e0b70eec04d9ed68b9b4d8.jpg?s=120&d=mm&r=g)
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
participants (1)
-
sanja@askmonty.org