1
--source include/have_innodb.inc
3
# Bug #56680 InnoDB may return wrong results from a case-insensitive index
6
SET @tx_isolation_orig = @@tx_isolation;
7
SET @innodb_file_per_table_orig = @@innodb_file_per_table;
8
SET @innodb_file_format_orig = @@innodb_file_format;
9
# The flag innodb_change_buffering_debug is only available in debug builds.
10
# It instructs InnoDB to try to evict pages from the buffer pool when
11
# change buffering is possible, so that the change buffer will be used
13
-- error 0,ER_UNKNOWN_SYSTEM_VARIABLE
14
SET @innodb_change_buffering_debug_orig = @@innodb_change_buffering_debug;
15
-- error 0,ER_UNKNOWN_SYSTEM_VARIABLE
16
SET GLOBAL innodb_change_buffering_debug = 1;
18
SET GLOBAL tx_isolation='REPEATABLE-READ';
19
SET GLOBAL innodb_file_format=Barracuda;
20
SET GLOBAL innodb_file_per_table=on;
22
CREATE TABLE bug56680(
23
a INT AUTO_INCREMENT PRIMARY KEY,
29
INSERT INTO bug56680 VALUES(0,'x',1);
31
SELECT b FROM bug56680;
33
connect (con1,localhost,root,,);
36
UPDATE bug56680 SET b='X';
39
# This should return the last committed value 'x', but would return 'X'
40
# due to a bug in row_search_for_mysql().
41
SELECT b FROM bug56680;
42
# This would always return the last committed value 'x'.
43
SELECT * FROM bug56680;
51
SELECT b FROM bug56680;
53
# For the rest of this test, use the READ UNCOMMITTED isolation level
54
# to see what exists in the secondary index.
55
SET GLOBAL tx_isolation='READ-UNCOMMITTED';
57
# Create enough rows for the table, so that the insert buffer will be
58
# used for modifying the secondary index page. There must be multiple
59
# index pages, because changes to the root page are never buffered.
61
INSERT INTO bug56680 SELECT 0,b,c FROM bug56680;
62
INSERT INTO bug56680 SELECT 0,b,c FROM bug56680;
63
INSERT INTO bug56680 SELECT 0,b,c FROM bug56680;
64
INSERT INTO bug56680 SELECT 0,b,c FROM bug56680;
65
INSERT INTO bug56680 SELECT 0,b,c FROM bug56680;
66
INSERT INTO bug56680 SELECT 0,b,c FROM bug56680;
67
INSERT INTO bug56680 SELECT 0,b,c FROM bug56680;
68
INSERT INTO bug56680 SELECT 0,b,c FROM bug56680;
69
INSERT INTO bug56680 SELECT 0,b,c FROM bug56680;
70
INSERT INTO bug56680 SELECT 0,b,c FROM bug56680;
71
INSERT INTO bug56680 SELECT 0,b,c FROM bug56680;
74
SELECT b FROM bug56680 LIMIT 2;
76
connect (con1,localhost,root,,);
79
DELETE FROM bug56680 WHERE a=1;
80
# This should be buffered, if innodb_change_buffering_debug = 1 is in effect.
81
INSERT INTO bug56680 VALUES(1,'X',1);
83
# This should force an insert buffer merge, and return 'X' in the first row.
84
SELECT b FROM bug56680 LIMIT 3;
87
SELECT b FROM bug56680 LIMIT 2;
92
SELECT b FROM bug56680 LIMIT 2;
98
SELECT b FROM bug56680 LIMIT 2;
100
CREATE TABLE bug56680_2(
101
a INT AUTO_INCREMENT PRIMARY KEY,
102
b VARCHAR(2) CHARSET latin1 COLLATE latin1_german2_ci,
107
INSERT INTO bug56680_2 SELECT 0,_latin1 0xdf,c FROM bug56680;
110
SELECT HEX(b) FROM bug56680_2 LIMIT 2;
111
DELETE FROM bug56680_2 WHERE a=1;
112
# This should be buffered, if innodb_change_buffering_debug = 1 is in effect.
113
INSERT INTO bug56680_2 VALUES(1,'SS',1);
115
# This should force an insert buffer merge, and return 'SS' in the first row.
116
SELECT HEX(b) FROM bug56680_2 LIMIT 3;
117
CHECK TABLE bug56680_2;
119
# Test this with compressed tables.
120
ALTER TABLE bug56680_2 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
122
SELECT HEX(b) FROM bug56680_2 LIMIT 2;
123
DELETE FROM bug56680_2 WHERE a=1;
124
# This should be buffered, if innodb_change_buffering_debug = 1 is in effect.
125
INSERT INTO bug56680_2 VALUES(1,_latin1 0xdf,1);
127
# This should force an insert buffer merge, and return 0xdf in the first row.
128
SELECT HEX(b) FROM bug56680_2 LIMIT 3;
129
CHECK TABLE bug56680_2;
131
DROP TABLE bug56680_2;
135
SET GLOBAL tx_isolation = @tx_isolation_orig;
136
SET GLOBAL innodb_file_per_table = @innodb_file_per_table_orig;
137
SET GLOBAL innodb_file_format = @innodb_file_format_orig;
138
-- error 0, ER_UNKNOWN_SYSTEM_VARIABLE
139
SET GLOBAL innodb_change_buffering_debug = @innodb_change_buffering_debug_orig;