Debian Codegraph @

mysql-test/suite/innodb_fts/t/ddl.test

raw
1 # This is the DDL function tests for innodb FTS
2
3 call mtr.add_suppression("Trying to free a missing index of table 'test/fts_test'");
4 call mtr.add_suppression("Failed to create index with index id .* of table 'test/fts_test'");
5
6 # Create FTS table
7 CREATE TABLE fts_test (
8 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
9 title VARCHAR(200),
10 body TEXT
11 ) ENGINE=InnoDB;
12
13 # Insert six rows
14 INSERT INTO fts_test (title,body) VALUES
15 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
16 ('How To Use MySQL Well','After you went through a ...'),
17 ('Optimizing MySQL','In this tutorial we will show ...'),
18 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
19 ('MySQL vs. YourSQL','In the following database comparison ...'),
20 ('MySQL Security','When configured properly, MySQL ...');
21
22 # Create the FTS index
23 CREATE FULLTEXT INDEX idx on fts_test (title, body);
24
25 # Select word "tutorial" in the table
26 SELECT * FROM fts_test WHERE MATCH (title, body)
27 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
28
29
30 # Drop the FTS idx
31 DROP INDEX idx ON fts_test;
32
33 # Continue insert some rows
34 INSERT INTO fts_test (title,body) VALUES
35 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
36 ('How To Use MySQL Well','After you went through a ...'),
37 ('Optimizing MySQL','In this tutorial we will show ...'),
38 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
39 ('MySQL vs. YourSQL','In the following database comparison ...'),
40 ('MySQL Security','When configured properly, MySQL ...');
41
42
43 # Recreate the FTS index
44 CREATE FULLTEXT INDEX idx on fts_test (title, body);
45
46 # Select word "tutorial" in the table
47 SELECT * FROM fts_test WHERE MATCH (title, body)
48 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
49
50 # Boolean search
51 # Select rows contain "MySQL" but not "YourSQL"
52 --sorted_result
53 SELECT * FROM fts_test WHERE MATCH (title,body)
54 AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
55
56 # Truncate table
57 TRUNCATE TABLE fts_test;
58
59 DROP INDEX idx ON fts_test;
60
61 # Continue insert some rows
62 INSERT INTO fts_test (title,body) VALUES
63 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
64 ('How To Use MySQL Well','After you went through a ...'),
65 ('Optimizing MySQL','In this tutorial we will show ...'),
66 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
67 ('MySQL vs. YourSQL','In the following database comparison ...'),
68 ('MySQL Security','When configured properly, MySQL ...');
69
70 # Recreate the FTS index
71 CREATE FULLTEXT INDEX idx on fts_test (title, body);
72
73 # Select word "tutorial" in the table
74 SELECT * FROM fts_test WHERE MATCH (title, body)
75 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
76
77 DROP TABLE fts_test;
78
79 # Create FTS table
80 CREATE TABLE fts_test (
81 FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
82 title VARCHAR(200),
83 body TEXT
84 ) ENGINE=InnoDB;
85
86 create unique index FTS_DOC_ID_INDEX on fts_test(FTS_DOC_ID DESC);
87
88 # Insert six rows
89 INSERT INTO fts_test (title,body) VALUES
90 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
91 ('How To Use MySQL Well','After you went through a ...'),
92 ('Optimizing MySQL','In this tutorial we will show ...'),
93 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
94 ('MySQL vs. YourSQL','In the following database comparison ...'),
95 ('MySQL Security','When configured properly, MySQL ...');
96
97 # Create the FTS index
98 # We could support online fulltext index creation when a FTS_DOC_ID
99 # column already exists. This has not been implemented yet.
100 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
101 CREATE FULLTEXT INDEX idx on fts_test (title, body) LOCK=NONE;
102 --error ER_INNODB_FT_WRONG_DOCID_INDEX
103 CREATE FULLTEXT INDEX idx on fts_test (title, body);
104
105 ALTER TABLE fts_test DROP INDEX FTS_DOC_ID_INDEX,
106 ADD UNIQUE INDEX FTS_DOC_ID_INDEX(FTS_DOC_ID), ALGORITHM=INPLACE;
107
108 CREATE FULLTEXT INDEX idx on fts_test (title, body);
109
110 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
111 ALTER TABLE fts_test ROW_FORMAT=REDUNDANT, LOCK=NONE;
112 ALTER TABLE fts_test ROW_FORMAT=REDUNDANT;
113
114 SELECT * FROM fts_test WHERE MATCH (title, body)
115 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
116
117 # Drop and recreate
118 drop index idx on fts_test;
119
120 CREATE FULLTEXT INDEX idx on fts_test (title, body);
121
122 SELECT * FROM fts_test WHERE MATCH (title, body)
123 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
124
125 # Drop the FTS_DOC_ID_INDEX and try again
126 drop index idx on fts_test;
127
128 drop index FTS_DOC_ID_INDEX on fts_test;
129
130 CREATE FULLTEXT INDEX idx on fts_test (title, body);
131
132 SELECT * FROM fts_test WHERE MATCH (title, body)
133 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
134
135 drop table fts_test;
136
137 # Test FTS_DOC_ID and FTS_DOC_ID_INDEX all in the create table clause
138 CREATE TABLE fts_test (
139 FTS_DOC_ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
140 title varchar(255) NOT NULL DEFAULT '',
141 text mediumtext NOT NULL,
142 PRIMARY KEY (FTS_DOC_ID),
143 UNIQUE KEY FTS_DOC_ID_INDEX (FTS_DOC_ID),
144 FULLTEXT KEY idx (title,text)
145 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
146
147 set @@auto_increment_increment=10;
148
149 INSERT INTO fts_test (title, text) VALUES
150 ('MySQL Tutorial','DBMS stands for DataBase ...'),
151 ('How To Use MySQL Well','After you went through a ...'),
152 ('Optimizing MySQL','In this tutorial we will show ...'),
153 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
154 ('MySQL vs. YourSQL','In the following database comparison ...'),
155 ('MySQL Security','When configured properly, MySQL ...');
156 -- disable_result_log
157 ANALYZE TABLE fts_test;
158 -- enable_result_log
159 set @@auto_increment_increment=1;
160
161 select *, match(title, text) AGAINST ('database') as score
162 from fts_test order by score desc;
163
164 drop index idx on fts_test;
165
166 drop table fts_test;
167
168 # This should fail:
169 # Create a FTS_DOC_ID of the wrong type (should be bigint)
170 --error ER_INNODB_FT_WRONG_DOCID_COLUMN
171 CREATE TABLE fts_test (
172 FTS_DOC_ID int(20) unsigned NOT NULL AUTO_INCREMENT,
173 title varchar(255) NOT NULL DEFAULT '',
174 text mediumtext NOT NULL,
175 PRIMARY KEY (FTS_DOC_ID),
176 UNIQUE KEY FTS_DOC_ID_INDEX (FTS_DOC_ID),
177 FULLTEXT KEY idx (title,text)
178 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
179
180 # This should fail:
181 # Create a FTS_DOC_ID_INDEX of the wrong type (should be unique)
182 --error ER_INNODB_FT_WRONG_DOCID_INDEX
183 CREATE TABLE fts_test (
184 FTS_DOC_ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
185 title varchar(255) NOT NULL DEFAULT '',
186 text mediumtext NOT NULL,
187 PRIMARY KEY (FTS_DOC_ID),
188 KEY FTS_DOC_ID_INDEX (FTS_DOC_ID),
189 FULLTEXT KEY idx (title,text)
190 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
191
192 CREATE TABLE articles (
193 FTS_DOC_ID BIGINT UNSIGNED NOT NULL ,
194 title VARCHAR(200),
195 body TEXT
196 ) ENGINE=InnoDB;
197
198 INSERT INTO articles (FTS_DOC_ID, title, body) VALUES
199 (9, 'MySQL Tutorial','DBMS stands for DataBase ...'),
200 (10, 'How To Use MySQL Well','After you went through a ...'),
201 (12, 'Optimizing MySQL','In this tutorial we will show ...'),
202 (14,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
203 (19, 'MySQL vs. YourSQL','In the following database comparison ...'),
204 (20, 'MySQL Security','When configured properly, MySQL ...');
205
206 --error ER_INNODB_FT_LIMIT
207 ALTER TABLE articles ADD FULLTEXT INDEX idx3 (title),
208 ADD FULLTEXT INDEX idx5 (title);
209
210 CREATE FULLTEXT INDEX idx on articles (title);
211 ALTER TABLE articles ADD FULLTEXT INDEX idx3 (title);
212
213 ALTER TABLE articles ADD INDEX t20 (title(20)), LOCK=NONE;
214 ALTER TABLE articles DROP INDEX t20;
215
216 INSERT INTO articles (FTS_DOC_ID, title, body) VALUES
217 (29, 'MySQL Tutorial','DBMS stands for DataBase ...'),
218 (30, 'How To Use MySQL Well','After you went through a ...'),
219 (32, 'Optimizing MySQL','In this tutorial we will show ...'),
220 (34,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
221 (39, 'MySQL vs. YourSQL','In the following database comparison ...'),
222 (40, 'MySQL Security','When configured properly, MySQL ...');
223
224 SELECT * FROM articles WHERE MATCH (title)
225 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
226
227 DROP INDEX idx ON articles;
228
229 SELECT * FROM articles WHERE MATCH (title)
230 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
231
232 CREATE FULLTEXT INDEX idx on articles (title, body);
233
234 SELECT * FROM articles WHERE MATCH (title, body)
235 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
236
237 DROP TABLE articles;
238
239 create table articles(`FTS_DOC_ID` serial,
240 `col32` timestamp not null,`col115` text) engine=innodb;
241
242 create fulltext index `idx5` on articles(`col115`) ;
243
244 alter table articles add primary key (`col32`) ;
245
246 drop table articles;
247
248 # Create a table with FTS index, this will create hidden column FTS_DOC_ID
249 CREATE TABLE articles (
250 id INT UNSIGNED NOT NULL,
251 title VARCHAR(200),
252 body TEXT
253 ) ENGINE=InnoDB;
254
255 INSERT INTO articles VALUES
256 (1, 'MySQL Tutorial','DBMS stands for DataBase ...') ,
257 (2, 'How To Use MySQL Well','After you went through a ...'),
258 (3, 'Optimizing MySQL','In this tutorial we will show ...'),
259 (4, '1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
260 (5, 'MySQL vs. YourSQL','In the following database comparison ...'),
261 (6, 'MySQL Security','When configured properly, MySQL ...');
262
263 CREATE FULLTEXT INDEX idx on articles (title, body);
264
265 # Drop the FTS index, however, this will keep the FTS_DOC_ID hidden
266 # column (to avoid a table rebuild)
267 DROP INDEX idx ON articles;
268
269 # Now create cluster index on id online; The rebuild should still
270 # have the FTS_DOC_ID
271 CREATE UNIQUE INDEX idx2 ON articles(id);
272
273 # Recreate FTS index, this should not require a rebuild,
274 # since the FTS_DOC_ID is still there
275 CREATE FULLTEXT INDEX idx on articles (title, body);
276
277 SELECT * FROM articles WHERE MATCH (title, body)
278 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
279
280 DROP TABLE articles;
281
282 # This should fail:
283 # Create fulltext index when an arbitrary index whose name case-insensitively
284 # matches FTS_DOC_ID_INDEX is present
285 CREATE TABLE t(t TEXT, FTS_DOC_ID BIGINT UNSIGNED NOT NULL, b INT);
286 CREATE UNIQUE INDEX FTS_DOC_Id_INDEX ON t(FTS_DOC_ID DESC);
287 --error ER_INNODB_FT_WRONG_DOCID_INDEX
288 CREATE FULLTEXT INDEX f ON t(t);
289 DROP INDEX FTS_DOC_Id_INDEX on t;
290
291 # This should fail:
292 # Create a fullltext index when FTS_DOC_ID_INDEX is not a single-column
293 # ascending unique index on FTS_DOC_ID.
294 CREATE UNIQUE INDEX FTS_DOC_ID_INDEX ON t(FTS_DOC_ID DESC, b DESC);
295 --error ER_INNODB_FT_WRONG_DOCID_INDEX
296 CREATE FULLTEXT INDEX f ON t(t);
297 DROP TABLE t;
298
299 # This should fail:
300 # Create a table with a column whose name case-insensitively matches FTS_DOC_ID
301 # matches FTS_DOC_ID_INDEX is present
302 --error ER_WRONG_COLUMN_NAME
303 CREATE TABLE t(t TEXT, FTS_DOC_iD BIGINT UNSIGNED NOT NULL, b INT);