Below is the list of changes that have just been committed into a local
4.1 repository of jimw. When jimw 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/in...urce-tree.html

ChangeSet
1.2108 05/03/11 11:07:32 jimw (AT) mysql (DOT) com +3 -0
Merge test and results

mysql-test/t/query_cache.test
1.39 05/03/11 11:07:29 jimw (AT) mysql (DOT) com +13 -13
Merge tests

mysql-test/r/query_cache.result
1.52 05/03/11 11:07:29 jimw (AT) mysql (DOT) com +1 -1
Update results

sql/sql_table.cc
1.275 05/03/11 11:06:18 jimw (AT) mysql (DOT) com +0 -0
Auto merged

mysql-test/t/query_cache.test
1.16.3.2 05/03/11 11:06:18 jimw (AT) mysql (DOT) com +0 -0
Turn off EOLN_NATIVE flag

mysql-test/r/query_cache.result
1.15.3.2 05/03/11 11:06:18 jimw (AT) mysql (DOT) com +0 -0
Turn off EOLN_NATIVE flag

# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: jimw
# Host: rama.(none)
# Root: /home/jimw/my/mysql-4.1-clean/RESYNC

--- 1.274/sql/sql_table.cc 2005-02-02 10:27:58 -08:00
+++ 1.275/sql/sql_table.cc 2005-03-11 11:06:18 -08:00
@@ -1971,7 +1971,9 @@
thd->exit_cond(old_message);
if (thd->killed)
goto err;
- open_for_modify=0;
+ /* Flush entries in the query cache involving this table. */
+ query_cache_invalidate3(thd, table->table, 0);
+ open_for_modify= 0;
}

int result_code = (table->table->file->*operator_func)(thd, check_opt);

