Debian Codegraph @

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

raw
1 --source include/have_innodb.inc
2 --source include/innodb_page_size_small.inc
3 --source include/no_valgrind_without_big.inc
4
5 #------------------------------------------------------------------------------
6 # FTS with FK and update cascade
7 #-------------------------------------------------------------------------------
8 set names utf8;
9
10 call mtr.add_suppression("\\[Warning\\] InnoDB: A new Doc ID must be supplied while updating FTS indexed columns.");
11 call mtr.add_suppression("\\[Warning\\] InnoDB: FTS Doc ID must be larger than [0-9]+ for table `test`.`t1`");
12
13 # Create FTS table
14 CREATE TABLE t1 (
15 id1 INT ,
16 a1 VARCHAR(200) ,
17 b1 TEXT ,
18 FULLTEXT KEY (a1,b1), PRIMARY KEY (a1, id1)
19 ) CHARACTER SET = utf8 , ENGINE = InnoDB;
20
21 CREATE TABLE t2 (
22 id2 INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
23 a2 VARCHAR(200),
24 b2 TEXT ,
25 FOREIGN KEY (a2) REFERENCES t1(a1) ON UPDATE CASCADE,
26 FULLTEXT KEY (b2,a2)
27 ) CHARACTER SET = utf8 ,ENGINE = InnoDB;
28
29 # Insert rows
30 INSERT INTO t1 (id1,a1,b1) VALUES
31 (1,'MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') ,
32 (2,'How To Use MySQL Well','After you went through a ...'),
33 (3,'Optimizing MySQL','In this tutorial we will show ...');
34
35 # Insert rows
36 INSERT INTO t1 (id1,a1,b1) VALUES
37 (4,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
38 (5,'MySQL vs. YourSQL','In the following database comparison ...'),
39 (6,'MySQL Security','When configured properly, MySQL ...');
40
41 # Insert rows in t2 fk table
42 INSERT INTO t2 (a2,b2) VALUES
43 ('MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') ,
44 ('How To Use MySQL Well','After you went through a ...'),
45 ('Optimizing MySQL','In this tutorial we will show ...');
46
47 # Insert rows t2 fk table
48 INSERT INTO t2 (a2,b2) VALUES
49 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
50 ('MySQL vs. YourSQL','In the following database comparison ...'),
51 ('MySQL Security','When configured properly, MySQL ...');
52
53 # error on violating fk constraint
54 --error 1452
55 INSERT INTO t2 (a2,b2) VALUES
56 ('MySQL Tricks','1. Never run mysqld as root. 2. ...');
57
58 # error on delete from parent table
59 --error 1451
60 DELETE FROM t1;
61
62 ANALYZE TABLE t1;
63 ANALYZE TABLE t2;
64
65 SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial') ORDER BY id1;
66 SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial') ORDER BY id2;
67
68 SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ORDER BY id1;
69 SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ORDER BY id2;
70
71 SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial' WITH QUERY EXPANSION) ORDER BY id1;
72 SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial' WITH QUERY EXPANSION) ORDER BY id2;
73
74
75 SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('"dbms database"@4' IN BOOLEAN MODE) ;
76 SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('"dbms database"@4' IN BOOLEAN MODE) ;
77
78 set global innodb_optimize_fulltext_only=1;
79 optimize table t1;
80 set global innodb_optimize_fulltext_only=0;
81 # Updating parent table hence child table should get updated due to 'update cascade' clause
82 UPDATE t1 SET a1 = "changing column - on update cascade" , b1 = "to check foreign constraint" WHERE
83 MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ;
84
85 # no records expected
86 SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ;
87 # InnoDB:Error child table shows records which is incorrect - UPADTE on Fix
88 SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ;
89
90 # it shows updated record
91 SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('+update +cascade' IN BOOLEAN MODE) ORDER BY id1;
92 # InnoDB:Error child table does not show the expected record
93 SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('+update +cascade' IN BOOLEAN MODE) ORDER BY id2;
94 SELECT id2 FROM t2 WHERE a2 LIKE '%UPDATE CASCADE%' ORDER BY id2;
95
96 DROP TABLE t2 , t1;
97
98 # on update cascade
99 create table t1 (s1 int, s2 varchar(200), primary key (s1,s2)) ENGINE = InnoDB;
100 create table t2 (s1 int, s2 varchar(200),
101 fulltext key(s2),
102 foreign key (s1,s2) references t1 (s1,s2) on update cascade) ENGINE = InnoDB;
103 insert into t1 values (1,'Sunshine'),(2,'Lollipops');
104 insert into t2 values (1,'Sunshine'),(2,'Lollipops');
105 update t1 set s2 = 'Rainbows' where s2 <> 'Sunshine';
106 commit;
107 select * from t2 where match(s2) against ('Lollipops');
108 DROP TABLE t2 , t1;
109
110 # on delete cascade
111 create table t1 (s1 int, s2 varchar(200), primary key (s1,s2)) ENGINE = InnoDB;
112 create table t2 (s1 int, s2 varchar(200),
113 fulltext key(s2),
114 foreign key (s1,s2) references t1 (s1,s2) on delete cascade) ENGINE = InnoDB;
115 insert into t1 values (1,'Sunshine'),(2,'Lollipops');
116 insert into t2 values (1,'Sunshine'),(2,'Lollipops');
117 delete from t1 where s2 <> 'Sunshine';
118 select * from t2 where match(s2) against ('Lollipops');
119 DROP TABLE t2 , t1;
120
121 # on delete set NULL
122 create table t1 (s1 int, s2 varchar(200), primary key (s1,s2)) ENGINE = InnoDB;
123 create table t2 (s1 int, s2 varchar(200),
124 fulltext key(s2),
125 foreign key (s1,s2) references t1 (s1,s2) on delete set null) ENGINE = InnoDB;
126 insert into t1 values (1,'Sunshine'),(2,'Lollipops');
127 insert into t2 values (1,'Sunshine'),(2,'Lollipops');
128 delete from t1 where s2 <> 'Sunshine';
129 select * from t2 where match(s2) against ('Lollipops');
130 DROP TABLE t2 , t1;
131
132
133 # on update set NULL
134 create table t1 (s1 int, s2 varchar(200), primary key (s1,s2)) ENGINE = InnoDB;
135 create table t2 (s1 int, s2 varchar(200),
136 fulltext key(s2),
137 foreign key (s1,s2) references t1 (s1,s2) on update set null) ENGINE = InnoDB;
138 insert into t1 values (1,'Sunshine'),(2,'Lollipops');
139 insert into t2 values (1,'Sunshine'),(2,'Lollipops');
140 update t1 set s2 = 'Rainbows' where s2 <> 'Sunshine';
141 commit;
142 select * from t2 where match(s2) against ('Lollipops');
143 DROP TABLE t2 , t1;
144
145 # When Doc ID is involved
146 create table t1 (s1 bigint unsigned not null, s2 varchar(200),
147 primary key (s1,s2)) ENGINE = InnoDB;
148 create table t2 (FTS_DOC_ID BIGINT UNSIGNED NOT NULL, s2 varchar(200),
149 foreign key (FTS_DOC_ID) references t1 (s1)
150 on update cascade) ENGINE = InnoDB;
151
152 create fulltext index idx on t2(s2);
153
154 show create table t2;
155
156 insert into t1 values (1,'Sunshine'),(2,'Lollipops');
157 insert into t2 values (1,'Sunshine'),(2,'Lollipops');
158
159 update t1 set s1 = 3 where s1=1;
160
161 select * from t2 where match(s2) against ('sunshine');
162
163 # FTS Doc ID cannot be reused
164 --error 1451
165 update t1 set s1 = 1 where s1=3;
166
167 DROP TABLE t2 , t1;
168
169 #------------------------------------------------------------------------------
170 # FTS with FK and delete casecade
171 #------------------------------------------------------------------------------
172
173 # Create FTS table
174 CREATE TABLE t1 (
175 id1 INT ,
176 a1 VARCHAR(200) PRIMARY KEY,
177 b1 TEXT character set utf8 ,
178 FULLTEXT KEY (a1,b1)
179 ) CHARACTER SET = utf8 ,ENGINE = InnoDB;
180
181 CREATE TABLE t2 (
182 id2 INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
183 a2 VARCHAR(200),
184 b2 TEXT character set utf8 ,
185 FOREIGN KEY (a2) REFERENCES t1(a1) ON DELETE CASCADE,
186 FULLTEXT KEY (b2,a2)
187 ) CHARACTER SET = utf8 ,ENGINE = InnoDB;
188
189 # Insert rows
190 INSERT INTO t1 (id1,a1,b1) VALUES
191 (1,'MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') ,
192 (2,'How To Use MySQL Well','After you went through a ...'),
193 (3,'Optimizing MySQL','In this tutorial we will show ...'),
194 (4,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
195 (5,'MySQL vs. YourSQL','In the following database comparison ...'),
196 (6,'MySQL Security','When configured properly, MySQL ...');
197
198 # Insert rows in t2
199 INSERT INTO t2 (a2,b2) VALUES
200 ('MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') ,
201 ('How To Use MySQL Well','After you went through a ...'),
202 ('Optimizing MySQL','In this tutorial we will show ...'),
203 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
204 ('MySQL vs. YourSQL','In the following database comparison ...'),
205 ('MySQL Security','When configured properly, MySQL ...');
206
207 # delete records from parent
208 DELETE FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ;
209
210 # no records expected
211 SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ;
212 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ;
213
214 SELECT * FROM t1 WHERE a1 LIKE '%tutorial%';
215 SELECT * FROM t2 WHERE a2 LIKE '%tutorial%';
216
217 DROP TABLE t2 , t1;
218
219 #------------------------------------------------------------------------------
220 # FTS with FK+transactions and UPDATE casecade with transaction
221 #-------------------------------------------------------------------------------
222
223 call mtr.add_suppression("\\[ERROR\\] InnoDB: FTS Doc ID must be larger than 3 for table `test`.`t2`");
224
225 # Create FTS table
226 CREATE TABLE t1 (
227 id1 INT ,
228 a1 VARCHAR(200) ,
229 b1 TEXT ,
230 FULLTEXT KEY (a1,b1), PRIMARY KEY(a1, id1)
231 ) CHARACTER SET = utf8 , ENGINE = InnoDB;
232
233 CREATE TABLE t2 (
234 id2 INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
235 a2 VARCHAR(200),
236 b2 TEXT ,
237 FOREIGN KEY (a2) REFERENCES t1(a1) ON UPDATE CASCADE,
238 FULLTEXT KEY (b2,a2)
239 ) CHARACTER SET = utf8 ,ENGINE = InnoDB;
240
241 # Insert rows
242 INSERT INTO t1 (id1,a1,b1) VALUES
243 (1,'MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') ,
244 (2,'How To Use MySQL Well','After you went through a ...'),
245 (3,'Optimizing MySQL','In this tutorial we will show ...');
246
247 # Insert rows in t2 fk table
248 INSERT INTO t2 (a2,b2) VALUES
249 ('MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') ,
250 ('How To Use MySQL Well','After you went through a ...'),
251 ('Optimizing MySQL','In this tutorial we will show ...');
252
253 START TRANSACTION;
254 # Insert rows
255 INSERT INTO t1 (id1,a1,b1) VALUES
256 (4,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
257 (5,'MySQL vs. YourSQL','In the following database comparison ...'),
258 (6,'MySQL Security','When configured properly, MySQL ...');
259
260 # Insert rows t2 fk table
261 INSERT INTO t2 (a2,b2) VALUES
262 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
263 ('MySQL vs. YourSQL','In the following database comparison ...'),
264 ('MySQL Security','When configured properly, MySQL ...');
265
266 # error on violating fk constraint
267 --error 1452
268 INSERT INTO t2 (a2,b2) VALUES
269 ('MySQL Tricks','1. Never run mysqld as root. 2. ...');
270
271 # error on DELETE FROM parent table
272 --error 1451
273 DELETE FROM t1;
274
275 # records expected
276 SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial') ORDER BY id1;
277 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial') ORDER BY id2;
278 SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ORDER BY id1;
279 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ORDER BY id2;
280 SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial' WITH QUERY EXPANSION) ORDER BY id1;
281 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial' WITH QUERY EXPANSION) ORDER BY id2;
282 SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('"dbms database"@4' IN BOOLEAN MODE) ;
283 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('"dbms database"@4' IN BOOLEAN MODE) ;
284
285 # no records as data not COMMITted.
286 SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('root') ;
287 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('root') ;
288 SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('mysqld (+root)' IN BOOLEAN MODE) ;
289 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('mysqld (-root)' IN BOOLEAN MODE) ;
290 SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('root' WITH QUERY EXPANSION) ;
291 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('root' WITH QUERY EXPANSION) ;
292 SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('"database comparison"@02' IN BOOLEAN MODE) ;
293 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('"database comparison"@02' IN BOOLEAN MODE) ;
294
295 SELECT * FROM t1 ORDER BY id1;
296 SELECT * FROM t2 ORDER BY id2;
297
298 COMMIT;
299
300 START TRANSACTION;
301 # Updating parent table hence child table should get updated due to 'UPDATE cascade' clause
302 UPDATE t1 SET a1 = "changing column - on UPDATE cascade" , b1 = "to check foreign constraint" WHERE
303 MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ;
304 COMMIT;
305
306 # no records expected
307 SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ;
308 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ;
309
310 # it shows updated record
311 SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('+UPDATE +cascade' IN BOOLEAN MODE) ORDER BY id1;
312 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('+UPDATE +cascade' IN BOOLEAN MODE) ORDER BY id2;
313 SELECT * FROM t2 WHERE a2 LIKE '%UPDATE CASCADE%' ORDER BY id2;
314
315 DROP TABLE t2 , t1;
316
317
318 # FTS with FK+transactions - UPDATE cascade
319 CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB;
320 CREATE TABLE t2 (s1 INT, s2 VARCHAR(200),
321 FULLTEXT KEY(s2),
322 FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on UPDATE cascade) ENGINE = InnoDB;
323 START TRANSACTION;
324 INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops');
325 INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops');
326 UPDATE t1 set s2 = 'Rainbows' WHERE s2 <> 'Sunshine';
327 COMMIT;
328 SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops');
329 DROP TABLE t2 , t1;
330
331 # FTS with FK+transactions - on DELETE cascade
332 CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB;
333 CREATE TABLE t2 (s1 INT, s2 VARCHAR(200),
334 FULLTEXT KEY(s2),
335 FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on DELETE cascade) ENGINE = InnoDB;
336 START TRANSACTION;
337 INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops');
338 INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops');
339 DELETE FROM t1 WHERE s2 <> 'Sunshine';
340 COMMIT;
341 SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops');
342 DROP TABLE t2 , t1;
343
344 # FTS with FK+transactions - DELETE SET NULL
345 CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB;
346 CREATE TABLE t2 (s1 INT, s2 VARCHAR(200),
347 FULLTEXT KEY(s2),
348 FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on DELETE SET NULL) ENGINE = InnoDB;
349 START TRANSACTION;
350 INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops');
351 INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops');
352 DELETE FROM t1 WHERE s2 <> 'Sunshine';
353 COMMIT;
354 SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops');
355 DROP TABLE t2 , t1;
356
357
358 # FTS with FK+transactions - UPDATE SET NULL
359 CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB;
360 CREATE TABLE t2 (s1 INT, s2 VARCHAR(200),
361 FULLTEXT KEY(s2),
362 FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on UPDATE SET NULL) ENGINE = InnoDB;
363 START TRANSACTION;
364 INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops');
365 INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops');
366 UPDATE t1 set s2 = 'Rainbows' WHERE s2 <> 'Sunshine';
367 COMMIT;
368 SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops');
369 DROP TABLE t2 , t1;
370
371
372 #-----------------------------------------------------------------------------
373
374 # FTS with FK+transactions - UPDATE cascade
375 CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB;
376 CREATE TABLE t2 (s1 INT, s2 VARCHAR(200),
377 FULLTEXT KEY(s2),
378 FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on UPDATE cascade) ENGINE = InnoDB;
379 START TRANSACTION;
380 INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops');
381 INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops');
382 UPDATE t1 set s2 = 'Rainbows' WHERE s2 <> 'Sunshine';
383 ROLLBACK;
384 SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops');
385 DROP TABLE t2 , t1;
386
387 # FTS with FK+transactions - DELETE cascade
388 CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB;
389 CREATE TABLE t2 (s1 INT, s2 VARCHAR(200),
390 FULLTEXT KEY(s2),
391 FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on DELETE cascade) ENGINE = InnoDB;
392 START TRANSACTION;
393 INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops');
394 INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops');
395 DELETE FROM t1 WHERE s2 <> 'Sunshine';
396 ROLLBACK;
397 SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops');
398 DROP TABLE t2 , t1;
399
400 # FTS with FK+transactions - DELETE SET NULL
401 CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB;
402 CREATE TABLE t2 (s1 INT, s2 VARCHAR(200),
403 FULLTEXT KEY(s2),
404 FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on DELETE SET NULL) ENGINE = InnoDB;
405 START TRANSACTION;
406 INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops');
407 INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops');
408 DELETE FROM t1 WHERE s2 <> 'Sunshine';
409 ROLLBACK;
410 SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops');
411 DROP TABLE t2 , t1;
412
413
414 # FTS with FK+transactions - UPDATE SET NULL
415 CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB;
416 CREATE TABLE t2 (s1 INT, s2 VARCHAR(200),
417 FULLTEXT KEY(s2),
418 FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on UPDATE SET NULL) ENGINE = InnoDB;
419 START TRANSACTION;
420 INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops');
421 INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops');
422 UPDATE t1 set s2 = 'Rainbows' WHERE s2 <> 'Sunshine';
423 ROLLBACK;
424 SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops');
425 DROP TABLE t2 , t1;
426
427
428 #------------------------------------------------------------------------------
429 # FTS index with compressed row format
430 #------------------------------------------------------------------------------
431
432 # Create FTS table
433 CREATE TABLE t1 (
434 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
435 a VARCHAR(200),
436 b TEXT
437 ) CHARACTER SET = utf8, ROW_FORMAT=COMPRESSED, ENGINE = InnoDB;
438
439 # Insert rows
440 INSERT INTO t1 (a,b) VALUES
441 ('MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') ,
442 ('How To Use MySQL Well','After you went through a ...'),
443 ('Optimizing MySQL','In this tutorial we will show ...');
444
445 # Create the FTS index Using Alter Table
446 ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b);
447 EVAL SHOW CREATE TABLE t1;
448
449 # Check whether individual space id created for AUX tables
450 SELECT count(*) FROM information_schema.innodb_sys_tables WHERE name LIKE "%FTS_%" AND space !=0;
451
452 # Insert rows
453 INSERT INTO t1 (a,b) VALUES
454 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
455 ('MySQL vs. YourSQL','In the following database comparison ...'),
456 ('MySQL Security','When configured properly, MySQL ...');
457
458 -- disable_result_log
459 ANALYZE TABLE t1;
460 -- enable_result_log
461
462 # Select word "tutorial" in the table
463 SELECT * FROM t1 WHERE MATCH (a,b)
464 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) ORDER BY id;
465
466 # boolean mode
467 select * from t1 where MATCH(a,b) AGAINST("+tutorial +VÐƷWİ" IN BOOLEAN MODE);
468 --error ER_PARSE_ERROR
469 select * from t1 where MATCH(a,b) AGAINST("+-VÐƷWİ" IN BOOLEAN MODE);
470 select * from t1 where MATCH(a,b) AGAINST("+Mysql +(tricks never)" IN BOOLEAN MODE);
471 select * from t1 where MATCH(a,b) AGAINST("+mysql -(tricks never)" IN BOOLEAN MODE) ORDER BY id;
472 select *, MATCH(a,b) AGAINST("mysql stands" IN BOOLEAN MODE) as x from t1 ORDER BY id;
473 select * from t1 where MATCH a,b AGAINST ("+database* +VÐƷW*" IN BOOLEAN MODE);
474 select * from t1 where MATCH a,b AGAINST ('"security mysql"' IN BOOLEAN MODE);
475
476 # query expansion
477 select * from t1 where MATCH(a,b) AGAINST ("VÐƷWİ" WITH QUERY EXPANSION) ORDER BY id;
478
479 # Drop index
480 ALTER TABLE t1 DROP INDEX idx;
481
482 # Create the FTS index again
483 CREATE FULLTEXT INDEX idx on t1 (a,b);
484
485 -- disable_query_log
486 -- disable_result_log
487 ANALYZE TABLE t1;
488 -- enable_result_log
489 -- enable_query_log
490
491 # Select word "tutorial" in the table
492 SELECT * FROM t1 WHERE MATCH (a,b)
493 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) ORDER BY id;
494
495 # boolean mode
496 select * from t1 where MATCH(a,b) AGAINST("+tutorial +VÐƷWİ" IN BOOLEAN MODE);
497 select * from t1 where MATCH(a,b) AGAINST("+dbms" IN BOOLEAN MODE);
498 select * from t1 where MATCH(a,b) AGAINST("+Mysql +(tricks never)" IN BOOLEAN MODE);
499 select * from t1 where MATCH(a,b) AGAINST("+mysql -(tricks never)" IN BOOLEAN MODE) ORDER BY id;
500 select *, MATCH(a,b) AGAINST("mysql VÐƷWİ" IN BOOLEAN MODE) as x from t1 ORDER BY id;
501 # Innodb:Assert eval0eval.c line 148
502 #select * from t1 where MATCH a,b AGAINST ("+database* +VÐƷWİ*" IN BOOLEAN MODE);
503 select * from t1 where MATCH a,b AGAINST ('"security mysql"' IN BOOLEAN MODE);
504
505 # query expansion
506 select * from t1 where MATCH(a,b) AGAINST ("VÐƷWİ" WITH QUERY EXPANSION) ORDER BY id;
507
508
509 # insert for proximity search
510 INSERT INTO t1 (a,b) VALUES ('test query expansion','for database ...');
511 # Insert into table with similar word of different distances
512 INSERT INTO t1 (a,b) VALUES
513 ('test proximity search, test, proximity and phrase',
514 'search, with proximity innodb');
515
516 INSERT INTO t1 (a,b) VALUES
517 ('test proximity fts search, test, proximity and phrase',
518 'search, with proximity innodb');
519
520 INSERT INTO t1 (a,b) VALUES
521 ('test more proximity fts search, test, more proximity and phrase',
522 'search, with proximity innodb');
523
524 # This should only return the first document
525 SELECT * FROM t1
526 WHERE MATCH (a,b)
527 AGAINST ('"proximity search"@2' IN BOOLEAN MODE);
528
529 # This would return no document
530 SELECT * FROM t1
531 WHERE MATCH (a,b)
532 AGAINST ('"proximity search"@1' IN BOOLEAN MODE);
533
534 # This give you all three documents
535 SELECT * FROM t1
536 WHERE MATCH (a,b)
537 AGAINST ('"proximity search"@3' IN BOOLEAN MODE) ORDER BY id;
538
539 # Similar boundary testing for the words
540 SELECT * FROM t1
541 WHERE MATCH (a,b)
542 AGAINST ('"test proximity"@5' IN BOOLEAN MODE) ORDER BY id;
543
544 # Test with more word The last document will return, please notice there
545 # is no ordering requirement for proximity search.
546 SELECT * FROM t1
547 WHERE MATCH (a,b)
548 AGAINST ('"more test proximity"@2' IN BOOLEAN MODE);
549
550 SELECT * FROM t1
551 WHERE MATCH (a,b)
552 AGAINST ('"more test proximity"@3' IN BOOLEAN MODE);
553
554 # The phrase search will not require exact word ordering
555 SELECT * FROM t1
556 WHERE MATCH (a,b)
557 AGAINST ('"more fts proximity"@03' IN BOOLEAN MODE);
558
559
560 UPDATE t1 SET a = UPPER(a) , b = UPPER(b) ;
561 UPDATE t1 SET a = UPPER(a) , b = LOWER(b) ;
562
563 select * from t1 where MATCH(a,b) AGAINST("+tutorial +dbms" IN BOOLEAN MODE);
564 select * from t1 where MATCH(a,b) AGAINST("+VÐƷWİ" IN BOOLEAN MODE);
565
566 SELECT * FROM t1 WHERE MATCH (a,b)
567 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) ORDER BY id;
568
569 DELETE FROM t1 WHERE MATCH (a,b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
570 DELETE FROM t1 WHERE MATCH (a,b) AGAINST ('"proximity search"@14' IN BOOLEAN MODE);
571
572
573 SELECT * FROM t1 WHERE MATCH (a,b)
574 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
575
576 SELECT * FROM t1 ORDER BY id;
577
578 DROP TABLE t1;
579
580 #------------------------------------------------------------------------------
581 # FTS index with utf8 character testcase
582 #------------------------------------------------------------------------------
583
584 # Create FTS table
585 EVAL CREATE TABLE t1 (
586 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
587 a VARCHAR(200),
588 b TEXT
589 ) CHARACTER SET = utf8, ENGINE=InnoDB;
590
591
592 # Insert rows from different languages
593 INSERT INTO t1 (a,b) VALUES
594 ('Я могу есть стекло', 'оно мне не вредит'),
595 ('Мога да ям стъкло', 'то не ми вреди'),
596 ('Μπορῶ νὰ φάω σπασμένα' ,'γυαλιὰ χωρὶς νὰ πάθω τίποτα'),
597 ('Příliš žluťoučký kůň', 'úpěl ďábelské kódy'),
598 ('Sævör grét', 'áðan því úlpan var ónýt'),
599 ('うゐのおくやま','けふこえて'),
600 ('いろはにほへど ちりぬる','あさきゆめみじ ゑひもせず');
601
602 # insert english text
603 INSERT INTO t1 (a,b) VALUES
604 ('MySQL Tutorial','request [email protected] ...') ,
605 ('Trial version','query performace @1255 minute on 2.1Hz Memory 2GB...') ,
606 ('when To Use MySQL Well','for free faq [email protected] ...');
607
608 # Create the FTS index again
609 CREATE FULLTEXT INDEX idx on t1 (a,b);
610
611 # FTS Queries
612 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("вредит χωρὶς") ORDER BY id;
613 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("оно" WITH QUERY EXPANSION);
614
615 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("вред*" IN BOOLEAN MODE) ORDER BY id;
616 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+γυαλιὰ +tutorial" IN BOOLEAN MODE);
617 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+tutorial +(Мога τίποτα)" IN BOOLEAN MODE);
618
619 # Innodb:error - no result returned (update result of query once fixed) (innodb limit , does not understand character boundry for japanses like charcter set)
620 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("あさきゆめみじ ゑひもせず");
621 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("ちりぬる" WITH QUERY EXPANSION);
622
623 # Innodb:error - no result returned (update result of query once fixed) (innodb limit , does not understand character boundry for japanses like charcter set)
624 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("+あさきゆめみじ +ゑひもせず" IN BOOLEAN MODE);
625 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("うゐのおく*" IN BOOLEAN MODE);
626 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+Sævör +úlpan" IN BOOLEAN MODE);
627
628 SELECT * FROM t1
629 WHERE MATCH (a,b)
630 AGAINST ('"γυαλιὰ χωρὶς"@2' IN BOOLEAN MODE);
631
632 SELECT * FROM t1
633 WHERE MATCH (a,b)
634 AGAINST ('"query performace"@02' IN BOOLEAN MODE);
635
636 SELECT * FROM t1
637 WHERE MATCH (a,b)
638 AGAINST ('"πάθω τίποτα"@2' IN BOOLEAN MODE);
639
640 SELECT * FROM t1
641 WHERE MATCH (a,b)
642 AGAINST ('"あさきゆめみじ ゑひもせず"@1' IN BOOLEAN MODE);
643
644 SELECT * FROM t1
645 WHERE MATCH (a,b)
646 AGAINST ('"あさきゆめみじ ゑひもせず"@2' IN BOOLEAN MODE);
647
648 ALTER TABLE t1 DROP INDEX idx;
649 # Create the FTS index again
650 CREATE FULLTEXT INDEX idx on t1 (a,b);
651
652 # Innodb:error - no result returned (update result of query once fixed) (innodb limit , does not understand character boundry for japanses like charcter set)
653 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("あさきゆめみじ ゑひもせず");
654 # Update fails because where condition do not succeed which is incorrect (update result of query once fixed)
655 UPDATE t1 SET a = "Pchnąć w tę łódź jeża" , b = "lub osiem skrzyń fig" WHERE MATCH(a,b) AGAINST ("あさきゆめみじ ゑひもせず");
656 UPDATE t1 SET a = "В чащах юга жил-был цитрус? Да", b = "но фальшивый экземпляр! ёъ" WHERE MATCH(a,b) AGAINST ("вред*" IN BOOLEAN MODE);
657 DELETE FROM t1 WHERE MATCH(a,b) AGAINST("+Sævör +úlpan" IN BOOLEAN MODE);
658
659 # Innodb error - no result returned
660 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("あさきゆめみじ ゑひもせず");
661 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("łódź osiem");
662 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("вред*" IN BOOLEAN MODE);
663 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("фальшив*" IN BOOLEAN MODE) ORDER BY id;
664 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+Sævör +úlpan" IN BOOLEAN MODE);
665
666 SELECT * FROM t1
667 WHERE MATCH (a,b)
668 AGAINST ('"łódź jeża"@2' IN BOOLEAN MODE);
669
670 SELECT * FROM t1 ORDER BY id;
671 DROP TABLE t1;
672
673 # This is to test the update operation on FTS indexed and non-indexed
674 # column
675 CREATE TABLE t1(ID INT PRIMARY KEY,
676 no_fts_field VARCHAR(10),
677 fts_field VARCHAR(10),
678 FULLTEXT INDEX f(fts_field)) ENGINE=INNODB;
679
680 INSERT INTO t1 VALUES (1, 'AAA', 'BBB');
681
682 SELECT * FROM t1 WHERE MATCH(fts_field) against("BBB");
683
684 # Update FULLTEXT indexed column, Doc ID will be updated
685 UPDATE t1 SET fts_field='anychange' where id = 1;
686
687 SELECT * FROM t1 WHERE MATCH(fts_field) against("anychange");
688
689 # Update non-FULLTEXT indexed column, Doc ID stay to be the same
690 UPDATE t1 SET no_fts_field='anychange' where id = 1;
691
692 SELECT * FROM t1 WHERE MATCH(fts_field) against("anychange");
693
694 # Update both FULLTEXT indexed and non-indexed column, Doc ID will be updated
695 UPDATE t1 SET no_fts_field='anychange', fts_field='other' where id = 1;
696
697 SELECT * FROM t1 WHERE MATCH(fts_field) against("other");
698
699 SELECT * FROM t1 WHERE MATCH(fts_field) against("BBB");
700
701 # FTS index dropped, the DOC_ID column is kept, however, the ID will not
702 # change
703 DROP INDEX f on t1;
704
705 UPDATE t1 SET fts_field='anychange' where id = 1;
706
707 UPDATE t1 SET no_fts_field='anychange' where id = 1;
708
709 UPDATE t1 SET no_fts_field='anychange', fts_field='other' where id = 1;
710
711 CREATE FULLTEXT INDEX f ON t1(FTS_FIELD);
712
713 SELECT * FROM t1 WHERE MATCH(fts_field) against("other");
714
715 DROP TABLE t1;
716
717 # Test on user supplied 'FTS_DOC_ID'
718 CREATE TABLE t1(`FTS_DOC_ID` serial,
719 no_fts_field VARCHAR(10),
720 fts_field VARCHAR(10),
721 FULLTEXT INDEX f(fts_field)) ENGINE=INNODB;
722
723 INSERT INTO t1 VALUES (1, 'AAA', 'BBB');
724
725 # Doc ID must be updated as well (HA_FTS_INVALID_DOCID).
726 --error 182
727 UPDATE t1 SET fts_field='anychange' where FTS_DOC_ID = 1;
728
729 UPDATE t1 SET fts_field='anychange', FTS_DOC_ID = 2 where FTS_DOC_ID = 1;
730
731 SELECT * FROM t1 WHERE MATCH(fts_field) against("anychange");
732
733 # "BBB" should be marked as deleted.
734 SELECT * FROM t1 WHERE MATCH(fts_field) against("BBB");
735
736 UPDATE t1 SET no_fts_field='anychange' where FTS_DOC_ID = 2;
737
738 SELECT * FROM t1 WHERE MATCH(fts_field) against("anychange");
739
740 # "HA_FTS_INVALID_DOCID"
741 --error 182
742 UPDATE t1 SET no_fts_field='anychange', fts_field='other' where FTS_DOC_ID = 2;
743
744 SELECT * FROM t1 WHERE MATCH(fts_field) against("other");
745
746 # Doc ID must be monotonically increase (HA_FTS_INVALID_DOCID)
747 --error 182
748 UPDATE t1 SET FTS_DOC_ID = 1 where FTS_DOC_ID = 2;
749
750 DROP INDEX f ON t1;
751
752 # After FULLTEXT index dropped, we can update the fields freely
753 UPDATE t1 SET fts_field='newchange' where FTS_DOC_ID = 2;
754
755 UPDATE t1 SET no_fts_field='anychange' where FTS_DOC_ID = 2;
756
757 SELECT * FROM t1;
758
759 DROP TABLE t1;
760
761 CREATE TABLE t1(ID INT PRIMARY KEY,
762 no_fts_field VARCHAR(10),
763 fts_field VARCHAR(10),
764 FULLTEXT INDEX f(fts_field), index k(fts_field)) ENGINE=INNODB;
765
766 CREATE TABLE t2(ID INT PRIMARY KEY,
767 no_fts_field VARCHAR(10),
768 fts_field VARCHAR(10),
769 FULLTEXT INDEX f(fts_field),
770 INDEX k2(fts_field),
771 FOREIGN KEY(fts_field) REFERENCES
772 t1(fts_field) ON UPDATE CASCADE) ENGINE=INNODB;
773
774 INSERT INTO t1 VALUES (1, 'AAA', 'BBB');
775
776 INSERT INTO t2 VALUES (1, 'AAA', 'BBB');
777
778 update t1 set fts_field='newchange' where id =1;
779
780 SELECT * FROM t1 WHERE MATCH(fts_field) against("BBB");
781 SELECT * FROM t2 WHERE MATCH(fts_field) against("BBB");
782 SELECT * FROM t1 WHERE MATCH(fts_field) against("newchange");
783 SELECT * FROM t2 WHERE MATCH(fts_field) against("newchange");
784
785 DROP TABLE t2;
786
787 DROP TABLE t1;
788
789 # Testcases adopted from innodb_multi_update.test
790
791 CREATE TABLE t1(id INT PRIMARY KEY,
792 fts_field VARCHAR(10),
793 FULLTEXT INDEX f(fts_field)) ENGINE=INNODB;
794
795
796 CREATE TABLE t2(id INT PRIMARY KEY,
797 fts_field VARCHAR(10),
798 FULLTEXT INDEX f(fts_field)) ENGINE=INNODB;
799
800 INSERT INTO t1 values (1,'100'),(2,'200'),(3,'300'),(4,'400'),(5,'500'),(6,'600'), (7,'700'),(8,'800'),(9,'900'),(10,'1000'),(11,'1100'),(12,'1200');
801 INSERT INTO t2 values (1,'100'),(2,'200'),(3,'300'),(4,'400'),(5,'500'),(6,'600'), (7,'700'),(8,'800');
802
803 UPDATE t1, t2 set t1.fts_field = CONCAT(t1.fts_field, 'foo');
804
805 UPDATE t1, t2 set t1.fts_field = CONCAT(t1.fts_field, 'foo') WHERE t1.fts_field = "100foo";
806
807 # Update two tables in the same statement
808 UPDATE t1, t2 set t1.fts_field = CONCAT(t1.fts_field, 'xoo'), t2.fts_field = CONCAT(t1.fts_field, 'xoo') where t1.fts_field=CONCAT(t2.fts_field, 'foo');
809
810 # Following selects shows whether the correct Doc ID are updated
811
812 # This row should present in table t1
813 SELECT * FROM t1 WHERE MATCH(fts_field) against("100foofoo");
814
815 # Following rows should be dropped
816 SELECT * FROM t1 WHERE MATCH(fts_field) against("100foo");
817 SELECT * FROM t1 WHERE MATCH(fts_field) against("100");
818
819 # This row should present in table t2
820 SELECT * FROM t2 WHERE MATCH(fts_field) against("400fooxoo");
821 SELECT * FROM t2 WHERE MATCH(fts_field) against("100");
822
823 # Follow rows should be marked as dropped
824 SELECT * FROM t2 WHERE MATCH(fts_field) against("200");
825 SELECT * FROM t2 WHERE MATCH(fts_field) against("400");
826
827
828 DROP TABLE t1;
829
830 DROP TABLE t2;
831
832
833 --echo
834 --echo BUG#13701973/64274: MYSQL THREAD WAS SUSPENDED WHEN EXECUTE UPDATE QUERY
835 --echo
836 # FTS setup did not track which tables it had already looked at to see whether
837 # they need initialization. Hilarity ensued when hitting circular dependencies.
838
839 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
840
841 CREATE TABLE t1 (
842 t1_id INT(10) UNSIGNED NOT NULL,
843 t2_id INT(10) UNSIGNED DEFAULT NULL,
844 PRIMARY KEY (t1_id),
845 FOREIGN KEY (t2_id) REFERENCES t2 (t2_id)
846 ON DELETE CASCADE ON UPDATE CASCADE
847 ) ENGINE=InnoDB;
848
849 CREATE TABLE t2 (
850 t1_id INT(10) UNSIGNED NOT NULL,
851 t2_id INT(10) UNSIGNED NOT NULL,
852 t3_id INT(10) UNSIGNED NOT NULL,
853 t4_id INT(10) UNSIGNED NOT NULL,
854 PRIMARY KEY (t2_id),
855 FOREIGN KEY (t1_id) REFERENCES t1 (t1_id),
856 FOREIGN KEY (t3_id) REFERENCES t3 (t3_id)
857 ON DELETE CASCADE ON UPDATE CASCADE,
858 FOREIGN KEY (t4_id) REFERENCES t4 (t4_id)
859 ) ENGINE=InnoDB;
860
861 CREATE TABLE t3 (
862 t3_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
863 payload char(3),
864 PRIMARY KEY (t3_id)
865 ) ENGINE=InnoDB;
866
867 INSERT INTO t3 VALUES (1, '100');
868
869 CREATE TABLE t4 (
870 t2_id INT(10) UNSIGNED DEFAULT NULL,
871 t4_id INT(10) UNSIGNED NOT NULL,
872 PRIMARY KEY (t4_id),
873 FOREIGN KEY (t2_id) REFERENCES t2 (t2_id)
874 ON DELETE CASCADE ON UPDATE CASCADE
875 ) ENGINE=InnoDB;
876
877 SET FOREIGN_KEY_CHECKS=1;
878
879 UPDATE t3 SET payload='101' WHERE t3_id=1;
880
881 SET FOREIGN_KEY_CHECKS=0;
882
883 DROP TABLE t1;
884 DROP TABLE t2;
885 DROP TABLE t3;
886 DROP TABLE t4;
887
888 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;