Gecko [ www.jdcpatala.org ]

Name	Size	Permission
basic.result	12.32 KB	-rw-r--r--
bug_32831765.result	6.29 KB	-rw-r--r--
compatibility.result	9.81 KB	-rw-r--r--
compatibility_win.result	9.81 KB	-rw-r--r--
ddl.result	10.34 KB	-rw-r--r--
fic.result	8.01 KB	-rw-r--r--
foreign_key_check.result	808 B	-rw-r--r--
foreign_key_update.result	663 B	-rw-r--r--
fulltext.result	28.64 KB	-rw-r--r--
fulltext2.result	8.45 KB	-rw-r--r--
fulltext3.result	1.79 KB	-rw-r--r--
fulltext_cache.result	2.77 KB	-rw-r--r--
fulltext_distinct.result	1.45 KB	-rw-r--r--
fulltext_left_join.result	4.27 KB	-rw-r--r--
fulltext_misc.result	8.19 KB	-rw-r--r--
fulltext_multi.result	965 B	-rw-r--r--
fulltext_order_by.result	6.42 KB	-rw-r--r--
fulltext_table_evict.result	688 B	-rw-r--r--
fulltext_update.result	1.12 KB	-rw-r--r--
fulltext_var.result	1.33 KB	-rw-r--r--
index_table.result	6.84 KB	-rw-r--r--
innobase_drop_fts_index_table....	209 B	-rw-r--r--
large_records.result	9.04 KB	-rw-r--r--
limit_union.result	6 KB	-rw-r--r--
mecab_sjis.result	6.62 KB	-rw-r--r--
mecab_ujis.result	6.62 KB	-rw-r--r--
mecab_utf8.result	6.65 KB	-rw-r--r--
misc.result	62.97 KB	-rw-r--r--
misc_1.result	38.56 KB	-rw-r--r--
misc_debug.result	3.72 KB	-rw-r--r--
multiple_index.result	9.16 KB	-rw-r--r--
ngram.result	38.03 KB	-rw-r--r--
ngram_1.result	395.4 KB	-rw-r--r--
ngram_2.result	5.11 KB	-rw-r--r--
ngram_debug.result	8.63 KB	-rw-r--r--
opt.result	68.87 KB	-rw-r--r--
phrase.result	2.1 KB	-rw-r--r--
phrase_clear_no_match.result	2.68 KB	-rw-r--r--
plugin.result	7.52 KB	-rw-r--r--
plugin_1.result	14.8 KB	-rw-r--r--
plugin_debug.result	13.39 KB	-rw-r--r--
proximity.result	7.33 KB	-rw-r--r--
result_cache_limit.result	1.48 KB	-rw-r--r--
savepoint.result	8.69 KB	-rw-r--r--
stopword.result	31.83 KB	-rw-r--r--
stopword_charset.result	10.47 KB	-rw-r--r--
subexpr.result	3.88 KB	-rw-r--r--
sync.result	5.56 KB	-rw-r--r--
sync_block.result	2.64 KB	-rw-r--r--
sync_ddl.result	4.61 KB	-rw-r--r--
tablespace_location.result	43.89 KB	-rw-r--r--
tablespace_location_error.resu...	16.93 KB	-rw-r--r--
transaction.result	42.15 KB	-rw-r--r--
truncate.result	1.22 KB	-rw-r--r--

Code Editor : fulltext_order_by.result

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 ;
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 ;
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 field list
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 field list
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;
text	id	betreff
(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;
id	betreff
(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: Can't find FULLTEXT index matching the column list
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;

${this.title}

Code Editor : fulltext_order_by.result