--- 1.15.3.1/mysql-test/r/query_cache.result 2005-03-07 18:15:17 -08:00
+++ 1.52/mysql-test/r/query_cache.result 2005-03-11 11:07:29 -08:00
@@ -44,7 +44,7 @@
insert into t1 values (1),(2),(3);
create table t2 (a int not null);
insert into t2 values (4),(5),(6);
-create table t3 (a int not null) type=MERGE UNION=(t1,t2) INSERT_METHOD=FIRST;
+create table t3 (a int not null) engine=MERGE UNION=(t1,t2) INSERT_METHOD=FIRST;
select * from t3;
a
1
@@ -292,7 +292,7 @@
select ENCRYPT("test") from t1;
ENCRYPT("test")
select LAST_INSERT_ID() from t1;
-last_insert_id()
+LAST_INSERT_ID()
select RAND() from t1;
RAND()
select UNIX_TIMESTAMP() from t1;
@@ -301,6 +301,11 @@
USER()
select benchmark(1,1) from t1;
benchmark(1,1)
+explain extended select benchmark(1,1) from t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 0 const row not found
+Warnings:
+Note 1003 select sql_no_cache benchmark(1,1) AS `benchmark(1,1)` from test.t1
show status like "Qcache_queries_in_cache";
Variable_name Value
Qcache_queries_in_cache 0
@@ -382,8 +387,9 @@
Variable_name Value
Qcache_queries_in_cache 1
drop table t1;
-create table t1 (a char(1) not null);
-insert into t1 values("á");
+create table t1 (a char(1) not null collate koi8r_general_ci);
+insert into t1 values(_koi8r"á");
+set CHARACTER SET koi8r;
select * from t1;
a
á
@@ -510,6 +516,74 @@
show status like "Qcache_queries_in_cache";
Variable_name Value
Qcache_queries_in_cache 0
+show global variables like "query_cache_min_res_unit";
+Variable_name Value
+query_cache_min_res_unit 4096
+set GLOBAL query_cache_min_res_unit=1001;
+show global variables like "query_cache_min_res_unit";
+Variable_name Value
+query_cache_min_res_unit 1008
+create table t1 (a int not null);
+insert into t1 values (1),(2),(3);
+create table t2 (a int not null);
+insert into t2 values (1),(2),(3);
+select * from t1;
+a
+1
+2
+3
+select * from t1;
+a
+1
+2
+3
+select * from t2;
+a
+1
+2
+3
+select * from t2;
+a
+1
+2
+3
+show status like "Qcache_hits";
+Variable_name Value
+Qcache_hits 11
+show status like "Qcache_queries_in_cache";
+Variable_name Value
+Qcache_queries_in_cache 2
+drop table t1;
+select a from t2;
+a
+1
+2
+3
+select a from t2;
+a
+1
+2
+3
+show status like "Qcache_hits";
+Variable_name Value
+Qcache_hits 12
+show status like "Qcache_queries_in_cache";
+Variable_name Value
+Qcache_queries_in_cache 2
+drop table t2;
+set GLOBAL query_cache_min_res_unit=default;
+show global variables like "query_cache_min_res_unit";
+Variable_name Value
+query_cache_min_res_unit 4096
+create table t1 (a int not null);
+insert into t1 values (1);
+select "aaa" from t1;
+aaa
+aaa
+select "AAA" from t1;
+AAA
+AAA
+drop table t1;
create table t1 (a int);
set GLOBAL query_cache_size=1000;
show global variables like "query_cache_size";
@@ -518,24 +592,32 @@
select * from t1;
a
set GLOBAL query_cache_size=1024;
+Warnings:
+Warning 1282 Query cache failed to set size 1024; new query cache size is 0
show global variables like "query_cache_size";
Variable_name Value
query_cache_size 0
select * from t1;
a
set GLOBAL query_cache_size=10240;
+Warnings:
+Warning 1282 Query cache failed to set size 10240; new query cache size is 0
show global variables like "query_cache_size";
Variable_name Value
query_cache_size 0
select * from t1;
a
set GLOBAL query_cache_size=20480;
+Warnings:
+Warning 1282 Query cache failed to set size 20480; new query cache size is 0
show global variables like "query_cache_size";
Variable_name Value
query_cache_size 0
select * from t1;
a
set GLOBAL query_cache_size=40960;
+Warnings:
+Warning 1282 Query cache failed to set size 40960; new query cache size is 0
show global variables like "query_cache_size";
Variable_name Value
query_cache_size 0
@@ -585,6 +667,7 @@
show status like "Qcache_queries_in_cache";
Variable_name Value
Qcache_queries_in_cache 2
+update t1 set i=(select distinct 1 from (select * from t2) a);
drop table t1, t2, t3;
use mysql;
select * from db;
@@ -603,28 +686,32 @@
2
alter table t1 rename to t2;
select * from t1 where id=2;
-Table 'test.t1' doesn't exist
+ERROR 42S02: Table 'test.t1' doesn't exist
drop table t2;
select * from t1 where id=2;
-Table 'test.t1' doesn't exist
+ERROR 42S02: Table 'test.t1' doesn't exist
create table t1 (word char(20) not null);
select * from t1;
word
show status like "Qcache_queries_in_cache";
Variable_name Value
Qcache_queries_in_cache 1
-load data infile '../../std_data/words.dat' into table t1;
+load data infile 'TEST_DIR/std_data/words.dat' into table t1;
show status like "Qcache_queries_in_cache";
Variable_name Value
Qcache_queries_in_cache 0
+select count(*) from t1;
+count(*)
+70
drop table t1;
-drop table if exists t1;
create table t1 (a int);
insert into t1 values (1),(2),(3);
show status like "Qcache_queries_in_cache";
Variable_name Value
Qcache_queries_in_cache 0
-select * from t1 into outfile "query_caceh.out.file";
+select * from t1 into outfile "query_cache.out.file";
+select * from t1 into outfile "query_cache.out.file";
+ERROR HY000: File 'query_cache.out.file' already exists
select * from t1 limit 1 into dumpfile "query_cache.dump.file";
show status like "Qcache_queries_in_cache";
Variable_name Value
@@ -716,6 +803,124 @@
select * from t1;
a
drop table t1;
+drop table t1;
+SET NAMES koi8r;
+CREATE TABLE t1 (a char(1) character set koi8r);
+INSERT INTO t1 VALUES (_koi8r'á'),(_koi8r'Á');
+SELECT a,'Â','â'='Â' FROM t1;
+a  'â'='Â'
+á Â 1
+Á Â 1
+show status like "Qcache_hits";
+Variable_name Value
+Qcache_hits 6
+show status like "Qcache_queries_in_cache";
+Variable_name Value
+Qcache_queries_in_cache 1
+set collation_connection=koi8r_bin;
+SELECT a,'Â','â'='Â' FROM t1;
+a  'â'='Â'
+á Â 0
+Á Â 0
+show status like "Qcache_hits";
+Variable_name Value
+Qcache_hits 6
+show status like "Qcache_queries_in_cache";
+Variable_name Value
+Qcache_queries_in_cache 2
+set character_set_client=cp1251;
+SELECT a,'Â','â'='Â' FROM t1;
+a ÷ '×'='÷'
+á ÷ 0
+Á ÷ 0
+show status like "Qcache_hits";
+Variable_name Value
+Qcache_hits 6
+show status like "Qcache_queries_in_cache";
+Variable_name Value
+Qcache_queries_in_cache 3
+set character_set_results=cp1251;
+SELECT a,'Â','â'='Â' FROM t1;
+a  'â'='Â'
+À Â 0
+à Â 0
+show status like "Qcache_hits";
+Variable_name Value
+Qcache_hits 6
+show status like "Qcache_queries_in_cache";
+Variable_name Value
+Qcache_queries_in_cache 4
+DROP TABLE t1;
+CREATE TABLE t1 (a int(1));
+CREATE DATABASE mysqltest;
+USE mysqltest;
+DROP DATABASE mysqltest;
+SELECT * FROM test.t1;
+a
+USE test;
+DROP TABLE t1;
+set character_set_results=null;
+select @@character_set_results;
+@@character_set_results
+NULL
+set character_set_results=default;
+set GLOBAL query_cache_size=1355776;
+create table t1 (id int auto_increment primary key, c char(25));
+insert into t1 set c = repeat('x',24);
+insert into t1 set c = concat(repeat('x',24),'x');
+insert into t1 set c = concat(repeat('x',24),'w');
+insert into t1 set c = concat(repeat('x',24),'y');
+set max_sort_length=200;
+select c from t1 order by c, id;
+c
+xxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxw
+xxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxy
+reset query cache;
+set max_sort_length=20;
+select c from t1 order by c, id;
+c
+xxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxw
+xxxxxxxxxxxxxxxxxxxxxxxxy
+set max_sort_length=200;
+select c from t1 order by c, id;
+c
+xxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxw
+xxxxxxxxxxxxxxxxxxxxxxxxx
+xxxxxxxxxxxxxxxxxxxxxxxxy
+set max_sort_length=default;
+select '1' || '3' from t1;
+'1' || '3'
+1
+1
+1
+1
+set SQL_MODE=oracle;
+select '1' || '3' from t1;
+'1' || '3'
+13
+13
+13
+13
+set SQL_MODE=default;
+drop table t1;
+create table t1 (a varchar(20), b int);
+insert into t1 values ('12345678901234567890', 1);
+set group_concat_max_len=10;
+select group_concat(a) FROM t1 group by b;
+group_concat(a)
+1234567890
+Warnings:
+Warning 1260 1 line(s) were cut by GROUP_CONCAT()
+set group_concat_max_len=1024;
+select group_concat(a) FROM t1 group by b;
+group_concat(a)
+12345678901234567890
+set group_concat_max_len=default;
drop table t1;
set global query_cache_size=1024*1024;
flush query cache;

