From: Date: August 21 2007 1:43pm
Subject: bk commit into 5.0 tree (thek:1.2481) BUG#30269
List-Archive: http://lists.mysql.com/commits/32805
X-Bug: 30269
Message-Id: <20070821114314.5F97A53011E@Adventure>

Below is the list of changes that have just been committed into a local
5.0 repository of thek. When thek does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-08-21 13:43:09+02:00, thek@adventure.(none) +5 -0
  Bug#30269 Query cache eats memory
  
  Although the query cache doesn't support retrieval of statements containing
  column level access control, it was still possible to cache such statements
  thus wasting memory.
    
  This patch extends the access control check on the target tables to avoid
  caching a statement with column level restrictions. 
  
  Views are excepted and can be cached but only retrieved by super user account.

  mysql-test/r/query_cache.result@stripped, 2007-08-21 13:43:07+02:00, thek@adventure.(none) +7 -2
    Modified test case to allow caching of views

  mysql-test/r/query_cache_with_views.result@stripped, 2007-08-21 12:29:11+02:00, thek@adventure.(none) +0 -0
    Rename: mysql-test/r/view_query_cache.result -> mysql-test/r/query_cache_with_views.result

  mysql-test/t/query_cache.test@stripped, 2007-08-21 13:43:07+02:00, thek@adventure.(none) +4 -1
    Modified test case to allow caching of views

  mysql-test/t/query_cache_with_views.test@stripped, 2007-08-21 12:28:27+02:00, thek@adventure.(none) +0 -0
    Rename: mysql-test/t/view_query_cache.test -> mysql-test/t/query_cache_with_views.test

  sql/sql_cache.cc@stripped, 2007-08-21 13:43:07+02:00, thek@adventure.(none) +12 -1
    Allow caching of views

diff -Nrup a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result
--- a/mysql-test/r/query_cache.result	2007-08-17 16:55:19 +02:00
+++ b/mysql-test/r/query_cache.result	2007-08-21 13:43:07 +02:00
@@ -1503,10 +1503,11 @@ a	(select count(*) from t2)
 4	0
 drop table t1,t2;
 DROP DATABASE IF EXISTS bug30269;
+FLUSH STATUS;
 CREATE DATABASE bug30269;
 USE bug30269;
 CREATE TABLE test1 (id int, name varchar(23));
-CREATE VIEW view1 AS SELECT id FROM test1;
+CREATE VIEW view1 AS SELECT * FROM test1;
 INSERT INTO test1 VALUES (5, 'testit');
 GRANT SELECT (id) ON TABLE bug30269.test1 TO 'bug30269'@'localhost';
 GRANT SELECT ON TABLE bug30269.view1 TO 'bug30269'@'localhost';
@@ -1515,15 +1516,19 @@ USE bug30269;
 show status like 'Qcache_queries_in_cache';
 Variable_name	Value
 Qcache_queries_in_cache	0
+# Select statement not stored in query cache because of column privileges.
 SELECT id FROM test1 WHERE id>2;
 id
 5
+show status like 'Qcache_queries_in_cache';
+Variable_name	Value
+Qcache_queries_in_cache	0
 SELECT id FROM view1 WHERE id>2;
 id
 5
 show status like 'Qcache_queries_in_cache';
 Variable_name	Value
-Qcache_queries_in_cache	0
+Qcache_queries_in_cache	1
 DROP DATABASE bug30269;
 DROP USER 'bug30269'@'localhost';
 set GLOBAL query_cache_type=default;
