~drizzle-trunk/drizzle/development

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
drop table if exists t1,t2,t3;
CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b));
INSERT INTO t1 VALUES('MySQL has now support', 'for full-text search'),
('Full-text indexes', 'are called collections'),
('Only MyISAM tables','support collections'),
('Function MATCH ... AGAINST()','is used to do a search'),
('Full-text search in MySQL', 'implements vector space model');
SHOW INDEX FROM t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_Comment
t1	1	a	1	a	NULL	NULL	NULL	NULL	YES	FULLTEXT		
t1	1	a	2	b	NULL	NULL	NULL	NULL	YES	FULLTEXT		
select * from t1 where MATCH(a,b) AGAINST ("collections");
a	b
Only MyISAM tables	support collections
Full-text indexes	are called collections
explain extended select * from t1 where MATCH(a,b) AGAINST ("collections");
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	fulltext	a	a	0		1	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (match `test`.`t1`.`a`,`test`.`t1`.`b` against ('collections'))
select * from t1 where MATCH(a,b) AGAINST ("indexes");
a	b
Full-text indexes	are called collections
select * from t1 where MATCH(a,b) AGAINST ("indexes collections");
a	b
Full-text indexes	are called collections
Only MyISAM tables	support collections
select * from t1 where MATCH(a,b) AGAINST ("only");
a	b
select * from t1 where MATCH(a,b) AGAINST ("collections" WITH QUERY EXPANSION);
a	b
Only MyISAM tables	support collections
Full-text indexes	are called collections
MySQL has now support	for full-text search
select * from t1 where MATCH(a,b) AGAINST ("indexes" WITH QUERY EXPANSION);
a	b
Full-text indexes	are called collections
Only MyISAM tables	support collections
select * from t1 where MATCH(a,b) AGAINST ("indexes collections" WITH QUERY EXPANSION);
a	b
Full-text indexes	are called collections
Only MyISAM tables	support collections
MySQL has now support	for full-text search
select * from t1 where MATCH(a,b) AGAINST ("indexes" IN NATURAL LANGUAGE MODE);
a	b
Full-text indexes	are called collections
select * from t1 where MATCH(a,b) AGAINST ("indexes" IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION);
a	b
Full-text indexes	are called collections
Only MyISAM tables	support collections
select * from t1 where MATCH(a,b) AGAINST ("indexes" IN BOOLEAN MODE WITH QUERY EXPANSION);
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'QUERY EXPANSION)' at line 1
explain select * from t1 where MATCH(a,b) AGAINST ("collections");
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	a	a	0		1	Using where
explain select * from t1 where MATCH(a,b) AGAINST ("collections")>0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	a	a	0		1	Using where
explain select * from t1 where MATCH(a,b) AGAINST ("collections")>1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	a	a	0		1	Using where
explain select * from t1 where MATCH(a,b) AGAINST ("collections")>=0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	Using where
explain select * from t1 where MATCH(a,b) AGAINST ("collections")>=1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	a	a	0		1	Using where
explain select * from t1 where 0<MATCH(a,b) AGAINST ("collections");
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	a	a	0		1	Using where
explain select * from t1 where 1<MATCH(a,b) AGAINST ("collections");
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	a	a	0		1	Using where
explain select * from t1 where 0<=MATCH(a,b) AGAINST ("collections");
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	Using where
explain select * from t1 where 1<=MATCH(a,b) AGAINST ("collections");
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	a	a	0		1	Using where
explain select * from t1 where MATCH(a,b) AGAINST ("collections")>0 and a like '%ll%';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	a	a	0		1	Using where
select * from t1 where MATCH(a,b) AGAINST("support -collections" IN BOOLEAN MODE);
a	b
MySQL has now support	for full-text search
explain extended select * from t1 where MATCH(a,b) AGAINST("support -collections" IN BOOLEAN MODE);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	fulltext	a	a	0		1	100.00	Using where
Warnings:
Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (match `test`.`t1`.`a`,`test`.`t1`.`b` against ('support -collections' in boolean mode))
select * from t1 where MATCH(a,b) AGAINST("support  collections" IN BOOLEAN MODE);
a	b
MySQL has now support	for full-text search
Full-text indexes	are called collections
Only MyISAM tables	support collections
select * from t1 where MATCH(a,b) AGAINST("support +collections" IN BOOLEAN MODE);
a	b
Full-text indexes	are called collections
Only MyISAM tables	support collections
select * from t1 where MATCH(a,b) AGAINST("sear*" IN BOOLEAN MODE);
a	b
MySQL has now support	for full-text search
Function MATCH ... AGAINST()	is used to do a search
Full-text search in MySQL	implements vector space model
select * from t1 where MATCH(a,b) AGAINST("+support +collections" IN BOOLEAN MODE);
a	b
Only MyISAM tables	support collections
select * from t1 where MATCH(a,b) AGAINST("+search" IN BOOLEAN MODE);
a	b
MySQL has now support	for full-text search
Function MATCH ... AGAINST()	is used to do a search
Full-text search in MySQL	implements vector space model
select * from t1 where MATCH(a,b) AGAINST("+search +(support vector)" IN BOOLEAN MODE);
a	b
MySQL has now support	for full-text search
Full-text search in MySQL	implements vector space model
select * from t1 where MATCH(a,b) AGAINST("+search -(support vector)" IN BOOLEAN MODE);
a	b
Function MATCH ... AGAINST()	is used to do a search
select *, MATCH(a,b) AGAINST("support collections" IN BOOLEAN MODE) as x from t1;
a	b	x
MySQL has now support	for full-text search	1
Full-text indexes	are called collections	1
Only MyISAM tables	support collections	2
Function MATCH ... AGAINST()	is used to do a search	0
Full-text search in MySQL	implements vector space model	0
select *, MATCH(a,b) AGAINST("collections support" IN BOOLEAN MODE) as x from t1;
a	b	x
MySQL has now support	for full-text search	1
Full-text indexes	are called collections	1
Only MyISAM tables	support collections	2
Function MATCH ... AGAINST()	is used to do a search	0
Full-text search in MySQL	implements vector space model	0
select * from t1 where MATCH a,b AGAINST ("+call* +coll*" IN BOOLEAN MODE);
a	b
Full-text indexes	are called collections
select * from t1 where MATCH a,b AGAINST ('"support now"' IN BOOLEAN MODE);
a	b
select * from t1 where MATCH a,b AGAINST ('"Now sUPPort"' IN BOOLEAN MODE);
a	b
MySQL has now support	for full-text search
select * from t1 where MATCH a,b AGAINST ('"now   support"' IN BOOLEAN MODE);
a	b
MySQL has now support	for full-text search
select * from t1 where MATCH a,b AGAINST ('"text search"  "now support"' IN BOOLEAN MODE);
a	b
MySQL has now support	for full-text search
Full-text search in MySQL	implements vector space model
select * from t1 where MATCH a,b AGAINST ('"text search" -"now support"' IN BOOLEAN MODE);
a	b
Full-text search in MySQL	implements vector space model
select * from t1 where MATCH a,b AGAINST ('"text search" +"now support"' IN BOOLEAN MODE);
a	b
MySQL has now support	for full-text search
select * from t1 where MATCH a,b AGAINST ('"text i"' IN BOOLEAN MODE);
a	b
select * from t1 where MATCH a,b AGAINST ('"xt indexes"' IN BOOLEAN MODE);
a	b
select * from t1 where MATCH a,b AGAINST ('+(support collections) +foobar*' IN BOOLEAN MODE);
a	b
select * from t1 where MATCH a,b AGAINST ('+(+(support collections)) +foobar*' IN BOOLEAN MODE);
a	b
select * from t1 where MATCH a,b AGAINST ('+collections -supp* -foobar*' IN BOOLEAN MODE);
a	b
Full-text indexes	are called collections
select * from t1 where MATCH a,b AGAINST('"space model' IN BOOLEAN MODE);
a	b
Full-text search in MySQL	implements vector space model
select * from t1 where MATCH a AGAINST ("search" IN BOOLEAN MODE);
a	b
Full-text search in MySQL	implements vector space model
select * from t1 where MATCH b AGAINST ("sear*" IN BOOLEAN MODE);
a	b
MySQL has now support	for full-text search
Function MATCH ... AGAINST()	is used to do a search
select * from t1 where MATCH(a,b) AGAINST ("collections") UNION ALL select * from t1 where MATCH(a,b) AGAINST ("indexes");
a	b
Only MyISAM tables	support collections
Full-text indexes	are called collections
Full-text indexes	are called collections
delete from t1 where a like "MySQL%";
update t1 set a='some test foobar' where MATCH a,b AGAINST ('model');
delete from t1 where MATCH(a,b) AGAINST ("indexes");
select * from t1;
a	b
Only MyISAM tables	support collections
Function MATCH ... AGAINST()	is used to do a search
some test foobar	implements vector space model
drop table t1;
create table t1 (a varchar(200) not null, fulltext (a));
insert t1 values ("aaa10 bbb20"), ("aaa20 bbb15"), ("aaa30 bbb10");
select * from t1 where match a against ("+aaa* +bbb*" in boolean mode);
a
aaa30 bbb10
aaa20 bbb15
aaa10 bbb20
select * from t1 where match a against ("+aaa* +bbb1*" in boolean mode);
a
aaa30 bbb10
aaa20 bbb15
select * from t1 where match a against ("+aaa* +ccc*" in boolean mode);
a
select * from t1 where match a against ("+aaa10 +(bbb*)" in boolean mode);
a
aaa10 bbb20
select * from t1 where match a against ("+(+aaa* +bbb1*)" in boolean mode);
a
aaa30 bbb10
aaa20 bbb15
select * from t1 where match a against ("(+aaa* +bbb1*)" in boolean mode);
a
aaa30 bbb10
aaa20 bbb15
drop table t1;
CREATE TABLE t1 (
id int(11),
ticket int(11),
KEY ti (id),
KEY tit (ticket)
);
INSERT INTO t1 VALUES (2,3),(1,2);
CREATE TABLE t2 (
ticket int(11),
inhalt text,
KEY tig (ticket),
fulltext index tix (inhalt)
);
INSERT INTO t2 VALUES (1,'foo'),(2,'bar'),(3,'foobar');
select t1.id FROM t2 as ttxt,t1,t1 as ticket2
WHERE ticket2.id = ttxt.ticket AND t1.id = ticket2.ticket and
match(ttxt.inhalt) against ('foobar');
id
select ticket2.id FROM t2 as ttxt,t2 INNER JOIN t1 as ticket2 ON
ticket2.id = t2.ticket
WHERE ticket2.id = ticket2.ticket and match(ttxt.inhalt) against ('foobar');
id
INSERT INTO t1 VALUES (3,3);
select ticket2.id FROM t2 as ttxt,t2
INNER JOIN t1 as ticket2 ON ticket2.id = t2.ticket
WHERE ticket2.id = ticket2.ticket and
match(ttxt.inhalt) against ('foobar');
id
3
show keys from t2;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_Comment
t2	1	tig	1	ticket	A	NULL	NULL	NULL	YES	BTREE		
t2	1	tix	1	inhalt	NULL	NULL	NULL	NULL	YES	FULLTEXT		
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `ticket` int(11) DEFAULT NULL,
  `inhalt` text,
  KEY `tig` (`ticket`),
  FULLTEXT KEY `tix` (`inhalt`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select * from t2 where MATCH inhalt AGAINST (NULL);
ticket	inhalt
select * from t2 where  MATCH inhalt AGAINST ('foobar');
ticket	inhalt
3	foobar
select * from t2 having MATCH inhalt AGAINST ('foobar');
ticket	inhalt
3	foobar
CREATE TABLE t3 (t int(11),i text,fulltext tix (t,i));
ERROR HY000: Column 't' cannot be part of FULLTEXT index
CREATE TABLE t3 (t int(11),i text,
j varchar(200) CHARACTER SET latin2,
fulltext tix (i,j));
ERROR HY000: Column 'j' cannot be part of FULLTEXT index
CREATE TABLE t3 (
ticket int(11),
inhalt text,
KEY tig (ticket),
fulltext index tix (inhalt)
);
select * from t2 where MATCH inhalt AGAINST (t2.inhalt);
ERROR HY000: Incorrect arguments to AGAINST
select * from t2 where MATCH ticket AGAINST ('foobar');
ERROR HY000: Can't find FULLTEXT index matching the column list
select * from t2,t3 where MATCH (t2.inhalt,t3.inhalt) AGAINST ('foobar');
ERROR HY000: Incorrect arguments to MATCH
drop table t1,t2,t3;
CREATE TABLE t1 (
id int(11)  auto_increment,
title varchar(100)  default '',
PRIMARY KEY  (id),
KEY ind5 (title)
) ENGINE=MyISAM;
CREATE FULLTEXT INDEX ft1 ON t1(title);
insert into t1 (title) values ('this is a test');
select * from t1 where match title against ('test' in boolean mode);
id	title
1	this is a test
update t1 set title='this is A test' where id=1;
check table t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
update t1 set title='this test once revealed a bug' where id=1;
select * from t1;
id	title
1	this test once revealed a bug
update t1 set title=NULL where id=1;
drop table t1;
CREATE TABLE t1 (a int(11), b text, FULLTEXT KEY (b)) ENGINE=MyISAM;
insert into t1 values (1,"I wonder why the fulltext index doesnt work?");
SELECT * from t1 where MATCH (b) AGAINST ('apples');
a	b
insert into t1 values (2,"fullaaa fullzzz");
select * from t1 where match b against ('full*' in boolean mode);
a	b
2	fullaaa fullzzz
1	I wonder why the fulltext index doesnt work?
drop table t1;
CREATE TABLE t1 ( id int(11) NOT NULL auto_increment primary key, mytext text NOT NULL, FULLTEXT KEY mytext (mytext)) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1,'my small mouse'),(2,'la-la-la'),(3,'It is so funny'),(4,'MySQL Tutorial');
select 8 from t1;
8
8
8
8
8
drop table t1;
create table t1 (a text, fulltext key (a));
insert into t1 values ('aaaa');
repair table t1;
Table	Op	Msg_type	Msg_text
test.t1	repair	status	OK
select * from t1 where match (a) against ('aaaa');
a
drop table t1;
create table t1 ( ref_mag text not null, fulltext (ref_mag));
insert into t1 values ('test');
select ref_mag from t1 where match ref_mag against ('+test' in boolean mode);
ref_mag
test
alter table t1 change ref_mag ref_mag char (255) not null;
select ref_mag from t1 where match ref_mag against ('+test' in boolean mode);
ref_mag
test
drop table t1;
create table t1 (t1_id int(11) primary key, name varchar(32));
insert into t1 values (1, 'data1');
insert into t1 values (2, 'data2');
create table t2 (t2_id int(11) primary key, t1_id int(11), name varchar(32));
insert into t2 values (1, 1, 'xxfoo');
insert into t2 values (2, 1, 'xxbar');
insert into t2 values (3, 1, 'xxbuz');
select * from t1 join t2 using(`t1_id`) where match (t1.name, t2.name) against('xxfoo' in boolean mode);
t1_id	name	t2_id	name
1	data1	1	xxfoo
select * from t2 where match name against ('*a*b*c*d*e*f*' in boolean mode);
t2_id	t1_id	name
drop table t1,t2;
create table t1 (a text, fulltext key (a));
insert into t1 select "xxxx yyyy zzzz";
drop table t1;
SET NAMES latin1;
CREATE TABLE t1 (t text character set utf8 not null, fulltext(t));
INSERT t1 VALUES ('Mit freundlichem Grüß'), ('aus Osnabrück');
SET NAMES koi8r;
INSERT t1 VALUES ("üÔÏ ÍÙ - ÏÐÉÌËÉ"),("ïÔÌÅÚØ, ÇÎÉÄÁ!"),
("îÅ ×ÌÅÚÁÊ, ÕÂØÅÔ!"),("É ÂÕÄÅÔ ÐÒÁ×!");
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('ïðéìëé');
t	collation(t)
üÔÏ ÍÙ - ÏÐÉÌËÉ	utf8_general_ci
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('ðÒá*' IN BOOLEAN MODE);
t	collation(t)
É ÂÕÄÅÔ ÐÒÁ×!	utf8_general_ci
SELECT * FROM t1 WHERE MATCH t AGAINST ('ÜÔÏ' IN BOOLEAN MODE);
t
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrück');
t	collation(t)
SET NAMES latin1;
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrück');
t	collation(t)
aus Osnabrück	utf8_general_ci
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrueck');
t	collation(t)
SELECT t, collation(t),FORMAT(MATCH t AGAINST ('Osnabruck'),6) FROM t1 WHERE MATCH t AGAINST ('Osnabruck');
t	collation(t)	FORMAT(MATCH t AGAINST ('Osnabruck'),6)
aus Osnabrück	utf8_general_ci	1.591140
alter table t1 modify t varchar(200) collate latin1_german2_ci not null;
Warnings:
Warning	1366	Incorrect string value: '\xD0\xAD\xD1\x82\xD0\xBE...' for column 't' at row 3
Warning	1366	Incorrect string value: '\xD0\x9E\xD1\x82\xD0\xBB...' for column 't' at row 4
Warning	1366	Incorrect string value: '\xD0\x9D\xD0\xB5 \xD0...' for column 't' at row 5
Warning	1366	Incorrect string value: '\xD0\xB8 \xD0\xB1\xD1...' for column 't' at row 6
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrück');
t	collation(t)
aus Osnabrück	latin1_german2_ci
SELECT t, collation(t) FROM t1 WHERE MATCH t AGAINST ('Osnabrueck');
t	collation(t)
aus Osnabrück	latin1_german2_ci
DROP TABLE t1;
CREATE TABLE t1 (s varchar(255), FULLTEXT (s)) DEFAULT CHARSET=utf8;
insert into t1 (s) values ('pära para para'),('para para para');
select * from t1 where match(s) against('para' in boolean mode);
s
pära para para
para para para
select * from t1 where match(s) against('par*' in boolean mode);
s
pära para para
para para para
DROP TABLE t1;
CREATE TABLE t1 (h text, FULLTEXT (h));
INSERT INTO t1 VALUES ('Jesses Hasse Ling and his syncopators of Swing');
REPAIR TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	repair	status	OK
select count(*) from t1;
count(*)
1
drop table t1;
CREATE TABLE t1 ( a TEXT, FULLTEXT (a) );
INSERT INTO t1 VALUES ('testing ft_nlq_find_relevance');
SELECT MATCH(a) AGAINST ('nosuchword') FROM t1;
MATCH(a) AGAINST ('nosuchword')
0
DROP TABLE t1;
create table t1 (a int primary key, b text, fulltext(b));
create table t2 (a int, b text);
insert t1 values (1, "aaaa"), (2, "bbbb");
insert t2 values (10, "aaaa"), (2, "cccc");
replace t1 select * from t2;
drop table t1, t2;
CREATE TABLE t1 (t VARCHAR(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci, FULLTEXT (t));
SET NAMES latin1;
INSERT INTO t1 VALUES('Mit freundlichem Grüß aus Osnabrück');
SELECT COUNT(*) FROM t1 WHERE MATCH(t) AGAINST ('"osnabrück"' IN BOOLEAN MODE);
COUNT(*)
1
DROP TABLE t1;
CREATE TABLE t1 (a VARCHAR(30), FULLTEXT(a));
INSERT INTO t1 VALUES('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbb');
SET myisam_repair_threads=2;
REPAIR TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	repair	status	OK
SET myisam_repair_threads=@@global.myisam_repair_threads;
INSERT INTO t1 VALUES('testword\'\'');
SELECT a FROM t1 WHERE MATCH a AGAINST('testword' IN BOOLEAN MODE);
a
testword''
SELECT a FROM t1 WHERE MATCH a AGAINST('testword\'\'' IN BOOLEAN MODE);
a
testword''
INSERT INTO t1 VALUES('test\'s');
SELECT a FROM t1 WHERE MATCH a AGAINST('test' IN BOOLEAN MODE);
a
test's
DROP TABLE t1;
CREATE TABLE t1 (a VARCHAR(10000), FULLTEXT(a));
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10000) DEFAULT NULL,
  FULLTEXT KEY `a` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
