Debian Codegraph @

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

raw
1 # Truncate a fts table with hidden fts doc id index but without
2 # fts index, the hidden index should be kept.
3
4 --echo # Case 1 : CREATE FULLTEXT/ID + TRUNCATE
5 CREATE TABLE articles (
6 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
7 title VARCHAR(200),
8 body TEXT,
9 FULLTEXT fidx(title, body)
10 ) ENGINE=InnoDB;
11
12 INSERT INTO articles (title, body) VALUES
13 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...');
14
15 SELECT title, body FROM articles WHERE
16 MATCH(title, body) AGAINST('mysql');
17
18 TRUNCATE TABLE articles;
19
20 INSERT INTO articles (title, body) VALUES
21 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...');
22
23 SELECT title, body FROM articles WHERE
24 MATCH(title, body) AGAINST('mysql');
25
26 ALTER TABLE articles DROP KEY fidx;
27
28 INSERT INTO articles (title, body) VALUES
29 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...');
30
31 TRUNCATE TABLE articles;
32
33 INSERT INTO articles (title, body) VALUES
34 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...');
35
36 --error ER_FT_MATCHING_KEY_NOT_FOUND
37 SELECT title, body FROM articles WHERE
38 MATCH(title, body) AGAINST('mysql');
39
40 ALTER TABLE articles ADD FULLTEXT KEY fidx(title, body);
41
42 INSERT INTO articles (title, body) VALUES
43 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...');
44
45 SELECT title, body FROM articles WHERE
46 MATCH(title, body) AGAINST('mysql');
47
48 DROP TABLE articles;
49
50 --echo # Case 2 : CREATE FULLTEXT/ID + ALTER + TRUNCATE
51 CREATE TABLE articles (
52 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
53 title VARCHAR(200),
54 body TEXT
55 ) ENGINE=InnoDB;
56
57 ALTER TABLE articles ADD FULLTEXT KEY fidx(title, body);
58
59 INSERT INTO articles (title, body) VALUES
60 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...');
61
62 SELECT title, body FROM articles WHERE
63 MATCH(title, body) AGAINST('mysql');
64
65 TRUNCATE TABLE articles;
66
67 INSERT INTO articles (title, body) VALUES
68 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...');
69
70 SELECT title, body FROM articles WHERE
71 MATCH(title, body) AGAINST('mysql');
72
73 ALTER TABLE articles DROP KEY fidx;
74
75 INSERT INTO articles (title, body) VALUES
76 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...');
77
78 TRUNCATE TABLE articles;
79
80 --source include/restart_mysqld.inc
81
82 INSERT INTO articles (title, body) VALUES
83 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...');
84
85 --error ER_FT_MATCHING_KEY_NOT_FOUND
86 SELECT title, body FROM articles WHERE
87 MATCH(title, body) AGAINST('mysql');
88
89 ALTER TABLE articles ADD FULLTEXT KEY fidx(title, body);
90
91 INSERT INTO articles (title, body) VALUES
92 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...');
93
94 SELECT title, body FROM articles WHERE
95 MATCH(title, body) AGAINST('mysql');
96
97 DROP TABLE articles;
98
99 --echo # Case 3 : CREATE FULLTEXT + TRUNCATE
100 CREATE TABLE articles (
101 title VARCHAR(200),
102 body TEXT,
103 FULLTEXT fidx(title, body)
104 ) ENGINE=InnoDB;
105
106 INSERT INTO articles (title, body) VALUES
107 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...');
108
109 SELECT title, body FROM articles WHERE
110 MATCH(title, body) AGAINST('mysql');
111
112 TRUNCATE TABLE articles;
113
114 INSERT INTO articles (title, body) VALUES
115 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...');
116
117 SELECT title, body FROM articles WHERE
118 MATCH(title, body) AGAINST('mysql');
119
120 ALTER TABLE articles DROP KEY fidx;
121
122 INSERT INTO articles (title, body) VALUES
123 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...');
124
125 TRUNCATE TABLE articles;
126
127 INSERT INTO articles (title, body) VALUES
128 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...');
129
130 --error ER_FT_MATCHING_KEY_NOT_FOUND
131 SELECT title, body FROM articles WHERE
132 MATCH(title, body) AGAINST('mysql');
133
134 ALTER TABLE articles ADD FULLTEXT KEY fidx(title, body);
135
136 INSERT INTO articles (title, body) VALUES
137 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...');
138
139 SELECT title, body FROM articles WHERE
140 MATCH(title, body) AGAINST('mysql');
141
142 DROP TABLE articles;
143
144 --echo # Case 4 : CREATE FULLTEXT + ALTER + TRUNCATE
145 CREATE TABLE articles (
146 title VARCHAR(200),
147 body TEXT
148 ) ENGINE=InnoDB;
149
150 ALTER TABLE articles ADD FULLTEXT KEY fidx(title, body);
151
152 INSERT INTO articles (title, body) VALUES
153 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...');
154
155 SELECT title, body FROM articles WHERE
156 MATCH(title, body) AGAINST('mysql');
157
158 TRUNCATE TABLE articles;
159
160 INSERT INTO articles (title, body) VALUES
161 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...');
162
163 SELECT title, body FROM articles WHERE
164 MATCH(title, body) AGAINST('mysql');
165
166 ALTER TABLE articles DROP KEY fidx;
167
168 INSERT INTO articles (title, body) VALUES
169 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...');
170
171 TRUNCATE TABLE articles;
172
173 --source include/restart_mysqld.inc
174
175 INSERT INTO articles (title, body) VALUES
176 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...');
177
178 --error ER_FT_MATCHING_KEY_NOT_FOUND
179 SELECT title, body FROM articles WHERE
180 MATCH(title, body) AGAINST('mysql');
181
182 ALTER TABLE articles ADD FULLTEXT KEY fidx(title, body);
183
184 INSERT INTO articles (title, body) VALUES
185 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...');
186
187 SELECT title, body FROM articles WHERE
188 MATCH(title, body) AGAINST('mysql');
189
190 DROP TABLE articles;