187
190
DROP TABLE t1,t2;
192
# Bug #19950568 WRONG RELEVANCE RANKING FOR INNODB FULL TEXT
193
# SEARCHES UNDER CERTAIN CONDITIONS
196
id int(11) NOT NULL AUTO_INCREMENT,
197
first_name VARCHAR(50) NOT NULL,
198
last_name VARCHAR(50) NOT NULL,
200
UNIQUE KEY idx_1 (first_name, last_name),
201
FULLTEXT KEY `idx_2` (first_name)
203
INSERT INTO t1 (id, first_name, last_name) VALUES
204
(1, 'Bart', 'Simpson'),
205
(2, 'Homer', 'Simpson'),
206
(3, 'Marge', 'Simpson'),
207
(4, 'Lisa', 'Simpson'),
208
(5, 'Maggie', 'Simpson'),
209
(6, 'Ned', 'Flanders'),
210
(7, 'Nelson', 'Muntz');
212
Table Op Msg_type Msg_text
213
test.t1 analyze status OK
214
EXPLAIN SELECT id, first_name, last_name, MATCH(first_name) AGAINST('Homer' IN
215
BOOLEAN MODE) AS score FROM t1;
216
id select_type table type possible_keys key key_len ref rows Extra
217
1 SIMPLE t1 ALL NULL NULL NULL NULL 7 NULL
218
SELECT id, first_name, last_name, MATCH(first_name) AGAINST('Homer' IN
219
BOOLEAN MODE) AS score FROM t1;
220
id first_name last_name score
222
2 Homer Simpson 0.7141907215118408
228
EXPLAIN SELECT id, first_name, last_name, MATCH(first_name) AGAINST('Homer' IN
229
BOOLEAN MODE) AS score FROM t1 ORDER BY id;
230
id select_type table type possible_keys key key_len ref rows Extra
231
1 SIMPLE t1 index NULL PRIMARY 4 NULL 7 NULL
232
SELECT id, first_name, last_name, MATCH(first_name) AGAINST('Homer' IN
233
BOOLEAN MODE) AS score FROM t1 ORDER BY id;
234
id first_name last_name score
236
2 Homer Simpson 0.7141907215118408
244
FTS_DOC_ID BIGINT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
245
id int(11) NOT NULL ,
246
first_name VARCHAR(50) NOT NULL,
247
last_name VARCHAR(50) NOT NULL,
249
UNIQUE KEY (FTS_DOC_ID),
250
UNIQUE KEY idx_1 (first_name, last_name),
251
FULLTEXT KEY `idx_2` (first_name)
253
INSERT INTO t1 (id, first_name, last_name) VALUES
254
(1, 'Bart', 'Simpson'),
255
(2, 'Homer', 'Simpson'),
256
(3, 'Marge', 'Simpson'),
257
(4, 'Lisa', 'Simpson'),
258
(5, 'Maggie', 'Simpson'),
259
(6, 'Ned', 'Flanders'),
260
(7, 'Nelson', 'Muntz');
262
Table Op Msg_type Msg_text
263
test.t1 analyze status OK
264
EXPLAIN SELECT id, first_name, last_name, MATCH(first_name) AGAINST('Homer' IN
265
BOOLEAN MODE) AS score FROM t1;
266
id select_type table type possible_keys key key_len ref rows Extra
267
1 SIMPLE t1 ALL NULL NULL NULL NULL 7 NULL
268
SELECT id, first_name, last_name, MATCH(first_name) AGAINST('Homer' IN
269
BOOLEAN MODE) AS score FROM t1;
270
id first_name last_name score
272
2 Homer Simpson 0.7141907215118408
278
EXPLAIN SELECT id, first_name, last_name, MATCH(first_name) AGAINST('Homer' IN
279
BOOLEAN MODE) AS score FROM t1 ORDER BY fts_doc_id;
280
id select_type table type possible_keys key key_len ref rows Extra
281
1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using filesort
282
SELECT id, first_name, last_name, MATCH(first_name) AGAINST('Homer' IN
283
BOOLEAN MODE) AS score FROM t1 ORDER BY fts_doc_id;
284
id first_name last_name score
286
2 Homer Simpson 0.7141907215118408
293
# Adding test case for Bug#20261601
294
CREATE TABLE t1(a INT PRIMARY KEY)ENGINE=INNODB;
295
INSERT INTO t1 VALUES(1),(2);
296
SELECT (SELECT MATCH(`a`)AGAINST('1') FROM t1) FROM t1;
297
ERROR HY000: Can't find FULLTEXT index matching the column list
298
SELECT 1, a IN (SELECT a FROM t1) FROM t1;
299
1 a IN (SELECT a FROM t1)
303
# End of test for Bug#19950568 and Bug#20261601