diff options
Diffstat (limited to 'mysql-test/r/fulltext.result')
| -rw-r--r-- | mysql-test/r/fulltext.result | 69 |
1 files changed, 62 insertions, 7 deletions
diff --git a/mysql-test/r/fulltext.result b/mysql-test/r/fulltext.result index 28977b88f32..ad93bfcd462 100644 --- a/mysql-test/r/fulltext.result +++ b/mysql-test/r/fulltext.result @@ -49,7 +49,7 @@ 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 MySQL server version for the right syntax to use near 'QUERY EXPANSION)' at line 1 +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 '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 @@ -505,6 +505,8 @@ 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 @@ -520,16 +522,24 @@ id select_type table type possible_keys key key_len ref rows Extra 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 4 Using where +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 4 Using where +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 4 Using where +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; @@ -565,12 +575,12 @@ DROP TABLE t1; # 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 MySQL server version for the right syntax to use near 'USING BTREE (col1))' at line 2 +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 MySQL server version for the right syntax to use near 'USING BTREE ON t2(col)' at line 1 +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 MySQL server version for the right syntax to use near 'USING BTREE (col1)' at line 1 +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 # @@ -689,3 +699,48 @@ EXECUTE stmt; 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; +# +# End of 5.5 tests +# |