diff -Nrup a/mysql-test/r/query_cache_with_views.result b/mysql-test/r/query_cache_with_views.result
--- /dev/null	Wed Dec 31 16:00:00 196900
+++ b/mysql-test/r/query_cache_with_views.result	2007-08-21 12:29:11 +02:00
@@ -0,0 +1,196 @@
+drop table if exists t1,t2,v1,v2,v3;
+drop view if exists t1,t2,v1,v2,v3;
+set GLOBAL query_cache_size=1355776;
+flush status;
+create table t1 (a int, b int);
+create view v1 (c,d) as select sql_no_cache a,b from t1;
+create view v2 (c,d) as select a+rand(),b from t1;
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+show status like "Qcache_inserts";
+Variable_name	Value
+Qcache_inserts	0
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	0
+select * from v1;
+c	d
+select * from v2;
+c	d
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+show status like "Qcache_inserts";
+Variable_name	Value
+Qcache_inserts	0
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	0
+select * from v1;
+c	d
+select * from v2;
+c	d
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+show status like "Qcache_inserts";
+Variable_name	Value
+Qcache_inserts	0
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	0
+drop view v1,v2;
+set query_cache_type=demand;
+flush status;
+create view v1 (c,d) as select sql_cache a,b from t1;
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+show status like "Qcache_inserts";
+Variable_name	Value
+Qcache_inserts	0
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	0
+select * from v1;
+c	d
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	1
+show status like "Qcache_inserts";
+Variable_name	Value
+Qcache_inserts	1
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	0
+select * from t1;
+a	b
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	1
+show status like "Qcache_inserts";
+Variable_name	Value
+Qcache_inserts	1
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	0
+select * from v1;
+c	d
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	1
+show status like "Qcache_inserts";
+Variable_name	Value
+Qcache_inserts	1
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	1
+select * from t1;
+a	b
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	1
+show status like "Qcache_inserts";
+Variable_name	Value
+Qcache_inserts	1
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	1
+drop view v1;
+set query_cache_type=default;
+drop table t1;
+create table t1 (a int);
+insert into t1 values (1), (2), (3);
+create view v1 as select a from t1 where a > 1;
+select * from v1;
+a
+2
+3
+alter view v1 as select a from t1 where a > 2;
+select * from v1;
+a
+3
+drop view v1;
+select * from v1;
+ERROR 42S02: Table 'test.v1' doesn't exist
+drop table t1;
+create table t1 (a int, primary key (a), b int);
+create table t2 (a int, primary key (a), b int);
+insert into t2 values (1000, 2000);
+create view v3 (a,b) as select t1.a as a, t2.a as b from t1, t2;
+select * from v3;
+a	b
+drop view v3;
+drop table t1, t2;
+create table t1(f1 int);
+insert into t1 values(1),(2),(3);
+create view v1 as select * from t1;
+set query_cache_wlock_invalidate=1;
+lock tables v1 read /*!32311 local */;
+unlock tables;
+set query_cache_wlock_invalidate=default;
+drop view v1;
+drop table t1;
+flush status;
+create table t1 (a int, b int);
+create algorithm=temptable view v1 as select * from t1;
+select * from v1;
+a	b
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	1
+show status like "Qcache_inserts";
+Variable_name	Value
+Qcache_inserts	1
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	0
+select * from v1;
+a	b
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	1
+show status like "Qcache_inserts";
+Variable_name	Value
+Qcache_inserts	1
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	1
+insert into t1 values (1,1);
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+show status like "Qcache_inserts";
+Variable_name	Value
+Qcache_inserts	1
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	1
+select * from v1;
+a	b
+1	1
+select * from v1;
+a	b
+1	1
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	1
+show status like "Qcache_inserts";
+Variable_name	Value
+Qcache_inserts	2
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	2
+drop view v1;
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+show status like "Qcache_inserts";
+Variable_name	Value
+Qcache_inserts	2
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	2
+drop table t1;
+set GLOBAL query_cache_size=default;
diff -Nrup a/mysql-test/r/view_query_cache.result b/mysql-test/r/view_query_cache.result
--- a/mysql-test/r/view_query_cache.result	2006-05-12 18:58:51 +02:00
+++ /dev/null	Wed Dec 31 16:00:00 196900
@@ -1,196 +0,0 @@
-drop table if exists t1,t2,v1,v2,v3;
-drop view if exists t1,t2,v1,v2,v3;
-set GLOBAL query_cache_size=1355776;
-flush status;
-create table t1 (a int, b int);
-create view v1 (c,d) as select sql_no_cache a,b from t1;
-create view v2 (c,d) as select a+rand(),b from t1;
-show status like "Qcache_queries_in_cache";
-Variable_name	Value
-Qcache_queries_in_cache	0
-show status like "Qcache_inserts";
-Variable_name	Value
-Qcache_inserts	0
-show status like "Qcache_hits";
-Variable_name	Value
-Qcache_hits	0
-select * from v1;
-c	d
-select * from v2;
-c	d
-show status like "Qcache_queries_in_cache";
-Variable_name	Value
-Qcache_queries_in_cache	0
-show status like "Qcache_inserts";
-Variable_name	Value
-Qcache_inserts	0
-show status like "Qcache_hits";
-Variable_name	Value
-Qcache_hits	0
-select * from v1;
-c	d
-select * from v2;
-c	d
-show status like "Qcache_queries_in_cache";
-Variable_name	Value
-Qcache_queries_in_cache	0
-show status like "Qcache_inserts";
-Variable_name	Value
-Qcache_inserts	0
-show status like "Qcache_hits";
-Variable_name	Value
-Qcache_hits	0
-drop view v1,v2;
-set query_cache_type=demand;
-flush status;
-create view v1 (c,d) as select sql_cache a,b from t1;
-show status like "Qcache_queries_in_cache";
-Variable_name	Value
-Qcache_queries_in_cache	0
-show status like "Qcache_inserts";
-Variable_name	Value
-Qcache_inserts	0
-show status like "Qcache_hits";
-Variable_name	Value
-Qcache_hits	0
-select * from v1;
-c	d
-show status like "Qcache_queries_in_cache";
-Variable_name	Value
-Qcache_queries_in_cache	1
-show status like "Qcache_inserts";
-Variable_name	Value
-Qcache_inserts	1
-show status like "Qcache_hits";
-Variable_name	Value
-Qcache_hits	0
-select * from t1;
-a	b
-show status like "Qcache_queries_in_cache";
-Variable_name	Value
-Qcache_queries_in_cache	1
-show status like "Qcache_inserts";
-Variable_name	Value
-Qcache_inserts	1
-show status like "Qcache_hits";
-Variable_name	Value
-Qcache_hits	0
-select * from v1;
-c	d
-show status like "Qcache_queries_in_cache";
-Variable_name	Value
-Qcache_queries_in_cache	1
-show status like "Qcache_inserts";
-Variable_name	Value
-Qcache_inserts	1
-show status like "Qcache_hits";
-Variable_name	Value
-Qcache_hits	1
-select * from t1;
-a	b
-show status like "Qcache_queries_in_cache";
-Variable_name	Value
-Qcache_queries_in_cache	1
-show status like "Qcache_inserts";
-Variable_name	Value
-Qcache_inserts	1
-show status like "Qcache_hits";
-Variable_name	Value
-Qcache_hits	1
-drop view v1;
-set query_cache_type=default;
-drop table t1;
-create table t1 (a int);
-insert into t1 values (1), (2), (3);
-create view v1 as select a from t1 where a > 1;
-select * from v1;
-a
-2
-3
-alter view v1 as select a from t1 where a > 2;
-select * from v1;
-a
-3
-drop view v1;
-select * from v1;
-ERROR 42S02: Table 'test.v1' doesn't exist
-drop table t1;
-create table t1 (a int, primary key (a), b int);
-create table t2 (a int, primary key (a), b int);
-insert into t2 values (1000, 2000);
-create view v3 (a,b) as select t1.a as a, t2.a as b from t1, t2;
-select * from v3;
-a	b
-drop view v3;
-drop table t1, t2;
-create table t1(f1 int);
-insert into t1 values(1),(2),(3);
-create view v1 as select * from t1;
-set query_cache_wlock_invalidate=1;
-lock tables v1 read /*!32311 local */;
-unlock tables;
-set query_cache_wlock_invalidate=default;
-drop view v1;
-drop table t1;
-flush status;
-create table t1 (a int, b int);
-create algorithm=temptable view v1 as select * from t1;
-select * from v1;
-a	b
-show status like "Qcache_queries_in_cache";
-Variable_name	Value
-Qcache_queries_in_cache	1
-show status like "Qcache_inserts";
-Variable_name	Value
-Qcache_inserts	1
-show status like "Qcache_hits";
-Variable_name	Value
-Qcache_hits	0
-select * from v1;
-a	b
-show status like "Qcache_queries_in_cache";
-Variable_name	Value
-Qcache_queries_in_cache	1
-show status like "Qcache_inserts";
-Variable_name	Value
-Qcache_inserts	1
-show status like "Qcache_hits";
-Variable_name	Value
-Qcache_hits	1
-insert into t1 values (1,1);
-show status like "Qcache_queries_in_cache";
-Variable_name	Value
-Qcache_queries_in_cache	0
-show status like "Qcache_inserts";
-Variable_name	Value
-Qcache_inserts	1
-show status like "Qcache_hits";
-Variable_name	Value
-Qcache_hits	1
-select * from v1;
-a	b
-1	1
-select * from v1;
-a	b
-1	1
-show status like "Qcache_queries_in_cache";
-Variable_name	Value
-Qcache_queries_in_cache	1
-show status like "Qcache_inserts";
-Variable_name	Value
-Qcache_inserts	2
-show status like "Qcache_hits";
-Variable_name	Value
-Qcache_hits	2
-drop view v1;
-show status like "Qcache_queries_in_cache";
-Variable_name	Value
-Qcache_queries_in_cache	0
-show status like "Qcache_inserts";
-Variable_name	Value
-Qcache_inserts	2
-show status like "Qcache_hits";
-Variable_name	Value
-Qcache_hits	2
-drop table t1;
-set GLOBAL query_cache_size=default;
diff -Nrup a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test
--- a/mysql-test/t/query_cache.test	2007-08-17 16:55:19 +02:00
+++ b/mysql-test/t/query_cache.test	2007-08-21 13:43:07 +02:00
@@ -1103,10 +1103,11 @@ disconnect user3;
 --disable_warnings
 DROP DATABASE IF EXISTS bug30269;
 --enable_warnings
