diff options
Diffstat (limited to 'mysql-test/main/fulltext.result')
| -rw-r--r-- | mysql-test/main/fulltext.result | 778 |
1 files changed, 778 insertions, 0 deletions
diff --git a/mysql-test/main/fulltext.result b/mysql-test/main/fulltext.result new file mode 100644 index 00000000000..308d1d7fcb9 --- /dev/null +++ b/mysql-test/main/fulltext.result @@ -0,0 +1,778 @@ +drop table if exists t1,t2,t3; +CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b)); +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'); +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 +t1 1 a 1 a NULL NULL NULL NULL YES FULLTEXT +t1 1 a 2 b NULL NULL NULL NULL YES FULLTEXT +select * from t1 where MATCH(a,b) AGAINST ("collections"); +a b +Only MyISAM tables support collections +Full-text indexes are called 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 +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 +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 +select * from t1 where MATCH(a,b) AGAINST ("indexes collections" WITH QUERY EXPANSION); +a b +Full-text indexes are called collections +Only MyISAM tables support collections +MySQL has now support for full-text search +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 +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 0<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 1<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 0<=MATCH(a,b) AGAINST ("collections"); +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 1<=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 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 +MySQL has now support for full-text search +Full-text indexes are called collections +Only MyISAM tables support collections +select * from t1 where MATCH(a,b) AGAINST("support +collections" IN BOOLEAN MODE); +a b +Full-text indexes are called collections +Only MyISAM tables support 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 +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("+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 1 +Full-text indexes are called collections 1 +Only MyISAM tables support collections 2 +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 1 +Full-text indexes are called collections 1 +Only MyISAM tables support collections 2 +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 +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 +select * from t1 where MATCH a AGAINST ("search" IN BOOLEAN MODE); +a b +Full-text search in MySQL implements vector space model +select * from t1 where MATCH 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 +select * from t1 where MATCH(a,b) AGAINST ("collections") UNION ALL select * from t1 where MATCH(a,b) AGAINST ("indexes"); +a b +Only MyISAM tables support collections +Full-text indexes are called collections +Full-text indexes are called collections +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)); +insert t1 values ("aaa10 bbb20"), ("aaa20 bbb15"), ("aaa30 bbb10"); +select * from t1 where match a against ("+aaa* +bbb*" in boolean mode); +a +aaa30 bbb10 +aaa20 bbb15 +aaa10 bbb20 +select * from t1 where match a against ("+aaa* +bbb1*" in boolean mode); +a +aaa30 bbb10 +aaa20 bbb15 +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 +aaa30 bbb10 +aaa20 bbb15 +select * from t1 where match a against ("(+aaa* +bbb1*)" in boolean mode); +a +aaa30 bbb10 +aaa20 bbb15 +drop table t1; +CREATE TABLE t1 ( +id int(11), +ticket int(11), +KEY ti (id), +KEY tit (ticket) +); +INSERT INTO t1 VALUES (2,3),(1,2); +CREATE TABLE t2 ( +ticket int(11), +inhalt text, +KEY tig (ticket), +fulltext index tix (inhalt) +); +INSERT INTO t2 VALUES (1,'foo'),(2,'bar'),(3,'foobar'); +select t1.id FROM t2 as ttxt,t1,t1 as ticket2 +WHERE ticket2.id = ttxt.ticket AND t1.id = ticket2.ticket and +match(ttxt.inhalt) against ('foobar'); +id +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 +INSERT INTO t1 VALUES (3,3); +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 +t2 1 tig 1 ticket A NULL NULL NULL YES BTREE +t2 1 tix 1 inhalt NULL NULL NULL NULL YES FULLTEXT +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=MyISAM 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) +); +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=MyISAM; +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=MyISAM; +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 +2 fullaaa fullzzz +1 I wonder why the fulltext index doesnt work? +drop table t1; +CREATE TABLE t1 ( id int(11) NOT NULL auto_increment primary key, mytext text NOT NULL, FULLTEXT KEY mytext (mytext)) ENGINE=MyISAM; +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)); +insert into t1 values ('aaaa'); +repair table t1; +Table Op Msg_type Msg_text +test.t1 repair status OK +select * from t1 where match (a) against ('aaaa'); +a +drop table t1; +create table t1 ( ref_mag text not null, fulltext (ref_mag)); +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)); +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)); +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); +t1_id name t2_id name +1 data1 1 xxfoo +select * from t2 where match name against ('*a*b*c*d*e*f*' in boolean mode); +t2_id t1_id name +drop table t1,t2; +create table t1 (a text, fulltext key (a)); +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)); +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 +SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('ðÒá*' IN BOOLEAN MODE); +t collation(t) +É ÂÕÄÅÔ ÐÒÁ×! utf8_general_ci +SELECT * FROM t1 WHERE MATCH t AGAINST ('ÜÔÏ' IN BOOLEAN MODE); +t +SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrück'); +t collation(t) +SET NAMES latin1; +SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrück'); +t collation(t) +aus Osnabrück utf8_general_ci +SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrueck'); +t collation(t) +SELECT t, collation(t),FORMAT(MATCH t AGAINST ('Osnabruck'),6) FROM t1 WHERE MATCH t AGAINST ('Osnabruck'); +t collation(t) FORMAT(MATCH t AGAINST ('Osnabruck'),6) +aus Osnabrück utf8_general_ci 1.591140 +SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR +alter table t1 modify t varchar(200) collate latin1_german2_ci not null; +Warnings: +Warning 1366 Incorrect string value: '\xD0\xAD\xD1\x82\xD0\xBE...' for column 't' at row 3 +Warning 1366 Incorrect string value: '\xD0\x9E\xD1\x82\xD0\xBB...' for column 't' at row 4 +Warning 1366 Incorrect string value: '\xD0\x9D\xD0\xB5 \xD0...' for column 't' at row 5 +Warning 1366 Incorrect string value: '\xD0\xB8 \xD0\xB1\xD1...' for column 't' at row 6 +SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrück'); +t collation(t) +aus Osnabrück latin1_german2_ci +SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrueck'); +t collation(t) +aus Osnabrück latin1_german2_ci +DROP TABLE t1; +CREATE TABLE t1 (s varchar(255), FULLTEXT (s)) 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 +pära para para +para para para +select * from t1 where match(s) against('par*' in boolean mode); +s +pära para para +para para para +DROP TABLE t1; +CREATE TABLE t1 (h text, FULLTEXT (h)); +INSERT INTO t1 VALUES ('Jesses Hasse Ling and his syncopators of Swing'); +REPAIR TABLE t1; +Table Op Msg_type Msg_text +test.t1 repair status OK +select count(*) from t1; +count(*) +1 +drop table t1; +CREATE TABLE t1 ( a TEXT, FULLTEXT (a) ); +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)); +create table t2 (a int, b text); +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)); +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)); +INSERT INTO t1 VALUES('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'); +SET myisam_repair_threads=2; +REPAIR TABLE t1; +Table Op Msg_type Msg_text +test.t1 repair status OK +SET myisam_repair_threads=@@global.myisam_repair_threads; +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)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10000) DEFAULT NULL, + FULLTEXT KEY `a` (`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +CREATE TABLE t1 (a TEXT, FULLTEXT KEY(a)); +INSERT INTO t1 VALUES('test'),('test1'),('test'); +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.685267 +EXECUTE stmt; +a FORMAT(MATCH(a) AGAINST('test1 test'),6) +test1 0.685267 +DEALLOCATE PREPARE stmt; +DROP TABLE t1; +CREATE TABLE t1 (a VARCHAR(255), FULLTEXT(a)); +SELECT * FROM t1 IGNORE INDEX(a) WHERE MATCH(a) AGAINST('test'); +a +ALTER TABLE t1 DISABLE KEYS; +SELECT * FROM t1 WHERE MATCH(a) AGAINST('test'); +ERROR HY000: Can't find FULLTEXT index matching the column list +DROP TABLE t1; +CREATE TABLE t1(a TEXT); +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)); +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 +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=myisam; +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)); +INSERT INTO t1 VALUES('test', 1),('test', 1),('test', 1),('test', 1), +('test', 1),('test', 2),('test', 3),('test', 4); +INSERT INTO t1 VALUES('test', 5),('test', 6),('test', 7),('test', 8), +('test', 5),('test', 6),('test', 7),('test', 8); +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; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref b b 5 const 5 Using where +EXPLAIN SELECT * FROM t1 USE INDEX(b) +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 ref b b 5 const 5 Using where +EXPLAIN SELECT * FROM t1 FORCE INDEX(b) +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 ref b b 5 const 5 Using where +DROP TABLE t1; +create table t1(a text,b date,fulltext index(a))engine=myisam; +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 CHAR(10)); +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) +1 +SELECT MATCH(a) AGAINST('aaa1* aaa14 aaa15 aaa16' IN BOOLEAN MODE) FROM t1; +MATCH(a) AGAINST('aaa1* aaa14 aaa15 aaa16' IN BOOLEAN MODE) +2 +DROP TABLE t1; +CREATE TABLE t1(a TEXT); +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)); +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)); +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); +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); +INSERT INTO t1 VALUES (1), (2); +CREATE TABLE t2 (a int, b2 char(10), FULLTEXT KEY b2 (b2)); +INSERT INTO t2 VALUES (1,'Scargill'); +CREATE TABLE t3 (a int, b int); +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 +# should return 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) +); +count(*) +0 +DROP TABLE t1,t2,t3; +CREATE TABLE t1 (a VARCHAR(4), FULLTEXT(a)); +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)); +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)); +INSERT INTO t1 VALUES('aaaa'); +SET myisam_sort_buffer_size=4; +Warnings: +Warning 1292 Truncated incorrect myisam_sort_buffer_size value: '4' +REPAIR TABLE t1; +Table Op Msg_type Msg_text +test.t1 repair status OK +SET myisam_sort_buffer_size=@@global.myisam_sort_buffer_size; +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)); +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 +# +# Start of 5.5 tests +# +# +# MDEV-6146 Can't mix (latin1_swedish_ci,NUMERIC) and (utf8_unicode_ci,IMPLICIT) for MATCH +# +SET NAMES utf8; +CREATE TABLE t1 +( +txt text COLLATE utf8_unicode_ci NOT NULL, +uid int(11) NOT NULL, +id2 int(11) NOT NULL, +KEY uid (uid), +KEY id2 (id2) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; +INSERT INTO t1 VALUES ('txt1',1234,5678); +SELECT * FROM t1 WHERE MATCH (id2, uid, txt) AGAINST ('txt1' IN BOOLEAN MODE); +txt uid id2 +txt1 1234 5678 +SELECT * FROM t1 WHERE MATCH (id2, uid, txt) AGAINST ('1234' IN BOOLEAN MODE); +txt uid id2 +txt1 1234 5678 +SELECT * FROM t1 WHERE MATCH (id2, uid, txt) AGAINST ('5678' IN BOOLEAN MODE); +txt uid id2 +txt1 1234 5678 +DROP TABLE t1; +CREATE TABLE t1 ( +txt1 text COLLATE utf8_unicode_ci NOT NULL, +txt2 text COLLATE latin1_swedish_ci NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; +INSERT INTO t1 VALUES ('nnn1 x1 y1 ööö1','mmm1 ùùù1'); +INSERT INTO t1 VALUES ('nnn2 x2 y2 ööö2','mmm2 ùùù2'); +INSERT INTO t1 VALUES ('nnn3 x3 y3 ööö3','mmm3 ùùù3'); +INSERT INTO t1 VALUES ('nnn4 x4 y4 ööö4','mmm4 ùùù4'); +INSERT INTO t1 VALUES ('nnn5 x5 y5 ööö5','mmm5 '); +SELECT * FROM t1 WHERE MATCH (txt1,txt2) AGAINST ('ööö1' IN BOOLEAN MODE); +txt1 txt2 +nnn1 x1 y1 ööö1 mmm1 ùùù1 +SELECT * FROM t1 WHERE MATCH (txt1,txt2) AGAINST ('ùùù2' IN BOOLEAN MODE); +txt1 txt2 +nnn2 x2 y2 ööö2 mmm2 ùùù2 +DROP TABLE t1; +# +# MDEV-14743: Server crashes in Item_func_match::init_search +# +CREATE TABLE t1 (f VARCHAR(8)); +INSERT INTO t1 VALUES ('foo'),('bar'); +SELECT 'foo' IN ( SELECT f FROM t1 GROUP BY MATCH(f) AGAINST ( 'qux' IN BOOLEAN MODE ) ); +'foo' IN ( SELECT f FROM t1 GROUP BY MATCH(f) AGAINST ( 'qux' IN BOOLEAN MODE ) ) +1 +SELECT 'foo' IN ( SELECT f FROM t1 GROUP BY MATCH(f) AGAINST ( 'qux' IN BOOLEAN MODE )) as f1, MATCH(f) AGAINST ( 'qux' IN BOOLEAN MODE ) as f2 from t1 ; +f1 f2 +1 0 +1 0 +explain extended +SELECT 'foo' IN ( SELECT f FROM t1 GROUP BY MATCH(f) AGAINST ( 'qux' IN BOOLEAN MODE )) as f1, MATCH(f) AGAINST ( 'qux' IN BOOLEAN MODE ) as f2 from t1 ; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 +2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 /* select#1 */ select <expr_cache><'foo'>(<in_optimizer>('foo',<exists>(/* select#2 */ select `test`.`t1`.`f` from `test`.`t1` where <cache>(convert('foo' using latin1)) = `test`.`t1`.`f` or `test`.`t1`.`f` is null having `test`.`t1`.`f` is null))) AS `f1`,(match `test`.`t1`.`f` against ('qux' in boolean mode)) AS `f2` from `test`.`t1` +drop table t1; +# +# End of 5.5 tests +# +CREATE TABLE t1 ( +id int(11) auto_increment, +title varchar(100) default '', +PRIMARY KEY (id), +KEY ind5 (title) +) ENGINE=MyISAM; +CREATE FULLTEXT INDEX IF NOT EXISTS ft1 ON t1(title); +CREATE FULLTEXT INDEX IF NOT EXISTS ft1 ON t1(title); +Warnings: +Note 1061 Duplicate key name 'ft1' +DROP TABLE t1; |