Debian Codegraph @

mysql-test/suite/innodb_fts/t/innodb-fts-fic.test

raw
1 # This is the basic function tests for innodb FTS
2
3 -- source include/have_innodb.inc
4 -- source include/maybe_versioning.inc
5
6 call mtr.add_suppression("\\[Warning\\] InnoDB: A new Doc ID must be supplied while updating FTS indexed columns.");
7 call mtr.add_suppression("\\[Warning\\] InnoDB: FTS Doc ID must be larger than [0-9]+ for table `test`.`articles`");
8 # Create FTS table
9 CREATE TABLE articles (
10 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
11 title VARCHAR(200),
12 body TEXT
13 ) ENGINE=InnoDB;
14
15 # Insert six rows
16 INSERT INTO articles (title,body) VALUES
17 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
18 ('How To Use MySQL Well','After you went through a ...'),
19 ('Optimizing MySQL','In this tutorial we will show ...'),
20 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
21 ('MySQL vs. YourSQL','In the following database comparison ...'),
22 ('MySQL Security','When configured properly, MySQL ...');
23
24 # Create the FTS index
25 CREATE FULLTEXT INDEX idx on articles (title, body);
26
27 SELECT * FROM articles WHERE MATCH (title, body)
28 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
29
30 SELECT COUNT(*) FROM articles
31 WHERE MATCH (title, body)
32 AGAINST ('database' IN NATURAL LANGUAGE MODE);
33
34 SELECT * FROM articles
35 WHERE MATCH (title, body)
36 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
37
38
39 SELECT COUNT(IF(MATCH (title, body)
40 AGAINST ('database' IN NATURAL LANGUAGE MODE), 1, NULL))
41 AS count FROM articles;
42
43 ANALYZE TABLE articles;
44
45 # Boolean search
46 # Select rows contain "MySQL" but not "YourSQL"
47 SELECT * FROM articles WHERE MATCH (title,body)
48 AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
49
50 # Select rows contain at least one of the two words
51 SELECT * FROM articles WHERE MATCH (title,body)
52 AGAINST ('DBMS Security' IN BOOLEAN MODE);
53
54 # Select rows contain both "MySQL" and "YourSQL"
55 SELECT * FROM articles WHERE MATCH (title,body)
56 AGAINST ('+MySQL +YourSQL' IN BOOLEAN MODE);
57
58 DROP INDEX idx ON articles;
59
60 # Create the FTS index
61 CREATE FULLTEXT INDEX idx on articles (title, body);
62
63 CREATE FULLTEXT INDEX idx1 on articles (title);
64
65 SELECT * FROM articles WHERE MATCH (title, body)
66 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
67
68 DROP INDEX idx ON articles;
69
70 DROP INDEX idx1 ON articles;
71
72 CREATE FULLTEXT INDEX idx1 on articles (title);
73
74 SELECT * FROM articles WHERE MATCH (title)
75 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
76
77 drop table articles;
78
79 # Create FTS table
80 CREATE TABLE articles (
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 articles(FTS_DOC_ID);
87
88 # Insert six rows
89 INSERT INTO articles (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 CREATE FULLTEXT INDEX idx on articles (title, body);
99
100 # "the" is in the default stopword, it would not be selected
101 SELECT * FROM articles WHERE MATCH (title, body)
102 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
103
104 drop table articles;
105
106 # Create FTS table
107 CREATE TABLE articles (
108 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
109 title VARCHAR(200),
110 body TEXT
111 ) ENGINE=InnoDB;
112
113 # Insert six rows
114 INSERT INTO articles (title,body) VALUES
115 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
116 ('How To Use MySQL Well','After you went through a ...'),
117 ('Optimizing MySQL','In this tutorial we will show ...'),
118 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
119 ('MySQL vs. YourSQL','In the following database comparison ...'),
120 ('MySQL Security','When configured properly, MySQL ...');
121
122 CREATE FULLTEXT INDEX idx on articles (title);
123 CREATE FULLTEXT INDEX idx2 on articles (body);
124
125 # "the" is in the default stopword, it would not be selected
126 --error 1191
127 SELECT * FROM articles WHERE MATCH (title, body)
128 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
129
130 SELECT * FROM articles WHERE MATCH (title)
131 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
132
133 SELECT * FROM articles WHERE MATCH (body)
134 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
135
136 drop index idx2 on articles;
137
138 --error 1191
139 SELECT * FROM articles WHERE MATCH (body)
140 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
141
142 CREATE FULLTEXT INDEX idx2 on articles (body);
143
144 SELECT * FROM articles WHERE MATCH (body)
145 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
146
147 UPDATE articles set title = 'aaaa'
148 WHERE MATCH(title) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
149
150 SELECT * FROM articles WHERE MATCH (title)
151 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
152
153 SELECT * FROM articles WHERE MATCH (title)
154 AGAINST ('aaaa' IN NATURAL LANGUAGE MODE);
155
156 drop table articles;
157
158 CREATE TABLE articles (
159 FTS_DOC_ID BIGINT UNSIGNED NOT NULL ,
160 title VARCHAR(200),
161 body TEXT
162 ) ENGINE=InnoDB;
163
164 CREATE FULLTEXT INDEX idx on articles (title);
165
166 INSERT INTO articles VALUES (9, 'MySQL Tutorial','DBMS stands for DataBase ...');
167
168 # This should fail since we did not supply a new Doc ID
169 -- error 182
170 UPDATE articles set title = 'bbbb' WHERE MATCH(title) AGAINST ('tutorial' IN NATURAL LANGUAGE MODE);
171
172 # This should fail, since the Doc ID supplied is less than the old value 9
173 -- error 182
174 UPDATE articles set title = 'bbbb', FTS_DOC_ID=8 WHERE MATCH(title) AGAINST ('tutorial' IN NATURAL LANGUAGE MODE);
175
176 # This should be successful
177 UPDATE articles set title = 'bbbb', FTS_DOC_ID=10 WHERE MATCH(title) AGAINST ('tutorial' IN NATURAL LANGUAGE MODE);
178
179 # Check update to be successful
180 SELECT * FROM articles WHERE MATCH (title) AGAINST ('bbbb' IN NATURAL LANGUAGE MODE);
181
182 SELECT * FROM articles WHERE MATCH (title) AGAINST ('tutorial' IN NATURAL LANGUAGE MODE);
183
184 CREATE FULLTEXT INDEX idx2 ON articles (body);
185
186 SELECT * FROM articles WHERE MATCH (body) AGAINST ('database' IN NATURAL LANGUAGE MODE);
187
188 UPDATE articles set body = 'bbbb', FTS_DOC_ID=11 WHERE MATCH(body) AGAINST ('database' IN NATURAL LANGUAGE MODE);
189
190 drop table articles;
191
192 create table `articles`(`a` varchar(2) not null)engine=innodb;
193
194 # This create index should fail. FTS_DOC_ID_INDEX is reserved as a unique
195 # index on FTS_DOC_ID
196 --error ER_INNODB_FT_WRONG_DOCID_INDEX
197 create fulltext index `FTS_DOC_ID_INDEX` on `articles`(`a`);
198
199 create unique index `a` on `articles`(`a`);
200
201 drop table articles;
202
203 # We will check validity of FTS_DOC_ID, which must be of an UNSIGNED
204 # NOT NULL bigint
205 CREATE TABLE wp(
206 FTS_DOC_ID bigint PRIMARY KEY,
207 title VARCHAR(255) NOT NULL DEFAULT '',
208 text MEDIUMTEXT NOT NULL ) ENGINE=InnoDB;
209
210 INSERT INTO wp (FTS_DOC_ID, title, text) VALUES
211 (1, 'MySQL Tutorial','DBMS stands for DataBase ...'),
212 (2, 'How To Use MySQL Well','After you went through a ...');
213
214 if ($MTR_COMBINATION_ORIG) {
215 --error ER_INNODB_FT_WRONG_DOCID_COLUMN
216 CREATE FULLTEXT INDEX idx ON wp(title, text);
217 }
218 if (!$MTR_COMBINATION_ORIG) {
219 CREATE FULLTEXT INDEX idx ON wp(title, text);
220 }
221
222 DROP TABLE wp;
223 CREATE TABLE wp(
224 FTS_DOC_ID bigint unsigned PRIMARY KEY,
225 title VARCHAR(255) NOT NULL DEFAULT '',
226 text MEDIUMTEXT NOT NULL ) ENGINE=InnoDB;
227
228 INSERT INTO wp (FTS_DOC_ID, title, text) VALUES
229 (1, 'MySQL Tutorial','DBMS stands for DataBase ...'),
230 (2, 'How To Use MySQL Well','After you went through a ...');
231
232 CREATE FULLTEXT INDEX idx ON wp(title, text);
233
234 SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('database')
235
236 FROM wp;
237
238 DROP TABLE wp;
239