Description:
I set the config "query_cache_size=1355776" and "query_cache_type=1" in the config file in order to use Query_cache.
However, for the same "Select *" operation, the Query_cache failed to identify them and stores the redundant queries, after I change the option "time_zone" on the fly.
It is not reasonable because the "Selected" table/rows are not time type or date type;
time_zone should not affect the query result. Similar situations for "default_week_format" and "lc_time_names". It would be more efficient for Query_cache to identify them accurately.
How to repeat:
I put the test case and result followed.
Test:
set time_zone = SYSTEM;
reset query cache;
flush status;
create database if not exists mysqltest;
create table mysqltest.t1 (i int not null);
insert into mysqltest.t1 (i) values (1);
show status like "Qcache_queries_in_cache";
select * from mysqltest.t1 order by i;
show status like "Qcache_queries_in_cache";
set time_zone = "+9:00";
select * from mysqltest.t1 order by i;
show status like "Qcache_queries_in_cache";
select * from mysqltest.t1 order by i;
show status like "Qcache_queries_in_cache";
Result:
set time_zone = SYSTEM;
reset query cache;
flush status;
create database if not exists mysqltest;
create table mysqltest.t1 (i int not null);
insert into mysqltest.t1 (i) values (1);
show status like "Qcache_queries_in_cache";
Variable_name Value
Qcache_queries_in_cache 0
select * from mysqltest.t1 order by i;
i
1
show status like "Qcache_queries_in_cache";
Variable_name Value
Qcache_queries_in_cache 1
set time_zone = "+9:00";
select * from mysqltest.t1 order by i;
i
1
show status like "Qcache_queries_in_cache";
Variable_name Value
Qcache_queries_in_cache 2
select * from mysqltest.t1 order by i;
i
1
show status like "Qcache_queries_in_cache";
Variable_name Value
Qcache_queries_in_cache 2
Suggested fix:
time_zone should not affect the query result, which are not time type of date type.
