Debian Codegraph @

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

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