Debian Codegraph @

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

raw
1 --source include/have_innodb.inc
2 --source include/have_simple_parser.inc
3 --source include/no_valgrind_without_big.inc
4 # Restart is not supported in embedded
5 --source include/not_embedded.inc
6
7 # Install fts parser plugin
8 INSTALL PLUGIN simple_parser SONAME 'mypluglib';
9
10 # Flush the table mysql.plugin in case the server shutdown would time out.
11 FLUSH TABLES;
12
13 -- echo # Test Part 1: Grammar Test
14 # Create a myisam table and alter it to innodb table
15 CREATE TABLE articles (
16 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
17 title VARCHAR(200),
18 FULLTEXT (title) WITH PARSER simple_parser
19 ) ENGINE=MyISAM;
20
21 ALTER TABLE articles ENGINE=InnoDB;
22
23 DROP TABLE articles;
24
25 # Create a table having a full text index with parser
26 CREATE TABLE articles (
27 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
28 title VARCHAR(200),
29 body TEXT,
30 comment TEXT,
31 FULLTEXT (title) WITH PARSER simple_parser
32 ) ENGINE=InnoDB;
33
34 # Alter table to add a full text index with parser
35 ALTER TABLE articles ADD FULLTEXT INDEX (body) WITH PARSER simple_parser;
36
37 # Create a full text index with parser
38 CREATE FULLTEXT INDEX ft_index ON articles(comment) WITH PARSER simple_parser;
39
40 DROP TABLE articles;
41
42 -- echo # Test Part 2: Create Index Test(CREATE TABLE WITH FULLTEXT INDEX)
43 CREATE TABLE articles (
44 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
45 title VARCHAR(200),
46 body TEXT,
47 FULLTEXT (title, body) WITH PARSER simple_parser
48 ) ENGINE=InnoDB;
49
50 INSERT INTO articles (title, body) VALUES
51 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...'),
52 ('How To Use MySQL Well','After you went through a ...'),
53 ('Optimizing MySQL','In this tutorial we will show ...'),
54 ('1001 MySQL Tricks','How to use full-text search engine'),
55 ('Go MySQL Tricks','How to use full text search engine');
56
57 # Simple term search
58 SELECT * FROM articles WHERE
59 MATCH(title, body) AGAINST('mysql') ORDER BY id;
60
61 # Test stopword and word len less than fts_min_token_size
62 SELECT * FROM articles WHERE
63 MATCH(title, body) AGAINST('will go');
64
65 -- echo # Test plugin parser tokenizer difference
66 SELECT * FROM articles WHERE
67 MATCH(title, body) AGAINST('full-text');
68
69 SELECT * FROM articles WHERE
70 MATCH(title, body) AGAINST('full text');
71
72 # No result here, we get '"mysql' 'database"' by simple parser
73 SELECT * FROM articles WHERE
74 MATCH(title, body) AGAINST('"mysql database"' IN BOOLEAN MODE);
75
76 DROP TABLE articles;
77
78 -- echo # Test Part 3: Row Merge Create Index Test(ALTER TABLE ADD FULLTEXT INDEX)
79 CREATE TABLE articles (
80 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
81 title VARCHAR(200),
82 body TEXT
83 ) ENGINE=InnoDB;
84
85 INSERT INTO articles (title, body) VALUES
86 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...'),
87 ('How To Use MySQL Well','After you went through a ...'),
88 ('Optimizing MySQL','In this tutorial we will show ...'),
89 ('1001 MySQL Tricks','How to use full-text search engine'),
90 ('Go MySQL Tricks','How to use full text search engine');
91
92 # Create fulltext index
93 ALTER TABLE articles ADD FULLTEXT INDEX (title, body) WITH PARSER simple_parser;
94
95 # Simple term search
96 SELECT * FROM articles WHERE
97 MATCH(title, body) AGAINST('mysql') ORDER BY id;
98
99 # Test stopword and word len less than fts_min_token_size
100 SELECT * FROM articles WHERE
101 MATCH(title, body) AGAINST('will go');
102
103 -- echo # Test plugin parser tokenizer difference
104 SELECT * FROM articles WHERE
105 MATCH(title, body) AGAINST('full-text');
106
107 SELECT * FROM articles WHERE
108 MATCH(title, body) AGAINST('full text');
109
110 # Test query expansion
111 SELECT * FROM articles WHERE
112 MATCH(title, body) AGAINST('full-text' WITH QUERY EXPANSION)
113 ORDER BY id;
114
115 SELECT * FROM articles WHERE
116 MATCH(title, body) AGAINST('full text' WITH QUERY EXPANSION)
117 ORDER BY id;
118
119 # No result here, we get '"mysql' 'database"' by simple parser
120 SELECT * FROM articles WHERE
121 MATCH(title, body) AGAINST('"mysql database"' IN BOOLEAN MODE);
122
123 DROP TABLE articles;
124 -- echo # Test Part 3 END
125
126 -- echo # Test Part 4:crash on commit(before/after)
127 CREATE TABLE articles (
128 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
129 title VARCHAR(200),
130 body TEXT,
131 FULLTEXT (title, body) WITH PARSER simple_parser
132 ) ENGINE=InnoDB;
133
134 # Enable MDEV-515 table-level undo logging when inserting into empty table
135 SET unique_checks=0, foreign_key_checks=0;
136 BEGIN;
137 INSERT INTO articles (title, body) VALUES
138 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...'),
139 ('How To Use MySQL Well','After you went through a ...'),
140 ('Optimizing MySQL','In this tutorial we will show ...'),
141 ('1001 MySQL Tricks','How to use full-text search engine'),
142 ('Go MySQL Tricks','How to use full text search engine');
143
144 --source include/restart_mysqld.inc
145
146 SELECT COUNT(*) FROM articles;
147 # Simple term search - no records expected
148 SELECT * FROM articles WHERE
149 MATCH(title, body) AGAINST('mysql');
150
151 INSERT INTO articles (title, body) VALUES
152 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...'),
153 ('How To Use MySQL Well','After you went through a ...'),
154 ('Optimizing MySQL','In this tutorial we will show ...'),
155 ('1001 MySQL Tricks','How to use full-text search engine'),
156 ('Go MariaDB Tricks','How to use full text search engine');
157
158 --source include/restart_mysqld.inc
159
160 SELECT * FROM articles WHERE
161 MATCH(title, body) AGAINST('MySQL') ORDER BY id;
162 SELECT * FROM articles WHERE
163 MATCH(title, body) AGAINST('tutorial') ORDER BY id;
164 SELECT * FROM articles WHERE
165 MATCH(title, body) AGAINST('Tricks') ORDER BY id;
166 SELECT * FROM articles WHERE
167 MATCH(title, body) AGAINST('full text search') ORDER BY id;
168 SELECT COUNT(*) FROM articles;
169
170 INSERT INTO articles (title, body) VALUES ('111', '1234 1234 1234');
171 DROP TABLE articles;
172
173 -- echo # Test Part 5: Test Uninstall Plugin After Index is Built
174 # Note: this test should be the last one because we uninstall plugin
175 CREATE TABLE articles (
176 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
177 title VARCHAR(200),
178 body TEXT,
179 FULLTEXT (title, body) WITH PARSER simple_parser
180 ) ENGINE=InnoDB;
181
182 # Uninstall plugin
183 UNINSTALL PLUGIN simple_parser;
184
185 -- error ER_PLUGIN_IS_NOT_LOADED
186 INSERT INTO articles (title, body) VALUES
187 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...');
188
189 # Reinstall plugin
190 INSTALL PLUGIN simple_parser SONAME 'mypluglib';
191
192 INSERT INTO articles (title, body) VALUES
193 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...'),
194 ('How To Use MySQL Well','After you went through a ...'),
195 ('Optimizing MySQL','In this tutorial we will show ...'),
196 ('1001 MySQL Tricks','How to use full-text search engine'),
197 ('Go MySQL Tricks','How to use full text search engine');
198
199 # Get warning here
200 UNINSTALL PLUGIN simple_parser;
201
202 # Simple term search
203 SELECT * FROM articles WHERE
204 MATCH(title, body) AGAINST('mysql')
205 ORDER BY id;
206
207 # Test stopword and word len less than fts_min_token_size
208 SELECT * FROM articles WHERE
209 MATCH(title, body) AGAINST('will go');
210
211 -- echo # Test plugin parser tokenizer difference
212 SELECT * FROM articles WHERE
213 MATCH(title, body) AGAINST('full-text');
214
215 SELECT * FROM articles WHERE
216 MATCH(title, body) AGAINST('full text');
217
218 -- error ER_FUNCTION_NOT_DEFINED
219 CREATE TABLE articles2 (
220 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
221 title VARCHAR(200),
222 body TEXT,
223 FULLTEXT (title, body) WITH PARSER simple_parser
224 ) ENGINE=InnoDB;
225
226 DROP TABLE articles;
227 # Uninstall plugin
228 -- error ER_SP_DOES_NOT_EXIST
229 UNINSTALL PLUGIN simple_parser;