1drop table if exists t1,t2,t3; 2CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b)); 3INSERT INTO t1 VALUES('MySQL has now support', 'for full-text search'), 4('Full-text indexes', 'are called collections'), 5('Only MyISAM tables','support collections'), 6('Function MATCH ... AGAINST()','is used to do a search'), 7('Full-text search in MySQL', 'implements vector space model'); 8SHOW INDEX FROM t1; 9Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 10t1 1 a 1 a NULL NULL NULL NULL YES FULLTEXT 11t1 1 a 2 b NULL NULL NULL NULL YES FULLTEXT 12select * from t1 where MATCH(a,b) AGAINST ("collections"); 13a b 14Only MyISAM tables support collections 15Full-text indexes are called collections 16explain extended select * from t1 where MATCH(a,b) AGAINST ("collections"); 17id select_type table type possible_keys key key_len ref rows filtered Extra 181 SIMPLE t1 fulltext a a 0 NULL 1 100.00 Using where 19Warnings: 20Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (match `test`.`t1`.`a`,`test`.`t1`.`b` against ('collections')) 21select * from t1 where MATCH(a,b) AGAINST ("indexes"); 22a b 23Full-text indexes are called collections 24select * from t1 where MATCH(a,b) AGAINST ("indexes collections"); 25a b 26Full-text indexes are called collections 27Only MyISAM tables support collections 28select * from t1 where MATCH(a,b) AGAINST ("only"); 29a b 30select * from t1 where MATCH(a,b) AGAINST ("collections" WITH QUERY EXPANSION); 31a b 32Only MyISAM tables support collections 33Full-text indexes are called collections 34MySQL has now support for full-text search 35select * from t1 where MATCH(a,b) AGAINST ("indexes" WITH QUERY EXPANSION); 36a b 37Full-text indexes are called collections 38Only MyISAM tables support collections 39select * from t1 where MATCH(a,b) AGAINST ("indexes collections" WITH QUERY EXPANSION); 40a b 41Full-text indexes are called collections 42Only MyISAM tables support collections 43MySQL has now support for full-text search 44select * from t1 where MATCH(a,b) AGAINST ("indexes" IN NATURAL LANGUAGE MODE); 45a b 46Full-text indexes are called collections 47select * from t1 where MATCH(a,b) AGAINST ("indexes" IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION); 48a b 49Full-text indexes are called collections 50Only MyISAM tables support collections 51select * from t1 where MATCH(a,b) AGAINST ("indexes" IN BOOLEAN MODE WITH QUERY EXPANSION); 52ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'QUERY EXPANSION)' at line 1 53explain select * from t1 where MATCH(a,b) AGAINST ("collections"); 54id select_type table type possible_keys key key_len ref rows Extra 551 SIMPLE t1 fulltext a a 0 NULL 1 Using where 56explain select * from t1 where MATCH(a,b) AGAINST ("collections")>0; 57id select_type table type possible_keys key key_len ref rows Extra 581 SIMPLE t1 fulltext a a 0 NULL 1 Using where 59explain select * from t1 where MATCH(a,b) AGAINST ("collections")>1; 60id select_type table type possible_keys key key_len ref rows Extra 611 SIMPLE t1 fulltext a a 0 NULL 1 Using where 62explain select * from t1 where MATCH(a,b) AGAINST ("collections")>=0; 63id select_type table type possible_keys key key_len ref rows Extra 641 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where 65explain select * from t1 where MATCH(a,b) AGAINST ("collections")>=1; 66id select_type table type possible_keys key key_len ref rows Extra 671 SIMPLE t1 fulltext a a 0 NULL 1 Using where 68explain select * from t1 where 0<MATCH(a,b) AGAINST ("collections"); 69id select_type table type possible_keys key key_len ref rows Extra 701 SIMPLE t1 fulltext a a 0 NULL 1 Using where 71explain select * from t1 where 1<MATCH(a,b) AGAINST ("collections"); 72id select_type table type possible_keys key key_len ref rows Extra 731 SIMPLE t1 fulltext a a 0 NULL 1 Using where 74explain select * from t1 where 0<=MATCH(a,b) AGAINST ("collections"); 75id select_type table type possible_keys key key_len ref rows Extra 761 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where 77explain select * from t1 where 1<=MATCH(a,b) AGAINST ("collections"); 78id select_type table type possible_keys key key_len ref rows Extra 791 SIMPLE t1 fulltext a a 0 NULL 1 Using where 80explain select * from t1 where MATCH(a,b) AGAINST ("collections")>0 and a like '%ll%'; 81id select_type table type possible_keys key key_len ref rows Extra 821 SIMPLE t1 fulltext a a 0 NULL 1 Using where 83select * from t1 where MATCH(a,b) AGAINST("support -collections" IN BOOLEAN MODE); 84a b 85MySQL has now support for full-text search 86explain extended select * from t1 where MATCH(a,b) AGAINST("support -collections" IN BOOLEAN MODE); 87id select_type table type possible_keys key key_len ref rows filtered Extra 881 SIMPLE t1 fulltext a a 0 NULL 1 100.00 Using where 89Warnings: 90Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (match `test`.`t1`.`a`,`test`.`t1`.`b` against ('support -collections' in boolean mode)) 91select * from t1 where MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE); 92a b 93MySQL has now support for full-text search 94Full-text indexes are called collections 95Only MyISAM tables support collections 96select * from t1 where MATCH(a,b) AGAINST("support +collections" IN BOOLEAN MODE); 97a b 98Full-text indexes are called collections 99Only MyISAM tables support collections 100select * from t1 where MATCH(a,b) AGAINST("sear*" IN BOOLEAN MODE); 101a b 102MySQL has now support for full-text search 103Function MATCH ... AGAINST() is used to do a search 104Full-text search in MySQL implements vector space model 105select * from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE); 106a b 107Only MyISAM tables support collections 108select * from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE); 109a b 110MySQL has now support for full-text search 111Function MATCH ... AGAINST() is used to do a search 112Full-text search in MySQL implements vector space model 113select * from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE); 114a b 115MySQL has now support for full-text search 116Full-text search in MySQL implements vector space model 117select * from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE); 118a b 119Function MATCH ... AGAINST() is used to do a search 120select *, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1; 121a b x 122MySQL has now support for full-text search 1 123Full-text indexes are called collections 1 124Only MyISAM tables support collections 2 125Function MATCH ... AGAINST() is used to do a search 0 126Full-text search in MySQL implements vector space model 0 127select *, MATCH(a,b) AGAINST("collections support" IN BOOLEAN MODE) as x from t1; 128a b x 129MySQL has now support for full-text search 1 130Full-text indexes are called collections 1 131Only MyISAM tables support collections 2 132Function MATCH ... AGAINST() is used to do a search 0 133Full-text search in MySQL implements vector space model 0 134select * from t1 where MATCH a,b AGAINST ("+call* +coll*" IN BOOLEAN MODE); 135a b 136Full-text indexes are called collections 137select * from t1 where MATCH a,b AGAINST ('"support now"' IN BOOLEAN MODE); 138a b 139select * from t1 where MATCH a,b AGAINST ('"Now sUPPort"' IN BOOLEAN MODE); 140a b 141MySQL has now support for full-text search 142select * from t1 where MATCH a,b AGAINST ('"now support"' IN BOOLEAN MODE); 143a b 144MySQL has now support for full-text search 145select * from t1 where MATCH a,b AGAINST ('"text search" "now support"' IN BOOLEAN MODE); 146a b 147MySQL has now support for full-text search 148Full-text search in MySQL implements vector space model 149select * from t1 where MATCH a,b AGAINST ('"text search" -"now support"' IN BOOLEAN MODE); 150a b 151Full-text search in MySQL implements vector space model 152select * from t1 where MATCH a,b AGAINST ('"text search" +"now support"' IN BOOLEAN MODE); 153a b 154MySQL has now support for full-text search 155select * from t1 where MATCH a,b AGAINST ('"text i"' IN BOOLEAN MODE); 156a b 157select * from t1 where MATCH a,b AGAINST ('"xt indexes"' IN BOOLEAN MODE); 158a b 159select * from t1 where MATCH a,b AGAINST ('+(support collections) +foobar*' IN BOOLEAN MODE); 160a b 161select * from t1 where MATCH a,b AGAINST ('+(+(support collections)) +foobar*' IN BOOLEAN MODE); 162a b 163select * from t1 where MATCH a,b AGAINST ('+collections -supp* -foobar*' IN BOOLEAN MODE); 164a b 165Full-text indexes are called collections 166select * from t1 where MATCH a,b AGAINST('"space model' IN BOOLEAN MODE); 167a b 168Full-text search in MySQL implements vector space model 169select * from t1 where MATCH a AGAINST ("search" IN BOOLEAN MODE); 170a b 171Full-text search in MySQL implements vector space model 172select * from t1 where MATCH b AGAINST ("sear*" IN BOOLEAN MODE); 173a b 174MySQL has now support for full-text search 175Function MATCH ... AGAINST() is used to do a search 176select * from t1 where MATCH(a,b) AGAINST ("collections") UNION ALL select * from t1 where MATCH(a,b) AGAINST ("indexes"); 177a b 178Only MyISAM tables support collections 179Full-text indexes are called collections 180Full-text indexes are called collections 181delete from t1 where a like "MySQL%"; 182update t1 set a='some test foobar' where MATCH a,b AGAINST ('model'); 183delete from t1 where MATCH(a,b) AGAINST ("indexes"); 184select * from t1; 185a b 186Only MyISAM tables support collections 187Function MATCH ... AGAINST() is used to do a search 188some test foobar implements vector space model 189drop table t1; 190create table t1 (a varchar(200) not null, fulltext (a)); 191insert t1 values ("aaa10 bbb20"), ("aaa20 bbb15"), ("aaa30 bbb10"); 192select * from t1 where match a against ("+aaa* +bbb*" in boolean mode); 193a 194aaa30 bbb10 195aaa20 bbb15 196aaa10 bbb20 197select * from t1 where match a against ("+aaa* +bbb1*" in boolean mode); 198a 199aaa30 bbb10 200aaa20 bbb15 201select * from t1 where match a against ("+aaa* +ccc*" in boolean mode); 202a 203select * from t1 where match a against ("+aaa10 +(bbb*)" in boolean mode); 204a 205aaa10 bbb20 206select * from t1 where match a against ("+(+aaa* +bbb1*)" in boolean mode); 207a 208aaa30 bbb10 209aaa20 bbb15 210select * from t1 where match a against ("(+aaa* +bbb1*)" in boolean mode); 211a 212aaa30 bbb10 213aaa20 bbb15 214drop table t1; 215CREATE TABLE t1 ( 216id int(11), 217ticket int(11), 218KEY ti (id), 219KEY tit (ticket) 220); 221INSERT INTO t1 VALUES (2,3),(1,2); 222CREATE TABLE t2 ( 223ticket int(11), 224inhalt text, 225KEY tig (ticket), 226fulltext index tix (inhalt) 227); 228INSERT INTO t2 VALUES (1,'foo'),(2,'bar'),(3,'foobar'); 229select t1.id FROM t2 as ttxt,t1,t1 as ticket2 230WHERE ticket2.id = ttxt.ticket AND t1.id = ticket2.ticket and 231match(ttxt.inhalt) against ('foobar'); 232id 233select ticket2.id FROM t2 as ttxt,t2 INNER JOIN t1 as ticket2 ON 234ticket2.id = t2.ticket 235WHERE ticket2.id = ticket2.ticket and match(ttxt.inhalt) against ('foobar'); 236id 237INSERT INTO t1 VALUES (3,3); 238select ticket2.id FROM t2 as ttxt,t2 239INNER JOIN t1 as ticket2 ON ticket2.id = t2.ticket 240WHERE ticket2.id = ticket2.ticket and 241match(ttxt.inhalt) against ('foobar'); 242id 2433 244show keys from t2; 245Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 246t2 1 tig 1 ticket A NULL NULL NULL YES BTREE 247t2 1 tix 1 inhalt NULL NULL NULL NULL YES FULLTEXT 248show create table t2; 249Table Create Table 250t2 CREATE TABLE `t2` ( 251 `ticket` int(11) DEFAULT NULL, 252 `inhalt` text, 253 KEY `tig` (`ticket`), 254 FULLTEXT KEY `tix` (`inhalt`) 255) ENGINE=MyISAM DEFAULT CHARSET=latin1 256select * from t2 where MATCH inhalt AGAINST (NULL); 257ticket inhalt 258select * from t2 where MATCH inhalt AGAINST ('foobar'); 259ticket inhalt 2603 foobar 261select * from t2 having MATCH inhalt AGAINST ('foobar'); 262ticket inhalt 2633 foobar 264CREATE TABLE t3 (t int(11),i text,fulltext tix (t,i)); 265ERROR HY000: Column 't' cannot be part of FULLTEXT index 266CREATE TABLE t3 (t int(11),i text, 267j varchar(200) CHARACTER SET latin2, 268fulltext tix (i,j)); 269ERROR HY000: Column 'j' cannot be part of FULLTEXT index 270CREATE TABLE t3 ( 271ticket int(11), 272inhalt text, 273KEY tig (ticket), 274fulltext index tix (inhalt) 275); 276select * from t2 where MATCH inhalt AGAINST (t2.inhalt); 277ERROR HY000: Incorrect arguments to AGAINST 278select * from t2 where MATCH ticket AGAINST ('foobar'); 279ERROR HY000: Can't find FULLTEXT index matching the column list 280select * from t2,t3 where MATCH (t2.inhalt,t3.inhalt) AGAINST ('foobar'); 281ERROR HY000: Incorrect arguments to MATCH 282drop table t1,t2,t3; 283CREATE TABLE t1 ( 284id int(11) auto_increment, 285title varchar(100) default '', 286PRIMARY KEY (id), 287KEY ind5 (title) 288) ENGINE=MyISAM; 289CREATE FULLTEXT INDEX ft1 ON t1(title); 290insert into t1 (title) values ('this is a test'); 291select * from t1 where match title against ('test' in boolean mode); 292id title 2931 this is a test 294update t1 set title='this is A test' where id=1; 295check table t1; 296Table Op Msg_type Msg_text 297test.t1 check status OK 298update t1 set title='this test once revealed a bug' where id=1; 299select * from t1; 300id title 3011 this test once revealed a bug 302update t1 set title=NULL where id=1; 303drop table t1; 304CREATE TABLE t1 (a int(11), b text, FULLTEXT KEY (b)) ENGINE=MyISAM; 305insert into t1 values (1,"I wonder why the fulltext index doesnt work?"); 306SELECT * from t1 where MATCH (b) AGAINST ('apples'); 307a b 308insert into t1 values (2,"fullaaa fullzzz"); 309select * from t1 where match b against ('full*' in boolean mode); 310a b 3112 fullaaa fullzzz 3121 I wonder why the fulltext index doesnt work? 313drop table t1; 314CREATE TABLE t1 ( id int(11) NOT NULL auto_increment primary key, mytext text NOT NULL, FULLTEXT KEY mytext (mytext)) ENGINE=MyISAM; 315INSERT INTO t1 VALUES (1,'my small mouse'),(2,'la-la-la'),(3,'It is so funny'),(4,'MySQL Tutorial'); 316select 8 from t1; 3178 3188 3198 3208 3218 322drop table t1; 323create table t1 (a text, fulltext key (a)); 324insert into t1 values ('aaaa'); 325repair table t1; 326Table Op Msg_type Msg_text 327test.t1 repair status OK 328select * from t1 where match (a) against ('aaaa'); 329a 330drop table t1; 331create table t1 ( ref_mag text not null, fulltext (ref_mag)); 332insert into t1 values ('test'); 333select ref_mag from t1 where match ref_mag against ('+test' in boolean mode); 334ref_mag 335test 336alter table t1 change ref_mag ref_mag char (255) not null; 337select ref_mag from t1 where match ref_mag against ('+test' in boolean mode); 338ref_mag 339test 340drop table t1; 341create table t1 (t1_id int(11) primary key, name varchar(32)); 342insert into t1 values (1, 'data1'); 343insert into t1 values (2, 'data2'); 344create table t2 (t2_id int(11) primary key, t1_id int(11), name varchar(32)); 345insert into t2 values (1, 1, 'xxfoo'); 346insert into t2 values (2, 1, 'xxbar'); 347insert into t2 values (3, 1, 'xxbuz'); 348select * from t1 join t2 using(`t1_id`) where match (t1.name, t2.name) against('xxfoo' in boolean mode); 349t1_id name t2_id name 3501 data1 1 xxfoo 351select * from t2 where match name against ('*a*b*c*d*e*f*' in boolean mode); 352t2_id t1_id name 353drop table t1,t2; 354create table t1 (a text, fulltext key (a)); 355insert into t1 select "xxxx yyyy zzzz"; 356drop table t1; 357SET NAMES latin1; 358CREATE TABLE t1 (t text character set utf8 not null, fulltext(t)); 359INSERT t1 VALUES ('Mit freundlichem Gr��'), ('aus Osnabr�ck'); 360SET NAMES koi8r; 361INSERT t1 VALUES ("��� �� - ������"),("������, �����!"), 362("�� ������, �����!"),("� ����� ����!"); 363SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('������'); 364t collation(t) 365��� �� - ������ utf8_general_ci 366SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('���*' IN BOOLEAN MODE); 367t collation(t) 368� ����� ����! utf8_general_ci 369SELECT * FROM t1 WHERE MATCH t AGAINST ('���' IN BOOLEAN MODE); 370t 371SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabr�ck'); 372t collation(t) 373SET NAMES latin1; 374SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabr�ck'); 375t collation(t) 376aus Osnabr�ck utf8_general_ci 377SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrueck'); 378t collation(t) 379SELECT t, collation(t),FORMAT(MATCH t AGAINST ('Osnabruck'),6) FROM t1 WHERE MATCH t AGAINST ('Osnabruck'); 380t collation(t) FORMAT(MATCH t AGAINST ('Osnabruck'),6) 381aus Osnabr�ck utf8_general_ci 1.591140 382alter table t1 modify t varchar(200) collate latin1_german2_ci not null; 383Warnings: 384Warning 1366 Incorrect string value: '\xD0\xAD\xD1\x82\xD0\xBE...' for column 't' at row 3 385Warning 1366 Incorrect string value: '\xD0\x9E\xD1\x82\xD0\xBB...' for column 't' at row 4 386Warning 1366 Incorrect string value: '\xD0\x9D\xD0\xB5 \xD0...' for column 't' at row 5 387Warning 1366 Incorrect string value: '\xD0\xB8 \xD0\xB1\xD1...' for column 't' at row 6 388SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabr�ck'); 389t collation(t) 390aus Osnabr�ck latin1_german2_ci 391SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrueck'); 392t collation(t) 393aus Osnabr�ck latin1_german2_ci 394DROP TABLE t1; 395CREATE TABLE t1 (s varchar(255), FULLTEXT (s)) DEFAULT CHARSET=utf8; 396insert into t1 (s) values ('p�ra para para'),('para para para'); 397select * from t1 where match(s) against('para' in boolean mode); 398s 399p�ra para para 400para para para 401select * from t1 where match(s) against('par*' in boolean mode); 402s 403p�ra para para 404para para para 405DROP TABLE t1; 406CREATE TABLE t1 (h text, FULLTEXT (h)); 407INSERT INTO t1 VALUES ('Jesses Hasse Ling and his syncopators of Swing'); 408REPAIR TABLE t1; 409Table Op Msg_type Msg_text 410test.t1 repair status OK 411select count(*) from t1; 412count(*) 4131 414drop table t1; 415CREATE TABLE t1 ( a TEXT, FULLTEXT (a) ); 416INSERT INTO t1 VALUES ('testing ft_nlq_find_relevance'); 417SELECT MATCH(a) AGAINST ('nosuchword') FROM t1; 418MATCH(a) AGAINST ('nosuchword') 4190 420DROP TABLE t1; 421create table t1 (a int primary key, b text, fulltext(b)); 422create table t2 (a int, b text); 423insert t1 values (1, "aaaa"), (2, "bbbb"); 424insert t2 values (10, "aaaa"), (2, "cccc"); 425replace t1 select * from t2; 426drop table t1, t2; 427CREATE TABLE t1 (t VARCHAR(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci, FULLTEXT (t)); 428SET NAMES latin1; 429INSERT INTO t1 VALUES('Mit freundlichem Gr�� aus Osnabr�ck'); 430SELECT COUNT(*) FROM t1 WHERE MATCH(t) AGAINST ('"osnabr�ck"' IN BOOLEAN MODE); 431COUNT(*) 4321 433DROP TABLE t1; 434CREATE TABLE t1 (a VARCHAR(30), FULLTEXT(a)); 435INSERT INTO t1 VALUES('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'); 436SET myisam_repair_threads=2; 437REPAIR TABLE t1; 438Table Op Msg_type Msg_text 439test.t1 repair status OK 440SET myisam_repair_threads=@@global.myisam_repair_threads; 441INSERT INTO t1 VALUES('testword\'\''); 442SELECT a FROM t1 WHERE MATCH a AGAINST('testword' IN BOOLEAN MODE); 443a 444testword'' 445SELECT a FROM t1 WHERE MATCH a AGAINST('testword\'\'' IN BOOLEAN MODE); 446a 447testword'' 448INSERT INTO t1 VALUES('test\'s'); 449SELECT a FROM t1 WHERE MATCH a AGAINST('test' IN BOOLEAN MODE); 450a 451test's 452DROP TABLE t1; 453CREATE TABLE t1 (a VARCHAR(10000), FULLTEXT(a)); 454SHOW CREATE TABLE t1; 455Table Create Table 456t1 CREATE TABLE `t1` ( 457 `a` varchar(10000) DEFAULT NULL, 458 FULLTEXT KEY `a` (`a`) 459) ENGINE=MyISAM DEFAULT CHARSET=latin1 460DROP TABLE t1; 461CREATE TABLE t1 (a TEXT, FULLTEXT KEY(a)); 462INSERT INTO t1 VALUES('test'),('test1'),('test'); 463PREPARE stmt from "SELECT a, FORMAT(MATCH(a) AGAINST('test1 test'),6) FROM t1 WHERE MATCH(a) AGAINST('test1 test')"; 464EXECUTE stmt; 465a FORMAT(MATCH(a) AGAINST('test1 test'),6) 466test1 0.685267 467EXECUTE stmt; 468a FORMAT(MATCH(a) AGAINST('test1 test'),6) 469test1 0.685267 470DEALLOCATE PREPARE stmt; 471DROP TABLE t1; 472CREATE TABLE t1 (a VARCHAR(255), FULLTEXT(a)); 473SELECT * FROM t1 IGNORE INDEX(a) WHERE MATCH(a) AGAINST('test'); 474a 475ALTER TABLE t1 DISABLE KEYS; 476SELECT * FROM t1 WHERE MATCH(a) AGAINST('test'); 477ERROR HY000: Can't find FULLTEXT index matching the column list 478DROP TABLE t1; 479CREATE TABLE t1(a TEXT); 480INSERT INTO t1 VALUES(' aaaaa aaaa'); 481SELECT * FROM t1 WHERE MATCH(a) AGAINST ('"aaaa"' IN BOOLEAN MODE); 482a 483 aaaaa aaaa 484DROP TABLE t1; 485CREATE TABLE t1(a VARCHAR(20), FULLTEXT(a)); 486INSERT INTO t1 VALUES('Offside'),('City Of God'); 487SELECT a FROM t1 WHERE MATCH a AGAINST ('+city of*' IN BOOLEAN MODE); 488a 489City Of God 490SELECT a FROM t1 WHERE MATCH a AGAINST ('+city (of*)' IN BOOLEAN MODE); 491a 492City Of God 493SELECT a FROM t1 WHERE MATCH a AGAINST ('+city* of*' IN BOOLEAN MODE); 494a 495City Of God 496DROP TABLE t1; 497create table t1(a text,b date,fulltext index(a))engine=myisam; 498insert into t1 set a='water',b='2008-08-04'; 499select 1 from t1 where match(a) against ('water' in boolean mode) and b>='2008-08-01'; 5001 5011 502drop table t1; 503show warnings; 504Level Code Message 505CREATE TABLE t1 (a VARCHAR(255), b INT, FULLTEXT(a), KEY(b)); 506INSERT INTO t1 VALUES('test', 1),('test', 1),('test', 1),('test', 1), 507('test', 1),('test', 2),('test', 3),('test', 4); 508EXPLAIN SELECT * FROM t1 509WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; 510id select_type table type possible_keys key key_len ref rows Extra 5111 SIMPLE t1 fulltext b,a a 0 NULL 1 Using where 512EXPLAIN SELECT * FROM t1 USE INDEX(a) 513WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; 514id select_type table type possible_keys key key_len ref rows Extra 5151 SIMPLE t1 fulltext a a 0 NULL 1 Using where 516EXPLAIN SELECT * FROM t1 FORCE INDEX(a) 517WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; 518id select_type table type possible_keys key key_len ref rows Extra 5191 SIMPLE t1 fulltext a a 0 NULL 1 Using where 520EXPLAIN SELECT * FROM t1 IGNORE INDEX(a) 521WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; 522id select_type table type possible_keys key key_len ref rows Extra 5231 SIMPLE t1 ref b b 5 const 4 Using where 524EXPLAIN SELECT * FROM t1 USE INDEX(b) 525WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; 526id select_type table type possible_keys key key_len ref rows Extra 5271 SIMPLE t1 ref b b 5 const 4 Using where 528EXPLAIN SELECT * FROM t1 FORCE INDEX(b) 529WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; 530id select_type table type possible_keys key key_len ref rows Extra 5311 SIMPLE t1 ref b b 5 const 4 Using where 532DROP TABLE t1; 533CREATE TABLE t1(a CHAR(10)); 534INSERT INTO t1 VALUES('aaa15'); 535SELECT MATCH(a) AGAINST('aaa1* aaa14 aaa16' IN BOOLEAN MODE) FROM t1; 536MATCH(a) AGAINST('aaa1* aaa14 aaa16' IN BOOLEAN MODE) 5371 538SELECT MATCH(a) AGAINST('aaa1* aaa14 aaa15 aaa16' IN BOOLEAN MODE) FROM t1; 539MATCH(a) AGAINST('aaa1* aaa14 aaa15 aaa16' IN BOOLEAN MODE) 5402 541DROP TABLE t1; 542CREATE TABLE t1(a TEXT); 543SELECT GROUP_CONCAT(a) AS st FROM t1 HAVING MATCH(st) AGAINST('test' IN BOOLEAN MODE); 544ERROR HY000: Incorrect arguments to MATCH 545DROP TABLE t1; 546CREATE TABLE t1(a VARCHAR(64), FULLTEXT(a)); 547INSERT INTO t1 VALUES('awrd bwrd cwrd'),('awrd bwrd cwrd'),('awrd bwrd cwrd'); 548SELECT * FROM t1 WHERE MATCH(a) AGAINST('+awrd bwrd* +cwrd*' IN BOOLEAN MODE); 549a 550awrd bwrd cwrd 551awrd bwrd cwrd 552awrd bwrd cwrd 553DROP TABLE t1; 554CREATE TABLE t1 (col text, FULLTEXT KEY full_text (col)); 555PREPARE s FROM 556"SELECT MATCH (col) AGAINST('findme') FROM t1 ORDER BY MATCH (col) AGAINST('findme')" 557 ; 558EXECUTE s; 559MATCH (col) AGAINST('findme') 560DEALLOCATE PREPARE s; 561DROP TABLE t1; 562# 563# Bug #49250 : spatial btree index corruption and crash 564# Part two : fulltext syntax check 565# 566CREATE TABLE t1(col1 TEXT, 567FULLTEXT INDEX USING BTREE (col1)); 568ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING BTREE (col1))' at line 2 569CREATE TABLE t2(col1 TEXT); 570CREATE FULLTEXT INDEX USING BTREE ON t2(col); 571ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING BTREE ON t2(col)' at line 1 572ALTER TABLE t2 ADD FULLTEXT INDEX USING BTREE (col1); 573ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING BTREE (col1)' at line 1 574DROP TABLE t2; 575End of 5.0 tests 576# 577# Bug #47930: MATCH IN BOOLEAN MODE returns too many results 578# inside subquery 579# 580CREATE TABLE t1 (a int); 581INSERT INTO t1 VALUES (1), (2); 582CREATE TABLE t2 (a int, b2 char(10), FULLTEXT KEY b2 (b2)); 583INSERT INTO t2 VALUES (1,'Scargill'); 584CREATE TABLE t3 (a int, b int); 585INSERT INTO t3 VALUES (1,1), (2,1); 586# t2 should use full text index 587EXPLAIN 588SELECT count(*) FROM t1 WHERE 589not exists( 590SELECT 1 FROM t2, t3 591WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE) 592); 593id select_type table type possible_keys key key_len ref rows Extra 5941 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where 5952 DEPENDENT SUBQUERY t2 fulltext b2 b2 0 NULL 1 Using where 5962 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 Using where 597# should return 0 598SELECT count(*) FROM t1 WHERE 599not exists( 600SELECT 1 FROM t2, t3 601WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE) 602); 603count(*) 6040 605# should return 0 606SELECT count(*) FROM t1 WHERE 607not exists( 608SELECT 1 FROM t2 IGNORE INDEX (b2), t3 609WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE) 610); 611count(*) 6120 613DROP TABLE t1,t2,t3; 614CREATE TABLE t1 (a VARCHAR(4), FULLTEXT(a)); 615INSERT INTO t1 VALUES 616('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), 617('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), 618('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), 619('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), 620('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), 621('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), 622('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), 623('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), 624('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), 625('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), 626('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), 627('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), 628('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('awrd'),('cwrd'), 629('awrd'); 630SELECT COUNT(*) FROM t1 WHERE MATCH(a) AGAINST("+awrd bwrd* +cwrd*" IN BOOLEAN MODE); 631COUNT(*) 6320 633DROP TABLE t1; 634# 635# Bug #49445: Assertion failed: 0, file .\item_row.cc, line 55 with 636# fulltext search and row op 637# 638CREATE TABLE t1(a CHAR(1),FULLTEXT(a)); 639SELECT 1 FROM t1 WHERE MATCH(a) AGAINST ('') AND ROW(a,a) > ROW(1,1); 6401 641DROP TABLE t1; 642# 643# BUG#51866 - crash with repair by sort and fulltext keys 644# 645CREATE TABLE t1(a CHAR(4), FULLTEXT(a)); 646INSERT INTO t1 VALUES('aaaa'); 647SET myisam_sort_buffer_size=4; 648Warnings: 649Warning 1292 Truncated incorrect myisam_sort_buffer_size value: '4' 650REPAIR TABLE t1; 651Table Op Msg_type Msg_text 652test.t1 repair status OK 653SET myisam_sort_buffer_size=@@global.myisam_sort_buffer_size; 654DROP TABLE t1; 655# 656# Bug#54484 explain + prepared statement: crash and Got error -1 from storage engine 657# 658CREATE TABLE t1(f1 VARCHAR(6) NOT NULL, FULLTEXT KEY(f1), UNIQUE(f1)); 659INSERT INTO t1 VALUES ('test'); 660SELECT 1 FROM t1 WHERE 1 > 661ALL((SELECT 1 FROM t1 JOIN t1 a 662ON (MATCH(t1.f1) against ("")) 663WHERE t1.f1 GROUP BY t1.f1)) xor f1; 6641 6651 666PREPARE stmt FROM 667'SELECT 1 FROM t1 WHERE 1 > 668 ALL((SELECT 1 FROM t1 RIGHT OUTER JOIN t1 a 669 ON (MATCH(t1.f1) against ("")) 670 WHERE t1.f1 GROUP BY t1.f1)) xor f1'; 671EXECUTE stmt; 6721 6731 674EXECUTE stmt; 6751 6761 677DEALLOCATE PREPARE stmt; 678PREPARE stmt FROM 679'SELECT 1 FROM t1 WHERE 1 > 680 ALL((SELECT 1 FROM t1 JOIN t1 a 681 ON (MATCH(t1.f1) against ("")) 682 WHERE t1.f1 GROUP BY t1.f1))'; 683EXECUTE stmt; 6841 6851 686EXECUTE stmt; 6871 6881 689DEALLOCATE PREPARE stmt; 690DROP TABLE t1; 691End of 5.1 tests 692# Bug#21140111: Explain ... match against: Assertion failed: ret ... 693CREATE TABLE z(a INTEGER) engine=innodb; 694CREATE TABLE q(b TEXT CHARSET latin1, fulltext(b)) engine=innodb; 695explain format=json SELECT 1 FROM q WHERE (SELECT MATCH(b) AGAINST ('*') FROM z); 696ERROR HY000: Incorrect arguments to MATCH 697DROP TABLE z, q; 698# 699# BUG#21625016: INNODB FULL TEXT CASE SENSITIVE NOT WORKING 700# 701CREATE TABLE t1(fld1 VARCHAR(10) COLLATE 'latin1_bin', FULLTEXT INDEX (fld1)) 702ENGINE=InnoDB; 703INSERT INTO t1 VALUES ('abCD'),('ABCD'); 704# With the patch, case sensitive comparison is performed since 705# binary collation is used. 706SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('abCD' IN BOOLEAN MODE); 707fld1 708abCD 709SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('ABCD' IN BOOLEAN MODE); 710fld1 711ABCD 712SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('abCD' IN NATURAL LANGUAGE MODE); 713fld1 714abCD 715SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('ABCD' IN NATURAL LANGUAGE MODE); 716fld1 717ABCD 718# Since binary collation is not used, case insensitive comparison is 719# performed. 720ALTER TABLE t1 MODIFY fld1 VARCHAR(10) COLLATE 'latin1_general_cs'; 721SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('abCD' IN BOOLEAN MODE); 722fld1 723abCD 724ABCD 725SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('ABCD' IN BOOLEAN MODE); 726fld1 727abCD 728ABCD 729SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('abCD' IN NATURAL LANGUAGE MODE); 730fld1 731abCD 732ABCD 733SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('ABCD' IN NATURAL LANGUAGE MODE); 734fld1 735abCD 736ABCD 737# With the patch, case sensitive comparison is performed. 738ALTER TABLE t1 MODIFY fld1 VARCHAR(10) COLLATE 'utf8mb4_bin'; 739SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('abCD' IN BOOLEAN MODE); 740fld1 741abCD 742SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('ABCD' IN BOOLEAN MODE); 743fld1 744ABCD 745SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('abCD' IN NATURAL LANGUAGE MODE); 746fld1 747abCD 748SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('ABCD' IN NATURAL LANGUAGE MODE); 749fld1 750ABCD 751# Test(using case insensitive collation) added for coverage 752ALTER TABLE t1 MODIFY fld1 VARCHAR(10) COLLATE 'utf8mb4_general_ci'; 753SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('abCD' IN BOOLEAN MODE); 754fld1 755abCD 756ABCD 757SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('ABCD' IN BOOLEAN MODE); 758fld1 759abCD 760ABCD 761SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('abCD' IN NATURAL LANGUAGE MODE); 762fld1 763abCD 764ABCD 765SELECT * FROM t1 WHERE MATCH(fld1) AGAINST ('ABCD' IN NATURAL LANGUAGE MODE); 766fld1 767abCD 768ABCD 769DROP TABLE t1; 770