+FLUSH STATUS;
 CREATE DATABASE bug30269;
 USE bug30269;
 CREATE TABLE test1 (id int, name varchar(23));
-CREATE VIEW view1 AS SELECT id FROM test1;
+CREATE VIEW view1 AS SELECT * FROM test1;
 INSERT INTO test1 VALUES (5, 'testit');
 GRANT SELECT (id) ON TABLE bug30269.test1 TO 'bug30269'@'localhost';
 GRANT SELECT ON TABLE bug30269.view1 TO 'bug30269'@'localhost';
@@ -1115,7 +1116,9 @@ connect (bug30269, localhost, bug30269,,
 connection bug30269;
 USE bug30269;
 show status like 'Qcache_queries_in_cache';
+--echo # Select statement not stored in query cache because of column privileges.
 SELECT id FROM test1 WHERE id>2;
+show status like 'Qcache_queries_in_cache';
 SELECT id FROM view1 WHERE id>2;
 show status like 'Qcache_queries_in_cache';
 
diff -Nrup a/mysql-test/t/query_cache_with_views.test b/mysql-test/t/query_cache_with_views.test
--- /dev/null	Wed Dec 31 16:00:00 196900
+++ b/mysql-test/t/query_cache_with_views.test	2007-08-21 12:28:27 +02:00
@@ -0,0 +1,130 @@
+-- source include/have_query_cache.inc
+#
+# QUERY CACHE options for VIEWs
+#
+--disable_warnings
+drop table if exists t1,t2,v1,v2,v3;
+drop view if exists t1,t2,v1,v2,v3;
+--enable_warnings
+
+set GLOBAL query_cache_size=1355776;
+flush status;
+create table t1 (a int, b int);
+
+# queries with following views should not be in query cache
+create view v1 (c,d) as select sql_no_cache a,b from t1;
+create view v2 (c,d) as select a+rand(),b from t1;
+show status like "Qcache_queries_in_cache";
+show status like "Qcache_inserts";
+show status like "Qcache_hits";
+select * from v1;
+select * from v2;
+show status like "Qcache_queries_in_cache";
+show status like "Qcache_inserts";
+show status like "Qcache_hits";
+select * from v1;
+select * from v2;
+show status like "Qcache_queries_in_cache";
+show status like "Qcache_inserts";
+show status like "Qcache_hits";
+
+drop view v1,v2;
+
+# SQL_CACHE option
+set query_cache_type=demand;
+flush status;
+# query with view will be cached, but direct acess to table will not
+create view v1 (c,d) as select sql_cache a,b from t1;
+show status like "Qcache_queries_in_cache";
+show status like "Qcache_inserts";
+show status like "Qcache_hits";
+select * from v1;
+show status like "Qcache_queries_in_cache";
+show status like "Qcache_inserts";
+show status like "Qcache_hits";
+select * from t1;
+show status like "Qcache_queries_in_cache";
+show status like "Qcache_inserts";
+show status like "Qcache_hits";
+select * from v1;
+show status like "Qcache_queries_in_cache";
+show status like "Qcache_inserts";
+show status like "Qcache_hits";
+select * from t1;
+show status like "Qcache_queries_in_cache";
+show status like "Qcache_inserts";
+show status like "Qcache_hits";
+drop view v1;
+set query_cache_type=default;
+
+drop table t1;
+
+#
+# invalidation of view
+#
+create table t1 (a int);
+insert into t1 values (1), (2), (3);
+create view v1 as select a from t1 where a > 1;
+select * from v1;
+alter view v1 as select a from t1 where a > 2;
+select * from v1;
+drop view v1;
+-- error 1146
+select * from v1;
+drop table t1;
+
+#
+# join view with QC
+#
+create table t1 (a int, primary key (a), b int);
+create table t2 (a int, primary key (a), b int);
+insert into t2 values (1000, 2000);
+create view v3 (a,b) as select t1.a as a, t2.a as b from t1, t2;
+select * from v3;
+drop view v3;
+drop table t1, t2;
+
+#
+# Bug #13424 locking view with query cache enabled crashes server
+#
+create table t1(f1 int);
+insert into t1 values(1),(2),(3);
+create view v1 as select * from t1;
+set query_cache_wlock_invalidate=1;
+lock tables v1 read /*!32311 local */;
+unlock tables;
+set query_cache_wlock_invalidate=default;
+drop view v1;
+drop table t1;
+
+#
+# BUG#15119: returning temptable view from the query cache.
+#
+flush status;
+create table t1 (a int, b int);
+create algorithm=temptable view v1 as select * from t1;
+select * from v1;
+show status like "Qcache_queries_in_cache";
+show status like "Qcache_inserts";
+show status like "Qcache_hits";
+select * from v1;
+show status like "Qcache_queries_in_cache";
+show status like "Qcache_inserts";
+show status like "Qcache_hits";
+insert into t1 values (1,1);
+show status like "Qcache_queries_in_cache";
+show status like "Qcache_inserts";
+show status like "Qcache_hits";
+select * from v1;
+select * from v1;
+show status like "Qcache_queries_in_cache";
+show status like "Qcache_inserts";
+show status like "Qcache_hits";
+drop view v1;
+show status like "Qcache_queries_in_cache";
+show status like "Qcache_inserts";
+show status like "Qcache_hits";
+drop table t1;
+
+# Reset default environment.
+set GLOBAL query_cache_size=default;
diff -Nrup a/mysql-test/t/view_query_cache.test b/mysql-test/t/view_query_cache.test
--- a/mysql-test/t/view_query_cache.test	2005-12-01 11:01:32 +01:00
+++ /dev/null	Wed Dec 31 16:00:00 196900
@@ -1,130 +0,0 @@
--- source include/have_query_cache.inc
-#
-# QUERY CACHE options for VIEWs
-#
---disable_warnings
-drop table if exists t1,t2,v1,v2,v3;
-drop view if exists t1,t2,v1,v2,v3;
---enable_warnings
-
-set GLOBAL query_cache_size=1355776;
-flush status;
-create table t1 (a int, b int);
-
-# queries with following views should not be in query cache
-create view v1 (c,d) as select sql_no_cache a,b from t1;
-create view v2 (c,d) as select a+rand(),b from t1;
-show status like "Qcache_queries_in_cache";
-show status like "Qcache_inserts";
-show status like "Qcache_hits";
-select * from v1;
-select * from v2;
-show status like "Qcache_queries_in_cache";
-show status like "Qcache_inserts";
-show status like "Qcache_hits";
-select * from v1;
-select * from v2;
-show status like "Qcache_queries_in_cache";
-show status like "Qcache_inserts";
-show status like "Qcache_hits";
-
-drop view v1,v2;
-
-# SQL_CACHE option
-set query_cache_type=demand;
-flush status;
-# query with view will be cached, but direct acess to table will not
-create view v1 (c,d) as select sql_cache a,b from t1;
-show status like "Qcache_queries_in_cache";
-show status like "Qcache_inserts";
-show status like "Qcache_hits";
-select * from v1;
-show status like "Qcache_queries_in_cache";
-show status like "Qcache_inserts";
-show status like "Qcache_hits";
-select * from t1;
-show status like "Qcache_queries_in_cache";
-show status like "Qcache_inserts";
-show status like "Qcache_hits";
-select * from v1;
-show status like "Qcache_queries_in_cache";
-show status like "Qcache_inserts";
-show status like "Qcache_hits";
-select * from t1;
-show status like "Qcache_queries_in_cache";
-show status like "Qcache_inserts";
-show status like "Qcache_hits";
-drop view v1;
-set query_cache_type=default;
-
-drop table t1;
-
-#
-# invalidation of view
-#
-create table t1 (a int);
-insert into t1 values (1), (2), (3);
-create view v1 as select a from t1 where a > 1;
-select * from v1;
-alter view v1 as select a from t1 where a > 2;
-select * from v1;
-drop view v1;
--- error 1146
-select * from v1;
-drop table t1;
-
-#
-# join view with QC
-#
-create table t1 (a int, primary key (a), b int);
-create table t2 (a int, primary key (a), b int);
-insert into t2 values (1000, 2000);
-create view v3 (a,b) as select t1.a as a, t2.a as b from t1, t2;
-select * from v3;
-drop view v3;
-drop table t1, t2;
-
-#
-# Bug #13424 locking view with query cache enabled crashes server
-#
-create table t1(f1 int);
-insert into t1 values(1),(2),(3);
-create view v1 as select * from t1;
-set query_cache_wlock_invalidate=1;
-lock tables v1 read /*!32311 local */;
-unlock tables;
-set query_cache_wlock_invalidate=default;
-drop view v1;
-drop table t1;
-
-#
-# BUG#15119: returning temptable view from the query cache.
-#
-flush status;
-create table t1 (a int, b int);
-create algorithm=temptable view v1 as select * from t1;
-select * from v1;
-show status like "Qcache_queries_in_cache";
-show status like "Qcache_inserts";
-show status like "Qcache_hits";
-select * from v1;
-show status like "Qcache_queries_in_cache";
-show status like "Qcache_inserts";
-show status like "Qcache_hits";
-insert into t1 values (1,1);
-show status like "Qcache_queries_in_cache";
-show status like "Qcache_inserts";
-show status like "Qcache_hits";
-select * from v1;
-select * from v1;
-show status like "Qcache_queries_in_cache";
-show status like "Qcache_inserts";
-show status like "Qcache_hits";
-drop view v1;
-show status like "Qcache_queries_in_cache";
-show status like "Qcache_inserts";
-show status like "Qcache_hits";
-drop table t1;
-
-# Reset default environment.
-set GLOBAL query_cache_size=default;
diff -Nrup a/sql/sql_cache.cc b/sql/sql_cache.cc
--- a/sql/sql_cache.cc	2007-08-17 16:55:19 +02:00
+++ b/sql/sql_cache.cc	2007-08-21 13:43:07 +02:00
@@ -3007,8 +3007,19 @@ Query_cache::process_and_count_tables(TH
       The grant.want_privileges flag was set to 1 in the
       check_grant() function earlier if the TABLE_LIST object
       had any associated column privileges.
+
+      We need to check that the TABLE_LIST object isn't part
+      of a VIEW definition because we want to be able to cache
+      views.
+
+      TODO: Although it is possible to cache views, the privilege
+      check on view tables always fall back on column privileges
+      even if there are more generic table privileges. Thus it isn't
+      currently possible to retrieve cached view-tables unless the
+      client has the super user privileges.
     */
-    if (tables_used->grant.want_privilege)
+    if (tables_used->grant.want_privilege &&
+        tables_used->belong_to_view == NULL)
     {
       DBUG_PRINT("qcache", ("Don't cache statement as it refers to "
                             "tables with column privileges."));