1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
|
SET NAMES utf8mb4;
#
# MDEV-11233 CREATE FULLTEXT INDEX with a token
# longer than 127 bytes crashes server
#
CREATE TABLE t(t TEXT CHARACTER SET utf8mb3) ENGINE=InnoDB;
INSERT INTO t SET t=REPEAT(CONCAT(REPEAT(_utf8mb3 0xE0B987, 4), REPEAT(_utf8mb3 0xE0B989, 5)), 5);
INSERT INTO t SET t=REPEAT(_utf8 0xefbc90,84);
INSERT INTO t SET t=REPEAT('befor',17);
INSERT INTO t SET t='BeforeTheIndexCreation';
CREATE FULLTEXT INDEX ft ON t(t);
INSERT INTO t SET t='this was inserted after creating the index';
INSERT INTO t SET t=REPEAT(_utf8 0xefbc91,84);
INSERT INTO t SET t=REPEAT('after',17);
INSERT INTO t SET t=REPEAT(_utf8mb3 0xe794b2e9aaa8e69687, 15);
# The data below is not 3-byte UTF-8, but 4-byte chars.
INSERT IGNORE INTO t SET t=REPEAT(_utf8mb4 0xf09f9695, 84);
Warnings:
Warning 1366 Incorrect string value: '\xF0\x9F\x96\x95\xF0\x9F...' for column `test`.`t`.`t` at row 1
INSERT IGNORE INTO t SET t=REPEAT(_utf8mb4 0xf09f9696, 85);
Warnings:
Warning 1366 Incorrect string value: '\xF0\x9F\x96\x96\xF0\x9F...' for column `test`.`t`.`t` at row 1
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST
(REPEAT(CONCAT(REPEAT(_utf8mb3 0xE0B987, 4), REPEAT(_utf8mb3 0xE0B989, 5)), 5));
COUNT(*)
1
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST ('BeforeTheIndexCreation');
COUNT(*)
1
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT('befor',17));
COUNT(*)
0
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST ('after');
COUNT(*)
1
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT('after',17));
COUNT(*)
0
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc90, 83));
COUNT(*)
0
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc90, 84));
COUNT(*)
1
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc90, 85));
COUNT(*)
0
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc91, 83));
COUNT(*)
0
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc91, 84));
COUNT(*)
1
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc91, 85));
COUNT(*)
0
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8mb4 0xf09f9695, 83));
COUNT(*)
0
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8mb4 0xf09f9695, 84));
COUNT(*)
0
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8mb4 0xf09f9696, 84));
COUNT(*)
0
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8mb4 0xf09f9696, 85));
COUNT(*)
0
SELECT * FROM t;
t
็็็็้้้้้็็็็้้้้้็็็็้้้้้็็็็้้้้้็็็็้้้้้
００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００
beforbeforbeforbeforbeforbeforbeforbeforbeforbeforbeforbeforbeforbeforbeforbeforbefor
BeforeTheIndexCreation
this was inserted after creating the index
１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１
afterafterafterafterafterafterafterafterafterafterafterafterafterafterafterafterafter
甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文
????????????????????????????????????????????????????????????????????????????????????
?????????????????????????????????????????????????????????????????????????????????????
SELECT len,COUNT(*) FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS where name='word' GROUP BY len;
len COUNT(*)
252 6
DROP TABLE t;
CREATE TABLE t(t TEXT CHARACTER SET utf8mb4) ENGINE=InnoDB;
INSERT INTO t SET t=REPEAT(_utf8mb3 0xe794b2e9aaa8e69687, 15);
INSERT INTO t SET t=REPEAT(_utf8 0xefbc90,84);
INSERT INTO t SET t=REPEAT('befor',17);
INSERT INTO t SET t='BeforeTheIndexCreation';
CREATE FULLTEXT INDEX ft ON t(t);
INSERT INTO t SET t='this was inserted after creating the index';
INSERT INTO t SET t=REPEAT(_utf8 0xefbc91,84);
INSERT INTO t SET t=REPEAT('after',17);
INSERT INTO t SET t=REPEAT(concat(repeat(_utf8mb3 0xE0B987, 4), repeat(_utf8mb3 0xE0B989, 5)), 5);
INSERT INTO t SET t=REPEAT(_utf8mb4 0xf09f9695, 84);
# The token below exceeds the 84-character limit.
INSERT INTO t SET t=REPEAT(_utf8mb4 0xf09f9696, 85);
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8mb3 0xe794b2e9aaa8e69687, 15));
COUNT(*)
1
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST ('BeforeTheIndexCreation');
COUNT(*)
1
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT('befor',17));
COUNT(*)
0
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST ('after');
COUNT(*)
1
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT('after',17));
COUNT(*)
0
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc90, 83));
COUNT(*)
0
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc90, 84));
COUNT(*)
1
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc90, 85));
COUNT(*)
0
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc91, 83));
COUNT(*)
0
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc91, 84));
COUNT(*)
1
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc91, 85));
COUNT(*)
0
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8mb4 0xf09f9695, 83));
COUNT(*)
0
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8mb4 0xf09f9695, 84));
COUNT(*)
0
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8mb4 0xf09f9696, 84));
COUNT(*)
0
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8mb4 0xf09f9696, 85));
COUNT(*)
0
SELECT * FROM t;
t
甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文甲骨文
００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００００
beforbeforbeforbeforbeforbeforbeforbeforbeforbeforbeforbeforbeforbeforbeforbeforbefor
BeforeTheIndexCreation
this was inserted after creating the index
１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１１
afterafterafterafterafterafterafterafterafterafterafterafterafterafterafterafterafter
็็็็้้้้้็็็็้้้้้็็็็้้้้้็็็็้้้้้็็็็้้้้้
🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕🖕
🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖🖖
SELECT len,COUNT(*) FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS where name='word' GROUP BY len;
len COUNT(*)
336 6
DROP TABLE t;
CREATE TABLE t(t TEXT CHARACTER SET latin1, FULLTEXT INDEX(t))
ENGINE=InnoDB;
SELECT len,COUNT(*) FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS where name='word' GROUP BY len;
len COUNT(*)
84 6
DROP TABLE t;
#
# MDEV-17923 Assertion memcmp(field, field_ref_zero, 7) failed in
# trx_undo_page_report_modify upon optimizing table
# under innodb_optimize_fulltext_only
#
CREATE TABLE t1 (f1 TEXT, f2 TEXT, FULLTEXT KEY (f2)) ENGINE=InnoDB;
INSERT INTO t1 (f1) VALUES ('foo'),('bar');
DELETE FROM t1 LIMIT 1;
ALTER TABLE t1 ADD FULLTEXT KEY (f1);
SET @optimize_fulltext.save= @@innodb_optimize_fulltext_only;
SET GLOBAL innodb_optimize_fulltext_only= 1;
OPTIMIZE TABLE t1;
Table Op Msg_type Msg_text
test.t1 optimize status OK
DROP TABLE t1;
SET GLOBAL innodb_optimize_fulltext_only= @optimize_fulltext.save;
#
# MDEV-24403 Segfault on CREATE TABLE with explicit FTS_DOC_ID_INDEX by multiple fields
#
create table t1 (
f1 int, f2 text,
FTS_DOC_ID bigint unsigned not null,
unique key FTS_DOC_ID_INDEX(FTS_DOC_ID, f1),
fulltext (f2))
engine=innodb;
ERROR HY000: Index 'FTS_DOC_ID_INDEX' is of wrong type for an InnoDB FULLTEXT index
#
# MDEV-26938 Support descending indexes internally in InnoDB
#
CREATE TABLE t1(a INT PRIMARY KEY, b TEXT, FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
UNIQUE KEY FTS_DOC_ID_INDEX(FTS_DOC_ID DESC), FULLTEXT(b))
ENGINE=InnoDB;
ERROR HY000: Index 'FTS_DOC_ID_INDEX' is of wrong type for an InnoDB FULLTEXT index
CREATE TABLE t1(a INT PRIMARY KEY, b TEXT, FTS_DOC_ID BIGINT UNSIGNED NOT NULL,
UNIQUE KEY FTS_DOC_ID_INDEX(FTS_DOC_ID DESC)) ENGINE=InnoDB;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) NOT NULL,
`b` text DEFAULT NULL,
`FTS_DOC_ID` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`a`),
UNIQUE KEY `FTS_DOC_ID_INDEX` (`FTS_DOC_ID` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
ALTER TABLE t1 ADD FULLTEXT INDEX(b), ALGORITHM=INPLACE;
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Not implemented for system-versioned operations. Try ALGORITHM=COPY
ALTER TABLE t1 ADD FULLTEXT INDEX(b), ALGORITHM=COPY;
ERROR HY000: Index 'FTS_DOC_ID_INDEX' is of wrong type for an InnoDB FULLTEXT index
DROP TABLE t1;
|
