413 lines
14 KiB
Plaintext
413 lines
14 KiB
Plaintext
DROP TABLE IF EXISTS t1,t2,t3;
|
|
CREATE TABLE t1 (
|
|
a INT AUTO_INCREMENT PRIMARY KEY,
|
|
message CHAR(20),
|
|
FULLTEXT(message)
|
|
) ENGINE = InnoDB comment = 'original testcase by sroussey@network54.com';
|
|
INSERT INTO t1 (message) VALUES ("Testing"),("table"),("testbug"),
|
|
("steve"),("is"),("cool"),("steve is cool");
|
|
ANALYZE TABLE t1;
|
|
SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE MATCH (message) AGAINST ('steve');
|
|
a FORMAT(MATCH (message) AGAINST ('steve'),6)
|
|
4 0.296010
|
|
7 0.296010
|
|
SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve');
|
|
a MATCH (message) AGAINST ('steve' IN BOOLEAN MODE)
|
|
4 0.2960100471973419
|
|
7 0.2960100471973419
|
|
SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE);
|
|
a FORMAT(MATCH (message) AGAINST ('steve'),6)
|
|
4 0.296010
|
|
7 0.296010
|
|
SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE);
|
|
a MATCH (message) AGAINST ('steve' IN BOOLEAN MODE)
|
|
4 0.2960100471973419
|
|
7 0.2960100471973419
|
|
SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY a;
|
|
a FORMAT(MATCH (message) AGAINST ('steve'),6)
|
|
4 0.296010
|
|
7 0.296010
|
|
SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) ORDER BY a;
|
|
a MATCH (message) AGAINST ('steve' IN BOOLEAN MODE)
|
|
4 0.2960100471973419
|
|
7 0.2960100471973419
|
|
SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE a in (2,7,4) and MATCH (message) AGAINST ('steve') ORDER BY a DESC;
|
|
a FORMAT(MATCH (message) AGAINST ('steve'),6)
|
|
7 0.296010
|
|
4 0.296010
|
|
SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE a in (2,7,4) and MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) ORDER BY a DESC;
|
|
a MATCH (message) AGAINST ('steve' IN BOOLEAN MODE)
|
|
7 0.2960100471973419
|
|
4 0.2960100471973419
|
|
SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE a=7 and MATCH (message) AGAINST ('steve') ORDER BY 1;
|
|
a FORMAT(MATCH (message) AGAINST ('steve'),6)
|
|
7 0.296010
|
|
SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE a=7 and MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) ORDER BY 1;
|
|
a MATCH (message) AGAINST ('steve' IN BOOLEAN MODE)
|
|
7 0.2960100471973419
|
|
SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) as rel FROM t1 ORDER BY rel;
|
|
a rel
|
|
1 0.000000
|
|
2 0.000000
|
|
3 0.000000
|
|
5 0.000000
|
|
6 0.000000
|
|
4 0.296010
|
|
7 0.296010
|
|
SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) as rel FROM t1 ORDER BY rel;
|
|
a rel
|
|
1 0
|
|
2 0
|
|
3 0
|
|
5 0
|
|
6 0
|
|
4 0.2960100471973419
|
|
7 0.2960100471973419
|
|
alter table t1 add key m (message);
|
|
ANALYZE TABLE t1;
|
|
explain SELECT message FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY message;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL fulltext message message 0 const 1 100.00 Using where; Ft_hints: no_ranking; Using filesort
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`message` AS `message` from `test`.`t1` where (match `test`.`t1`.`message` against ('steve')) order by `test`.`t1`.`message`
|
|
SELECT message FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY message desc;
|
|
message
|
|
steve is cool
|
|
steve
|
|
drop table t1;
|
|
CREATE TABLE t1 (
|
|
a INT AUTO_INCREMENT PRIMARY KEY,
|
|
message CHAR(20),
|
|
FULLTEXT(message)
|
|
) ENGINE = InnoDB;
|
|
INSERT INTO t1 (message) VALUES ("testbug"),("testbug foobar");
|
|
ANALYZE TABLE t1;
|
|
SELECT a, MATCH (message) AGAINST ('t* f*' IN BOOLEAN MODE) as rel FROM t1;
|
|
a rel
|
|
1 0.000000001885928302414186
|
|
2 0.0906190574169159
|
|
SELECT a, MATCH (message) AGAINST ('t* f*' IN BOOLEAN MODE) as rel FROM t1 ORDER BY rel,a;
|
|
a rel
|
|
1 0.000000001885928302414186
|
|
2 0.0906190574169159
|
|
drop table t1;
|
|
CREATE TABLE t1 (
|
|
id int(11) NOT NULL auto_increment,
|
|
thread int(11) NOT NULL default '0',
|
|
beitrag longtext NOT NULL,
|
|
PRIMARY KEY (id),
|
|
KEY thread (thread),
|
|
FULLTEXT KEY beitrag (beitrag)
|
|
) ENGINE =InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=7923 ;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
|
|
CREATE TABLE t2 (
|
|
id int(11) NOT NULL auto_increment,
|
|
text varchar(100) NOT NULL default '',
|
|
PRIMARY KEY (id),
|
|
KEY text (text)
|
|
) ENGINE = InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=63 ;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
|
|
CREATE TABLE t3 (
|
|
id int(11) NOT NULL auto_increment,
|
|
forum int(11) NOT NULL default '0',
|
|
betreff varchar(70) NOT NULL default '',
|
|
PRIMARY KEY (id),
|
|
KEY forum (forum),
|
|
FULLTEXT KEY betreff (betreff)
|
|
) ENGINE = InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=996 ;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
|
|
select a.text, b.id, b.betreff
|
|
from
|
|
t2 a inner join t3 b on a.id = b.forum inner join
|
|
t1 c on b.id = c.thread
|
|
where
|
|
match(b.betreff) against ('+abc' in boolean mode)
|
|
group by a.text, b.id, b.betreff
|
|
union
|
|
select a.text, b.id, b.betreff
|
|
from
|
|
t2 a inner join t3 b on a.id = b.forum inner join
|
|
t1 c on b.id = c.thread
|
|
where
|
|
match(c.beitrag) against ('+abc' in boolean mode)
|
|
group by
|
|
a.text, b.id, b.betreff
|
|
order by
|
|
match(b.betreff) against ('+abc' in boolean mode) desc;
|
|
ERROR 42000: Table 'b' from one of the SELECTs cannot be used in global ORDER clause
|
|
select a.text, b.id, b.betreff
|
|
from
|
|
t2 a inner join t3 b on a.id = b.forum inner join
|
|
t1 c on b.id = c.thread
|
|
where
|
|
match(b.betreff) against ('+abc' in boolean mode)
|
|
union
|
|
select a.text, b.id, b.betreff
|
|
from
|
|
t2 a inner join t3 b on a.id = b.forum inner join
|
|
t1 c on b.id = c.thread
|
|
where
|
|
match(c.beitrag) against ('+abc' in boolean mode)
|
|
order by
|
|
match(b.betreff) against ('+abc' in boolean mode) desc;
|
|
ERROR 42000: Table 'b' from one of the SELECTs cannot be used in global ORDER clause
|
|
select a.text, b.id, b.betreff
|
|
from
|
|
t2 a inner join t3 b on a.id = b.forum inner join
|
|
t1 c on b.id = c.thread
|
|
where
|
|
match(b.betreff) against ('+abc' in boolean mode)
|
|
union
|
|
select a.text, b.id, b.betreff
|
|
from
|
|
t2 a inner join t3 b on a.id = b.forum inner join
|
|
t1 c on b.id = c.thread
|
|
where
|
|
match(c.beitrag) against ('+abc' in boolean mode)
|
|
order by
|
|
match(betreff) against ('+abc' in boolean mode) desc;
|
|
ERROR HY000: The used table type doesn't support FULLTEXT indexes
|
|
(select b.id, b.betreff from t3 b) union
|
|
(select b.id, b.betreff from t3 b)
|
|
order by match(betreff) against ('+abc' in boolean mode) desc;
|
|
ERROR HY000: The used table type doesn't support FULLTEXT indexes
|
|
(select b.id, b.betreff from t3 b) union
|
|
(select b.id, b.betreff from t3 b)
|
|
order by match(betreff) against ('+abc') desc;
|
|
ERROR HY000: The used table type doesn't support FULLTEXT indexes
|
|
select distinct b.id, b.betreff from t3 b
|
|
order by match(betreff) against ('+abc' in boolean mode) desc;
|
|
id betreff
|
|
select b.id, b.betreff from t3 b group by b.id+1
|
|
order by match(betreff) against ('+abc' in boolean mode) desc;
|
|
id betreff
|
|
drop table t1,t2,t3;
|
|
#
|
|
# Bug#28940361 QUERY AGAINST FULL TEXT INDEX WITH ORDER BY SILENTLY FAILS
|
|
#
|
|
# restart: --sort-buffer-size=32768
|
|
# Test with explicit FTS index
|
|
CREATE TABLE t_fts (
|
|
FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
|
|
c0000 longtext CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
|
|
FULLTEXT KEY i0000 (c0000)
|
|
);
|
|
INSERT INTO t_fts(c0000) VALUES
|
|
('1537001596'), ('1537001596'), ('1537000966'), ('1537000966'), ('1537001075'),
|
|
('1537001075'), ('1537000596'), ('1537000596'), ('1537000633'), ('1537000633'),
|
|
('1537001651'), ('1537001651'), ('1537000604'), ('1537000604'), ('1537000064'),
|
|
('1537000064'), ('1536999311'), ('1536999311'), ('1537001654'), ('1537001654'),
|
|
('1537000338'), ('1537000338'), ('1537000463'), ('1537000463'), ('1537001269'),
|
|
('1537001269'), ('1537001271'), ('1537001271'), ('1537001129'), ('1537001129'),
|
|
('1537001706'), ('1537001706'), ('1537001455'), ('1537001455'), ('1536999158'),
|
|
('1536999158'), ('1536999904'), ('1536999904'), ('1537001553'), ('1537001553'),
|
|
('1537002066'), ('1537002066'), ('1537001451'), ('1537001451'), ('1537000484'),
|
|
('1537000484'), ('1537001365'), ('1537001365'), ('1536999200'), ('1536999200'),
|
|
('1537001994'), ('1537001994'), ('1536999319'), ('1536999319'), ('1537001730'),
|
|
('1537001730'), ('1537001144'), ('1537001144'), ('1537001152'), ('1537001152'),
|
|
('1537001376'), ('1537001376'), ('1537001692'), ('1537001692'), ('1537002082'),
|
|
('1537002082'), ('1537000926'), ('1537000926'), ('1537000606'), ('1537000606');
|
|
SELECT c0000 , c0000 FROM t_fts WHERE
|
|
MATCH( c0000 ) AGAINST ( '15370*' IN BOOLEAN MODE) ORDER BY c0000;
|
|
c0000 c0000
|
|
1537000064 1537000064
|
|
1537000064 1537000064
|
|
1537000338 1537000338
|
|
1537000338 1537000338
|
|
1537000463 1537000463
|
|
1537000463 1537000463
|
|
1537000484 1537000484
|
|
1537000484 1537000484
|
|
1537000596 1537000596
|
|
1537000596 1537000596
|
|
1537000604 1537000604
|
|
1537000604 1537000604
|
|
1537000606 1537000606
|
|
1537000606 1537000606
|
|
1537000633 1537000633
|
|
1537000633 1537000633
|
|
1537000926 1537000926
|
|
1537000926 1537000926
|
|
1537000966 1537000966
|
|
1537000966 1537000966
|
|
1537001075 1537001075
|
|
1537001075 1537001075
|
|
1537001129 1537001129
|
|
1537001129 1537001129
|
|
1537001144 1537001144
|
|
1537001144 1537001144
|
|
1537001152 1537001152
|
|
1537001152 1537001152
|
|
1537001269 1537001269
|
|
1537001269 1537001269
|
|
1537001271 1537001271
|
|
1537001271 1537001271
|
|
1537001365 1537001365
|
|
1537001365 1537001365
|
|
1537001376 1537001376
|
|
1537001376 1537001376
|
|
1537001451 1537001451
|
|
1537001451 1537001451
|
|
1537001455 1537001455
|
|
1537001455 1537001455
|
|
1537001553 1537001553
|
|
1537001553 1537001553
|
|
1537001596 1537001596
|
|
1537001596 1537001596
|
|
1537001651 1537001651
|
|
1537001651 1537001651
|
|
1537001654 1537001654
|
|
1537001654 1537001654
|
|
1537001692 1537001692
|
|
1537001692 1537001692
|
|
1537001706 1537001706
|
|
1537001706 1537001706
|
|
1537001730 1537001730
|
|
1537001730 1537001730
|
|
1537001994 1537001994
|
|
1537001994 1537001994
|
|
1537002066 1537002066
|
|
1537002066 1537002066
|
|
1537002082 1537002082
|
|
1537002082 1537002082
|
|
SELECT c0000 , c0000 FROM t_fts WHERE
|
|
MATCH( c0000 ) AGAINST ( '1537000*' IN BOOLEAN MODE) ORDER BY c0000;
|
|
c0000 c0000
|
|
1537000064 1537000064
|
|
1537000064 1537000064
|
|
1537000338 1537000338
|
|
1537000338 1537000338
|
|
1537000463 1537000463
|
|
1537000463 1537000463
|
|
1537000484 1537000484
|
|
1537000484 1537000484
|
|
1537000596 1537000596
|
|
1537000596 1537000596
|
|
1537000604 1537000604
|
|
1537000604 1537000604
|
|
1537000606 1537000606
|
|
1537000606 1537000606
|
|
1537000633 1537000633
|
|
1537000633 1537000633
|
|
1537000926 1537000926
|
|
1537000926 1537000926
|
|
1537000966 1537000966
|
|
1537000966 1537000966
|
|
DROP TABLE t_fts;
|
|
# Test with implicit FTS index
|
|
CREATE TABLE t_nofts (
|
|
c0000 longtext CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
|
|
FULLTEXT KEY i0000 (c0000)
|
|
);
|
|
INSERT INTO t_nofts(c0000) VALUES
|
|
('1537001596'), ('1537001596'), ('1537000966'), ('1537000966'), ('1537001075'),
|
|
('1537001075'), ('1537000596'), ('1537000596'), ('1537000633'), ('1537000633'),
|
|
('1537001651'), ('1537001651'), ('1537000604'), ('1537000604'), ('1537000064'),
|
|
('1537000064'), ('1536999311'), ('1536999311'), ('1537001654'), ('1537001654'),
|
|
('1537000338'), ('1537000338'), ('1537000463'), ('1537000463'), ('1537001269'),
|
|
('1537001269'), ('1537001271'), ('1537001271'), ('1537001129'), ('1537001129'),
|
|
('1537001706'), ('1537001706'), ('1537001455'), ('1537001455'), ('1536999158'),
|
|
('1536999158'), ('1536999904'), ('1536999904'), ('1537001553'), ('1537001553'),
|
|
('1537002066'), ('1537002066'), ('1537001451'), ('1537001451'), ('1537000484'),
|
|
('1537000484'), ('1537001365'), ('1537001365'), ('1536999200'), ('1536999200'),
|
|
('1537001994'), ('1537001994'), ('1536999319'), ('1536999319'), ('1537001730'),
|
|
('1537001730'), ('1537001144'), ('1537001144'), ('1537001152'), ('1537001152'),
|
|
('1537001376'), ('1537001376'), ('1537001692'), ('1537001692'), ('1537002082'),
|
|
('1537002082'), ('1537000926'), ('1537000926'), ('1537000606'), ('1537000606');
|
|
SELECT c0000 , c0000 FROM t_nofts WHERE
|
|
MATCH( c0000 ) AGAINST ( '15370*' IN BOOLEAN MODE) ORDER BY c0000;
|
|
c0000 c0000
|
|
1537000064 1537000064
|
|
1537000064 1537000064
|
|
1537000338 1537000338
|
|
1537000338 1537000338
|
|
1537000463 1537000463
|
|
1537000463 1537000463
|
|
1537000484 1537000484
|
|
1537000484 1537000484
|
|
1537000596 1537000596
|
|
1537000596 1537000596
|
|
1537000604 1537000604
|
|
1537000604 1537000604
|
|
1537000606 1537000606
|
|
1537000606 1537000606
|
|
1537000633 1537000633
|
|
1537000633 1537000633
|
|
1537000926 1537000926
|
|
1537000926 1537000926
|
|
1537000966 1537000966
|
|
1537000966 1537000966
|
|
1537001075 1537001075
|
|
1537001075 1537001075
|
|
1537001129 1537001129
|
|
1537001129 1537001129
|
|
1537001144 1537001144
|
|
1537001144 1537001144
|
|
1537001152 1537001152
|
|
1537001152 1537001152
|
|
1537001269 1537001269
|
|
1537001269 1537001269
|
|
1537001271 1537001271
|
|
1537001271 1537001271
|
|
1537001365 1537001365
|
|
1537001365 1537001365
|
|
1537001376 1537001376
|
|
1537001376 1537001376
|
|
1537001451 1537001451
|
|
1537001451 1537001451
|
|
1537001455 1537001455
|
|
1537001455 1537001455
|
|
1537001553 1537001553
|
|
1537001553 1537001553
|
|
1537001596 1537001596
|
|
1537001596 1537001596
|
|
1537001651 1537001651
|
|
1537001651 1537001651
|
|
1537001654 1537001654
|
|
1537001654 1537001654
|
|
1537001692 1537001692
|
|
1537001692 1537001692
|
|
1537001706 1537001706
|
|
1537001706 1537001706
|
|
1537001730 1537001730
|
|
1537001730 1537001730
|
|
1537001994 1537001994
|
|
1537001994 1537001994
|
|
1537002066 1537002066
|
|
1537002066 1537002066
|
|
1537002082 1537002082
|
|
1537002082 1537002082
|
|
SELECT c0000 , c0000 FROM t_nofts WHERE
|
|
MATCH( c0000 ) AGAINST ( '1537000*' IN BOOLEAN MODE) ORDER BY c0000;
|
|
c0000 c0000
|
|
1537000064 1537000064
|
|
1537000064 1537000064
|
|
1537000338 1537000338
|
|
1537000338 1537000338
|
|
1537000463 1537000463
|
|
1537000463 1537000463
|
|
1537000484 1537000484
|
|
1537000484 1537000484
|
|
1537000596 1537000596
|
|
1537000596 1537000596
|
|
1537000604 1537000604
|
|
1537000604 1537000604
|
|
1537000606 1537000606
|
|
1537000606 1537000606
|
|
1537000633 1537000633
|
|
1537000633 1537000633
|
|
1537000926 1537000926
|
|
1537000926 1537000926
|
|
1537000966 1537000966
|
|
1537000966 1537000966
|
|
DROP TABLE t_nofts;
|
|
# restart:
|