CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b)) ENGINE = InnoDB; INSERT INTO t1 VALUES('MySQL has now support', 'for full-text search'), ('Full-text indexes', 'are called collections'), ('Only MyISAM tables','support collections'), ('Function MATCH ... AGAINST()','is used to do a search'), ('Full-text search in MySQL', 'implements vector space model'); ANALYZE TABLE t1; SHOW INDEX FROM t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored t1 1 a 1 a NULL NULL NULL NULL YES FULLTEXT NO t1 1 a 2 b NULL NULL NULL NULL YES FULLTEXT NO select * from t1 where MATCH(a,b) AGAINST ("collections"); a b Full-text indexes are called collections Only MyISAM tables support collections explain extended select * from t1 where MATCH(a,b) AGAINST ("collections"); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 fulltext a a 0 1 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (match `test`.`t1`.`a`,`test`.`t1`.`b` against ('collections')) select * from t1 where MATCH(a,b) AGAINST ("indexes"); a b Full-text indexes are called collections select * from t1 where MATCH(a,b) AGAINST ("indexes collections"); a b Full-text indexes are called collections Only MyISAM tables support collections select * from t1 where MATCH(a,b) AGAINST ("only"); a b Only MyISAM tables support collections select * from t1 where MATCH(a,b) AGAINST ("collections" WITH QUERY EXPANSION); a b Only MyISAM tables support collections Full-text indexes are called collections MySQL has now support for full-text search Full-text search in MySQL implements vector space model select * from t1 where MATCH(a,b) AGAINST ("indexes" WITH QUERY EXPANSION); a b Full-text indexes are called collections Only MyISAM tables support collections MySQL has now support for full-text search Full-text search in MySQL implements vector space model select * from t1 where MATCH(a,b) AGAINST ("indexes collections" WITH QUERY EXPANSION); a b Only MyISAM tables support collections Full-text indexes are called collections MySQL has now support for full-text search Full-text search in MySQL implements vector space model select * from t1 where MATCH(a,b) AGAINST ("indexes" IN NATURAL LANGUAGE MODE); a b Full-text indexes are called collections select * from t1 where MATCH(a,b) AGAINST ("indexes" IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION); a b Full-text indexes are called collections Only MyISAM tables support collections MySQL has now support for full-text search Full-text search in MySQL implements vector space model select * from t1 where MATCH(a,b) AGAINST ("indexes" IN BOOLEAN MODE WITH QUERY EXPANSION); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WITH QUERY EXPANSION)' at line 1 explain select * from t1 where MATCH(a,b) AGAINST ("collections"); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 fulltext a a 0 1 Using where explain select * from t1 where MATCH(a,b) AGAINST ("collections")>0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 fulltext a a 0 1 Using where explain select * from t1 where MATCH(a,b) AGAINST ("collections")>1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 fulltext a a 0 1 Using where explain select * from t1 where MATCH(a,b) AGAINST ("collections")>=0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where explain select * from t1 where MATCH(a,b) AGAINST ("collections")>=1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 fulltext a a 0 1 Using where explain select * from t1 where 00 and a like '%ll%'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 fulltext a a 0 1 Using where select * from t1 where MATCH(a,b) AGAINST("support -collections" IN BOOLEAN MODE); a b MySQL has now support for full-text search explain extended select * from t1 where MATCH(a,b) AGAINST("support -collections" IN BOOLEAN MODE); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 fulltext a a 0 1 100.00 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (match `test`.`t1`.`a`,`test`.`t1`.`b` against ('support -collections' in boolean mode)) select * from t1 where MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE); a b Only MyISAM tables support collections MySQL has now support for full-text search Full-text indexes are called collections select * from t1 where MATCH(a,b) AGAINST("support +collections" IN BOOLEAN MODE); a b Only MyISAM tables support collections Full-text indexes are called collections select * from t1 where MATCH(a,b) AGAINST("sear*" IN BOOLEAN MODE); a b MySQL has now support for full-text search Function MATCH ... AGAINST() is used to do a search Full-text search in MySQL implements vector space model select * from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE); a b Only MyISAM tables support collections select * from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE); a b MySQL has now support for full-text search Function MATCH ... AGAINST() is used to do a search Full-text search in MySQL implements vector space model select * from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE); a b Full-text search in MySQL implements vector space model MySQL has now support for full-text search select * from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE); a b Function MATCH ... AGAINST() is used to do a search select *, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1; a b x MySQL has now support for full-text search 0.15835624933242798 Full-text indexes are called collections 0.15835624933242798 Only MyISAM tables support collections 0.31671249866485596 Function MATCH ... AGAINST() is used to do a search 0 Full-text search in MySQL implements vector space model 0 select *, MATCH(a,b) AGAINST("collections support" IN BOOLEAN MODE) as x from t1; a b x MySQL has now support for full-text search 0.15835624933242798 Full-text indexes are called collections 0.15835624933242798 Only MyISAM tables support collections 0.31671249866485596 Function MATCH ... AGAINST() is used to do a search 0 Full-text search in MySQL implements vector space model 0 select * from t1 where MATCH a,b AGAINST ("+call* +coll*" IN BOOLEAN MODE); a b Full-text indexes are called collections select * from t1 where MATCH a,b AGAINST ('"support now"' IN BOOLEAN MODE); a b select * from t1 where MATCH a,b AGAINST ('"Now sUPPort"' IN BOOLEAN MODE); a b MySQL has now support for full-text search select * from t1 where MATCH a,b AGAINST ('"now support"' IN BOOLEAN MODE); a b MySQL has now support for full-text search select * from t1 where MATCH a,b AGAINST ('"text search" "now support"' IN BOOLEAN MODE); a b MySQL has now support for full-text search Full-text search in MySQL implements vector space model select * from t1 where MATCH a,b AGAINST ('"text search" -"now support"' IN BOOLEAN MODE); a b Full-text search in MySQL implements vector space model select * from t1 where MATCH a,b AGAINST ('"text search" +"now support"' IN BOOLEAN MODE); a b MySQL has now support for full-text search select * from t1 where MATCH a,b AGAINST ('"text i"' IN BOOLEAN MODE); a b select * from t1 where MATCH a,b AGAINST ('"xt indexes"' IN BOOLEAN MODE); a b Full-text indexes are called collections select * from t1 where MATCH a,b AGAINST ('+(support collections) +foobar*' IN BOOLEAN MODE); a b select * from t1 where MATCH a,b AGAINST ('+(+(support collections)) +foobar*' IN BOOLEAN MODE); a b select * from t1 where MATCH a,b AGAINST ('+collections -supp* -foobar*' IN BOOLEAN MODE); a b Full-text indexes are called collections select * from t1 where MATCH a,b AGAINST('"space model' IN BOOLEAN MODE); a b Full-text search in MySQL implements vector space model delete from t1 where a like "MySQL%"; update t1 set a='some test foobar' where MATCH a,b AGAINST ('model'); delete from t1 where MATCH(a,b) AGAINST ("indexes"); select * from t1; a b Only MyISAM tables support collections Function MATCH ... AGAINST() is used to do a search some test foobar implements vector space model drop table t1; create table t1 (a varchar(200) not null, fulltext (a)) engine = innodb; insert t1 values ("aaa10 bbb20"), ("aaa20 bbb15"), ("aaa30 bbb10"); select * from t1 where match a against ("+aaa* +bbb*" in boolean mode); a aaa10 bbb20 aaa20 bbb15 aaa30 bbb10 select * from t1 where match a against ("+aaa* +bbb1*" in boolean mode); a aaa20 bbb15 aaa30 bbb10 select * from t1 where match a against ("+aaa* +ccc*" in boolean mode); a select * from t1 where match a against ("+aaa10 +(bbb*)" in boolean mode); a aaa10 bbb20 select * from t1 where match a against ("+(+aaa* +bbb1*)" in boolean mode); a aaa20 bbb15 aaa30 bbb10 select * from t1 where match a against ("(+aaa* +bbb1*)" in boolean mode); a aaa20 bbb15 aaa30 bbb10 drop table t1; CREATE TABLE t1 ( id int(11), ticket int(11), KEY ti (id), KEY tit (ticket) ) ENGINE = InnoDB; INSERT INTO t1 VALUES (2,3),(1,2); CREATE TABLE t2 ( ticket int(11), inhalt text, KEY tig (ticket), fulltext index tix (inhalt) ) ENGINE = InnoDB; INSERT INTO t2 VALUES (1,'foo'),(2,'bar'),(3,'foobar'); INSERT INTO t1 VALUES (3,3); ANALYZE TABLE t1; ANALYZE TABLE t2; select ticket2.id FROM t2 as ttxt,t2 INNER JOIN t1 as ticket2 ON ticket2.id = t2.ticket WHERE ticket2.id = ticket2.ticket and match(ttxt.inhalt) against ('foobar'); id 3 show keys from t2; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored t2 1 tig 1 ticket A 3 NULL NULL YES BTREE NO t2 1 tix 1 inhalt NULL NULL NULL NULL YES FULLTEXT NO show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `ticket` int(11) DEFAULT NULL, `inhalt` text DEFAULT NULL, KEY `tig` (`ticket`), FULLTEXT KEY `tix` (`inhalt`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 select * from t2 where MATCH inhalt AGAINST (NULL); ticket inhalt select * from t2 where MATCH inhalt AGAINST ('foobar'); ticket inhalt 3 foobar select * from t2 having MATCH inhalt AGAINST ('foobar'); ticket inhalt 3 foobar CREATE TABLE t3 (t int(11),i text,fulltext tix (t,i)); ERROR HY000: Column 't' cannot be part of FULLTEXT index CREATE TABLE t3 (t int(11),i text, j varchar(200) CHARACTER SET latin2, fulltext tix (i,j)); ERROR HY000: Column 'j' cannot be part of FULLTEXT index CREATE TABLE t3 ( ticket int(11), inhalt text, KEY tig (ticket), fulltext index tix (inhalt) ) ENGINE = InnoDB; select * from t2 where MATCH inhalt AGAINST (t2.inhalt); ERROR HY000: Incorrect arguments to AGAINST select * from t2 where MATCH ticket AGAINST ('foobar'); ERROR HY000: Can't find FULLTEXT index matching the column list select * from t2,t3 where MATCH (t2.inhalt,t3.inhalt) AGAINST ('foobar'); ERROR HY000: Incorrect arguments to MATCH drop table t1,t2,t3; CREATE TABLE t1 ( id int(11) auto_increment, title varchar(100) default '', PRIMARY KEY (id), KEY ind5 (title) ) ENGINE = InnoDB; CREATE FULLTEXT INDEX ft1 ON t1(title); insert into t1 (title) values ('this is a test'); select * from t1 where match title against ('test' in boolean mode); id title 1 this is a test update t1 set title='this is A test' where id=1; check table t1; Table Op Msg_type Msg_text test.t1 check status OK update t1 set title='this test once revealed a bug' where id=1; select * from t1; id title 1 this test once revealed a bug update t1 set title=NULL where id=1; drop table t1; CREATE TABLE t1 (a int(11), b text, FULLTEXT KEY (b)) ENGINE = InnoDB; insert into t1 values (1,"I wonder why the fulltext index doesnt work?"); SELECT * from t1 where MATCH (b) AGAINST ('apples'); a b insert into t1 values (2,"fullaaa fullzzz"); select * from t1 where match b against ('full*' in boolean mode); a b 1 I wonder why the fulltext index doesnt work? 2 fullaaa fullzzz drop table t1; CREATE TABLE t1 ( id int(11) NOT NULL auto_increment primary key, mytext text NOT NULL, FULLTEXT KEY mytext (mytext)) ENGINE = InnoDB; INSERT INTO t1 VALUES (1,'my small mouse'),(2,'la-la-la'),(3,'It is so funny'),(4,'MySQL Tutorial'); select 8 from t1; 8 8 8 8 8 drop table t1; create table t1 (a text, fulltext key (a)) ENGINE = InnoDB; insert into t1 values ('aaaa'); select * from t1 where match (a) against ('aaaa'); a aaaa drop table t1; create table t1 ( ref_mag text not null, fulltext (ref_mag)) ENGINE = InnoDB; insert into t1 values ('test'); select ref_mag from t1 where match ref_mag against ('+test' in boolean mode); ref_mag test alter table t1 change ref_mag ref_mag char (255) not null; select ref_mag from t1 where match ref_mag against ('+test' in boolean mode); ref_mag test drop table t1; create table t1 (t1_id int(11) primary key, name varchar(32)) ENGINE = InnoDB; insert into t1 values (1, 'data1'); insert into t1 values (2, 'data2'); create table t2 (t2_id int(11) primary key, t1_id int(11), name varchar(32)) ENGINE = InnoDB; insert into t2 values (1, 1, 'xxfoo'); insert into t2 values (2, 1, 'xxbar'); insert into t2 values (3, 1, 'xxbuz'); select * from t1 join t2 using(`t1_id`) where match (t1.name, t2.name) against('xxfoo' in boolean mode); ERROR HY000: Incorrect arguments to MATCH select * from t2 where match name against ('*a*b*c*d*e*f*' in boolean mode); ERROR HY000: Can't find FULLTEXT index matching the column list drop table t1,t2; create table t1 (a text, fulltext key (a)) ENGINE = InnoDB; insert into t1 select "xxxx yyyy zzzz"; drop table t1; SET NAMES latin1; CREATE TABLE t1 (t text character set utf8 not null, fulltext(t)) ENGINE = InnoDB; INSERT t1 VALUES ('Mit freundlichem Grüß'), ('aus Osnabrück'); SET NAMES koi8r; INSERT t1 VALUES ("üÔÏ ÍÙ - ÏÐÉÌËÉ"),("ïÔÌÅÚØ, ÇÎÉÄÁ!"), ("îÅ ×ÌÅÚÁÊ, ÕÂØÅÔ!"),("É ÂÕÄÅÔ ÐÒÁ×!"); SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('ïðéìëé'); t collation(t) üÔÏ ÍÙ - ÏÐÉÌËÉ utf8_general_ci DROP TABLE t1; CREATE TABLE t1 (s varchar(255), FULLTEXT (s)) ENGINE = InnoDB DEFAULT CHARSET=utf8; insert into t1 (s) values ('pära para para'),('para para para'); select * from t1 where match(s) against('para' in boolean mode); s para para para pära para para select * from t1 where match(s) against('par*' in boolean mode); s para para para pära para para DROP TABLE t1; CREATE TABLE t1 (h text, FULLTEXT (h)) ENGINE = InnoDB; INSERT INTO t1 VALUES ('Jesses Hasse Ling and his syncopators of Swing'); select count(*) from t1; count(*) 1 drop table t1; CREATE TABLE t1 ( a TEXT, FULLTEXT (a) ) ENGINE = InnoDB; INSERT INTO t1 VALUES ('testing ft_nlq_find_relevance'); SELECT MATCH(a) AGAINST ('nosuchword') FROM t1; MATCH(a) AGAINST ('nosuchword') 0 DROP TABLE t1; create table t1 (a int primary key, b text, fulltext(b)) ENGINE = InnoDB; create table t2 (a int, b text) ENGINE = InnoDB; insert t1 values (1, "aaaa"), (2, "bbbb"); insert t2 values (10, "aaaa"), (2, "cccc"); replace t1 select * from t2; drop table t1, t2; CREATE TABLE t1 (t VARCHAR(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci, FULLTEXT (t)) ENGINE = InnoDB; SET NAMES latin1; INSERT INTO t1 VALUES('Mit freundlichem Grüß aus Osnabrück'); SELECT COUNT(*) FROM t1 WHERE MATCH(t) AGAINST ('"osnabrück"' IN BOOLEAN MODE); COUNT(*) 1 DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(30), FULLTEXT(a)) ENGINE = InnoDB; INSERT INTO t1 VALUES('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'); INSERT INTO t1 VALUES('testword\'\''); SELECT a FROM t1 WHERE MATCH a AGAINST('testword' IN BOOLEAN MODE); a testword'' SELECT a FROM t1 WHERE MATCH a AGAINST('testword\'\'' IN BOOLEAN MODE); a testword'' INSERT INTO t1 VALUES('test\'s'); SELECT a FROM t1 WHERE MATCH a AGAINST('test' IN BOOLEAN MODE); a test's DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(10000), FULLTEXT(a)) ENGINE = InnoDB; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10000) DEFAULT NULL, FULLTEXT KEY `a` (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 DROP TABLE t1; CREATE TABLE t1 (a TEXT, FULLTEXT KEY(a)) ENGINE = InnoDB; INSERT INTO t1 VALUES('test'),('test1'),('test'); ANALYZE TABLE t1; PREPARE stmt from "SELECT a, FORMAT(MATCH(a) AGAINST('test1 test'),6) FROM t1 WHERE MATCH(a) AGAINST('test1 test')"; EXECUTE stmt; a FORMAT(MATCH(a) AGAINST('test1 test'),6) test1 0.227645 test 0.031008 test 0.031008 EXECUTE stmt; a FORMAT(MATCH(a) AGAINST('test1 test'),6) test1 0.227645 test 0.031008 test 0.031008 DEALLOCATE PREPARE stmt; DROP TABLE t1; CREATE TABLE t1 (a VARCHAR(255), FULLTEXT(a)) ENGINE = InnoDB; SELECT * FROM t1 IGNORE INDEX(a) WHERE MATCH(a) AGAINST('test'); a SELECT * FROM t1 WHERE MATCH(a) AGAINST('test'); a DROP TABLE t1; CREATE TABLE t1(a TEXT, fulltext(a)) ENGINE = InnoDB; INSERT INTO t1 VALUES(' aaaaa aaaa'); SELECT * FROM t1 WHERE MATCH(a) AGAINST ('"aaaa"' IN BOOLEAN MODE); a aaaaa aaaa DROP TABLE t1; CREATE TABLE t1(a VARCHAR(20), FULLTEXT(a)) ENGINE = InnoDB; INSERT INTO t1 VALUES('Offside'),('City Of God'); SELECT a FROM t1 WHERE MATCH a AGAINST ('+city of*' IN BOOLEAN MODE); a City Of God SELECT a FROM t1 WHERE MATCH a AGAINST ('+city (of*)' IN BOOLEAN MODE); a Offside City Of God SELECT a FROM t1 WHERE MATCH a AGAINST ('+city* of*' IN BOOLEAN MODE); a City Of God DROP TABLE t1; create table t1(a text,b date,fulltext index(a)) ENGINE = InnoDB; insert into t1 set a='water',b='2008-08-04'; select 1 from t1 where match(a) against ('water' in boolean mode) and b>='2008-08-01'; 1 1 drop table t1; show warnings; Level Code Message CREATE TABLE t1 (a VARCHAR(255), b INT, FULLTEXT(a), KEY(b)) ENGINE = InnoDB; INSERT INTO t1 VALUES('test', 1),('test', 1),('test', 1),('test', 1), ('test', 1),('test', 2),('test', 3),('test', 4); ANALYZE TABLE t1; EXPLAIN SELECT * FROM t1 WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 fulltext b,a a 0 1 Using where EXPLAIN SELECT * FROM t1 USE INDEX(a) WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 fulltext a a 0 1 Using where EXPLAIN SELECT * FROM t1 FORCE INDEX(a) WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 fulltext a a 0 1 Using where EXPLAIN SELECT * FROM t1 IGNORE INDEX(a) WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; ERROR HY000: Can't find FULLTEXT index matching the column list EXPLAIN SELECT * FROM t1 USE INDEX(b) WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; ERROR HY000: Can't find FULLTEXT index matching the column list EXPLAIN SELECT * FROM t1 FORCE INDEX(b) WHERE MATCH(a) AGAINST('test' IN BOOLEAN MODE) AND b=1; ERROR HY000: Can't find FULLTEXT index matching the column list DROP TABLE t1; CREATE TABLE t1(a CHAR(10), fulltext(a)) ENGINE = InnoDB; INSERT INTO t1 VALUES('aaa15'); SELECT MATCH(a) AGAINST('aaa1* aaa14 aaa16' IN BOOLEAN MODE) FROM t1; MATCH(a) AGAINST('aaa1* aaa14 aaa16' IN BOOLEAN MODE) 0.000000001885928302414186 SELECT MATCH(a) AGAINST('aaa1* aaa14 aaa15 aaa16' IN BOOLEAN MODE) FROM t1; MATCH(a) AGAINST('aaa1* aaa14 aaa15 aaa16' IN BOOLEAN MODE) 0.000000003771856604828372 DROP TABLE t1; CREATE TABLE t1(a TEXT) ENGINE = InnoDB; SELECT GROUP_CONCAT(a) AS st FROM t1 HAVING MATCH(st) AGAINST('test' IN BOOLEAN MODE); ERROR HY000: Incorrect arguments to MATCH DROP TABLE t1; CREATE TABLE t1(a VARCHAR(64), FULLTEXT(a)) ENGINE = InnoDB; INSERT INTO t1 VALUES('awrd bwrd cwrd'),('awrd bwrd cwrd'),('awrd bwrd cwrd'); SELECT * FROM t1 WHERE MATCH(a) AGAINST('+awrd bwrd* +cwrd*' IN BOOLEAN MODE); a awrd bwrd cwrd awrd bwrd cwrd awrd bwrd cwrd DROP TABLE t1; CREATE TABLE t1 (col text, FULLTEXT KEY full_text (col)) ENGINE = InnoDB; PREPARE s FROM "SELECT MATCH (col) AGAINST('findme') FROM t1 ORDER BY MATCH (col) AGAINST('findme')" ; EXECUTE s; MATCH (col) AGAINST('findme') DEALLOCATE PREPARE s; DROP TABLE t1; # # Bug #49250 : spatial btree index corruption and crash # Part two : fulltext syntax check # CREATE TABLE t1(col1 TEXT, FULLTEXT INDEX USING BTREE (col1)); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'USING BTREE (col1))' at line 2 CREATE TABLE t2(col1 TEXT) ENGINE = InnoDB; CREATE FULLTEXT INDEX USING BTREE ON t2(col); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'USING BTREE ON t2(col)' at line 1 ALTER TABLE t2 ADD FULLTEXT INDEX USING BTREE (col1); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'USING BTREE (col1)' at line 1 DROP TABLE t2; End of 5.0 tests # # Bug #47930: MATCH IN BOOLEAN MODE returns too many results # inside subquery # CREATE TABLE t1 (a int) ENGINE = InnoDB; INSERT INTO t1 VALUES (1), (2); CREATE TABLE t2 (a int, b2 char(10), FULLTEXT KEY b2 (b2)) ENGINE = InnoDB; INSERT INTO t2 VALUES (1,'Scargill'); CREATE TABLE t3 (a int, b int) ENGINE = InnoDB; INSERT INTO t3 VALUES (1,1), (2,1); # t2 should use full text index EXPLAIN SELECT count(*) FROM t1 WHERE not exists( SELECT 1 FROM t2, t3 WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE) ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where 2 MATERIALIZED t2 fulltext b2 b2 0 1 Using where 2 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 Using where # should return 0 SELECT count(*) FROM t1 WHERE not exists( SELECT 1 FROM t2, t3 WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE) ); count(*) 0 SELECT count(*) FROM t1 WHERE not exists( SELECT 1 FROM t2 IGNORE INDEX (b2), t3 WHERE t3.a=t1.a AND MATCH(b2) AGAINST('scargill' IN BOOLEAN MODE) ); ERROR HY000: Can't find FULLTEXT index matching the column list DROP TABLE t1,t2,t3; CREATE TABLE t1 (a VARCHAR(4), FULLTEXT(a)) ENGINE = InnoDB; INSERT INTO t1 VALUES ('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), ('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), ('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), ('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), ('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), ('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), ('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), ('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), ('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), ('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), ('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), ('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'), ('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('cwrd'),('awrd'),('cwrd'), ('awrd'); SELECT COUNT(*) FROM t1 WHERE MATCH(a) AGAINST("+awrd bwrd* +cwrd*" IN BOOLEAN MODE); COUNT(*) 0 DROP TABLE t1; # # Bug #49445: Assertion failed: 0, file .\item_row.cc, line 55 with # fulltext search and row op # CREATE TABLE t1(a CHAR(1),FULLTEXT(a)) ENGINE = InnoDB; SELECT 1 FROM t1 WHERE MATCH(a) AGAINST ('') AND ROW(a,a) > ROW(1,1); 1 DROP TABLE t1; # # BUG#51866 - crash with repair by sort and fulltext keys # CREATE TABLE t1(a CHAR(4), FULLTEXT(a)) ENGINE = InnoDB; INSERT INTO t1 VALUES('aaaa'); DROP TABLE t1; # # Bug#54484 explain + prepared statement: crash and Got error -1 from storage engine # CREATE TABLE t1(f1 VARCHAR(6) NOT NULL, FULLTEXT KEY(f1), UNIQUE(f1)) ENGINE = InnoDB; INSERT INTO t1 VALUES ('test'); SELECT 1 FROM t1 WHERE 1 > ALL((SELECT 1 FROM t1 JOIN t1 a ON (MATCH(t1.f1) against ("")) WHERE t1.f1 GROUP BY t1.f1)) xor f1; 1 1 PREPARE stmt FROM 'SELECT 1 FROM t1 WHERE 1 > ALL((SELECT 1 FROM t1 RIGHT OUTER JOIN t1 a ON (MATCH(t1.f1) against ("")) WHERE t1.f1 GROUP BY t1.f1)) xor f1'; EXECUTE stmt; 1 1 EXECUTE stmt; 1 1 DEALLOCATE PREPARE stmt; PREPARE stmt FROM 'SELECT 1 FROM t1 WHERE 1 > ALL((SELECT 1 FROM t1 JOIN t1 a ON (MATCH(t1.f1) against ("")) WHERE t1.f1 GROUP BY t1.f1))'; EXECUTE stmt; 1 1 EXECUTE stmt; 1 1 DEALLOCATE PREPARE stmt; DROP TABLE t1; End of 5.1 tests CREATE TABLE z(a INTEGER) engine=innodb; CREATE TABLE q(b TEXT CHARSET latin1, fulltext(b)) engine=innodb; EXPLAIN SELECT 1 FROM q WHERE (SELECT MATCH(b) AGAINST ('*') FROM z); ERROR 42000: syntax error, unexpected $end, expecting FTS_TERM or FTS_NUMB or '*' SELECT 1 FROM q WHERE (SELECT MATCH(b) AGAINST ('*') FROM z); ERROR 42000: syntax error, unexpected $end, expecting FTS_TERM or FTS_NUMB or '*' EXPLAIN SELECT MATCH(b) AGAINST ('*') FROM z; ERROR 42S22: Unknown column 'b' in 'field list' SELECT MATCH(b) AGAINST ('*') FROM z; ERROR 42S22: Unknown column 'b' in 'field list' EXPLAIN SELECT MATCH(a) AGAINST ('*') FROM z; ERROR HY000: Can't find FULLTEXT index matching the column list SELECT MATCH(a) AGAINST ('*') FROM z; ERROR HY000: Can't find FULLTEXT index matching the column list EXPLAIN SELECT MATCH(b) AGAINST ('*') FROM q; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE q ALL NULL NULL NULL NULL 1 SELECT MATCH(b) AGAINST ('*') FROM q; ERROR 42000: syntax error, unexpected $end, expecting FTS_TERM or FTS_NUMB or '*' DROP TABLE z, q; create table t ( FTS_DOC_ID BIGINT UNSIGNED PRIMARY KEY, t TEXT, FULLTEXT KEY (t) ) ENGINE=InnoDB; INSERT INTO t values (1, 'foo bar'), (2, 'foo bar'), (3, 'foo'); SELECT * FROM t WHERE MATCH(t) AGAINST ('foo bar' IN BOOLEAN MODE) LIMIT 0; FTS_DOC_ID t SELECT * FROM t WHERE MATCH(t) AGAINST ('foo bar' IN BOOLEAN MODE) LIMIT 1; FTS_DOC_ID t 1 foo bar SELECT * FROM t WHERE MATCH(t) AGAINST ('foo bar' IN BOOLEAN MODE) LIMIT 2; FTS_DOC_ID t 1 foo bar 2 foo bar SELECT * FROM t WHERE MATCH(t) AGAINST ('foo bar' IN BOOLEAN MODE) LIMIT 3; FTS_DOC_ID t 1 foo bar 2 foo bar 3 foo SELECT * FROM t WHERE MATCH(t) AGAINST ('foo bar' IN BOOLEAN MODE) LIMIT 4; FTS_DOC_ID t 1 foo bar 2 foo bar 3 foo SELECT * FROM t WHERE MATCH(t) AGAINST ('foo bar' IN BOOLEAN MODE) LIMIT 5; FTS_DOC_ID t 1 foo bar 2 foo bar 3 foo DROP TABLE t; # # MDEV-25295 Aborted FTS_DOC_ID_INDEX considered as # existing FTS_DOC_ID_INDEX during DDL # SET sql_mode=''; CREATE TABLE t1 (FTS_DOC_ID BIGINT UNSIGNED NOT NULL,title CHAR(1),body TEXT)engine=innodb; INSERT INTO t1 (FTS_DOC_ID,title,body)VALUES(1,0,0), (1,0,0); CREATE FULLTEXT INDEX idx1 ON t1 (title,body); ERROR 23000: Duplicate entry '' for key '*UNKNOWN*' CREATE FULLTEXT INDEX idx1 ON t1 (title,body); ERROR 23000: Duplicate entry '' for key '*UNKNOWN*' DROP TABLE t1; SET sql_mode = DEFAULT; # # MDEV-25070 SIGSEGV in fts_create_in_mem_aux_table # CREATE TABLE t1 (a CHAR, FULLTEXT KEY(a)) ENGINE=InnoDB; ALTER TABLE t1 DISCARD TABLESPACE; ALTER TABLE t1 ADD FULLTEXT INDEX (a); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` char(1) DEFAULT NULL, FULLTEXT KEY `a` (`a`), FULLTEXT KEY `a_2` (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 DROP TABLE t1; # End of 10.3 tests