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