############# mysql-test\t\query_cache_type_func.test #########################
#                                                                             #
# Variable Name: query_cache_type                                             #
# Scope: GLOBAL & SESSION                                                     #
# Access Type: Dynamic                                                        #
# Data Type: enumeration                                                      #
# Default Value: ON                                                           #
# Values: ON, OFF, DEMAND                                                     #
#                                                                             #
#                                                                             #
# Creation Date: 2008-02-20                                                   #
# Author:  Sharique Abdullah                                                  #
#                                                                             #
# Description: Test Cases of Dynamic System Variable "query_cache_type"       #
#              that checks behavior of this variable in the following ways    #
#                                                                             #
# Reference:                                                                  #
#   http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html       #
#                                                                             #
###############################################################################

--echo ** Setup **
--echo

# Setup
#

#
# Save initial value
#

--echo ** Connecting to con0 using root **
connect (con0,localhost,root,,);
--echo ** Connection con0 **
connection con0;

SET @start_global_value = @@GLOBAL.query_cache_type;
SET @start_session_value = @@SESSION.query_cache_type;
SET @start_query_cache_size= @@global.query_cache_size;

#
# Creating test table
#

CREATE TABLE t1(id int, value varchar(10));

INSERT INTO t1 VALUES(1, 'val1');
INSERT INTO t1 VALUES(2, 'val2');
INSERT INTO t1 VALUES(3, 'val3');

#
# Clearing the query cache and setting up cache size
#

SET @@GLOBAL.query_cache_size = 65536;

--echo FLUSHING CACHE
SET @@GLOBAL.query_cache_size = 0;
SET @@GLOBAL.query_cache_size = 65536;

#
# SESSION SCOPE
#

--echo ** TESTING SESSION SCOPE **

--echo '#--------------------FN_DYNVARS_135_01-------------------------#'
#
# Testing default value it should be ON by default
#

SET @@query_cache_type = DEFAULT;
SELECT @@query_cache_type;
--echo ON Expected

--echo '#--------------------FN_DYNVARS_135_02-------------------------#'
#
# Testing valid possible values
#

--error ER_WRONG_VALUE_FOR_VAR
SET @@query_cache_type = NULL;
--echo Expected error: Wrong value for variable

SET @@query_cache_type = ON;
SELECT @@query_cache_type;
--echo ON Expected

SET @@query_cache_type = OFF;
SELECT @@query_cache_type;
--echo OFF Expected

SET @@query_cache_type = DEMAND;
SELECT @@query_cache_type;
--echo DEMAND Expected

--error ER_WRONG_VALUE_FOR_VAR
SET @@query_cache_type = XYZ;
--echo Expected error: Wrong value for variable


#
# TESTING FOR VALUE ON or 1
#

--echo ** Turning cache to ON **

SET @@query_cache_type = ON;

--echo '#--------------------FN_DYNVARS_135_03-------------------------#'
#
# TESTING CACHE ADDITION
#

--echo ** CACHE ADDITION **
--echo

SHOW STATUS LIKE 'Qcache_queries_in_cache';
--echo 0 Expected

SELECT * FROM t1;

SHOW STATUS LIKE 'Qcache_queries_in_cache';
--echo 1 Expected

SELECT value FROM t1;

SHOW STATUS LIKE 'Qcache_queries_in_cache';
--echo 2 Expected

--echo '#--------------------FN_DYNVARS_135_04-------------------------#'
#
# TESTING CACHE HIT
#

--echo ** CACHE HIT **

SELECT * FROM t1;

SHOW STATUS LIKE 'Qcache_queries_in_cache';
--echo 2 Expected

SELECT SQL_CACHE value FROM t1;

SHOW STATUS LIKE 'Qcache_queries_in_cache';
--echo 3 Expected

SELECT SQL_NO_CACHE value FROM t1;

SHOW STATUS LIKE 'Qcache_queries_in_cache';
--echo 3 Expected

--echo '#--------------------FN_DYNVARS_135_05-------------------------#'
#
# Testing with store procedure
#

