Debian Codegraph @

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

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