--- 1.16.3.1/mysql-test/t/query_cache.test 2005-03-07 18:15:17 -08:00
+++ 1.39/mysql-test/t/query_cache.test 2005-03-11 11:07:29 -08:00
@@ -11,8 +11,10 @@
flush query cache; # This crashed in some versions
reset query cache;
flush status;
+--disable_warnings
drop table if exists t1,t2,t3,t4,t11,t21;
drop database if exists mysqltest;
+--enable_warnings

#
# First simple test
@@ -43,7 +45,7 @@
insert into t1 values (1),(2),(3);
create table t2 (a int not null);
insert into t2 values (4),(5),(6);
-create table t3 (a int not null) type=MERGE UNION=(t1,t2) INSERT_METHOD=FIRST;
+create table t3 (a int not null) engine=MERGE UNION=(t1,t2) INSERT_METHOD=FIRST;
# insert
select * from t3;
select * from t3;
@@ -183,6 +185,7 @@
select UNIX_TIMESTAMP() from t1;
select USER() from t1;
select benchmark(1,1) from t1;
+explain extended select benchmark(1,1) from t1;
show status like "Qcache_queries_in_cache";
#
# Tests when the cache is filled
@@ -263,8 +266,9 @@
#
# Charset convertion (cp1251_koi8 always present)
#
-create table t1 (a char(1) not null);
-insert into t1 values("á");
+create table t1 (a char(1) not null collate koi8r_general_ci);
+insert into t1 values(_koi8r"á");
+set CHARACTER SET koi8r;
select * from t1;
set CHARACTER SET cp1251_koi8;
select * from t1;
@@ -347,6 +351,40 @@
show status like "Qcache_queries_in_cache";