--echo Testing with store procedure

delimiter |;

CREATE PROCEDURE testProcHit()
BEGIN
        SELECT * FROM t1;
        SELECT SQL_CACHE value FROM t1;
END|

delimiter ;|

CALL testProcHit();

SHOW STATUS LIKE 'Qcache_queries_in_cache';
--echo Expected value : 3
--echo ==================
--echo Bug#35388: Stored procedure execution causes improper behavior of query cache.


--echo '#--------------------FN_DYNVARS_135_06-------------------------#'
#
# TESTING FOR VALUE OFF or 0
#

--echo FLUSHING CACHE
SET @@GLOBAL.query_cache_size = 0;
SET @@GLOBAL.query_cache_size = 65536;

--echo ** Turning cache to OFF **

SET @@query_cache_type = OFF;
SELECT @@query_cache_type;

SELECT id FROM t1;

SHOW STATUS LIKE 'Qcache_queries_in_cache';
--echo 0 Expected

--echo '#--------------------FN_DYNVARS_135_07-------------------------#'
#
# TESTING QUERY WITH SQL_CACHE
#

SELECT SQL_CACHE id FROM t1;

SHOW STATUS LIKE 'Qcache_queries_in_cache';
--echo 0 Expected

SELECT SQL_CACHE * FROM t1;

SHOW STATUS LIKE 'Qcache_queries_in_cache';
--echo 0 Expected

--echo '#--------------------FN_DYNVARS_135_08-------------------------#'
#
# TESTING FOR VALUE DEMAND or 2
#

--echo FLUSHING CACHE
SET @@GLOBAL.query_cache_size = 0;
SET @@GLOBAL.query_cache_size = 65536;

--echo ** Turning cache to DEMAND **

SET @@query_cache_type = DEMAND;
SELECT @@query_cache_type;

--echo '#--------------------FN_DYNVARS_135_09-------------------------#'
#
# TESTING NORMAL QUERY
#

SELECT value,id FROM t1;

SHOW STATUS LIKE 'Qcache_queries_in_cache';
--echo 0 Expected

SELECT SQL_CACHE * FROM t1;

SHOW STATUS LIKE 'Qcache_queries_in_cache';
--echo 0 Expected

#
#       GLOBAL SCOPE
#

--echo ** TESTING GLOBAL SCOPE **

--echo '#--------------------FN_DYNVARS_135_10-------------------------#'
#
# Turning global cache to OFF
#

SET @@GLOBAL.query_cache_type = OFF;

--echo ** Connecting con1 using root **
connect (con1,localhost,root,,);

--echo ** Connection con1 **
connection con1;

SELECT @@query_cache_type;
--echo OFF Expected

--echo '#--------------------FN_DYNVARS_135_11-------------------------#'
#
# Turning global cache to DEMAND
#
SET @@GLOBAL.query_cache_type = DEMAND;

--echo ** Connecting con2 using root **
connect (con2,localhost,root,,);

--echo ** Connection con2 **
connection con2;

SELECT @@query_cache_type;
--echo DEMAND Expected

--echo '#--------------------FN_DYNVARS_135_12-------------------------#'
#
# Turning global cache to ON
#
SET @@GLOBAL.query_cache_type = ON;

--echo ** Connecting con3 using root **
connect (con3,localhost,root,,);
--echo ** Connection con3 **
connection con3;

SELECT @@query_cache_type;
--echo ON Expected

--echo
--echo ** Cleanup **
--echo
#
# Cleanup
#

--echo Disconnecting con1,con2,con3
disconnect con1;
disconnect con2;
disconnect con3;

--echo ** Connection con0 **
connection con0;

SET @@GLOBAL.query_cache_type = @start_global_value;
SET @@SESSION.query_cache_type = @start_session_value;
SET GLOBAL query_cache_size = @start_query_cache_size;

DROP TABLE t1;

DROP PROCEDURE testProcHit;

--echo Disconnecting con0
disconnect con0;

