1CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b)) ENGINE = InnoDB; 2INSERT INTO t1 VALUES('MySQL has now support', 'for full-text search'), 3('Full-text indexes', 'are called collections'), 4('Only MyISAM tables','support collections'), 5('Function MATCH ... AGAINST()','is used to do a search'), 6('Full-text search in MySQL', 'implements vector space model'); 7ANALYZE TABLE t1; 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 14Full-text indexes are called collections 15Only MyISAM tables support 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 1 100.00 Using where 19Warnings: 20Note 1003 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 30Only MyISAM tables support collections 31select * from t1 where MATCH(a,b) AGAINST ("collections" WITH QUERY EXPANSION); 32a b 33Only MyISAM tables support collections 34Full-text indexes are called collections 35MySQL has now support for full-text search 36Full-text search in MySQL implements vector space model 37select * from t1 where MATCH(a,b) AGAINST ("indexes" WITH QUERY EXPANSION); 38a b 39Full-text indexes are called collections 40Only MyISAM tables support collections 41MySQL has now support for full-text search 42Full-text search in MySQL implements vector space model 43select * from t1 where MATCH(a,b) AGAINST ("indexes collections" WITH QUERY EXPANSION); 44a b 45Only MyISAM tables support collections 46Full-text indexes are called collections 47MySQL has now support for full-text search 48Full-text search in MySQL implements vector space model 49select * from t1 where MATCH(a,b) AGAINST ("indexes" IN NATURAL LANGUAGE MODE); 50a b 51Full-text indexes are called collections 52select * from t1 where MATCH(a,b) AGAINST ("indexes" IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION); 53a b 54Full-text indexes are called collections 55Only MyISAM tables support collections 56MySQL has now support for full-text search 57Full-text search in MySQL implements vector space model 58select * from t1 where MATCH(a,b) AGAINST ("indexes" IN BOOLEAN MODE WITH QUERY EXPANSION); 59ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WITH QUERY EXPANSION)' at line 1 60explain select * from t1 where MATCH(a,b) AGAINST ("collections"); 61id select_type table type possible_keys key key_len ref rows Extra 621 SIMPLE t1 fulltext a a 0 1 Using where 63explain select * from t1 where MATCH(a,b) AGAINST ("collections")>0; 64id select_type table type possible_keys key key_len ref rows Extra 651 SIMPLE t1 fulltext a a 0 1 Using where 66explain select * from t1 where MATCH(a,b) AGAINST ("collections")>1; 67id select_type table type possible_keys key key_len ref rows Extra 681 SIMPLE t1 fulltext a a 0 1 Using where 69explain select * from t1 where MATCH(a,b) AGAINST ("collections")>=0; 70id select_type table type possible_keys key key_len ref rows Extra 711 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where 72explain select * from t1 where MATCH(a,b) AGAINST ("collections")>=1; 73id select_type table type possible_keys key key_len ref rows Extra 741 SIMPLE t1 fulltext a a 0 1 Using where 75explain select * from t1 where 0<MATCH(a,b) AGAINST ("collections"); 76id select_type table type possible_keys key key_len ref rows Extra 771 SIMPLE t1 fulltext a a 0 1 Using where 78explain select * from t1 where 1<MATCH(a,b) AGAINST ("collections"); 79id select_type table type possible_keys key key_len ref rows Extra 801 SIMPLE t1 fulltext a a 0 1 Using where 81explain select * from t1 where 0<=MATCH(a,b) AGAINST ("collections"); 82id select_type table type possible_keys key key_len ref rows Extra 831 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where 84explain select * from t1 where 1<=MATCH(a,b) AGAINST ("collections"); 85id select_type table type possible_keys key key_len ref rows Extra 861 SIMPLE t1 fulltext a a 0 1 Using where 87explain select * from t1 where MATCH(a,b) AGAINST ("collections")>0 and a like '%ll%'; 88id select_type table type possible_keys key key_len ref rows Extra 891 SIMPLE t1 fulltext a a 0 1 Using where 90select * from t1 where MATCH(a,b) AGAINST("support -collections" IN BOOLEAN MODE); 91a b 92MySQL has now support for full-text search 93explain extended select * from t1 where MATCH(a,b) AGAINST("support -collections" IN BOOLEAN MODE); 94id select_type table type possible_keys key key_len ref rows filtered Extra 951 SIMPLE t1 fulltext a a 0 1 100.00 Using where 96Warnings: 97Note 1003 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)) 98select * from t1 where MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE); 99a b 100Only MyISAM tables support collections 101MySQL has now support for full-text search 102Full-text indexes are called collections 103select * from t1 where MATCH(a,b) AGAINST("support +collections" IN BOOLEAN MODE); 104a b 105Only MyISAM tables support collections 106Full-text indexes are called collections 107select * from t1 where MATCH(a,b) AGAINST("sear*" IN BOOLEAN MODE); 108a b 109MySQL has now support for full-text search 110Function MATCH ... AGAINST() is used to do a search 111Full-text search in MySQL implements vector space model 112select * from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE); 113a b 114Only MyISAM tables support collections 115select * from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE); 116a b 117MySQL has now support for full-text search 118Function MATCH ... AGAINST() is used to do a search 119Full-text search in MySQL implements vector space model 120select * from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE); 121a b 122Full-text search in MySQL implements vector space model 123MySQL has now support for full-text search 124select * from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE); 125a b 126Function MATCH ... AGAINST() is used to do a search 127select *, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1; 128a b x 129MySQL has now support for full-text search 0.15835624933242798 130Full-text indexes are called collections 0.15835624933242798 131Only MyISAM tables support collections 0.31671249866485596 132Function MATCH ... AGAINST() is used to do a search 0 133Full-text search in MySQL implements vector space model 0 134select *, MATCH(a,b) AGAINST("collections support" IN BOOLEAN MODE) as x from t1; 135a b x 136MySQL has now support for full-text search 0.15835624933242798 137Full-text indexes are called collections 0.15835624933242798 138Only MyISAM tables support collections 0.31671249866485596 139Function MATCH ... AGAINST() is used to do a search 0 140Full-text search in MySQL implements vector space model 0 141select * from t1 where MATCH a,b AGAINST ("+call* +coll*" IN BOOLEAN MODE); 142a b 143Full-text indexes are called collections 144select * from t1 where MATCH a,b AGAINST ('"support now"' IN BOOLEAN MODE); 145a b 146select * from t1 where MATCH a,b AGAINST ('"Now sUPPort"' IN BOOLEAN MODE); 147a b 148MySQL has now support for full-text search 149select * from t1 where MATCH a,b AGAINST ('"now support"' IN BOOLEAN MODE); 150a b 151MySQL has now support for full-text search 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 155Full-text search in MySQL implements vector space model 156select * from t1 where MATCH a,b AGAINST ('"text search" -"now support"' IN BOOLEAN MODE); 157a b 158Full-text search in MySQL implements vector space model 159select * from t1 where MATCH a,b AGAINST ('"text search" +"now support"' IN BOOLEAN MODE); 160a b 161MySQL has now support for full-text search 162select * from t1 where MATCH a,b AGAINST ('"text i"' IN BOOLEAN MODE); 163a b 164select * from t1 where MATCH a,b AGAINST ('"xt indexes"' IN BOOLEAN MODE); 165a b 166Full-text indexes are called collections 167select * from t1 where MATCH a,b AGAINST ('+(support collections) +foobar*' IN BOOLEAN MODE); 168a b 169select * from t1 where MATCH a,b AGAINST ('+(+(support collections)) +foobar*' IN BOOLEAN MODE); 170a b 171select * from t1 where MATCH a,b AGAINST ('+collections -supp* -foobar*' IN BOOLEAN MODE); 172a b 173Full-text indexes are called collections 174select * from t1 where MATCH a,b AGAINST('"space model' IN BOOLEAN MODE); 175a b 176Full-text search in MySQL implements vector space model 177delete from t1 where a like "MySQL%"; 178update t1 set a='some test foobar' where MATCH a,b AGAINST ('model'); 179delete from t1 where MATCH(a,b) AGAINST ("indexes"); 180select * from t1; 181a b 182Only MyISAM tables support collections 183Function MATCH ... AGAINST() is used to do a search 184some test foobar implements vector space model 185drop table t1; 186create table t1 (a varchar(200) not null, fulltext (a)) engine = innodb; 187insert t1 values ("aaa10 bbb20"), ("aaa20 bbb15"), ("aaa30 bbb10"); 188select * from t1 where match a against ("+aaa* +bbb*" in boolean mode); 189a 190aaa10 bbb20 191aaa20 bbb15 192aaa30 bbb10 193select * from t1 where match a against ("+aaa* +bbb1*" in boolean mode); 194a 195aaa20 bbb15 196aaa30 bbb10 197select * from t1 where match a against ("+aaa* +ccc*" in boolean mode); 198a 199select * from t1 where match a against ("+aaa10 +(bbb*)" in boolean mode); 200a 201aaa10 bbb20 202select * from t1 where match a against ("+(+aaa* +bbb1*)" in boolean mode); 203a 204aaa20 bbb15 205aaa30 bbb10 206select * from t1 where match a against ("(+aaa* +bbb1*)" in boolean mode); 207a 208aaa20 bbb15 209aaa30 bbb10 210drop table t1; 211CREATE TABLE t1 ( 212id int(11), 213ticket int(11), 214KEY ti (id), 215KEY tit (ticket) 216) ENGINE = InnoDB; 217INSERT INTO t1 VALUES (2,3),(1,2); 218CREATE TABLE t2 ( 219ticket int(11), 220inhalt text, 221KEY tig (ticket), 222fulltext index tix (inhalt) 223) ENGINE = InnoDB; 224INSERT INTO t2 VALUES (1,'foo'),(2,'bar'),(3,'foobar'); 225INSERT INTO t1 VALUES (3,3); 226ANALYZE TABLE t1; 227ANALYZE TABLE t2; 228select ticket2.id FROM t2 as ttxt,t2 229INNER JOIN t1 as ticket2 ON ticket2.id = t2.ticket 230WHERE ticket2.id = ticket2.ticket and 231match(ttxt.inhalt) against ('foobar'); 232id 2333 234show keys from t2; 235Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 236t2 1 tig 1 ticket A 3 NULL NULL YES BTREE 237t2 1 tix 1 inhalt NULL NULL NULL NULL YES FULLTEXT 238show create table t2; 239Table Create Table 240t2 CREATE TABLE `t2` ( 241 `ticket` int(11) DEFAULT NULL, 242 `inhalt` text DEFAULT NULL, 243 KEY `tig` (`ticket`), 244 FULLTEXT KEY `tix` (`inhalt`) 245) ENGINE=InnoDB DEFAULT CHARSET=latin1 246select * from t2 where MATCH inhalt AGAINST (NULL); 247ticket inhalt 248select * from t2 where MATCH inhalt AGAINST ('foobar'); 249ticket inhalt 2503 foobar 251select * from t2 having MATCH inhalt AGAINST ('foobar'); 252ticket inhalt 2533 foobar 254CREATE TABLE t3 (t int(11),i text,fulltext tix (t,i)); 255ERROR HY000: Column 't' cannot be part of FULLTEXT index 256CREATE TABLE t3 (t int(11),i text, 257j varchar(200) CHARACTER SET latin2, 258fulltext tix (i,j)); 259ERROR HY000: Column 'j' cannot be part of FULLTEXT index 260CREATE TABLE t3 ( 261ticket int(11), 262inhalt text, 263KEY tig (ticket), 264fulltext index tix (inhalt) 265) ENGINE = InnoDB; 266select * from t2 where MATCH inhalt AGAINST (t2.inhalt); 267ERROR HY000: Incorrect arguments to AGAINST 268select * from t2 where MATCH ticket AGAINST ('foobar'); 269ERROR HY000: Can't find FULLTEXT index matching the column list 270select * from t2,t3 where MATCH (t2.inhalt,t3.inhalt) AGAINST ('foobar'); 271ERROR HY000: Incorrect arguments to MATCH 272drop table t1,t2,t3; 273CREATE TABLE t1 ( 274id int(11) auto_increment, 275title varchar(100) default '', 276PRIMARY KEY (id), 277KEY ind5 (title) 278) ENGINE = InnoDB; 279CREATE FULLTEXT INDEX ft1 ON t1(title); 280insert into t1 (title) values ('this is a test'); 281select * from t1 where match title against ('test' in boolean mode); 282id title 2831 this is a test 284update t1 set title='this is A test' where id=1; 285check table t1; 286Table Op Msg_type Msg_text 287test.t1 check status OK 288update t1 set title='this test once revealed a bug' where id=1; 289select * from t1; 290id title 2911 this test once revealed a bug 292update t1 set title=NULL where id=1; 293drop table t1; 294CREATE TABLE t1 (a int(11), b text, FULLTEXT KEY (b)) ENGINE = InnoDB; 295insert into t1 values (1,"I wonder why the fulltext index doesnt work?"); 296SELECT * from t1 where MATCH (b) AGAINST ('apples'); 297a b 298insert into t1 values (2,"fullaaa fullzzz"); 299select * from t1 where match b against ('full*' in boolean mode); 300a b 3011 I wonder why the fulltext index doesnt work? 3022 fullaaa fullzzz 303drop table t1; 304CREATE TABLE t1 ( id int(11) NOT NULL auto_increment primary key, mytext text NOT NULL, FULLTEXT KEY mytext (mytext)) ENGINE = InnoDB; 305INSERT INTO t1 VALUES (1,'my small mouse'),(2,'la-la-la'),(3,'It is so funny'),(4,'MySQL Tutorial'); 306select 8 from t1; 3078 3088 3098 3108 3118 312drop table t1; 313create table t1 (a text, fulltext key (a)) ENGINE = InnoDB; 314insert into t1 values ('aaaa'); 315select * from t1 where match (a) against ('aaaa'); 316a 317aaaa 318drop table t1; 319create table t1 ( ref_mag text not null, fulltext (ref_mag)) ENGINE = InnoDB; 320insert into t1 values ('test'); 321select ref_mag from t1 where match ref_mag against ('+test' in boolean mode); 322ref_mag 323test 324alter table t1 change ref_mag ref_mag char (255) not null; 325select ref_mag from t1 where match ref_mag against ('+test' in boolean mode); 326ref_mag 327test 328drop table t1; 329create table t1 (t1_id int(11) primary key, name varchar(32)) ENGINE = InnoDB; 330insert into t1 values (1, 'data1'); 331insert into t1 values (2, 'data2'); 332create table t2 (t2_id int(11) primary key, t1_id int(11), name varchar(32)) ENGINE = InnoDB; 333insert into t2 values (1, 1, 'xxfoo'); 334insert into t2 values (2, 1, 'xxbar'); 335insert into t2 values (3, 1, 'xxbuz'); 336select * from t1 join t2 using(`t1_id`) where match (t1.name, t2.name) against('xxfoo' in boolean mode); 337ERROR HY000: Incorrect arguments to MATCH 338select * from t2 where match name against ('*a*b*c*d*e*f*' in boolean mode); 339ERROR HY000: Can't find FULLTEXT index matching the column list 340drop table t1,t2; 341create table t1 (a text, fulltext key (a)) ENGINE = InnoDB; 342insert into t1 select "xxxx yyyy zzzz"; 343drop table t1; 344SET NAMES latin1; 345CREATE TABLE t1 (t text character set utf8 not null, fulltext(t)) ENGINE = InnoDB; 346INSERT t1 VALUES ('Mit freundlichem Gr��'), ('aus Osnabr�ck'); 347SET NAMES koi8r; 348INSERT t1 VALUES ("��� �� - ������"),("������, �����!"), 349("�� ������, �����!"),("� ����� ����!"); 350SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('������'); 351t collation(t) 352��� �� - ������ utf8_general_ci 353DROP TABLE t1; 354CREATE TABLE t1 (s varchar(255), FULLTEXT (s)) ENGINE = InnoDB DEFAULT CHARSET=utf8; 355insert into t1 (s) values ('p�ra para para'),('para para para'); 356select * from t1 where match(s) against('para' in boolean mode); 357s 358para para para 359p�ra para para 360select * from t1 where match(s) against('par*' in boolean mode); 361s 362para para para 363p�ra para para 364DROP TABLE t1; 365CREATE TABLE t1 (h text, FULLTEXT (h)) ENGINE = InnoDB; 366INSERT INTO t1 VALUES ('Jesses Hasse Ling and his syncopators of Swing'); 367select count(*) from t1; 368count(*) 3691 370drop table t1; 371CREATE TABLE t1 ( a TEXT, FULLTEXT (a) ) ENGINE = InnoDB; 372INSERT INTO t1 VALUES ('testing ft_nlq_find_relevance'); 373SELECT MATCH(a) AGAINST ('nosuchword') FROM t1; 374MATCH(a) AGAINST ('nosuchword') 3750 376DROP TABLE t1; 377create table t1 (a int primary key, b text, fulltext(b)) ENGINE = InnoDB; 378create table t2 (a int, b text) ENGINE = InnoDB; 379insert t1 values (1, "aaaa"), (2, "bbbb"); 380insert t2 values (10, "aaaa"), (2, "cccc"); 381replace t1 select * from t2; 382drop table t1, t2; 383CREATE TABLE t1 (t VARCHAR(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci, FULLTEXT (t)) ENGINE = InnoDB; 384SET NAMES latin1; 385INSERT INTO t1 VALUES('Mit freundlichem Gr�� aus Osnabr�ck'); 386SELECT COUNT(*) FROM t1 WHERE MATCH(t) AGAINST ('"osnabr�ck"' IN BOOLEAN MODE); 387COUNT(*) 3881 389DROP TABLE t1; 390CREATE TABLE t1 (a VARCHAR(30), FULLTEXT(a)) ENGINE = InnoDB; 391INSERT INTO t1 VALUES('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'); 392INSERT INTO t1 VALUES('testword\'\''); 393SELECT a FROM t1 WHERE MATCH a AGAINST('testword' IN BOOLEAN MODE); 394a 395testword'' 396SELECT a FROM t1 WHERE MATCH a AGAINST('testword\'\'' IN BOOLEAN MODE); 397a 398testword'' 399INSERT INTO t1 VALUES('test\'s'); 400SELECT a FROM t1 WHERE MATCH a AGAINST('test' IN BOOLEAN MODE); 401a 402test's 403DROP TABLE t1; 404CREATE TABLE t1 (a VARCHAR(10000), FULLTEXT(a)) ENGINE = InnoDB; 405SHOW CREATE TABLE t1; 406Table Create Table 407t1 CREATE TABLE `t1` ( 408 `a` varchar(10000) DEFAULT NULL, 409 FULLTEXT KEY `a` (`a`) 410) ENGINE=InnoDB DEFAULT CHARSET=latin1 411DROP TABLE t1; 412CREATE TABLE t1 (a TEXT, FULLTEXT KEY(a)) ENGINE = InnoDB; 413INSERT INTO t1 VALUES('test'),('test1'),('test'); 414ANALYZE TABLE t1; 415PREPARE stmt from "SELECT a, FORMAT(MATCH(a) AGAINST('test1 test'),6) FROM t1 WHERE MATCH(a) AGAINST('test1 test')"; 416EXECUTE stmt; 417a FORMAT(MATCH(a) AGAINST('test1 test'),6) 418test1 0.227645 419test 0.031008 420test 0.031008 421EXECUTE stmt; 422a FORMAT(MATCH(a) AGAINST('test1 test'),6) 423test1 0.227645 424test 0.031008 425test 0.031008 426DEALLOCATE PREPARE stmt; 427DROP TABLE t1; 428CREATE TABLE t1 (a VARCHAR(255), FULLTEXT(a)) ENGINE = InnoDB; 429SELECT * FROM t1 IGNORE INDEX(a) WHERE MATCH(a) AGAINST('test'); 430a 431SELECT * FROM t1 WHERE MATCH(a) AGAINST('test'); 432a 433DROP TABLE t1; 434CREATE TABLE t1(a TEXT, fulltext(a)) ENGINE = InnoDB; 435INSERT INTO t1 VALUES(' aaaaa aaaa'); 436SELECT * FROM t1 WHERE MATCH(a) AGAINST ('"aaaa"' IN BOOLEAN MODE); 437a 438 aaaaa aaaa 439DROP TABLE t1; 440CREATE TABLE t1(a VARCHAR(20), FULLTEXT(a)) ENGINE = InnoDB; 441INSERT INTO t1 VALUES('Offside'),('City Of God'); 442SELECT a FROM t1 WHERE MATCH a AGAINST ('+city of*' IN BOOLEAN MODE); 443a 444City Of God 445SELECT a FROM t1 WHERE MATCH a AGAINST ('+city (of*)' IN BOOLEAN MODE); 446a 447Offside 448City Of God 449SELECT a FROM t1 WHERE MATCH a AGAINST ('+city* of*' IN BOOLEAN MODE); 450a 451City Of God 452DROP TABLE t1; 453create table t1(a text,b date,fulltext index(a)) ENGINE = InnoDB; 454insert into t1 set a='water',b='2008-08-04'; 455select 1 from t1 where match(a) against ('water' in boolean mode) and b>='2008-08-01'; 4561 4571 458drop table t1; 459show warnings; 460Level Code Message 461CREATE TABLE t1 (a VARCHAR(255), b INT, FULLTEXT(a), KEY(b)) ENGINE = InnoDB; 462INSERT INTO t1 VALUES('test', 1),('test', 1),('test', 1),('test', 1), 463('test', 1),('test', 2),('test', 3),('test', 4); 464ANALYZE TABLE t1; 465EXPLAIN SELECT * FROM t1 466WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; 467id select_type table type possible_keys key key_len ref rows Extra 4681 SIMPLE t1 fulltext b,a a 0 1 Using where 469EXPLAIN SELECT * FROM t1 USE INDEX(a) 470WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; 471id select_type table type possible_keys key key_len ref rows Extra 4721 SIMPLE t1 fulltext a a 0 1 Using where 473EXPLAIN SELECT * FROM t1 FORCE INDEX(a) 474WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; 475id select_type table type possible_keys key key_len ref rows Extra 4761 SIMPLE t1 fulltext a a 0 1 Using where 477EXPLAIN SELECT * FROM t1 IGNORE INDEX(a) 478WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; 479ERROR HY000: Can't find FULLTEXT index matching the column list 480EXPLAIN SELECT * FROM t1 USE INDEX(b) 481WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; 482ERROR HY000: Can't find FULLTEXT index matching the column list 483EXPLAIN SELECT * FROM t1 FORCE INDEX(b) 484WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; 485ERROR HY000: Can't find FULLTEXT index matching the column list 486DROP TABLE t1; 487CREATE TABLE t1(a CHAR(10), fulltext(a)) ENGINE = InnoDB; 488INSERT INTO t1 VALUES('aaa15'); 489SELECT MATCH(a) AGAINST('aaa1* aaa14 aaa16' IN BOOLEAN MODE) FROM t1; 490MATCH(a) AGAINST('aaa1* aaa14 aaa16' IN BOOLEAN MODE) 4910.000000001885928302414186 492SELECT MATCH(a) AGAINST('aaa1* aaa14 aaa15 aaa16' IN BOOLEAN MODE) FROM t1; 493MATCH(a) AGAINST('aaa1* aaa14 aaa15 aaa16' IN BOOLEAN MODE) 4940.000000003771856604828372 495DROP TABLE t1; 496CREATE TABLE t1(a TEXT) ENGINE = InnoDB; 497SELECT GROUP_CONCAT(a) AS st FROM t1 HAVING MATCH(st) AGAINST('test' IN BOOLEAN MODE); 498ERROR HY000: Incorrect arguments to MATCH 499DROP TABLE t1; 500CREATE TABLE t1(a VARCHAR(64), FULLTEXT(a)) ENGINE = InnoDB; 501INSERT INTO t1 VALUES('awrd bwrd cwrd'),('awrd bwrd cwrd'),('awrd bwrd cwrd'); 502SELECT * FROM t1 WHERE MATCH(a) AGAINST('+awrd bwrd* +cwrd*' IN BOOLEAN MODE); 503a 504awrd bwrd cwrd 505awrd bwrd cwrd 506awrd bwrd cwrd 507DROP TABLE t1; 508CREATE TABLE t1 (col text, FULLTEXT KEY full_text (col)) ENGINE = InnoDB; 509PREPARE s FROM 510"SELECT MATCH (col) AGAINST('findme') FROM t1 ORDER BY MATCH (col) AGAINST('findme')" 511 ; 512EXECUTE s; 513MATCH (col) AGAINST('findme') 514DEALLOCATE PREPARE s; 515DROP TABLE t1; 516# 517# Bug #49250 : spatial btree index corruption and crash 518# Part two : fulltext syntax check 519# 520CREATE TABLE t1(col1 TEXT, 521FULLTEXT INDEX USING BTREE (col1)); 522ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'USING BTREE (col1))' at line 2 523CREATE TABLE t2(col1 TEXT) ENGINE = InnoDB; 524CREATE FULLTEXT INDEX USING BTREE ON t2(col); 525ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'USING BTREE ON t2(col)' at line 1 526ALTER TABLE t2 ADD FULLTEXT INDEX USING BTREE (col1); 527ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'USING BTREE (col1)' at line 1 528DROP TABLE t2; 529End of 5.0 tests 530# 531# Bug #47930: MATCH IN BOOLEAN MODE returns too many results 532# inside subquery 533# 534CREATE TABLE t1 (a int) ENGINE = InnoDB; 535INSERT INTO t1 VALUES (1), (2); 536CREATE TABLE t2 (a int, b2 char(10), FULLTEXT KEY b2 (b2)) ENGINE = InnoDB; 537INSERT INTO t2 VALUES (1,'Scargill'); 538CREATE TABLE t3 (a int, b int) ENGINE = InnoDB; 539INSERT INTO t3 VALUES (1,1), (2,1); 540# t2 should use full text index 541EXPLAIN 542SELECT count(*) FROM t1 WHERE 543not exists( 544SELECT 1 FROM t2, t3 545WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE) 546); 547id select_type table type possible_keys key key_len ref rows Extra 5481 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where 5492 MATERIALIZED t2 fulltext b2 b2 0 1 Using where 5502 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 Using where 551# should return 0 552SELECT count(*) FROM t1 WHERE 553not exists( 554SELECT 1 FROM t2, t3 555WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE) 556); 557count(*) 5580 559SELECT count(*) FROM t1 WHERE 560not exists( 561SELECT 1 FROM t2 IGNORE INDEX (b2), t3 562WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE) 563); 564ERROR HY000: Can't find FULLTEXT index matching the column list 565DROP TABLE t1,t2,t3; 566CREATE TABLE t1 (a VARCHAR(4), FULLTEXT(a)) ENGINE = InnoDB; 567INSERT INTO t1 VALUES 568('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), 569('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), 570('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), 571('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), 572('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), 573('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), 574('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), 575('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), 576('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), 577('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), 578('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), 579('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), 580('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('awrd'),('cwrd'), 581('awrd'); 582SELECT COUNT(*) FROM t1 WHERE MATCH(a) AGAINST("+awrd bwrd* +cwrd*" IN BOOLEAN MODE); 583COUNT(*) 5840 585DROP TABLE t1; 586# 587# Bug #49445: Assertion failed: 0, file .\item_row.cc, line 55 with 588# fulltext search and row op 589# 590CREATE TABLE t1(a CHAR(1),FULLTEXT(a)) ENGINE = InnoDB; 591SELECT 1 FROM t1 WHERE MATCH(a) AGAINST ('') AND ROW(a,a) > ROW(1,1); 5921 593DROP TABLE t1; 594# 595# BUG#51866 - crash with repair by sort and fulltext keys 596# 597CREATE TABLE t1(a CHAR(4), FULLTEXT(a)) ENGINE = InnoDB; 598INSERT INTO t1 VALUES('aaaa'); 599DROP TABLE t1; 600# 601# Bug#54484 explain + prepared statement: crash and Got error -1 from storage engine 602# 603CREATE TABLE t1(f1 VARCHAR(6) NOT NULL, FULLTEXT KEY(f1), UNIQUE(f1)) ENGINE = InnoDB; 604INSERT INTO t1 VALUES ('test'); 605SELECT 1 FROM t1 WHERE 1 > 606ALL((SELECT 1 FROM t1 JOIN t1 a 607ON (MATCH(t1.f1) against ("")) 608WHERE t1.f1 GROUP BY t1.f1)) xor f1; 6091 6101 611PREPARE stmt FROM 612'SELECT 1 FROM t1 WHERE 1 > 613 ALL((SELECT 1 FROM t1 RIGHT OUTER JOIN t1 a 614 ON (MATCH(t1.f1) against ("")) 615 WHERE t1.f1 GROUP BY t1.f1)) xor f1'; 616EXECUTE stmt; 6171 6181 619EXECUTE stmt; 6201 6211 622DEALLOCATE PREPARE stmt; 623PREPARE stmt FROM 624'SELECT 1 FROM t1 WHERE 1 > 625 ALL((SELECT 1 FROM t1 JOIN t1 a 626 ON (MATCH(t1.f1) against ("")) 627 WHERE t1.f1 GROUP BY t1.f1))'; 628EXECUTE stmt; 6291 6301 631EXECUTE stmt; 6321 6331 634DEALLOCATE PREPARE stmt; 635DROP TABLE t1; 636End of 5.1 tests 637CREATE TABLE z(a INTEGER) engine=innodb; 638CREATE TABLE q(b TEXT CHARSET latin1, fulltext(b)) engine=innodb; 639EXPLAIN SELECT 1 FROM q WHERE (SELECT MATCH(b) AGAINST ('*') FROM z); 640ERROR 42000: syntax error, unexpected $end, expecting FTS_TERM or FTS_NUMB or '*' 641SELECT 1 FROM q WHERE (SELECT MATCH(b) AGAINST ('*') FROM z); 642ERROR 42000: syntax error, unexpected $end, expecting FTS_TERM or FTS_NUMB or '*' 643EXPLAIN SELECT MATCH(b) AGAINST ('*') FROM z; 644ERROR 42S22: Unknown column 'b' in 'field list' 645SELECT MATCH(b) AGAINST ('*') FROM z; 646ERROR 42S22: Unknown column 'b' in 'field list' 647EXPLAIN SELECT MATCH(a) AGAINST ('*') FROM z; 648ERROR HY000: Can't find FULLTEXT index matching the column list 649SELECT MATCH(a) AGAINST ('*') FROM z; 650ERROR HY000: Can't find FULLTEXT index matching the column list 651EXPLAIN SELECT MATCH(b) AGAINST ('*') FROM q; 652id select_type table type possible_keys key key_len ref rows Extra 6531 SIMPLE q ALL NULL NULL NULL NULL 1 654SELECT MATCH(b) AGAINST ('*') FROM q; 655ERROR 42000: syntax error, unexpected $end, expecting FTS_TERM or FTS_NUMB or '*' 656DROP TABLE z, q; 657create table t ( 658FTS_DOC_ID BIGINT UNSIGNED PRIMARY KEY, t TEXT, FULLTEXT KEY (t) 659) ENGINE=InnoDB; 660INSERT INTO t values (1, 'foo bar'), (2, 'foo bar'), (3, 'foo'); 661SELECT * FROM t WHERE MATCH(t) AGAINST ('foo bar' IN BOOLEAN MODE) 662LIMIT 0; 663FTS_DOC_ID t 664SELECT * FROM t WHERE MATCH(t) AGAINST ('foo bar' IN BOOLEAN MODE) 665LIMIT 1; 666FTS_DOC_ID t 6671 foo bar 668SELECT * FROM t WHERE MATCH(t) AGAINST ('foo bar' IN BOOLEAN MODE) 669LIMIT 2; 670FTS_DOC_ID t 6711 foo bar 6722 foo bar 673SELECT * FROM t WHERE MATCH(t) AGAINST ('foo bar' IN BOOLEAN MODE) 674LIMIT 3; 675FTS_DOC_ID t 6761 foo bar 6772 foo bar 6783 foo 679SELECT * FROM t WHERE MATCH(t) AGAINST ('foo bar' IN BOOLEAN MODE) 680LIMIT 4; 681FTS_DOC_ID t 6821 foo bar 6832 foo bar 6843 foo 685SELECT * FROM t WHERE MATCH(t) AGAINST ('foo bar' IN BOOLEAN MODE) 686LIMIT 5; 687FTS_DOC_ID t 6881 foo bar 6892 foo bar 6903 foo 691DROP TABLE t; 692# 693# MDEV-25295 Aborted FTS_DOC_ID_INDEX considered as 694# existing FTS_DOC_ID_INDEX during DDL 695# 696SET sql_mode=''; 697CREATE TABLE t1 (FTS_DOC_ID BIGINT UNSIGNED NOT NULL,title CHAR(1),body TEXT)engine=innodb; 698INSERT INTO t1 (FTS_DOC_ID,title,body)VALUES(1,0,0), (1,0,0); 699CREATE FULLTEXT INDEX idx1 ON t1 (title,body); 700ERROR 23000: Duplicate entry '' for key '*UNKNOWN*' 701CREATE FULLTEXT INDEX idx1 ON t1 (title,body); 702ERROR 23000: Duplicate entry '' for key '*UNKNOWN*' 703DROP TABLE t1; 704SET sql_mode = DEFAULT; 705# 706# MDEV-25070 SIGSEGV in fts_create_in_mem_aux_table 707# 708CREATE TABLE t1 (a CHAR, FULLTEXT KEY(a)) ENGINE=InnoDB; 709ALTER TABLE t1 DISCARD TABLESPACE; 710ALTER TABLE t1 ADD FULLTEXT INDEX (a); 711Warnings: 712Warning 1814 Tablespace has been discarded for table `t1` 713SHOW CREATE TABLE t1; 714Table Create Table 715t1 CREATE TABLE `t1` ( 716 `a` char(1) DEFAULT NULL, 717 FULLTEXT KEY `a` (`a`), 718 FULLTEXT KEY `a_2` (`a`) 719) ENGINE=InnoDB DEFAULT CHARSET=latin1 720DROP TABLE t1; 721# End of 10.3 tests 722