Debian Codegraph @

mysql-test/suite/innodb_fts/t/fulltext_order_by.test

raw
1
2 --source include/have_innodb.inc
3 --source include/maybe_versioning.inc
4
5 --disable_warnings
6 DROP TABLE IF EXISTS t1,t2,t3;
7 --enable_warnings
8
9 CREATE TABLE t1 (
10 a INT AUTO_INCREMENT PRIMARY KEY,
11 message CHAR(20),
12 FULLTEXT(message)
13 ) ENGINE = InnoDB comment = 'original testcase by [email protected]';
14 INSERT INTO t1 (message) VALUES ("Testing"),("table"),("testbug"),
15 ("steve"),("is"),("cool"),("steve is cool");
16 -- disable_result_log
17 ANALYZE TABLE t1;
18 -- enable_result_log
19 # basic MATCH
20 SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE MATCH (message) AGAINST ('steve');
21 SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve');
22 SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE);
23 SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE);
24
25 # MATCH + ORDER BY (with ft-ranges)
26 SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY a;
27 SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) ORDER BY a;
28
29 # MATCH + ORDER BY (with normal ranges) + UNIQUE
30 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;
31 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;
32
33 # MATCH + ORDER BY + UNIQUE (const_table)
34 SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE a=7 and MATCH (message) AGAINST ('steve') ORDER BY 1;
35 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;
36
37 # ORDER BY MATCH
38 # INNODB_FTS: INVESITGATE
39 SELECT if(a in (4,7),2,1), FORMAT(MATCH (message) AGAINST ('steve'),6) as rel FROM t1 ORDER BY rel;
40 SELECT if(a in (4,7),2,1), MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) as rel FROM t1 ORDER BY rel;
41
42 #
43 # BUG#6635 - test_if_skip_sort_order() thought it can skip filesort
44 # for fulltext searches too
45 #
46 alter table t1 add key m (message);
47 -- disable_result_log
48 ANALYZE TABLE t1;
49 -- enable_result_log
50 explain SELECT message FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY message;
51 SELECT message FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY message desc;
52
53 drop table t1;
54
55 #
56 # reused boolean scan bug
57 #
58 CREATE TABLE t1 (
59 a INT AUTO_INCREMENT PRIMARY KEY,
60 message CHAR(20),
61 FULLTEXT(message)
62 ) ENGINE = InnoDB;
63 INSERT INTO t1 (message) VALUES ("testbug"),("testbug foobar");
64 -- disable_result_log
65 ANALYZE TABLE t1;
66 -- enable_result_log
67 SELECT a, MATCH (message) AGAINST ('t* f*' IN BOOLEAN MODE) as rel FROM t1;
68 SELECT a, MATCH (message) AGAINST ('t* f*' IN BOOLEAN MODE) as rel FROM t1 ORDER BY rel,a;
69 drop table t1;
70
71 # BUG#11869
72 CREATE TABLE t1 (
73 id int(11) NOT NULL auto_increment,
74 thread int(11) NOT NULL default '0',
75 beitrag longtext NOT NULL,
76 PRIMARY KEY (id),
77 KEY thread (thread),
78 FULLTEXT KEY beitrag (beitrag)
79 ) ENGINE =InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=7923 ;
80
81 CREATE TABLE t2 (
82 id int(11) NOT NULL auto_increment,
83 text varchar(100) NOT NULL default '',
84 PRIMARY KEY (id),
85 KEY text (text)
86 ) ENGINE = InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=63 ;
87
88 CREATE TABLE t3 (
89 id int(11) NOT NULL auto_increment,
90 forum int(11) NOT NULL default '0',
91 betreff varchar(70) NOT NULL default '',
92 PRIMARY KEY (id),
93 KEY forum (forum),
94 FULLTEXT KEY betreff (betreff)
95 ) ENGINE = InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=996 ;
96
97 --error ER_TABLENAME_NOT_ALLOWED_HERE
98 select a.text, b.id, b.betreff
99 from
100 t2 a inner join t3 b on a.id = b.forum inner join
101 t1 c on b.id = c.thread
102 where
103 match(b.betreff) against ('+abc' in boolean mode)
104 group by a.text, b.id, b.betreff
105 union
106 select a.text, b.id, b.betreff
107 from
108 t2 a inner join t3 b on a.id = b.forum inner join
109 t1 c on b.id = c.thread
110 where
111 match(c.beitrag) against ('+abc' in boolean mode)
112 group by
113 a.text, b.id, b.betreff
114 order by
115 match(b.betreff) against ('+abc' in boolean mode) desc;
116
117 --error ER_TABLENAME_NOT_ALLOWED_HERE
118 select a.text, b.id, b.betreff
119 from
120 t2 a inner join t3 b on a.id = b.forum inner join
121 t1 c on b.id = c.thread
122 where
123 match(b.betreff) against ('+abc' in boolean mode)
124 union
125 select a.text, b.id, b.betreff
126 from
127 t2 a inner join t3 b on a.id = b.forum inner join
128 t1 c on b.id = c.thread
129 where
130 match(c.beitrag) against ('+abc' in boolean mode)
131 order by
132 match(b.betreff) against ('+abc' in boolean mode) desc;
133
134 select a.text, b.id, b.betreff
135 from
136 t2 a inner join t3 b on a.id = b.forum inner join
137 t1 c on b.id = c.thread
138 where
139 match(b.betreff) against ('+abc' in boolean mode)
140 union
141 select a.text, b.id, b.betreff
142 from
143 t2 a inner join t3 b on a.id = b.forum inner join
144 t1 c on b.id = c.thread
145 where
146 match(c.beitrag) against ('+abc' in boolean mode)
147 order by
148 match(betreff) against ('+abc' in boolean mode) desc;
149
150 # BUG#11869 part2: used table type doesn't support FULLTEXT indexes error
151 (select b.id, b.betreff from t3 b) union
152 (select b.id, b.betreff from t3 b)
153 order by match(betreff) against ('+abc' in boolean mode) desc;
154
155 --error ER_FT_MATCHING_KEY_NOT_FOUND
156 (select b.id, b.betreff from t3 b) union
157 (select b.id, b.betreff from t3 b)
158 order by match(betreff) against ('+abc') desc;
159
160 select distinct b.id, b.betreff from t3 b
161 order by match(betreff) against ('+abc' in boolean mode) desc;
162
163 select b.id, b.betreff from t3 b group by b.id+1
164 order by match(betreff) against ('+abc' in boolean mode) desc;
165
166 drop table t1,t2,t3;
167
168 # End of 4.1 tests