Debian Codegraph @

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

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