#
+# Test of min result data unit size changing
+#
+show global variables like "query_cache_min_res_unit";
+set GLOBAL query_cache_min_res_unit=1001;
+show global variables like "query_cache_min_res_unit";
+create table t1 (a int not null);
+insert into t1 values (1),(2),(3);
+create table t2 (a int not null);
+insert into t2 values (1),(2),(3);
+select * from t1;
+select * from t1;
+select * from t2;
+select * from t2;
+show status like "Qcache_hits";
+show status like "Qcache_queries_in_cache";
+drop table t1;
+select a from t2;
+select a from t2;
+show status like "Qcache_hits";
+show status like "Qcache_queries_in_cache";
+drop table t2;
+set GLOBAL query_cache_min_res_unit=default;
+show global variables like "query_cache_min_res_unit";
+
+#
+# Case sensitive test
+#
+create table t1 (a int not null);
+insert into t1 values (1);
+select "aaa" from t1;
+select "AAA" from t1;
+drop table t1;
+
+#
# Test of query cache resizing
#
create table t1 (a int);
@@ -392,6 +430,7 @@
show status like "Qcache_queries_in_cache";
select * from t3;
show status like "Qcache_queries_in_cache";
+update t1 set i=(select distinct 1 from (select * from t2) a);
drop table t1, t2, t3;

#
@@ -427,18 +466,21 @@
create table t1 (word char(20) not null);
select * from t1;
show status like "Qcache_queries_in_cache";
-load data infile '../../std_data/words.dat' into table t1;
+--replace_result $MYSQL_TEST_DIR TEST_DIR
+eval load data infile '$MYSQL_TEST_DIR/std_data/words.dat' into table t1;
show status like "Qcache_queries_in_cache";
+select count(*) from t1;
drop table t1;

#
# INTO OUTFILE/DUMPFILE test
#
-drop table if exists t1;
create table t1 (a int);
insert into t1 values (1),(2),(3);
show status like "Qcache_queries_in_cache";
-select * from t1 into outfile "query_caceh.out.file";
+select * from t1 into outfile "query_cache.out.file";
+--error 1086
+select * from t1 into outfile "query_cache.out.file";
select * from t1 limit 1 into dumpfile "query_cache.dump.file";
show status like "Qcache_queries_in_cache";
drop table t1;
@@ -456,7 +498,6 @@
SET OPTION SQL_SELECT_LIMIT=DEFAULT;
drop table t1;

