1SET @query_cache_type=         @@global.query_cache_type;
2SET @query_cache_limit=        @@global.query_cache_limit;
3SET @query_cache_min_res_unit= @@global.query_cache_min_res_unit;
4SET @query_cache_size=         @@global.query_cache_size;
5# Bug#28249 Query Cache returns wrong result with concurrent insert/ certain lock
6connect  user1,localhost,root,,test,,;
7connect  user2,localhost,root,,test,,;
8connect  user3,localhost,root,,test,,;
9connection user1;
10SET GLOBAL query_cache_type=1;
11SET GLOBAL query_cache_limit=10000;
12SET GLOBAL query_cache_min_res_unit=0;
13SET GLOBAL query_cache_size= 102400;
14FLUSH TABLES;
15DROP TABLE IF EXISTS t1, t2;
16CREATE TABLE t1 (a INT);
17CREATE TABLE t2 (a INT);
18INSERT INTO t1 VALUES (1),(2),(3);
19connection user2;
20LOCK TABLE t2 WRITE;
21connection user1;
22# "send" the next select, "reap" the result later.
23# The select will be blocked by the write lock on the t1.
24SELECT *, (SELECT COUNT(*) FROM t2) FROM t1;
25connection user3;
26# Poll till the select of connection user1 is blocked by the write lock on t1.
27SELECT user,command,state,info FROM information_schema.processlist
28WHERE state = 'Waiting for table metadata lock'
29  AND info = 'SELECT *, (SELECT COUNT(*) FROM t2) FROM t1';
30user	command	state	info
31root	Query	Waiting for table metadata lock	SELECT *, (SELECT COUNT(*) FROM t2) FROM t1
32INSERT INTO t1 VALUES (4);
33connection user2;
34UNLOCK TABLES;
35connection user1;
36# Collecting ("reap") the result from the previously blocked select.
37# The printing of the result (varies between 3 and 4 rows) set has to be suppressed.
38connection user3;
39# The next select enforces that effects of "concurrent_inserts" like the
40# record with a = 4 is missing in result sets can no more happen.
41SELECT 1 FROM t1 WHERE a = 4;
421
431
44connection user1;
45# The next result set must contain 4 rows.
46SELECT *, (SELECT COUNT(*) FROM t2) FROM t1;
47a	(SELECT COUNT(*) FROM t2)
481	0
492	0
503	0
514	0
52RESET QUERY CACHE;
53SELECT *, (SELECT COUNT(*) FROM t2) FROM t1;
54a	(SELECT COUNT(*) FROM t2)
551	0
562	0
573	0
584	0
59DROP TABLE t1,t2;
60connection default;
61disconnect user1;
62disconnect user2;
63disconnect user3;
64SET GLOBAL query_cache_type=         @query_cache_type;
65SET GLOBAL query_cache_limit=        @query_cache_limit;
66SET GLOBAL query_cache_min_res_unit= @query_cache_min_res_unit;
67SET GLOBAL query_cache_size=         @query_cache_size;
68