1# 2# Test of fulltext index 3# 4 5--disable_warnings 6drop table if exists t1,t2,t3; 7--enable_warnings 8 9CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b)) ENGINE = InnoDB; 10INSERT INTO t1 VALUES('MySQL has now support', 'for full-text search'), 11 ('Full-text indexes', 'are called collections'), 12 ('Only MyISAM tables','support collections'), 13 ('Function MATCH ... AGAINST()','is used to do a search'), 14 ('Full-text search in MySQL', 'implements vector space model'); 15-- disable_result_log 16ANALYZE TABLE t1; 17-- enable_result_log 18SHOW INDEX FROM t1; 19 20# nl search 21 22select * from t1 where MATCH(a,b) AGAINST ("collections"); 23explain extended select * from t1 where MATCH(a,b) AGAINST ("collections"); 24select * from t1 where MATCH(a,b) AGAINST ("indexes"); 25select * from t1 where MATCH(a,b) AGAINST ("indexes collections"); 26select * from t1 where MATCH(a,b) AGAINST ("only"); 27 28# query expansion 29 30select * from t1 where MATCH(a,b) AGAINST ("collections" WITH QUERY EXPANSION); 31select * from t1 where MATCH(a,b) AGAINST ("indexes" WITH QUERY EXPANSION); 32select * from t1 where MATCH(a,b) AGAINST ("indexes collections" WITH QUERY EXPANSION); 33 34# IN NATURAL LANGUAGE MODE 35select * from t1 where MATCH(a,b) AGAINST ("indexes" IN NATURAL LANGUAGE MODE); 36select * from t1 where MATCH(a,b) AGAINST ("indexes" IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION); 37--error 1064 38select * from t1 where MATCH(a,b) AGAINST ("indexes" IN BOOLEAN MODE WITH QUERY EXPANSION); 39 40# add_ft_keys() tests 41 42explain select * from t1 where MATCH(a,b) AGAINST ("collections"); 43explain select * from t1 where MATCH(a,b) AGAINST ("collections")>0; 44explain select * from t1 where MATCH(a,b) AGAINST ("collections")>1; 45explain select * from t1 where MATCH(a,b) AGAINST ("collections")>=0; 46explain select * from t1 where MATCH(a,b) AGAINST ("collections")>=1; 47explain select * from t1 where 0<MATCH(a,b) AGAINST ("collections"); 48explain select * from t1 where 1<MATCH(a,b) AGAINST ("collections"); 49explain select * from t1 where 0<=MATCH(a,b) AGAINST ("collections"); 50explain select * from t1 where 1<=MATCH(a,b) AGAINST ("collections"); 51explain select * from t1 where MATCH(a,b) AGAINST ("collections")>0 and a like '%ll%'; 52 53# boolean search 54 55select * from t1 where MATCH(a,b) AGAINST("support -collections" IN BOOLEAN MODE); 56explain extended select * from t1 where MATCH(a,b) AGAINST("support -collections" IN BOOLEAN MODE); 57select * from t1 where MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE); 58select * from t1 where MATCH(a,b) AGAINST("support +collections" IN BOOLEAN MODE); 59select * from t1 where MATCH(a,b) AGAINST("sear*" IN BOOLEAN MODE); 60select * from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE); 61select * from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE); 62select * from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE); 63select * from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE); 64select *, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1; 65select *, MATCH(a,b) AGAINST("collections support" IN BOOLEAN MODE) as x from t1; 66 67select * from t1 where MATCH a,b AGAINST ("+call* +coll*" IN BOOLEAN MODE); 68 69select * from t1 where MATCH a,b AGAINST ('"support now"' IN BOOLEAN MODE); 70select * from t1 where MATCH a,b AGAINST ('"Now sUPPort"' IN BOOLEAN MODE); 71select * from t1 where MATCH a,b AGAINST ('"now support"' IN BOOLEAN MODE); 72select * from t1 where MATCH a,b AGAINST ('"text search" "now support"' IN BOOLEAN MODE); 73select * from t1 where MATCH a,b AGAINST ('"text search" -"now support"' IN BOOLEAN MODE); 74select * from t1 where MATCH a,b AGAINST ('"text search" +"now support"' IN BOOLEAN MODE); 75select * from t1 where MATCH a,b AGAINST ('"text i"' IN BOOLEAN MODE); 76select * from t1 where MATCH a,b AGAINST ('"xt indexes"' IN BOOLEAN MODE); 77 78select * from t1 where MATCH a,b AGAINST ('+(support collections) +foobar*' IN BOOLEAN MODE); 79select * from t1 where MATCH a,b AGAINST ('+(+(support collections)) +foobar*' IN BOOLEAN MODE); 80select * from t1 where MATCH a,b AGAINST ('+collections -supp* -foobar*' IN BOOLEAN MODE); 81 82# bug#2708, bug#3870 crash 83 84select * from t1 where MATCH a,b AGAINST('"space model' IN BOOLEAN MODE); 85 86# INNODB_FTS: No FTS index on column a or b. InnoDB do now support 87# FT type search when there is no FTS INDEX 88 89# select * from t1 where MATCH a AGAINST ("search" IN BOOLEAN MODE); 90# select * from t1 where MATCH b AGAINST ("sear*" IN BOOLEAN MODE); 91 92# UNION of fulltext's 93#select * from t1 where MATCH(a,b) AGAINST ("collections") UNION ALL select * from t1 where MATCH(a,b) AGAINST ("indexes"); 94 95#update/delete with fulltext index 96 97delete from t1 where a like "MySQL%"; 98update t1 set a='some test foobar' where MATCH a,b AGAINST ('model'); 99delete from t1 where MATCH(a,b) AGAINST ("indexes"); 100select * from t1; 101drop table t1; 102 103# 104# why to scan strings for trunc* 105# 106 107create table t1 (a varchar(200) not null, fulltext (a)) engine = innodb; 108insert t1 values ("aaa10 bbb20"), ("aaa20 bbb15"), ("aaa30 bbb10"); 109select * from t1 where match a against ("+aaa* +bbb*" in boolean mode); 110select * from t1 where match a against ("+aaa* +bbb1*" in boolean mode); 111select * from t1 where match a against ("+aaa* +ccc*" in boolean mode); 112select * from t1 where match a against ("+aaa10 +(bbb*)" in boolean mode); 113 114# FTS_INNODB: INVESTIGATE 115select * from t1 where match a against ("+(+aaa* +bbb1*)" in boolean mode); 116select * from t1 where match a against ("(+aaa* +bbb1*)" in boolean mode); 117drop table t1; 118 119# 120# Check bug reported by Matthias Urlichs 121# 122 123CREATE TABLE t1 ( 124 id int(11), 125 ticket int(11), 126 KEY ti (id), 127 KEY tit (ticket) 128) ENGINE = InnoDB; 129INSERT INTO t1 VALUES (2,3),(1,2); 130 131CREATE TABLE t2 ( 132 ticket int(11), 133 inhalt text, 134 KEY tig (ticket), 135 fulltext index tix (inhalt) 136) ENGINE = InnoDB; 137INSERT INTO t2 VALUES (1,'foo'),(2,'bar'),(3,'foobar'); 138 139#select t1.id FROM t2 as ttxt,t1,t1 as ticket2 140#WHERE ticket2.id = ttxt.ticket AND t1.id = ticket2.ticket and 141#match(ttxt.inhalt) against ('foobar'); 142 143# In the following query MySQL didn't use the fulltext index 144#select ticket2.id FROM t2 as ttxt,t2 INNER JOIN t1 as ticket2 ON 145#ticket2.id = t2.ticket 146#WHERE ticket2.id = ticket2.ticket and match(ttxt.inhalt) against ('foobar'); 147 148INSERT INTO t1 VALUES (3,3); 149-- disable_result_log 150ANALYZE TABLE t1; 151ANALYZE TABLE t2; 152-- enable_result_log 153select ticket2.id FROM t2 as ttxt,t2 154INNER JOIN t1 as ticket2 ON ticket2.id = t2.ticket 155WHERE ticket2.id = ticket2.ticket and 156 match(ttxt.inhalt) against ('foobar'); 157 158# Check that we get 'fulltext' index in SHOW CREATE 159 160show keys from t2; 161show create table t2; 162 163# check for bug reported by Stephan Skusa 164 165select * from t2 where MATCH inhalt AGAINST (NULL); 166 167# MATCH in HAVING (pretty useless, but still it should work) 168 169select * from t2 where MATCH inhalt AGAINST ('foobar'); 170 171# INNODB_FTS: INVESTIGATE 172select * from t2 having MATCH inhalt AGAINST ('foobar'); 173 174# 175# check of fulltext errors 176# 177 178--error 1283 179CREATE TABLE t3 (t int(11),i text,fulltext tix (t,i)); 180--error 1283 181CREATE TABLE t3 (t int(11),i text, 182 j varchar(200) CHARACTER SET latin2, 183 fulltext tix (i,j)); 184 185CREATE TABLE t3 ( 186 ticket int(11), 187 inhalt text, 188 KEY tig (ticket), 189 fulltext index tix (inhalt) 190) ENGINE = InnoDB; 191 192--error 1210 193select * from t2 where MATCH inhalt AGAINST (t2.inhalt); 194--error 1191 195select * from t2 where MATCH ticket AGAINST ('foobar'); 196--error 1210 197select * from t2,t3 where MATCH (t2.inhalt,t3.inhalt) AGAINST ('foobar'); 198 199drop table t1,t2,t3; 200 201# 202# three more bugtests 203# 204 205CREATE TABLE t1 ( 206 id int(11) auto_increment, 207 title varchar(100) default '', 208 PRIMARY KEY (id), 209 KEY ind5 (title) 210) ENGINE = InnoDB; 211 212CREATE FULLTEXT INDEX ft1 ON t1(title); 213insert into t1 (title) values ('this is a test'); 214select * from t1 where match title against ('test' in boolean mode); 215update t1 set title='this is A test' where id=1; 216 217check table t1; 218update t1 set title='this test once revealed a bug' where id=1; 219select * from t1; 220update t1 set title=NULL where id=1; 221 222drop table t1; 223 224# one more bug - const_table related 225 226CREATE TABLE t1 (a int(11), b text, FULLTEXT KEY (b)) ENGINE = InnoDB; 227insert into t1 values (1,"I wonder why the fulltext index doesnt work?"); 228SELECT * from t1 where MATCH (b) AGAINST ('apples'); 229 230insert into t1 values (2,"fullaaa fullzzz"); 231select * from t1 where match b against ('full*' in boolean mode); 232 233drop table t1; 234 235CREATE TABLE t1 ( id int(11) NOT NULL auto_increment primary key, mytext text NOT NULL, FULLTEXT KEY mytext (mytext)) ENGINE = InnoDB; 236INSERT INTO t1 VALUES (1,'my small mouse'),(2,'la-la-la'),(3,'It is so funny'),(4,'MySQL Tutorial'); 237select 8 from t1; 238drop table t1; 239 240# 241# Check bug reported by Julian Ladisch 242# ERROR 1030: Got error 127 from table handler 243# 244 245create table t1 (a text, fulltext key (a)) ENGINE = InnoDB; 246insert into t1 values ('aaaa'); 247 248# INNODB_FTS: InnoDB do not support "repair" command 249# repair table t1; 250select * from t1 where match (a) against ('aaaa'); 251drop table t1; 252 253# 254# bug #283 by jocelyn fournier <joc@presence-pc.com> 255# FULLTEXT index on a TEXT filed converted to a CHAR field doesn't work anymore 256# 257 258create table t1 ( ref_mag text not null, fulltext (ref_mag)) ENGINE = InnoDB; 259insert into t1 values ('test'); 260select ref_mag from t1 where match ref_mag against ('+test' in boolean mode); 261alter table t1 change ref_mag ref_mag char (255) not null; 262select ref_mag from t1 where match ref_mag against ('+test' in boolean mode); 263drop table t1; 264 265# 266# bug #942: JOIN 267# NOTE: Not related to FTS, no FTS index created 268 269create table t1 (t1_id int(11) primary key, name varchar(32)) ENGINE = InnoDB; 270insert into t1 values (1, 'data1'); 271insert into t1 values (2, 'data2'); 272 273create table t2 (t2_id int(11) primary key, t1_id int(11), name varchar(32)) ENGINE = InnoDB; 274insert into t2 values (1, 1, 'xxfoo'); 275insert into t2 values (2, 1, 'xxbar'); 276insert into t2 values (3, 1, 'xxbuz'); 277# INNODB_FTS: InnoDB do not support MATCH expressions with arguments from 278# different tables 279--error ER_WRONG_ARGUMENTS 280select * from t1 join t2 using(`t1_id`) where match (t1.name, t2.name) against('xxfoo' in boolean mode); 281 282# 283# Bug #7858: bug with many short (< ft_min_word_len) words in boolean search 284# 285# INNODB_FTS: Note there is no fulltext index on table. InnoDB do not support 286# Fulltext search in such case 287--error ER_FT_MATCHING_KEY_NOT_FOUND 288select * from t2 where match name against ('*a*b*c*d*e*f*' in boolean mode); 289drop table t1,t2; 290 291# 292# bug with repair-by-sort and incorrect records estimation 293# 294 295create table t1 (a text, fulltext key (a)) ENGINE = InnoDB; 296insert into t1 select "xxxx yyyy zzzz"; 297drop table t1; 298 299# 300# UTF8 301# INNODB_FTS: Charset Support (FIX) 302SET NAMES latin1; 303CREATE TABLE t1 (t text character set utf8 not null, fulltext(t)) ENGINE = InnoDB; 304INSERT t1 VALUES ('Mit freundlichem Gr��'), ('aus Osnabr�ck'); 305SET NAMES koi8r; 306INSERT t1 VALUES ("��� �� - ������"),("������, �����!"), 307 ("�� ������, �����!"),("� ����� ����!"); 308SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('������'); 309#SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('���*' IN BOOLEAN MODE); 310#SELECT * FROM t1 WHERE MATCH t AGAINST ('���' IN BOOLEAN MODE); 311#SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabr�ck'); 312#SET NAMES latin1; 313#SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabr�ck'); 314#SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrueck'); 315#SELECT t, collation(t),FORMAT(MATCH t AGAINST ('Osnabruck'),6) FROM t1 WHERE MATCH t AGAINST ('Osnabruck'); 316#alter table t1 modify t text character set latin1 collate latin1_german2_ci not null; 317#alter table t1 modify t varchar(200) collate latin1_german2_ci not null; 318#SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabr�ck'); 319#SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrueck'); 320DROP TABLE t1; 321 322# 323# bug#3964 324# 325 326CREATE TABLE t1 (s varchar(255), FULLTEXT (s)) ENGINE = InnoDB DEFAULT CHARSET=utf8; 327insert into t1 (s) values ('p�ra para para'),('para para para'); 328select * from t1 where match(s) against('para' in boolean mode); 329select * from t1 where match(s) against('par*' in boolean mode); 330DROP TABLE t1; 331 332# 333# icc -ip bug (ip = interprocedural optimization) 334# bug#5528 335# 336CREATE TABLE t1 (h text, FULLTEXT (h)) ENGINE = InnoDB; 337INSERT INTO t1 VALUES ('Jesses Hasse Ling and his syncopators of Swing'); 338 339# INNODB_FTS: InnoDB do not support "repair" command 340# REPAIR TABLE t1; 341select count(*) from t1; 342drop table t1; 343 344# 345# testing out of bounds memory access in ft_nlq_find_relevance() 346# (bug#8522); visible in valgrind. 347# 348CREATE TABLE t1 ( a TEXT, FULLTEXT (a) ) ENGINE = InnoDB; 349INSERT INTO t1 VALUES ('testing ft_nlq_find_relevance'); 350SELECT MATCH(a) AGAINST ('nosuchword') FROM t1; 351DROP TABLE t1; 352# 353# bug#6784 354# mi_flush_bulk_insert (on dup key error in mi_write) 355# was mangling info->dupp_key_pos 356# 357 358create table t1 (a int primary key, b text, fulltext(b)) ENGINE = InnoDB; 359create table t2 (a int, b text) ENGINE = InnoDB; 360insert t1 values (1, "aaaa"), (2, "bbbb"); 361insert t2 values (10, "aaaa"), (2, "cccc"); 362replace t1 select * from t2; 363drop table t1, t2; 364 365# 366# bug#8351 367# 368# INNODB_FTS: Charset Support 369CREATE TABLE t1 (t VARCHAR(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci, FULLTEXT (t)) ENGINE = InnoDB; 370SET NAMES latin1; 371INSERT INTO t1 VALUES('Mit freundlichem Gr�� aus Osnabr�ck'); 372SELECT COUNT(*) FROM t1 WHERE MATCH(t) AGAINST ('"osnabr�ck"' IN BOOLEAN MODE); 373DROP TABLE t1; 374 375# 376# BUG#11684 - repair crashes mysql when table has fulltext index 377# 378 379CREATE TABLE t1 (a VARCHAR(30), FULLTEXT(a)) ENGINE = InnoDB; 380INSERT INTO t1 VALUES('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'); 381 382# INNODB_FTS: InnoDB do not support repair 383#SET myisam_repair_threads=2; 384#REPAIR TABLE t1; 385#SET myisam_repair_threads=@@global.myisam_repair_threads; 386 387# 388# BUG#5686 - #1034 - Incorrect key file for table - only utf8 389# 390INSERT INTO t1 VALUES('testword\'\''); 391SELECT a FROM t1 WHERE MATCH a AGAINST('testword' IN BOOLEAN MODE); 392SELECT a FROM t1 WHERE MATCH a AGAINST('testword\'\'' IN BOOLEAN MODE); 393 394# 395# BUG#14194: Problem with fulltext boolean search and apostrophe 396# 397# INNODB_FTS: Add "apostrophe" support 398INSERT INTO t1 VALUES('test\'s'); 399SELECT a FROM t1 WHERE MATCH a AGAINST('test' IN BOOLEAN MODE); 400DROP TABLE t1; 401 402# 403# BUG#13835: max key length is 1000 bytes when trying to create 404# a fulltext index 405# 406CREATE TABLE t1 (a VARCHAR(10000), FULLTEXT(a)) ENGINE = InnoDB; 407SHOW CREATE TABLE t1; 408DROP TABLE t1; 409 410# 411# BUG#14496: Crash or strange results with prepared statement, 412# MATCH and FULLTEXT 413# 414CREATE TABLE t1 (a TEXT, FULLTEXT KEY(a)) ENGINE = InnoDB; 415INSERT INTO t1 VALUES('test'),('test1'),('test'); 416-- disable_result_log 417ANALYZE TABLE t1; 418-- enable_result_log 419PREPARE stmt from "SELECT a, FORMAT(MATCH(a) AGAINST('test1 test'),6) FROM t1 WHERE MATCH(a) AGAINST('test1 test')"; 420 421EXECUTE stmt; 422EXECUTE stmt; 423DEALLOCATE PREPARE stmt; 424DROP TABLE t1; 425 426# 427# BUG#25951 - ignore/use index does not work with fulltext 428# 429CREATE TABLE t1 (a VARCHAR(255), FULLTEXT(a)) ENGINE = InnoDB; 430SELECT * FROM t1 IGNORE INDEX(a) WHERE MATCH(a) AGAINST('test'); 431# INNODB_FTS: InnoDB do have have this option (disable keys) 432# ALTER TABLE t1 DISABLE KEYS; 433# --error 1191 434SELECT * FROM t1 WHERE MATCH(a) AGAINST('test'); 435DROP TABLE t1; 436 437# 438# BUG#11392 - fulltext search bug 439# 440CREATE TABLE t1(a TEXT, fulltext(a)) ENGINE = InnoDB; 441INSERT INTO t1 VALUES(' aaaaa aaaa'); 442SELECT * FROM t1 WHERE MATCH(a) AGAINST ('"aaaa"' IN BOOLEAN MODE); 443DROP TABLE t1; 444 445# 446# BUG#29445 - match ... against () never returns 447# 448CREATE TABLE t1(a VARCHAR(20), FULLTEXT(a)) ENGINE = InnoDB; 449INSERT INTO t1 VALUES('Offside'),('City Of God'); 450 451SELECT a FROM t1 WHERE MATCH a AGAINST ('+city of*' IN BOOLEAN MODE); 452SELECT a FROM t1 WHERE MATCH a AGAINST ('+city (of*)' IN BOOLEAN MODE); 453SELECT a FROM t1 WHERE MATCH a AGAINST ('+city* of*' IN BOOLEAN MODE); 454DROP TABLE t1; 455 456# End of 4.1 tests 457 458# 459# bug#34374 - mysql generates incorrect warning 460# 461create table t1(a text,b date,fulltext index(a)) ENGINE = InnoDB; 462insert into t1 set a='water',b='2008-08-04'; 463select 1 from t1 where match(a) against ('water' in boolean mode) and b>='2008-08-01'; 464drop table t1; 465show warnings; 466 467# 468# BUG#38842 - Fix for 25951 seems incorrect 469# 470CREATE TABLE t1 (a VARCHAR(255), b INT, FULLTEXT(a), KEY(b)) ENGINE = InnoDB; 471INSERT INTO t1 VALUES('test', 1),('test', 1),('test', 1),('test', 1), 472 ('test', 1),('test', 2),('test', 3),('test', 4); 473 474-- disable_result_log 475ANALYZE TABLE t1; 476-- enable_result_log 477EXPLAIN SELECT * FROM t1 478WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; 479 480EXPLAIN SELECT * FROM t1 USE INDEX(a) 481WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; 482 483EXPLAIN SELECT * FROM t1 FORCE INDEX(a) 484WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; 485 486--error ER_FT_MATCHING_KEY_NOT_FOUND 487EXPLAIN SELECT * FROM t1 IGNORE INDEX(a) 488WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; 489 490--error ER_FT_MATCHING_KEY_NOT_FOUND 491EXPLAIN SELECT * FROM t1 USE INDEX(b) 492WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; 493 494--error ER_FT_MATCHING_KEY_NOT_FOUND 495EXPLAIN SELECT * FROM t1 FORCE INDEX(b) 496WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; 497 498DROP TABLE t1; 499 500# 501# BUG#37245 - Full text search problem 502# 503CREATE TABLE t1(a CHAR(10), fulltext(a)) ENGINE = InnoDB; 504INSERT INTO t1 VALUES('aaa15'); 505 506SELECT MATCH(a) AGAINST('aaa1* aaa14 aaa16' IN BOOLEAN MODE) FROM t1; 507SELECT MATCH(a) AGAINST('aaa1* aaa14 aaa15 aaa16' IN BOOLEAN MODE) FROM t1; 508DROP TABLE t1; 509 510# 511# BUG#36737 - having + full text operator crashes mysql 512# 513CREATE TABLE t1(a TEXT) ENGINE = InnoDB; 514--error ER_WRONG_ARGUMENTS 515SELECT GROUP_CONCAT(a) AS st FROM t1 HAVING MATCH(st) AGAINST('test' IN BOOLEAN MODE); 516DROP TABLE t1; 517 518# 519# BUG#42907 - Multi-term boolean fulltext query containing a single 520# quote fails in 5.1.x 521# 522CREATE TABLE t1(a VARCHAR(64), FULLTEXT(a)) ENGINE = InnoDB; 523INSERT INTO t1 VALUES('awrd bwrd cwrd'),('awrd bwrd cwrd'),('awrd bwrd cwrd'); 524SELECT * FROM t1 WHERE MATCH(a) AGAINST('+awrd bwrd* +cwrd*' IN BOOLEAN MODE); 525DROP TABLE t1; 526 527# 528# BUG#37740 Server crashes on execute statement with full text search and match against 529# 530CREATE TABLE t1 (col text, FULLTEXT KEY full_text (col)) ENGINE = InnoDB; 531 532PREPARE s FROM 533 "SELECT MATCH (col) AGAINST('findme') FROM t1 ORDER BY MATCH (col) AGAINST('findme')" 534 ; 535 536EXECUTE s; 537DEALLOCATE PREPARE s; 538DROP TABLE t1; 539 540 541--echo # 542--echo # Bug #49250 : spatial btree index corruption and crash 543--echo # Part two : fulltext syntax check 544--echo # 545 546--error ER_PARSE_ERROR 547CREATE TABLE t1(col1 TEXT, 548 FULLTEXT INDEX USING BTREE (col1)); 549CREATE TABLE t2(col1 TEXT) ENGINE = InnoDB; 550--error ER_PARSE_ERROR 551CREATE FULLTEXT INDEX USING BTREE ON t2(col); 552--error ER_PARSE_ERROR 553ALTER TABLE t2 ADD FULLTEXT INDEX USING BTREE (col1); 554 555DROP TABLE t2; 556 557 558--echo End of 5.0 tests 559 560 561--echo # 562--echo # Bug #47930: MATCH IN BOOLEAN MODE returns too many results 563--echo # inside subquery 564--echo # 565 566CREATE TABLE t1 (a int) ENGINE = InnoDB; 567INSERT INTO t1 VALUES (1), (2); 568 569CREATE TABLE t2 (a int, b2 char(10), FULLTEXT KEY b2 (b2)) ENGINE = InnoDB; 570INSERT INTO t2 VALUES (1,'Scargill'); 571 572CREATE TABLE t3 (a int, b int) ENGINE = InnoDB; 573INSERT INTO t3 VALUES (1,1), (2,1); 574 575--echo # t2 should use full text index 576EXPLAIN 577SELECT count(*) FROM t1 WHERE 578 not exists( 579 SELECT 1 FROM t2, t3 580 WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE) 581 ); 582 583# INNODB_FTS: INVESTIGATE 584--echo # should return 0 585SELECT count(*) FROM t1 WHERE 586 not exists( 587 SELECT 1 FROM t2, t3 588 WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE) 589 ); 590 591--error ER_FT_MATCHING_KEY_NOT_FOUND 592SELECT count(*) FROM t1 WHERE 593 not exists( 594 SELECT 1 FROM t2 IGNORE INDEX (b2), t3 595 WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE) 596 ); 597 598DROP TABLE t1,t2,t3; 599 600# 601# BUG#50351 - ft_min_word_len=2 Causes query to hang 602# 603CREATE TABLE t1 (a VARCHAR(4), FULLTEXT(a)) ENGINE = InnoDB; 604INSERT INTO t1 VALUES 605('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), 606('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), 607('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), 608('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), 609('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), 610('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), 611('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), 612('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), 613('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), 614('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), 615('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), 616('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), 617('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('awrd'),('cwrd'), 618('awrd'); 619SELECT COUNT(*) FROM t1 WHERE MATCH(a) AGAINST("+awrd bwrd* +cwrd*" IN BOOLEAN MODE); 620DROP TABLE t1; 621 622--echo # 623--echo # Bug #49445: Assertion failed: 0, file .\item_row.cc, line 55 with 624--echo # fulltext search and row op 625--echo # 626 627CREATE TABLE t1(a CHAR(1),FULLTEXT(a)) ENGINE = InnoDB; 628SELECT 1 FROM t1 WHERE MATCH(a) AGAINST ('') AND ROW(a,a) > ROW(1,1); 629DROP TABLE t1; 630 631--echo # 632--echo # BUG#51866 - crash with repair by sort and fulltext keys 633--echo # 634CREATE TABLE t1(a CHAR(4), FULLTEXT(a)) ENGINE = InnoDB; 635INSERT INTO t1 VALUES('aaaa'); 636# INNODB_FTS: Do not support "set myisam_sort_buffer" commands 637#SET myisam_sort_buffer_size=4; 638#REPAIR TABLE t1; 639#SET myisam_sort_buffer_size=@@global.myisam_sort_buffer_size; 640DROP TABLE t1; 641 642--echo # 643--echo # Bug#54484 explain + prepared statement: crash and Got error -1 from storage engine 644--echo # 645 646CREATE TABLE t1(f1 VARCHAR(6) NOT NULL, FULLTEXT KEY(f1), UNIQUE(f1)) ENGINE = InnoDB; 647INSERT INTO t1 VALUES ('test'); 648--disable_warnings 649SELECT 1 FROM t1 WHERE 1 > 650 ALL((SELECT 1 FROM t1 JOIN t1 a 651 ON (MATCH(t1.f1) against ("")) 652 WHERE t1.f1 GROUP BY t1.f1)) xor f1; 653 654PREPARE stmt FROM 655'SELECT 1 FROM t1 WHERE 1 > 656 ALL((SELECT 1 FROM t1 RIGHT OUTER JOIN t1 a 657 ON (MATCH(t1.f1) against ("")) 658 WHERE t1.f1 GROUP BY t1.f1)) xor f1'; 659 660EXECUTE stmt; 661EXECUTE stmt; 662 663DEALLOCATE PREPARE stmt; 664 665PREPARE stmt FROM 666'SELECT 1 FROM t1 WHERE 1 > 667 ALL((SELECT 1 FROM t1 JOIN t1 a 668 ON (MATCH(t1.f1) against ("")) 669 WHERE t1.f1 GROUP BY t1.f1))'; 670 671EXECUTE stmt; 672EXECUTE stmt; 673 674DEALLOCATE PREPARE stmt; 675--enable_warnings 676 677DROP TABLE t1; 678 679--echo End of 5.1 tests 680