CREATE TABLE t1 (a TEXT, FULLTEXT KEY(a));
INSERT INTO t1 VALUES('test'),('test1'),('test');
PREPARE stmt from "SELECT a, FORMAT(MATCH(a) AGAINST('test1 test'),6) FROM t1 WHERE MATCH(a) AGAINST('test1 test')";
EXECUTE stmt;
a	FORMAT(MATCH(a) AGAINST('test1 test'),6)
test1	0.685267
EXECUTE stmt;
a	FORMAT(MATCH(a) AGAINST('test1 test'),6)
test1	0.685267
DEALLOCATE PREPARE stmt;
DROP TABLE t1;
CREATE TABLE t1 (a VARCHAR(255), FULLTEXT(a));
SELECT * FROM t1 IGNORE INDEX(a) WHERE MATCH(a) AGAINST('test');
a
ALTER TABLE t1 DISABLE KEYS;
SELECT * FROM t1 WHERE MATCH(a) AGAINST('test');
ERROR HY000: Can't find FULLTEXT index matching the column list
DROP TABLE t1;
CREATE TABLE t1(a TEXT);
INSERT INTO t1 VALUES(' aaaaa aaaa');
SELECT * FROM t1 WHERE MATCH(a) AGAINST ('"aaaa"' IN BOOLEAN MODE);
a
 aaaaa aaaa
DROP TABLE t1;
CREATE TABLE t1(a VARCHAR(20), FULLTEXT(a));
INSERT INTO t1 VALUES('Offside'),('City Of God');
SELECT a FROM t1 WHERE MATCH a AGAINST ('+city of*' IN BOOLEAN MODE);
a
City Of God
SELECT a FROM t1 WHERE MATCH a AGAINST ('+city (of*)' IN BOOLEAN MODE);
a
City Of God
SELECT a FROM t1 WHERE MATCH a AGAINST ('+city* of*' IN BOOLEAN MODE);
a
City Of God
DROP TABLE t1;