245 lines
9.3 KiB
Plaintext
245 lines
9.3 KiB
Plaintext
|
|
--disable_warnings
|
|
DROP TABLE IF EXISTS t1,t2,t3;
|
|
--enable_warnings
|
|
|
|
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");
|
|
-- disable_result_log
|
|
ANALYZE TABLE t1;
|
|
-- enable_result_log
|
|
# basic MATCH
|
|
SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE MATCH (message) AGAINST ('steve');
|
|
SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve');
|
|
SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE);
|
|
SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE);
|
|
|
|
# MATCH + ORDER BY (with ft-ranges)
|
|
SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY a;
|
|
SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) ORDER BY a;
|
|
|
|
# MATCH + ORDER BY (with normal ranges) + UNIQUE
|
|
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;
|
|
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;
|
|
|
|
# MATCH + ORDER BY + UNIQUE (const_table)
|
|
SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE a=7 and MATCH (message) AGAINST ('steve') ORDER BY 1;
|
|
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;
|
|
|
|
# ORDER BY MATCH
|
|
# INNODB_FTS: INVESITGATE
|
|
SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) as rel FROM t1 ORDER BY rel;
|
|
SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) as rel FROM t1 ORDER BY rel;
|
|
|
|
#
|
|
# BUG#6635 - test_if_skip_sort_order() thought it can skip filesort
|
|
# for fulltext searches too
|
|
#
|
|
alter table t1 add key m (message);
|
|
-- disable_result_log
|
|
ANALYZE TABLE t1;
|
|
-- enable_result_log
|
|
explain SELECT message FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY message;
|
|
SELECT message FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY message desc;
|
|
|
|
drop table t1;
|
|
|
|
#
|
|
# reused boolean scan bug
|
|
#
|
|
CREATE TABLE t1 (
|
|
a INT AUTO_INCREMENT PRIMARY KEY,
|
|
message CHAR(20),
|
|
FULLTEXT(message)
|
|
) ENGINE = InnoDB;
|
|
INSERT INTO t1 (message) VALUES ("testbug"),("testbug foobar");
|
|
-- disable_result_log
|
|
ANALYZE TABLE t1;
|
|
-- enable_result_log
|
|
SELECT a, MATCH (message) AGAINST ('t* f*' IN BOOLEAN MODE) as rel FROM t1;
|
|
SELECT a, MATCH (message) AGAINST ('t* f*' IN BOOLEAN MODE) as rel FROM t1 ORDER BY rel,a;
|
|
drop table t1;
|
|
|
|
# BUG#11869
|
|
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 ;
|
|
|
|
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 ;
|
|
|
|
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 ;
|
|
|
|
--error ER_TABLENAME_NOT_ALLOWED_HERE
|
|
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 ER_TABLENAME_NOT_ALLOWED_HERE
|
|
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 ER_TABLE_CANT_HANDLE_FT
|
|
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;
|
|
|
|
# BUG#11869 part2: used table type doesn't support FULLTEXT indexes error
|
|
--error ER_TABLE_CANT_HANDLE_FT
|
|
(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 ER_TABLE_CANT_HANDLE_FT
|
|
(select b.id, b.betreff from t3 b) union
|
|
(select b.id, b.betreff from t3 b)
|
|
order by match(betreff) against ('+abc') desc;
|
|
|
|
select distinct b.id, b.betreff from t3 b
|
|
order by match(betreff) against ('+abc' in boolean mode) desc;
|
|
|
|
--source include/turn_off_only_full_group_by.inc
|
|
select b.id, b.betreff from t3 b group by b.id+1
|
|
order by match(betreff) against ('+abc' in boolean mode) desc;
|
|
--source include/restore_sql_mode_after_turn_off_only_full_group_by.inc
|
|
|
|
drop table t1,t2,t3;
|
|
|
|
# End of 4.1 tests
|
|
|
|
--echo #
|
|
--echo # Bug#28940361 QUERY AGAINST FULL TEXT INDEX WITH ORDER BY SILENTLY FAILS
|
|
--echo #
|
|
|
|
# Restart the server with a small sort buffer
|
|
--let $restart_parameters = restart: --sort-buffer-size=32768
|
|
--source include/restart_mysqld.inc
|
|
|
|
--echo # Test with explicit FTS index
|
|
--disable_warnings
|
|
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)
|
|
);
|
|
--enable_warnings
|
|
|
|
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;
|
|
SELECT c0000 , c0000 FROM t_fts WHERE
|
|
MATCH( c0000 ) AGAINST ( '1537000*' IN BOOLEAN MODE) ORDER BY c0000;
|
|
|
|
DROP TABLE t_fts;
|
|
|
|
--echo # Test with implicit FTS index
|
|
--disable_warnings
|
|
CREATE TABLE t_nofts (
|
|
c0000 longtext CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
|
|
FULLTEXT KEY i0000 (c0000)
|
|
);
|
|
--enable_warnings
|
|
|
|
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;
|
|
SELECT c0000 , c0000 FROM t_nofts WHERE
|
|
MATCH( c0000 ) AGAINST ( '1537000*' IN BOOLEAN MODE) ORDER BY c0000;
|
|
|
|
DROP TABLE t_nofts;
|
|
|
|
# Restart server with default settings
|
|
--let $restart_parameters = restart:
|
|
--source include/restart_mysqld.inc
|