At file:///home/bell/maria/bzr/work-maria-5.3-scache/ ------------------------------------------------------------ revno: 2791 revision-id: sanja@askmonty.org-20100525125457-5rwbiihh0vtghdrj parent: sanja@askmonty.org-20100525104536-zw06otfk8ut7fias committer: sanja@askmonty.org branch nick: work-maria-5.3-scache timestamp: Tue 2010-05-25 15:54:57 +0300 message: Prevent cacheing subqueries with random parameters and side effect functions. === modified file 'mysql-test/r/subquery_cache.result' --- a/mysql-test/r/subquery_cache.result 2010-05-25 10:45:36 +0000 +++ b/mysql-test/r/subquery_cache.result 2010-05-25 12:54:57 +0000 @@ -1,4 +1,5 @@ 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); @@ -552,4 +553,38 @@ 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 set optimizer_switch='subquery_cache=default'; === modified file 'mysql-test/t/subquery_cache.test' --- a/mysql-test/t/subquery_cache.test 2010-05-25 10:45:36 +0000 +++ b/mysql-test/t/subquery_cache.test 2010-05-25 12:54:57 +0000 @@ -1,5 +1,6 @@ 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); @@ -188,4 +189,15 @@ 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%"; + set optimizer_switch='subquery_cache=default'; === modified file 'sql/item_cmpfunc.cc' --- a/sql/item_cmpfunc.cc 2010-05-24 17:29:56 +0000 +++ b/sql/item_cmpfunc.cc 2010-05-25 12:54:57 +0000 @@ -1738,7 +1738,9 @@ const_item_cache&= args[1]->const_item(); DBUG_ASSERT(scache == NULL); if (args[0]->cols() ==1 && - thd->variables.optimizer_switch & OPTIMIZER_SWITCH_SUBQUERY_CACHE) + 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); === modified file 'sql/item_subselect.cc' --- a/sql/item_subselect.cc 2010-05-24 17:29:56 +0000 +++ b/sql/item_subselect.cc 2010-05-25 12:54:57 +0000 @@ -760,7 +760,10 @@ (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)) + 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); @@ -1100,7 +1103,9 @@ /* 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)) + 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);