maria-developers team mailing list archive

[<sanja@xxxxxxxxxxxx>]

At file:///home/bell/maria/bzr/work-maria-5.3-scache/

------------------------------------------------------------
revno: 2791
revision-id: sanja@xxxxxxxxxxxx-20100525125457-5rwbiihh0vtghdrj
parent: sanja@xxxxxxxxxxxx-20100525104536-zw06otfk8ut7fias
committer: sanja@xxxxxxxxxxxx
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);