1 files changed, 113 insertions, 0 deletions
diff --git a/mysql-test/main/query_cache_28249.test b/mysql-test/main/query_cache_28249.test
new file mode 100644
index 00000000..a08371fe
--- /dev/null
+++ b/mysql-test/main/query_cache_28249.test
@@ -0,0 +1,113 @@
+### t/query_cache_28249.test ###
+#
+# Test for
+# Bug#28249 Query Cache returns wrong result with concurrent insert / certain lock
+#
+# Last modification:
+# 2008-11-27 mleich - Move this test out of query_cache.test
+#                   - Fix Bug#40179 Test main.query_cache failing randomly on Pushbuild,
+#                                   test weakness
+#                   - Minor improvements (comments,formatting etc.)
+#
+
+--source include/have_query_cache.inc
+--source include/not_embedded.inc
+
+SET @query_cache_type=         @@global.query_cache_type;
+SET @query_cache_limit=        @@global.query_cache_limit;
+SET @query_cache_min_res_unit= @@global.query_cache_min_res_unit;
+SET @query_cache_size=         @@global.query_cache_size;
+
+--echo # Bug#28249 Query Cache returns wrong result with concurrent insert/ certain lock
+connect (user1,localhost,root,,test,,);
+connect (user2,localhost,root,,test,,);
+connect (user3,localhost,root,,test,,);
+
+connection user1;
+
+SET GLOBAL query_cache_type=1;
+SET GLOBAL query_cache_limit=10000;
+SET GLOBAL query_cache_min_res_unit=0;
+SET GLOBAL query_cache_size= 102400;
+
+FLUSH TABLES;
+--disable_warnings
+DROP TABLE IF EXISTS t1, t2;
+--enable_warnings
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+INSERT INTO t1 VALUES (1),(2),(3);
+
+connection user2;
+LOCK TABLE t2 WRITE;
+
+connection user1;
+--echo # "send" the next select, "reap" the result later.
+--echo # The select will be blocked by the write lock on the t1.
+let $select_for_qc =
+SELECT *, (SELECT COUNT(*) FROM t2) FROM t1;
+send;
+eval $select_for_qc;
+
+connection user3;
+# Typical information_schema.processlist content after sufficient sleep time
+#   ID  USER  COMMAND  TIME  STATE   INFO
+#   ....
+#   2   root  Query       5  Waiting for table metadata lock  SELECT *, (SELECT COUNT(*) FROM t2) FROM t1
+#   ....
+#                            XXXXXX  XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
+# The values marked with 'X' must be reached.
+--echo # Poll till the select of connection user1 is blocked by the write lock on t1.
+let $wait_condition= SELECT COUNT(*) = 1 FROM information_schema.processlist
+WHERE state = 'Waiting for table metadata lock'
+  AND info = '$select_for_qc';
+--source include/wait_condition.inc
+eval
+SELECT user,command,state,info FROM information_schema.processlist
+WHERE state = 'Waiting for table metadata lock'
+  AND info = '$select_for_qc';
+INSERT INTO t1 VALUES (4);
+
+connection user2;
+UNLOCK TABLES;
+
+connection user1;
+#
+# Since the lock ordering rule in thr_multi_lock depends on
+# pointer values, from execution to execution we might have
+# different lock order, and therefore, sometimes lock t1 and block
+# on t2, and sometimes block on t2 right away. In the second case,
+# the following insert succeeds, and only then this select can
+# proceed, and we actually test nothing, as the very first select
+# returns 4 rows right away.
+# It's fine to have a test case that covers the problematic area
+# at least once in a while.
+--echo # Collecting ("reap") the result from the previously blocked select.
+--echo # The printing of the result (varies between 3 and 4 rows) set has to be suppressed.
+--disable_result_log
+--reap
+--enable_result_log
+
+connection user3;
+--echo # The next select enforces that effects of "concurrent_inserts" like the
+--echo # record with a = 4 is missing in result sets can no more happen.
+SELECT 1 FROM t1 WHERE a = 4;
+
+connection user1;
+--echo # The next result set must contain 4 rows.
+# If not, we have a regression of Bug#28249
+eval $select_for_qc;
+RESET QUERY CACHE;
+eval $select_for_qc;
+
+DROP TABLE t1,t2;
+
+connection default;
+disconnect user1;
+disconnect user2;
+disconnect user3;
+
+SET GLOBAL query_cache_type=         @query_cache_type;
+SET GLOBAL query_cache_limit=        @query_cache_limit;
+SET GLOBAL query_cache_min_res_unit= @query_cache_min_res_unit;
+SET GLOBAL query_cache_size=         @query_cache_size;