-
#
# query cache crash on using same table twice in one query test
#
@@ -465,7 +506,6 @@
flush status;
set GLOBAL query_cache_size=1048576;

-
create table t1 (a int not null);
insert into t1 values (1),(2),(3);
create table t2 (a text not null);
@@ -531,6 +571,105 @@
primary key);
select * from t1;
drop table t1;
+drop table t1;
+
+#
+# Test character set related variables:
+# character_set_result
+# character_set_client
+# charactet_set_connection/collation_connection
+# If at least one of the above variables has changed,
+# the cached query can't be reused. In the below test
+# absolutely the same query is used several times,
+# SELECT should fetch different results for every instance.
+# No hits should be produced.
+# New cache entry should appear for every SELECT.
+#
+SET NAMES koi8r;
+CREATE TABLE t1 (a char(1) character set koi8r);
+INSERT INTO t1 VALUES (_koi8r'á'),(_koi8r'Á');
+#
+# Run select
+#
+SELECT a,'Â','â'='Â' FROM t1;
+show status like "Qcache_hits";
+show status like "Qcache_queries_in_cache";
+#
+# Change collation_connection and run the same query again
+#
+set collation_connection=koi8r_bin;
+SELECT a,'Â','â'='Â' FROM t1;
+show status like "Qcache_hits";
+show status like "Qcache_queries_in_cache";
+#
+# Now change character_set_client and run the same query again
+#
+set character_set_client=cp1251;
+SELECT a,'Â','â'='Â' FROM t1;
+show status like "Qcache_hits";
+show status like "Qcache_queries_in_cache";
+#
+# And finally change character_set_results and run the same query again
+#
+set character_set_results=cp1251;
+SELECT a,'Â','â'='Â' FROM t1;
+show status like "Qcache_hits";
+show status like "Qcache_queries_in_cache";
+#
+# Keep things tidy
+#
+DROP TABLE t1;
+
+#
+# DROP current database test
+#
+CREATE TABLE t1 (a int(1));
+CREATE DATABASE mysqltest;
+USE mysqltest;
+DROP DATABASE mysqltest;
+SELECT * FROM test.t1;
+USE test;
+DROP TABLE t1;
+
+#
+# charset with NULL
+#
+set character_set_results=null;
+select @@character_set_results;
+set character_set_results=default;
+
+#
+# query cache and environment variables
+#
+# max_sort_length
+set GLOBAL query_cache_size=1355776;
+create table t1 (id int auto_increment primary key, c char(25));
+insert into t1 set c = repeat('x',24);
+insert into t1 set c = concat(repeat('x',24),'x');
+insert into t1 set c = concat(repeat('x',24),'w');
+insert into t1 set c = concat(repeat('x',24),'y');
+set max_sort_length=200;
+select c from t1 order by c, id;
+reset query cache;
+set max_sort_length=20;
+select c from t1 order by c, id;
+set max_sort_length=200;
+select c from t1 order by c, id;
+set max_sort_length=default;
+# sql_mode
+select '1' || '3' from t1;
+set SQL_MODE=oracle;
+select '1' || '3' from t1;
+set SQL_MODE=default;
+drop table t1;
+# group_concat_max_len
+create table t1 (a varchar(20), b int);
+insert into t1 values ('12345678901234567890', 1);
+set group_concat_max_len=10;
+select group_concat(a) FROM t1 group by b;
+set group_concat_max_len=1024;
+select group_concat(a) FROM t1 group by b;
+set group_concat_max_len=default;
drop table t1;

# Bug #8480: REPAIR TABLE needs to flush the table from the query cache

--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: http://lists.mysql.com/internals?uns...ie.nctu.edu.tw