Debian Codegraph @

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

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