3 files changed, 86 insertions, 28 deletions
diff --git a/mysql-test/suite/innodb/r/innodb_query_cache.result b/mysql-test/suite/innodb/r/innodb_query_cache.result
new file mode 100644
index 00000000000..bbacbdfd748
--- /dev/null
+++ b/mysql-test/suite/innodb/r/innodb_query_cache.result
@@ -0,0 +1,39 @@
+#
+# MDEV-16087 Inconsistent SELECT results when query cache is enabled
+#
+set GLOBAL query_cache_type=ON;
+set LOCAL query_cache_type=ON;
+create table t1 (id bigint(20) auto_increment, primary key (id)) ENGINE=InnoDB;
+create table t2 (id bigint(20) auto_increment, primary key (id)) ENGINE=InnoDB;
+create table t3 (id bigint(20) auto_increment, primary key (id)) ENGINE=InnoDB;
+connect  con1,localhost,root;
+START TRANSACTION WITH CONSISTENT SNAPSHOT;
+select * from t2;
+id
+connection default;
+insert into t3 () values ();
+connection con1;
+insert into t1 () values ();
+select * from t3;
+id
+connect  con2,localhost,root;
+START TRANSACTION WITH CONSISTENT SNAPSHOT;
+select * from t3;
+id
+1
+select * from t3;
+id
+1
+select sql_no_cache * from t3;
+id
+1
+rollback;
+connection con1;
+rollback;
+disconnect con1;
+disconnect con2;
+connection default;
+drop table t1;
+drop table t2;
+drop table t3;
+set GLOBAL query_cache_type=default;
diff --git a/mysql-test/suite/innodb/t/innodb_bug27216817.test b/mysql-test/suite/innodb/t/innodb_bug27216817.test
deleted file mode 100644
index a93932b4a04..00000000000
--- a/mysql-test/suite/innodb/t/innodb_bug27216817.test
+++ /dev/null
@@ -1,28 +0,0 @@
-#
-# BUG#27216817: INNODB: FAILING ASSERTION:
-#               PREBUILT->TABLE->N_MYSQL_HANDLES_OPENED == 1
-#
-
-source include/have_innodb.inc;
-create table t1 (a int not null, b int not null) engine=innodb;
-insert t1 values (1,2),(3,4);
-
-lock table t1 write, t1 tr read;
-flush status;
-alter table t1 add primary key (b);
-show status like 'Handler_read_rnd_next';
-unlock tables;
-alter table t1 drop primary key;
-
-lock table t1 write;
-flush status;
-alter table t1 add primary key (b);
-show status like 'Handler_read_rnd_next';
-unlock tables;
-alter table t1 drop primary key;
-
-flush status;
-alter table t1 add primary key (b);
-show status like 'Handler_read_rnd_next';
-
-drop table t1;
diff --git a/mysql-test/suite/innodb/t/innodb_query_cache.test b/mysql-test/suite/innodb/t/innodb_query_cache.test
new file mode 100644
index 00000000000..7dbdf986946
--- /dev/null
+++ b/mysql-test/suite/innodb/t/innodb_query_cache.test
@@ -0,0 +1,47 @@
+-- source include/have_innodb.inc
+-- source include/have_query_cache.inc
+-- source include/not_embedded.inc
+
+--echo #
+--echo # MDEV-16087 Inconsistent SELECT results when query cache is enabled
+--echo #
+
+set GLOBAL query_cache_type=ON;
+set LOCAL query_cache_type=ON;
+
+create table t1 (id bigint(20) auto_increment, primary key (id)) ENGINE=InnoDB;
+create table t2 (id bigint(20) auto_increment, primary key (id)) ENGINE=InnoDB;
+create table t3 (id bigint(20) auto_increment, primary key (id)) ENGINE=InnoDB;
+
+connect (con1,localhost,root);
+START TRANSACTION WITH CONSISTENT SNAPSHOT;
+select * from t2;
+
+connection default;
+insert into t3 () values ();
+
+connection con1;
+insert into t1 () values ();
+select * from t3;
+
+connect (con2,localhost,root);
+START TRANSACTION WITH CONSISTENT SNAPSHOT;
+select * from t3;
+select * from t3;
+select sql_no_cache * from t3;
+
+rollback;
+
+connection con1;
+
+rollback;
+
+disconnect con1;
+disconnect con2;
+connection default;
+
+drop table t1;
+drop table t2;
+drop table t3;
+
+set GLOBAL query_cache_type=default;