Debian Codegraph @

mysql-test/suite/innodb_fts/r/fulltext_order_by.result

raw
1 DROP TABLE IF EXISTS t1,t2,t3;
2 CREATE TABLE t1 (
3 a INT AUTO_INCREMENT PRIMARY KEY,
4 message CHAR(20),
5 FULLTEXT(message)
6 ) ENGINE = InnoDB comment = 'original testcase by [email protected]';
7 INSERT INTO t1 (message) VALUES ("Testing"),("table"),("testbug"),
8 ("steve"),("is"),("cool"),("steve is cool");
9 ANALYZE TABLE t1;
10 SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE MATCH (message) AGAINST ('steve');
11 a FORMAT(MATCH (message) AGAINST ('steve'),6)
12 4 0.296010
13 7 0.296010
14 SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve');
15 a MATCH (message) AGAINST ('steve' IN BOOLEAN MODE)
16 4 0.2960100471973419
17 7 0.2960100471973419
18 SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE);
19 a FORMAT(MATCH (message) AGAINST ('steve'),6)
20 4 0.296010
21 7 0.296010
22 SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE);
23 a MATCH (message) AGAINST ('steve' IN BOOLEAN MODE)
24 4 0.2960100471973419
25 7 0.2960100471973419
26 SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY a;
27 a FORMAT(MATCH (message) AGAINST ('steve'),6)
28 4 0.296010
29 7 0.296010
30 SELECT a, MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) FROM t1 WHERE MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) ORDER BY a;
31 a MATCH (message) AGAINST ('steve' IN BOOLEAN MODE)
32 4 0.2960100471973419
33 7 0.2960100471973419
34 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;
35 a FORMAT(MATCH (message) AGAINST ('steve'),6)
36 7 0.296010
37 4 0.296010
38 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;
39 a MATCH (message) AGAINST ('steve' IN BOOLEAN MODE)
40 7 0.2960100471973419
41 4 0.2960100471973419
42 SELECT a, FORMAT(MATCH (message) AGAINST ('steve'),6) FROM t1 WHERE a=7 and MATCH (message) AGAINST ('steve') ORDER BY 1;
43 a FORMAT(MATCH (message) AGAINST ('steve'),6)
44 7 0.296010
45 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;
46 a MATCH (message) AGAINST ('steve' IN BOOLEAN MODE)
47 7 0.2960100471973419
48 SELECT if(a in (4,7),2,1), FORMAT(MATCH (message) AGAINST ('steve'),6) as rel FROM t1 ORDER BY rel;
49 if(a in (4,7),2,1) rel
50 1 0.000000
51 1 0.000000
52 1 0.000000
53 1 0.000000
54 1 0.000000
55 2 0.296010
56 2 0.296010
57 SELECT if(a in (4,7),2,1), MATCH (message) AGAINST ('steve' IN BOOLEAN MODE) as rel FROM t1 ORDER BY rel;
58 if(a in (4,7),2,1) rel
59 1 0
60 1 0
61 1 0
62 1 0
63 1 0
64 2 0.2960100471973419
65 2 0.2960100471973419
66 alter table t1 add key m (message);
67 ANALYZE TABLE t1;
68 explain SELECT message FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY message;
69 id select_type table type possible_keys key key_len ref rows Extra
70 1 SIMPLE t1 fulltext message message 0 1 Using where; Using filesort
71 SELECT message FROM t1 WHERE MATCH (message) AGAINST ('steve') ORDER BY message desc;
72 message
73 steve is cool
74 steve
75 drop table t1;
76 CREATE TABLE t1 (
77 a INT AUTO_INCREMENT PRIMARY KEY,
78 message CHAR(20),
79 FULLTEXT(message)
80 ) ENGINE = InnoDB;
81 INSERT INTO t1 (message) VALUES ("testbug"),("testbug foobar");
82 ANALYZE TABLE t1;
83 SELECT a, MATCH (message) AGAINST ('t* f*' IN BOOLEAN MODE) as rel FROM t1;
84 a rel
85 1 0.000000001885928302414186
86 2 0.0906190574169159
87 SELECT a, MATCH (message) AGAINST ('t* f*' IN BOOLEAN MODE) as rel FROM t1 ORDER BY rel,a;
88 a rel
89 1 0.000000001885928302414186
90 2 0.0906190574169159
91 drop table t1;
92 CREATE TABLE t1 (
93 id int(11) NOT NULL auto_increment,
94 thread int(11) NOT NULL default '0',
95 beitrag longtext NOT NULL,
96 PRIMARY KEY (id),
97 KEY thread (thread),
98 FULLTEXT KEY beitrag (beitrag)
99 ) ENGINE =InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=7923 ;
100 CREATE TABLE t2 (
101 id int(11) NOT NULL auto_increment,
102 text varchar(100) NOT NULL default '',
103 PRIMARY KEY (id),
104 KEY text (text)
105 ) ENGINE = InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=63 ;
106 CREATE TABLE t3 (
107 id int(11) NOT NULL auto_increment,
108 forum int(11) NOT NULL default '0',
109 betreff varchar(70) NOT NULL default '',
110 PRIMARY KEY (id),
111 KEY forum (forum),
112 FULLTEXT KEY betreff (betreff)
113 ) ENGINE = InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=996 ;
114 select a.text, b.id, b.betreff
115 from
116 t2 a inner join t3 b on a.id = b.forum inner join
117 t1 c on b.id = c.thread
118 where
119 match(b.betreff) against ('+abc' in boolean mode)
120 group by a.text, b.id, b.betreff
121 union
122 select a.text, b.id, b.betreff
123 from
124 t2 a inner join t3 b on a.id = b.forum inner join
125 t1 c on b.id = c.thread
126 where
127 match(c.beitrag) against ('+abc' in boolean mode)
128 group by
129 a.text, b.id, b.betreff
130 order by
131 match(b.betreff) against ('+abc' in boolean mode) desc;
132 ERROR 42000: Table 'b' from one of the SELECTs cannot be used in ORDER BY
133 select a.text, b.id, b.betreff
134 from
135 t2 a inner join t3 b on a.id = b.forum inner join
136 t1 c on b.id = c.thread
137 where
138 match(b.betreff) against ('+abc' in boolean mode)
139 union
140 select a.text, b.id, b.betreff
141 from
142 t2 a inner join t3 b on a.id = b.forum inner join
143 t1 c on b.id = c.thread
144 where
145 match(c.beitrag) against ('+abc' in boolean mode)
146 order by
147 match(b.betreff) against ('+abc' in boolean mode) desc;
148 ERROR 42000: Table 'b' from one of the SELECTs cannot be used in ORDER BY
149 select a.text, b.id, b.betreff
150 from
151 t2 a inner join t3 b on a.id = b.forum inner join
152 t1 c on b.id = c.thread
153 where
154 match(b.betreff) against ('+abc' in boolean mode)
155 union
156 select a.text, b.id, b.betreff
157 from
158 t2 a inner join t3 b on a.id = b.forum inner join
159 t1 c on b.id = c.thread
160 where
161 match(c.beitrag) against ('+abc' in boolean mode)
162 order by
163 match(betreff) against ('+abc' in boolean mode) desc;
164 text id betreff
165 (select b.id, b.betreff from t3 b) union
166 (select b.id, b.betreff from t3 b)
167 order by match(betreff) against ('+abc' in boolean mode) desc;
168 id betreff
169 (select b.id, b.betreff from t3 b) union
170 (select b.id, b.betreff from t3 b)
171 order by match(betreff) against ('+abc') desc;
172 ERROR HY000: Can't find FULLTEXT index matching the column list
173 select distinct b.id, b.betreff from t3 b
174 order by match(betreff) against ('+abc' in boolean mode) desc;
175 id betreff
176 select b.id, b.betreff from t3 b group by b.id+1
177 order by match(betreff) against ('+abc' in boolean mode) desc;
178 id betreff
179 drop table t1,t2,t3;