Debian Codegraph @

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

raw
1 #------------------------------------------------------------------------------
2 # Test With two FTS index on same table + alter/create/drop index + tnx
3 #------------------------------------------------------------------------------
4 --source include/have_innodb.inc
5 --source include/maybe_versioning.inc
6
7 --disable_warnings
8 drop table if exists t1;
9 --enable_warnings
10
11 # Create FTS table
12 CREATE TABLE t1 (
13 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
14 a VARCHAR(200),
15 b TEXT
16 ) ENGINE = InnoDB STATS_PERSISTENT=0;
17
18 # Insert rows
19 INSERT INTO t1 (a,b) VALUES
20 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
21 ('How To Use MySQL Well','After you went through a ...'),
22 ('Optimizing MySQL','In this tutorial we will show ...');
23
24 # Create the 2 FTS index Using Alter on same table
25 ALTER TABLE t1 ADD FULLTEXT INDEX idx_1 (a);
26 ALTER TABLE t1 ADD FULLTEXT INDEX idx_2 (b);
27 EVAL SHOW CREATE TABLE t1;
28
29 # check multiple index with transaction
30 START TRANSACTION;
31 # Insert rows
32 INSERT INTO t1 (a,b) VALUES
33 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
34 ('MySQL vs. YourSQL','In the following database comparison ...'),
35 ('MySQL Security','When configured properly, MySQL ...');
36 ROLLBACK;
37
38 # Select word "tutorial" in the table
39 SELECT * FROM t1 WHERE MATCH (a)
40 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
41
42 # boolean mode
43 select * from t1 where MATCH(a) AGAINST("+mysql +Tutorial" IN BOOLEAN MODE);
44 select * from t1 where MATCH(b) AGAINST("+Tutorial" IN BOOLEAN MODE);
45 select * from t1 where MATCH(b) AGAINST("+stands +(DataBase)" IN BOOLEAN MODE);
46 select * from t1 where MATCH(b) AGAINST("+DataBase -(comparison)" IN BOOLEAN MODE);
47 select *, MATCH(a) AGAINST("Optimizing MySQL" IN BOOLEAN MODE) as x from t1;
48 select *, MATCH(b) AGAINST("collections support" IN BOOLEAN MODE) as x from t1;
49 select * from t1 where MATCH a AGAINST ("+Optimiz* +Optimiz*" IN BOOLEAN MODE);
50 select * from t1 where MATCH b AGAINST ('"DBMS stands"' IN BOOLEAN MODE);
51 select * from t1 where MATCH b AGAINST ('"DBMS STANDS"' IN BOOLEAN MODE);
52
53 # query expansion
54 select * from t1 where MATCH(b) AGAINST ("DataBase" WITH QUERY EXPANSION);
55 select * from t1 where MATCH(a) AGAINST ("Security" WITH QUERY EXPANSION);
56
57 # Drop index
58 ALTER TABLE t1 DROP INDEX idx_1;
59 ALTER TABLE t1 DROP INDEX idx_2;
60
61 # Create the FTS index again
62 ALTER TABLE t1 ADD FULLTEXT INDEX idx_1 (a);
63 ALTER TABLE t1 ADD FULLTEXT INDEX idx_2 (b);
64
65 # Select word "tutorial" in the table
66 SELECT * FROM t1 WHERE MATCH (a)
67 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
68
69 # boolean mode
70 select * from t1 where MATCH(a) AGAINST("+mysql +Tutorial" IN BOOLEAN MODE);
71 select * from t1 where MATCH(b) AGAINST("+Tutorial" IN BOOLEAN MODE);
72 select * from t1 where MATCH(b) AGAINST("+stands +(DataBase)" IN BOOLEAN MODE);
73 select * from t1 where MATCH(b) AGAINST("+DataBase -(comparison)" IN BOOLEAN MODE);
74 select *, MATCH(a) AGAINST("Optimizing MySQL" IN BOOLEAN MODE) as x from t1;
75 select *, MATCH(b) AGAINST("collections support" IN BOOLEAN MODE) as x from t1;
76 select * from t1 where MATCH a AGAINST ("+Optimiz* +Optimiz*" IN BOOLEAN MODE);
77 select * from t1 where MATCH b AGAINST ('"DBMS stands"' IN BOOLEAN MODE);
78 select * from t1 where MATCH b AGAINST ('"DBMS STANDS"' IN BOOLEAN MODE);
79
80 # query expansion
81 select * from t1 where MATCH(b) AGAINST ("DataBase" WITH QUERY EXPANSION);
82 select * from t1 where MATCH(a) AGAINST ("Security" WITH QUERY EXPANSION);
83
84 # insert for proximity search
85 INSERT INTO t1 (a,b) VALUES ('test query expansion','for database ...');
86 # Insert into table with similar word of different distances
87 INSERT INTO t1 (a,b) VALUES
88 ('test proximity search, test, proximity and phrase',
89 'search, with proximity innodb');
90
91 INSERT INTO t1 (a,b) VALUES
92 ('test proximity fts search, test, proximity and phrase',
93 'search, with proximity innodb');
94
95 INSERT INTO t1 (a,b) VALUES
96 ('test more of proximity for fts search, test, more proximity and phrase',
97 'search, with proximity innodb');
98
99 # This should only return the first document
100 SELECT * FROM t1
101 WHERE MATCH (a)
102 AGAINST ('"proximity search"@3' IN BOOLEAN MODE);
103
104 # This would return no document
105 SELECT * FROM t1
106 WHERE MATCH (a)
107 AGAINST ('"proximity search"@2' IN BOOLEAN MODE);
108
109 # This give you all three documents
110 SELECT * FROM t1
111 WHERE MATCH (b)
112 AGAINST ('"proximity innodb"@4' IN BOOLEAN MODE);
113
114 # Similar boundary testing for the words
115 SELECT * FROM t1
116 WHERE MATCH (a)
117 AGAINST ('"test proximity"@3' IN BOOLEAN MODE);
118
119 # Test with more word The last document will return, please notice there
120 # is no ordering requirement for proximity search.
121 SELECT * FROM t1
122 WHERE MATCH (a)
123 AGAINST ('"more test proximity"@3' IN BOOLEAN MODE);
124
125 SELECT * FROM t1
126 WHERE MATCH (a)
127 AGAINST ('"more test proximity"@2' IN BOOLEAN MODE);
128
129 # The phrase search will not require exact word ordering
130 SELECT * FROM t1
131 WHERE MATCH (a)
132 AGAINST ('"more fts proximity"@02' IN BOOLEAN MODE);
133
134
135 # Select word "tutorial" in the table - innodb crash
136 SELECT * FROM t1 WHERE CONCAT(t1.a,t1.b) IN (
137 SELECT CONCAT(a,b) FROM t1 AS t2 WHERE
138 MATCH (t2.a) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE)
139 ) OR t1.id = 3 ;
140
141
142 # Select word "tutorial" in the table - innodb crash
143 SELECT * FROM t1 WHERE CONCAT(t1.a,t1.b) IN (
144 SELECT CONCAT(a,b) FROM t1 AS t2
145 WHERE MATCH (t2.a) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE)
146 AND t2.id != 3) ;
147
148 # Select word "tutorial" in the table
149 SELECT * FROM t1 WHERE id IN (SELECT MIN(id) FROM t1 WHERE
150 MATCH (b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE)) OR id = 3 ;
151
152 # Select word except "tutorial" in the table
153 SELECT * FROM t1 WHERE id NOT IN (SELECT MIN(id) FROM t1
154 WHERE MATCH (b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE)) ;
155
156
157 # Select word "tutorial" in the table
158 SELECT * FROM t1 WHERE EXISTS (SELECT t2.id FROM t1 AS t2 WHERE
159 MATCH (t2.b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE)
160 AND t1.id = t2.id) ;
161
162
163 # Select not word like "tutorial" using subquery
164 SELECT * FROM t1 WHERE NOT EXISTS (SELECT t2.id FROM t1 AS t2 WHERE
165 MATCH (t2.a) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE)
166 AND t1.id = t2.id) ;
167
168
169 SELECT * FROM t1 WHERE t1.id = (SELECT MAX(t2.id) FROM t1 AS t2 WHERE
170 MATCH(t2.a) AGAINST ('"proximity search"@3' IN BOOLEAN MODE));
171 SELECT * FROM t1 WHERE t1.id > (SELECT MIN(t2.id) FROM t1 AS t2 WHERE
172 MATCH(t2.b) AGAINST ('"proximity innodb"@3' IN BOOLEAN MODE));
173
174 DROP TABLE t1;
175