Debian Codegraph @

mysql-test/suite/innodb_fts/r/basic.result

raw
1 CREATE TEMPORARY TABLE articles (
2 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
3 title VARCHAR(200),
4 body TEXT,
5 FULLTEXT (title,body)
6 ) ENGINE=InnoDB;
7 ERROR HY000: Cannot create FULLTEXT index on temporary InnoDB table
8 INSERT INTO articles (title,body) VALUES
9 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
10 ('How To Use MySQL Well','After you went through a ...'),
11 ('Optimizing MySQL','In this tutorial we will show ...'),
12 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
13 ('MySQL vs. YourSQL','In the following database comparison ...'),
14 ('MySQL Security','When configured properly, MySQL ...');
15 ANALYZE TABLE articles;
16 SELECT * FROM articles
17 WHERE MATCH (title,body)
18 AGAINST ('Database' IN NATURAL LANGUAGE MODE);
19 id title body
20 1 MySQL Tutorial DBMS stands for DataBase ...
21 5 MySQL vs. YourSQL In the following database comparison ...
22 SELECT COUNT(*) FROM articles
23 WHERE MATCH (title,body)
24 AGAINST ('database' IN NATURAL LANGUAGE MODE);
25 COUNT(*)
26 2
27 SELECT * FROM articles
28 WHERE MATCH (title, body)
29 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
30 id title body
31 1 MySQL Tutorial DBMS stands for DataBase ...
32 3 Optimizing MySQL In this tutorial we will show ...
33 SELECT COUNT(IF(MATCH (title,body)
34 AGAINST ('database' IN NATURAL LANGUAGE MODE), 1, NULL))
35 AS count FROM articles;
36 count
37 2
38 SELECT id, body, MATCH (title,body)
39 AGAINST ('Database' IN NATURAL LANGUAGE MODE) AS score
40 FROM articles;
41 id body score
42 1 DBMS stands for DataBase ... 0.22764469683170319
43 2 After you went through a ... 0
44 3 In this tutorial we will show ... 0
45 4 1. Never run mysqld as root. 2. ... 0
46 5 In the following database comparison ... 0.22764469683170319
47 6 When configured properly, MySQL ... 0
48 SELECT * FROM articles
49 WHERE MATCH (title,body)
50 AGAINST ('MySQL' IN NATURAL LANGUAGE MODE);
51 id title body
52 6 MySQL Security When configured properly, MySQL ...
53 1 MySQL Tutorial DBMS stands for DataBase ...
54 2 How To Use MySQL Well After you went through a ...
55 3 Optimizing MySQL In this tutorial we will show ...
56 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
57 5 MySQL vs. YourSQL In the following database comparison ...
58 SELECT * FROM articles WHERE MATCH (title,body)
59 AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
60 id title body
61 6 MySQL Security When configured properly, MySQL ...
62 1 MySQL Tutorial DBMS stands for DataBase ...
63 2 How To Use MySQL Well After you went through a ...
64 3 Optimizing MySQL In this tutorial we will show ...
65 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
66 SELECT * FROM articles WHERE MATCH (title,body)
67 AGAINST ('DBMS Security' IN BOOLEAN MODE);
68 id title body
69 1 MySQL Tutorial DBMS stands for DataBase ...
70 6 MySQL Security When configured properly, MySQL ...
71 SELECT * FROM articles WHERE MATCH (title,body)
72 AGAINST ('+MySQL +YourSQL' IN BOOLEAN MODE);
73 id title body
74 5 MySQL vs. YourSQL In the following database comparison ...
75 SELECT * FROM articles WHERE MATCH (title,body)
76 AGAINST ('+MySQL YourSQL' IN BOOLEAN MODE);
77 id title body
78 5 MySQL vs. YourSQL In the following database comparison ...
79 6 MySQL Security When configured properly, MySQL ...
80 1 MySQL Tutorial DBMS stands for DataBase ...
81 2 How To Use MySQL Well After you went through a ...
82 3 Optimizing MySQL In this tutorial we will show ...
83 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
84 SELECT * FROM articles WHERE MATCH (title,body)
85 AGAINST ('+MySQL ~YourSQL' IN BOOLEAN MODE);
86 id title body
87 6 MySQL Security When configured properly, MySQL ...
88 1 MySQL Tutorial DBMS stands for DataBase ...
89 2 How To Use MySQL Well After you went through a ...
90 3 Optimizing MySQL In this tutorial we will show ...
91 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
92 5 MySQL vs. YourSQL In the following database comparison ...
93 SELECT * FROM articles WHERE MATCH (title,body)
94 AGAINST ('t*' IN BOOLEAN MODE);
95 id title body
96 1 MySQL Tutorial DBMS stands for DataBase ...
97 2 How To Use MySQL Well After you went through a ...
98 3 Optimizing MySQL In this tutorial we will show ...
99 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
100 SELECT * FROM articles WHERE MATCH (title,body)
101 AGAINST ('MY*' IN BOOLEAN MODE);
102 id title body
103 6 MySQL Security When configured properly, MySQL ...
104 1 MySQL Tutorial DBMS stands for DataBase ...
105 2 How To Use MySQL Well After you went through a ...
106 3 Optimizing MySQL In this tutorial we will show ...
107 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
108 5 MySQL vs. YourSQL In the following database comparison ...
109 SELECT * FROM articles WHERE MATCH (title,body)
110 AGAINST ('ru*' IN BOOLEAN MODE);
111 id title body
112 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
113 SELECT * FROM articles WHERE MATCH (title,body)
114 AGAINST ('+ MySQL >Well < stands' IN BOOLEAN MODE);
115 id title body
116 2 How To Use MySQL Well After you went through a ...
117 6 MySQL Security When configured properly, MySQL ...
118 3 Optimizing MySQL In this tutorial we will show ...
119 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
120 5 MySQL vs. YourSQL In the following database comparison ...
121 1 MySQL Tutorial DBMS stands for DataBase ...
122 SELECT * FROM articles WHERE MATCH (title,body)
123 AGAINST ('+ MySQL - (Well stands)' IN BOOLEAN MODE);
124 id title body
125 6 MySQL Security When configured properly, MySQL ...
126 3 Optimizing MySQL In this tutorial we will show ...
127 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
128 5 MySQL vs. YourSQL In the following database comparison ...
129 SELECT * FROM articles WHERE MATCH (title,body) AGAINST
130 ('(((((((((((((((((((((((((((((((((Security)))))))))))))))))))))))))))))))))'
131 IN BOOLEAN MODE);
132 ERROR HY000: Table handler out of memory
133 SELECT * FROM articles WHERE MATCH (title,body) AGAINST
134 ('((((((((((((((((((((((((((((((((Security))))))))))))))))))))))))))))))))'
135 IN BOOLEAN MODE);
136 id title body
137 6 MySQL Security When configured properly, MySQL ...
138 SELECT * FROM articles WHERE MATCH (title,body) AGAINST
139 ('(((((((((((((((((((((((((((((((vs))))))))))))))))))))))))))))))),(((to)))'
140 IN BOOLEAN MODE);
141 id title body
142 SELECT * FROM articles WHERE MATCH (title,body) AGAINST
143 ('((((((((((((((((((((((((((((((((Security)))))))))))))))))))))))))))))))'
144 IN BOOLEAN MODE);
145 ERROR 42000: syntax error, unexpected $end
146 SELECT * FROM articles WHERE MATCH (title,body) AGAINST
147 ('(((((((((((((((((((((((((((((((((Security))))))))))))))))))))))))))))))))'
148 IN BOOLEAN MODE);
149 ERROR 42000: syntax error, unexpected $end
150 SELECT * FROM articles WHERE MATCH (title,body)
151 AGAINST ('+ MySQL + (>Well < stands)' IN BOOLEAN MODE);
152 id title body
153 2 How To Use MySQL Well After you went through a ...
154 1 MySQL Tutorial DBMS stands for DataBase ...
155 SELECT * FROM articles WHERE MATCH (title,body)
156 AGAINST ('YourSQL + (+MySQL - (Tricks Security))' IN BOOLEAN MODE);
157 id title body
158 5 MySQL vs. YourSQL In the following database comparison ...
159 1 MySQL Tutorial DBMS stands for DataBase ...
160 2 How To Use MySQL Well After you went through a ...
161 3 Optimizing MySQL In this tutorial we will show ...
162 SELECT * FROM articles WHERE MATCH (title,body)
163 AGAINST ('(+MySQL - (Tricks Security)) - YourSQL' IN BOOLEAN MODE);
164 id title body
165 1 MySQL Tutorial DBMS stands for DataBase ...
166 2 How To Use MySQL Well After you went through a ...
167 3 Optimizing MySQL In this tutorial we will show ...
168 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('mysql - Security&DBMS' IN BOOLEAN MODE);
169 id title body
170 2 How To Use MySQL Well After you went through a ...
171 3 Optimizing MySQL In this tutorial we will show ...
172 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
173 5 MySQL vs. YourSQL In the following database comparison ...
174 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('mysql - (Security DBMS)' IN BOOLEAN MODE);
175 id title body
176 2 How To Use MySQL Well After you went through a ...
177 3 Optimizing MySQL In this tutorial we will show ...
178 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
179 5 MySQL vs. YourSQL In the following database comparison ...
180 SELECT * FROM articles WHERE MATCH (title,body) AGAINST (' - Security&DBMS + YourSQL' IN BOOLEAN MODE);
181 id title body
182 5 MySQL vs. YourSQL In the following database comparison ...
183 SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+YourSQL - Security&DBMS' IN BOOLEAN MODE);
184 id title body
185 5 MySQL vs. YourSQL In the following database comparison ...
186 SELECT COUNT(*) FROM articles
187 WHERE MATCH (title,body)
188 AGAINST ('database' WITH QUERY EXPANSION);
189 COUNT(*)
190 6
191 INSERT INTO articles (title,body) VALUES
192 ('test query expansion','for database ...');
193 SELECT * FROM articles
194 WHERE MATCH (title,body)
195 AGAINST ('test' WITH QUERY EXPANSION);
196 id title body
197 7 test query expansion for database ...
198 1 MySQL Tutorial DBMS stands for DataBase ...
199 5 MySQL vs. YourSQL In the following database comparison ...
200 SELECT * FROM articles
201 WHERE MATCH (title,body)
202 AGAINST ('"following comparison"@3' IN BOOLEAN MODE);
203 id title body
204 5 MySQL vs. YourSQL In the following database comparison ...
205 SELECT * FROM articles
206 WHERE MATCH (title,body)
207 AGAINST ('"following comparison"@2' IN BOOLEAN MODE);
208 id title body
209 SELECT * FROM articles
210 WHERE MATCH (title,body)
211 AGAINST ('"following database"' IN BOOLEAN MODE);
212 id title body
213 5 MySQL vs. YourSQL In the following database comparison ...
214 INSERT INTO articles (title,body) VALUES
215 ('test proximity search, test, proximity and phrase',
216 'search, with proximity innodb');
217 INSERT INTO articles (title,body) VALUES
218 ('test my proximity fts new search, test, proximity and phrase',
219 'search, with proximity innodb');
220 INSERT INTO articles (title,body) VALUES
221 ('test more of proximity fts search, test, more proximity and phrase',
222 'search, with proximity innodb');
223 SELECT * FROM articles
224 WHERE MATCH (title,body)
225 AGAINST ('"proximity search"@3' IN BOOLEAN MODE);
226 id title body
227 8 test proximity search, test, proximity and phrase search, with proximity innodb
228 9 test my proximity fts new search, test, proximity and phrase search, with proximity innodb
229 10 test more of proximity fts search, test, more proximity and phrase search, with proximity innodb
230 SELECT * FROM articles
231 WHERE MATCH (title,body)
232 AGAINST ('"proximity search"@2' IN BOOLEAN MODE);
233 id title body
234 8 test proximity search, test, proximity and phrase search, with proximity innodb
235 SELECT * FROM articles
236 WHERE MATCH (title,body)
237 AGAINST ('"proximity search"@5' IN BOOLEAN MODE);
238 id title body
239 8 test proximity search, test, proximity and phrase search, with proximity innodb
240 9 test my proximity fts new search, test, proximity and phrase search, with proximity innodb
241 10 test more of proximity fts search, test, more proximity and phrase search, with proximity innodb
242 SELECT * FROM articles
243 WHERE MATCH (title,body)
244 AGAINST ('"test proximity"@5' IN BOOLEAN MODE);
245 id title body
246 8 test proximity search, test, proximity and phrase search, with proximity innodb
247 9 test my proximity fts new search, test, proximity and phrase search, with proximity innodb
248 10 test more of proximity fts search, test, more proximity and phrase search, with proximity innodb
249 SELECT * FROM articles
250 WHERE MATCH (title,body)
251 AGAINST ('"test proximity"@1' IN BOOLEAN MODE);
252 id title body
253 SELECT * FROM articles
254 WHERE MATCH (title,body)
255 AGAINST ('"test proximity"@4' IN BOOLEAN MODE);
256 id title body
257 8 test proximity search, test, proximity and phrase search, with proximity innodb
258 9 test my proximity fts new search, test, proximity and phrase search, with proximity innodb
259 10 test more of proximity fts search, test, more proximity and phrase search, with proximity innodb
260 SELECT * FROM articles
261 WHERE MATCH (title,body)
262 AGAINST ('"test proximity"@3' IN BOOLEAN MODE);
263 id title body
264 8 test proximity search, test, proximity and phrase search, with proximity innodb
265 9 test my proximity fts new search, test, proximity and phrase search, with proximity innodb
266 10 test more of proximity fts search, test, more proximity and phrase search, with proximity innodb
267 SELECT * FROM articles
268 WHERE MATCH (title,body)
269 AGAINST ('"more test proximity"@4' IN BOOLEAN MODE);
270 id title body
271 10 test more of proximity fts search, test, more proximity and phrase search, with proximity innodb
272 SELECT * FROM articles
273 WHERE MATCH (title,body)
274 AGAINST ('"more test proximity"@3' IN BOOLEAN MODE);
275 id title body
276 10 test more of proximity fts search, test, more proximity and phrase search, with proximity innodb
277 SELECT * FROM articles
278 WHERE MATCH (title,body)
279 AGAINST ('"more test proximity"' IN BOOLEAN MODE);
280 id title body
281 drop table articles;
282 #
283 # Bug #22679185 INVALID INNODB FTS DOC ID DURING INSERT
284 #
285 create table t1 (f1 int not null primary key, f2 varchar(100),
286 FTS_DOC_ID bigint(20) unsigned not null,
287 unique key `FTS_DOC_ID_INDEX` (`FTS_DOC_ID`),
288 fulltext key (f2))engine=innodb;
289 insert into t1 values(1, "This is the first record", 20000);
290 insert into t1 values(2, "This is the second record", 40000);
291 select FTS_DOC_ID from t1;
292 FTS_DOC_ID
293 20000
294 40000
295 drop table t1;
296 create table t1 (f1 int not null primary key, f2 varchar(100),
297 FTS_DOC_ID bigint(20) unsigned not null auto_increment,
298 unique key `FTS_DOC_ID_INDEX` (`FTS_DOC_ID`),
299 fulltext key (f2))engine=innodb;
300 set auto_increment_increment = 65535;
301 insert into t1(f1, f2) values(1, "This is the first record");
302 insert into t1(f1, f2) values(2, "This is the second record");
303 insert into t1(f1, f2) values(3, "This is the third record");
304 select FTS_DOC_ID from t1;
305 FTS_DOC_ID
306 1
307 65536
308 131071
309 drop table t1;
310 CREATE TABLE t1 (FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
311 title VARCHAR(200), FULLTEXT(title)) ENGINE=InnoDB;
312 INSERT INTO t1 VALUES (NULL, NULL), (20030101000000, 20030102000000);
313 DROP TABLE t1;