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