Bug#56423: Different count with SELECT and CREATE SELECT queries

This is the 5.5 version of the fix. The 5.1 version was too complicated to
merge and was null merged.

This is a regression from the fix for bug no 38999. A storage engine capable
of reading only a subset of a table's columns updates corresponding bits in
the read buffer to signal that it has read NULL values for the corresponding
columns. It cannot, and should not, update any other bits. Bug no 38999
occurred because the implementation of UPDATE statements compare the NULL bits
using memcmp, inadvertently comparing bits that were never requested from the
storage engine. The regression was caused by the storage engine trying to
alleviate the situation by writing to all NULL bits, even those that it had no
knowledge of. This has devastating effects for the index merge algorithm,
which relies on all NULL bits, except those explicitly requested, being left
unchanged.

The fix reverts the fix for bug no 38999 in both InnoDB and InnoDB plugin and
changes the server's method of comparing records. For engines that always read
entire rows, we proceed as usual. For engines capable of reading only select
columns, the record buffers are now compared on a column by column basis. An
assertion was also added so that non comparable buffers are never read. Some
relevant copy-pasted code was also consolidated in a new function.

1 files changed, 112 insertions, 0 deletions

diff --git a/mysql-test/include/index_merge2.inc b/mysql-test/include/index_merge2.inc
index 9b98eb3ebf2..23c8c6466c7 100644
--- a/mysql-test/include/index_merge2.inc
+++ b/mysql-test/include/index_merge2.inc
@@ -351,3 +351,115 @@ explain select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 4
 select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
 drop table t1;
 
+--echo #
+--echo # Bug#56423: Different count with SELECT and CREATE SELECT queries
+--echo #
+
+CREATE TABLE t1 (
+  a INT,
+  b INT,
+  c INT,
+  d INT,
+  PRIMARY KEY (a),
+  KEY (c),
+  KEY bd (b,d)
+);
+
+INSERT INTO t1 VALUES
+(1, 0, 1, 0),
+(2, 1, 1, 1),
+(3, 1, 1, 1),
+(4, 0, 1, 1);
+
+EXPLAIN
+SELECT a
+FROM t1
+WHERE c = 1 AND b = 1 AND d = 1;
+
+CREATE TABLE t2 ( a INT )
+SELECT a
+FROM t1
+WHERE c = 1 AND b = 1 AND d = 1;
+
+SELECT * FROM t2;
+
+DROP TABLE t1, t2;
+
+CREATE TABLE t1( a INT, b INT, KEY(a), KEY(b) );
+INSERT INTO t1 VALUES (1, 2), (1, 2), (1, 2), (1, 2);
+SELECT * FROM t1 FORCE INDEX(a, b) WHERE a = 1 AND b = 2;
+
+DROP TABLE t1;
+
+--echo # Code coverage of fix.
+CREATE TABLE t1 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT);
+INSERT INTO t1 (b) VALUES (1);
+UPDATE t1 SET b = 2 WHERE a = 1;
+SELECT * FROM t1;
+
+CREATE TABLE t2 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b VARCHAR(1) );
+INSERT INTO t2 (b) VALUES ('a');
+UPDATE t2 SET b = 'b' WHERE a = 1;
+SELECT * FROM t2;
+
+DROP TABLE t1, t2;
+
+# The test was inactive for InnoDB at the time of pushing. The following is
+# expected result for the Bug#56423 test. It can be uncommented and pasted
+# into result file when reactivating the test.
+
+##
+## Bug#56423: Different count with SELECT and CREATE SELECT queries
+##
+#CREATE TABLE t1 (
+#a INT,
+#b INT,
+#c INT,
+#d INT,
+#PRIMARY KEY (a),
+#KEY (c),
+#KEY bd (b,d)
+#);
+#INSERT INTO t1 VALUES
+#(1, 0, 1, 0),
+#(2, 1, 1, 1),
+#(3, 1, 1, 1),
+#(4, 0, 1, 1);
+#EXPLAIN
+#SELECT a
+#FROM t1
+#WHERE c = 1 AND b = 1 AND d = 1;
+#id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+#1	SIMPLE	t1	ref	c,bd	bd	10	const,const	2	Using where
+#CREATE TABLE t2 ( a INT )
+#SELECT a
+#FROM t1
+#WHERE c = 1 AND b = 1 AND d = 1;
+#SELECT * FROM t2;
+#a
+#2
+#3
+#DROP TABLE t1, t2;
+#CREATE TABLE t1( a INT, b INT, KEY(a), KEY(b) );
+#INSERT INTO t1 VALUES (1, 2), (1, 2), (1, 2), (1, 2);
+#SELECT * FROM t1 FORCE INDEX(a, b) WHERE a = 1 AND b = 2;
+#a	b
+#1	2
+#1	2
+#1	2
+#1	2
+#DROP TABLE t1;
+## Code coverage of fix.
+#CREATE TABLE t1 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT);
+#INSERT INTO t1 (b) VALUES (1);
+#UPDATE t1 SET b = 2 WHERE a = 1;
+#SELECT * FROM t1;
+#a	b
+#1	2
+#CREATE TABLE t2 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b VARCHAR(1) );
+#INSERT INTO t2 (b) VALUES ('a');
+#UPDATE t2 SET b = 'b' WHERE a = 1;
+#SELECT * FROM t2;
+#a	b
+#1	b
+#DROP TABLE t1, t2;