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