summaryrefslogtreecommitdiffstats
path: root/mysql-test/main/fulltext.result
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 18:00:34 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 18:00:34 +0000
commit3f619478f796eddbba6e39502fe941b285dd97b1 (patch)
treee2c7b5777f728320e5b5542b6213fd3591ba51e2 /mysql-test/main/fulltext.result
parentInitial commit. (diff)
downloadmariadb-upstream.tar.xz
mariadb-upstream.zip
Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'mysql-test/main/fulltext.result')
-rw-r--r--mysql-test/main/fulltext.result790
1 files changed, 790 insertions, 0 deletions
diff --git a/mysql-test/main/fulltext.result b/mysql-test/main/fulltext.result
new file mode 100644
index 00000000..233dd549
--- /dev/null
+++ b/mysql-test/main/fulltext.result
@@ -0,0 +1,790 @@
+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 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
+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 Ignored
+t2 1 tig 1 ticket A NULL 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=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+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)
+üÔÏ ÍÙ - ÏÐÉÌËÉ utf8mb3_general_ci
+SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('ðÒá*' IN BOOLEAN MODE);
+t collation(t)
+É ÂÕÄÅÔ ÐÒÁ×! utf8mb3_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 utf8mb3_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 utf8mb3_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 `test`.`t1`.`t` at row 3
+Warning 1366 Incorrect string value: '\xD0\x9E\xD1\x82\xD0\xBB...' for column `test`.`t1`.`t` at row 4
+Warning 1366 Incorrect string value: '\xD0\x9D\xD0\xB5 \xD0...' for column `test`.`t1`.`t` at row 5
+Warning 1366 Incorrect string value: '\xD0\xB8 \xD0\xB1\xD1...' for column `test`.`t1`.`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 COLLATE=latin1_swedish_ci
+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 ) ) as exp;
+exp
+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;
+#
+# MDEV-22275 (Memory leak)
+#
+CREATE TEMPORARY TABLE tmp (a TEXT) ENGINE=MyISAM;
+ALTER TABLE tmp ADD FULLTEXT (a);
+INSERT INTO tmp VALUES ('foo');
+DROP TABLE tmp;
+CREATE TEMPORARY TABLE tmp (a TEXT) ENGINE=Aria;
+ALTER TABLE tmp ADD FULLTEXT (a);
+INSERT INTO tmp VALUES ('foo');
+DROP TABLE tmp;