Debian Codegraph @

mysql-test/suite/innodb_fts/r/alter.result

raw
1 # Case 1 : WITHOUT FTS_DOC_ID
2 CREATE TABLE t1 (
3 id INT,
4 title VARCHAR(200),
5 FULLTEXT fidx(title)
6 ) ENGINE=InnoDB;
7 INSERT INTO t1 (id, title) VALUES (1, 'MySQL Tutorial');
8 SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
9 id title
10 1 MySQL Tutorial
11 ALTER TABLE t1 ALTER COLUMN id DROP DEFAULT;
12 INSERT INTO t1 (id, title) VALUES (2, 'MySQL Tutorial');
13 SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
14 id title
15 1 MySQL Tutorial
16 2 MySQL Tutorial
17 # restart
18 ALTER TABLE t1 DROP KEY fidx;
19 ALTER TABLE t1 ALTER COLUMN id DROP DEFAULT;
20 DELETE FROM t1 WHERE id < 2;
21 INSERT INTO t1 (id, title) VALUES (3, 'MySQL Tutorial');
22 SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
23 ERROR HY000: Can't find FULLTEXT index matching the column list
24 # restart
25 INSERT INTO t1 (id, title) VALUES (4, 'MySQL Tutorial');
26 SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
27 ERROR HY000: Can't find FULLTEXT index matching the column list
28 ALTER TABLE t1 ADD FULLTEXT KEY fidx (title);
29 INSERT INTO t1 (id, title) VALUES (5, 'MySQL Tutorial');
30 SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
31 id title
32 2 MySQL Tutorial
33 3 MySQL Tutorial
34 4 MySQL Tutorial
35 5 MySQL Tutorial
36 ALTER TABLE t1 DROP KEY fidx;
37 ALTER TABLE t1 ALTER COLUMN id DROP DEFAULT;
38 DELETE FROM t1 WHERE id < 5;
39 INSERT INTO t1 (id, title) VALUES (6, 'MySQL Tutorial');
40 SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
41 ERROR HY000: Can't find FULLTEXT index matching the column list
42 ALTER TABLE t1 ADD FULLTEXT KEY fidx(title);
43 INSERT INTO t1 (id, title) VALUES (7, 'MySQL Tutorial');
44 SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
45 id title
46 5 MySQL Tutorial
47 6 MySQL Tutorial
48 7 MySQL Tutorial
49 DROP TABLE t1;
50 # Case 2 : WITH FTS_DOC_ID PRIMARY KEY
51 CREATE TABLE t1 (
52 FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
53 id INT,
54 title VARCHAR(200),
55 PRIMARY KEY(FTS_DOC_ID),
56 FULLTEXT fidx(title)
57 ) ENGINE=InnoDB;
58 INSERT INTO t1 (id, title) VALUES (1, 'MySQL Tutorial');
59 SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
60 id title
61 1 MySQL Tutorial
62 ALTER TABLE t1 ALTER COLUMN id DROP DEFAULT;
63 INSERT INTO t1 (id, title) VALUES (2, 'MySQL Tutorial');
64 SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
65 id title
66 1 MySQL Tutorial
67 2 MySQL Tutorial
68 # restart
69 ALTER TABLE t1 DROP KEY fidx;
70 ALTER TABLE t1 ALTER COLUMN id DROP DEFAULT;
71 DELETE FROM t1 WHERE id < 2;
72 INSERT INTO t1 (id, title) VALUES (3, 'MySQL Tutorial');
73 SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
74 ERROR HY000: Can't find FULLTEXT index matching the column list
75 # restart
76 INSERT INTO t1 (id, title) VALUES (4, 'MySQL Tutorial');
77 SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
78 ERROR HY000: Can't find FULLTEXT index matching the column list
79 ALTER TABLE t1 ADD FULLTEXT KEY fidx (title);
80 INSERT INTO t1 (id, title) VALUES (5, 'MySQL Tutorial');
81 SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
82 id title
83 2 MySQL Tutorial
84 3 MySQL Tutorial
85 4 MySQL Tutorial
86 5 MySQL Tutorial
87 ALTER TABLE t1 DROP KEY fidx;
88 ALTER TABLE t1 ALTER COLUMN id DROP DEFAULT;
89 DELETE FROM t1 WHERE id < 5;
90 INSERT INTO t1 (id, title) VALUES (6, 'MySQL Tutorial');
91 SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
92 ERROR HY000: Can't find FULLTEXT index matching the column list
93 ALTER TABLE t1 ADD FULLTEXT KEY fidx(title);
94 INSERT INTO t1 (id, title) VALUES (7, 'MySQL Tutorial');
95 SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
96 id title
97 5 MySQL Tutorial
98 6 MySQL Tutorial
99 7 MySQL Tutorial
100 DROP TABLE t1;
101 # Case 3 : WITH FTS_DOC_ID FTS_DOC_ID_INDEX KEY
102 CREATE TABLE t1 (
103 FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
104 id INT,
105 title VARCHAR(200),
106 UNIQUE KEY FTS_DOC_ID_INDEX(FTS_DOC_ID),
107 FULLTEXT fidx(title)
108 ) ENGINE=InnoDB;
109 INSERT INTO t1 (id, title) VALUES (1, 'MySQL Tutorial');
110 SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
111 id title
112 1 MySQL Tutorial
113 ALTER TABLE t1 ALTER COLUMN id DROP DEFAULT;
114 INSERT INTO t1 (id, title) VALUES (2, 'MySQL Tutorial');
115 SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
116 id title
117 1 MySQL Tutorial
118 2 MySQL Tutorial
119 # restart
120 ALTER TABLE t1 DROP KEY fidx;
121 ALTER TABLE t1 ALTER COLUMN id DROP DEFAULT;
122 DELETE FROM t1 WHERE id < 2;
123 INSERT INTO t1 (id, title) VALUES (3, 'MySQL Tutorial');
124 SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
125 ERROR HY000: Can't find FULLTEXT index matching the column list
126 # restart
127 INSERT INTO t1 (id, title) VALUES (4, 'MySQL Tutorial');
128 SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
129 ERROR HY000: Can't find FULLTEXT index matching the column list
130 ALTER TABLE t1 ADD FULLTEXT KEY fidx (title);
131 INSERT INTO t1 (id, title) VALUES (5, 'MySQL Tutorial');
132 SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
133 id title
134 2 MySQL Tutorial
135 3 MySQL Tutorial
136 4 MySQL Tutorial
137 5 MySQL Tutorial
138 ALTER TABLE t1 DROP KEY fidx;
139 ALTER TABLE t1 ALTER COLUMN id DROP DEFAULT;
140 DELETE FROM t1 WHERE id < 5;
141 INSERT INTO t1 (id, title) VALUES (6, 'MySQL Tutorial');
142 SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
143 ERROR HY000: Can't find FULLTEXT index matching the column list
144 ALTER TABLE t1 ADD FULLTEXT KEY fidx(title);
145 INSERT INTO t1 (id, title) VALUES (7, 'MySQL Tutorial');
146 SELECT id, title FROM t1 WHERE MATCH(title) AGAINST('mysql');
147 id title
148 5 MySQL Tutorial
149 6 MySQL Tutorial
150 7 MySQL Tutorial
151 DROP TABLE t1;