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
|
--source include/have_innodb.inc
--source include/maybe_versioning.inc
SET NAMES utf8mb4;
--echo #
--echo # MDEV-11233 CREATE FULLTEXT INDEX with a token
--echo # longer than 127 bytes crashes server
--echo #
# This bug is the result of merging the Oracle MySQL follow-up fix
# BUG#22963169 MYSQL CRASHES ON CREATE FULLTEXT INDEX
# without merging a fix of Bug#79475 Insert a token of 84 4-bytes
# chars into fts index causes server crash.
# Oracle did not publish tests for either of the above MySQL bugs.
# The tests below were developed for MariaDB Server.
# The maximum length of a fulltext-indexed word is 84 characters.
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); # too long, will not be indexed
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); # too long, will not be indexed
INSERT INTO t SET t=REPEAT(_utf8mb3 0xe794b2e9aaa8e69687, 15);
--echo # The data below is not 3-byte UTF-8, but 4-byte chars.
INSERT IGNORE INTO t SET t=REPEAT(_utf8mb4 0xf09f9695, 84);
INSERT IGNORE INTO t SET t=REPEAT(_utf8mb4 0xf09f9696, 85);
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST
(REPEAT(CONCAT(REPEAT(_utf8mb3 0xE0B987, 4), REPEAT(_utf8mb3 0xE0B989, 5)), 5));
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST ('BeforeTheIndexCreation');
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT('befor',17));
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST ('after');
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT('after',17));
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc90, 83));
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc90, 84));
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc90, 85));
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc91, 83));
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc91, 84));
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc91, 85));
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8mb4 0xf09f9695, 83));
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8mb4 0xf09f9695, 84));
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8mb4 0xf09f9696, 84));
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8mb4 0xf09f9696, 85));
SELECT * FROM t;
# The column length should be 252 bytes (84 characters * 3 bytes/character).
SELECT len,COUNT(*) FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS where name='word' GROUP BY len;
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); # too long, will not be indexed
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); # too long, will not be indexed
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);
--echo # 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));
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST ('BeforeTheIndexCreation');
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT('befor',17));
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST ('after');
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT('after',17));
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc90, 83));
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc90, 84));
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc90, 85));
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc91, 83));
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc91, 84));
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8 0xefbc91, 85));
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8mb4 0xf09f9695, 83));
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8mb4 0xf09f9695, 84));
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8mb4 0xf09f9696, 84));
SELECT COUNT(*) FROM t WHERE MATCH t AGAINST (REPEAT(_utf8mb4 0xf09f9696, 85));
SELECT * FROM t;
# The column length should be 336 bytes (84 characters * 4 bytes/character).
SELECT len,COUNT(*) FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS where name='word' GROUP BY len;
DROP TABLE t;
CREATE TABLE t(t TEXT CHARACTER SET latin1, FULLTEXT INDEX(t))
ENGINE=InnoDB;
# The column length should be 84 bytes (84 characters * 1 byte/character).
SELECT len,COUNT(*) FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS where name='word' GROUP BY len;
DROP TABLE t;
--echo #
--echo # MDEV-17923 Assertion memcmp(field, field_ref_zero, 7) failed in
--echo # trx_undo_page_report_modify upon optimizing table
--echo # under innodb_optimize_fulltext_only
--echo #
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;
DROP TABLE t1;
SET GLOBAL innodb_optimize_fulltext_only= @optimize_fulltext.save;
--echo #
--echo # MDEV-24403 Segfault on CREATE TABLE with explicit FTS_DOC_ID_INDEX by multiple fields
--echo #
--error ER_INNODB_FT_WRONG_DOCID_INDEX
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;
--echo #
--echo # MDEV-26938 Support descending indexes internally in InnoDB
--echo #
--error ER_INNODB_FT_WRONG_DOCID_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), FULLTEXT(b))
ENGINE=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)) ENGINE=InnoDB;
SHOW CREATE TABLE t1;
--error ER_INNODB_FT_WRONG_DOCID_INDEX
ALTER TABLE t1 ADD FULLTEXT INDEX(b), ALGORITHM=INPLACE;
--error ER_INNODB_FT_WRONG_DOCID_INDEX
ALTER TABLE t1 ADD FULLTEXT INDEX(b), ALGORITHM=COPY;
DROP TABLE t1;
|
