Debian Codegraph @

mysql-test/suite/ndbcluster/invisible_indexes.result

raw
1 SET SESSION default_storage_engine = ndbcluster;
2 #
3 # WL#8697: Invisible Indexes
4 #
5 SET SESSION information_schema_stats_expiry=0;
6 # Test of ALTER INDEX syntax.
7 CREATE TABLE t1 ( a INT, KEY (a) );
8 SHOW KEYS FROM t1;
9 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
10 t1 1 a 1 a A NULL NULL NULL YES BTREE YES NULL
11 ALTER TABLE t1 ALTER INDEX a INVISIBLE;
12 SHOW KEYS FROM t1;
13 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
14 t1 1 a 1 a A NULL NULL NULL YES BTREE NO NULL
15 ALTER TABLE t1 ALTER INDEX a VISIBLE;
16 SHOW KEYS FROM t1;
17 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
18 t1 1 a 1 a A NULL NULL NULL YES BTREE YES NULL
19 DROP TABLE t1;
20 # Test of CREATE INDEX syntax with invisible indexes.
21 CREATE TABLE t1 ( a INT, b INT );
22 CREATE INDEX a_invisible ON t1(a) INVISIBLE;
23 CREATE INDEX b_visible ON t1(a) VISIBLE;
24 Warnings:
25 Warning 1831 Duplicate index 'b_visible' defined on the table 'test.t1'. This is deprecated and will be disallowed in a future release.
26 CREATE INDEX a_b_invisible ON t1(a, b) INVISIBLE;
27 SHOW INDEXES FROM t1;
28 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
29 t1 1 a_invisible 1 a A NULL NULL NULL YES BTREE NO NULL
30 t1 1 b_visible 1 a A NULL NULL NULL YES BTREE YES NULL
31 t1 1 a_b_invisible 1 a A NULL NULL NULL YES BTREE NO NULL
32 t1 1 a_b_invisible 2 b A NULL NULL NULL YES BTREE NO NULL
33 DROP TABLE t1;
34 # Test that invisible indexes are not used.
35 CREATE TABLE t1 ( a INT, KEY (a) );
36 CREATE TABLE t2 ( a INT, KEY (a) INVISIBLE );
37 INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
38 INSERT INTO t2 SELECT * FROM t1;
39 ANALYZE TABLE t1, t2;
40 Table Op Msg_type Msg_text
41 test.t1 analyze status OK
42 test.t2 analyze status OK
43 EXPLAIN SELECT a FROM t1;
44 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
45 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 5 100.00 NULL
46 Warnings:
47 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1`
48 ALTER TABLE t1 ALTER INDEX a INVISIBLE;
49 EXPLAIN SELECT a FROM t1;
50 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
51 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 5 100.00 NULL
52 Warnings:
53 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1`
54 ALTER TABLE t1 ALTER INDEX a VISIBLE;
55 EXPLAIN SELECT a FROM t1;
56 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
57 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 5 100.00 NULL
58 Warnings:
59 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1`
60 EXPLAIN SELECT a FROM t2;
61 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
62 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 5 100.00 NULL
63 Warnings:
64 Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2`
65 ALTER TABLE t2 ALTER INDEX a VISIBLE;
66 EXPLAIN SELECT a FROM t2;
67 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
68 1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 5 100.00 NULL
69 Warnings:
70 Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2`
71 DROP TABLE t1, t2;
72 # Test that renaming an index does not change visibility and vice versa.
73 CREATE TABLE t1 (
74 a INT, INDEX (a),
75 b INT, INDEX (b) INVISIBLE
76 );
77 SHOW INDEXES FROM t1;
78 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
79 t1 1 a 1 a A NULL NULL NULL YES BTREE YES NULL
80 t1 1 b 1 b A NULL NULL NULL YES BTREE NO NULL
81 ALTER TABLE t1 RENAME INDEX a TO a1;
82 ALTER TABLE t1 RENAME INDEX b TO b1;
83 SHOW INDEXES FROM t1;
84 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
85 t1 1 a1 1 a A NULL NULL NULL YES BTREE YES NULL
86 t1 1 b1 1 b A NULL NULL NULL YES BTREE NO NULL
87 ALTER TABLE t1 ALTER INDEX a1 INVISIBLE;
88 ALTER TABLE t1 ALTER INDEX b1 VISIBLE;
89 SHOW INDEXES FROM t1;
90 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
91 t1 1 a1 1 a A NULL NULL NULL YES BTREE NO NULL
92 t1 1 b1 1 b A NULL NULL NULL YES BTREE YES NULL
93 DROP TABLE t1;
94 # Test of SHOW CREATE TABLE.
95 CREATE TABLE t1 (
96 a INT,
97 b INT,
98 c INT,
99 INDEX (a) VISIBLE,
100 INDEX (b) INVISIBLE,
101 INDEX (c)
102 );
103 SHOW CREATE TABLE t1;
104 Table Create Table
105 t1 CREATE TABLE `t1` (
106 `a` int DEFAULT NULL,
107 `b` int DEFAULT NULL,
108 `c` int DEFAULT NULL,
109 KEY `a` (`a`),
110 KEY `b` (`b`) /*!80000 INVISIBLE */,
111 KEY `c` (`c`)
112 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
113 DROP TABLE t1;
114 # Test that primary key indexes can't be made invisible.
115 CREATE TABLE t1 ( a INT, PRIMARY KEY (a) INVISIBLE );
116 ERROR HY000: A primary key index cannot be invisible
117 ALTER TABLE t1 ALTER INDEX PRIMARY INVISIBLE;
118 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PRIMARY INVISIBLE' at line 1
119 ALTER TABLE t1 ADD PRIMARY KEY (a) INVISIBLE;
120 ERROR HY000: A primary key index cannot be invisible
121 #
122 # Currently we allow to name the primary key index, but the name is
123 # silently changed to PRIMARY. If this behavior changes in the future,
124 # we need to take some additional measures to rule out invisible primary
125 # key indexes. The below two tests serve as triggers for such a change.
126 #
127 CREATE TABLE t1( a INT );
128 ALTER TABLE t1 ADD CONSTRAINT pk PRIMARY KEY (a);
129 SHOW INDEXES FROM t1;
130 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
131 t1 0 PRIMARY 1 a A 0 NULL NULL BTREE YES NULL
132 DROP TABLE t1;
133 CREATE TABLE t1( a INT, PRIMARY KEY pk (a) );
134 SHOW INDEXES FROM t1;
135 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
136 t1 0 PRIMARY 1 a A 0 NULL NULL BTREE YES NULL
137 DROP TABLE t1;
138 CREATE TABLE t1 (
139 a INT, KEY (a),
140 b INT, KEY (b) INVISIBLE
141 );
142 ALTER TABLE t1 RENAME INDEX no_such_index TO x;
143 ERROR 42000: Key 'no_such_index' doesn't exist in table 't1'
144 ALTER TABLE t1 ALTER INDEX no_such_index INVISIBLE;
145 ERROR 42000: Key 'no_such_index' doesn't exist in table 't1'
146 #
147 # Repeated alter actions. Should work.
148 #
149 ALTER TABLE t1 ALTER INDEX a INVISIBLE, ALTER INDEX a VISIBLE;
150 ALTER TABLE t1 ALTER INDEX a INVISIBLE, ALTER INDEX b VISIBLE;
151 SHOW INDEXES FROM t1;
152 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
153 t1 1 a 1 a A NULL NULL NULL YES BTREE NO NULL
154 t1 1 b 1 b A NULL NULL NULL YES BTREE YES NULL
155 #
156 # Various combinations of RENAME INDEX and ALTER INDEX ... INVISIBLE.
157 #
158 ALTER TABLE t1 RENAME INDEX a TO x, RENAME INDEX x TO a;
159 ERROR 42000: Key 'x' doesn't exist in table 't1'
160 ALTER TABLE t1 RENAME INDEX a TO x, ALTER INDEX x INVISIBLE;
161 ERROR 42000: Key 'x' doesn't exist in table 't1'
162 ALTER TABLE t1 RENAME INDEX a TO x, ALTER INDEX a VISIBLE;
163 ERROR 42000: Key 'a' doesn't exist in table 't1'
164 ALTER TABLE t1 ALTER INDEX a VISIBLE, RENAME INDEX a TO x;
165 ERROR 42000: Key 'a' doesn't exist in table 't1'
166 #
167 # Various algorithms and their effects.
168 #
169 INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3);
170 ANALYZE TABLE t1;
171 Table Op Msg_type Msg_text
172 test.t1 analyze status OK
173 ALTER TABLE t1 ALTER INDEX a INVISIBLE, ALGORITHM = COPY;
174 affected rows: 3
175 info: Records: 3 Duplicates: 0 Warnings: 0
176 ANALYZE TABLE t1;
177 Table Op Msg_type Msg_text
178 test.t1 analyze status OK
179 SHOW INDEXES FROM t1;
180 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
181 t1 1 a 1 a A NULL NULL NULL YES BTREE NO NULL
182 t1 1 b 1 b A NULL NULL NULL YES BTREE YES NULL
183 ALTER TABLE t1 ALTER INDEX a VISIBLE, ALGORITHM = INPLACE;
184 affected rows: 0
185 info: Records: 0 Duplicates: 0 Warnings: 0
186 ANALYZE TABLE t1;
187 Table Op Msg_type Msg_text
188 test.t1 analyze status OK
189 SHOW INDEXES FROM t1;
190 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
191 t1 1 a 1 a A NULL NULL NULL YES BTREE YES NULL
192 t1 1 b 1 b A NULL NULL NULL YES BTREE YES NULL
193 ALTER TABLE t1 ALTER INDEX a INVISIBLE, ALGORITHM = DEFAULT;
194 affected rows: 0
195 info: Records: 0 Duplicates: 0 Warnings: 0
196 ANALYZE TABLE t1;
197 Table Op Msg_type Msg_text
198 test.t1 analyze status OK
199 SHOW INDEXES FROM t1;
200 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
201 t1 1 a 1 a A NULL NULL NULL YES BTREE NO NULL
202 t1 1 b 1 b A NULL NULL NULL YES BTREE YES NULL
203 ALTER TABLE t1 ALTER INDEX a VISIBLE;
204 affected rows: 0
205 info: Records: 0 Duplicates: 0 Warnings: 0
206 ANALYZE TABLE t1;
207 Table Op Msg_type Msg_text
208 test.t1 analyze status OK
209 SHOW INDEXES FROM t1;
210 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
211 t1 1 a 1 a A NULL NULL NULL YES BTREE YES NULL
212 t1 1 b 1 b A NULL NULL NULL YES BTREE YES NULL
213 ALTER TABLE t1 ADD INDEX ab(a, b), ALTER INDEX ab INVISIBLE;
214 ERROR 42000: Key 'ab' doesn't exist in table 't1'
215 DROP TABLE t1;
216 #
217 # Test that constraints implemented by the indexes are still enforced
218 # while the index is invisible.
219 #
220 CREATE TABLE t1 ( a INT, UNIQUE KEY (a) INVISIBLE );
221 CREATE TABLE t2 ( a INT UNIQUE );
222 CREATE TABLE t3 ( a INT NOT NULL, KEY (a) INVISIBLE, b INT PRIMARY KEY );
223 CREATE TABLE t4 ( a INT NOT NULL, UNIQUE KEY (a) INVISIBLE,
224 b INT PRIMARY KEY AUTO_INCREMENT );
225 CREATE TABLE t5 ( a INT, b INT, c INT, UNIQUE KEY (a, b, c) INVISIBLE );
226 INSERT INTO t1 VALUES (1);
227 INSERT INTO t1 VALUES (1);
228 ERROR 23000: Duplicate entry '1' for key 't1.a'
229 ALTER TABLE t2 ALTER INDEX a INVISIBLE;
230 INSERT INTO t2 VALUES (1);
231 INSERT INTO t2 VALUES (1);
232 ERROR 23000: Duplicate entry '1' for key 't2.a'
233 INSERT INTO t3(a) VALUES (NULL);
234 ERROR 23000: Column 'a' cannot be null
235 INSERT INTO t4(a) VALUES (NULL);
236 ERROR 23000: Column 'a' cannot be null
237 INSERT INTO t4(a) VALUES (1);
238 INSERT INTO t4(a) VALUES (1);
239 ERROR 23000: Duplicate entry '1' for key 't4.a'
240 INSERT INTO t5 VALUES (1, 2, 3);
241 INSERT INTO t5 VALUES (1, 2, 3);
242 ERROR 23000: Duplicate entry '1-2-3' for key 't5.a'
243 DROP TABLE t1, t2, t3, t4, t5;
244 #
245 # Bug#23256900: WL#8697: ASSERTION
246 # `TABLE_SHARE->IS_MISSING_PRIMARY_KEY() ...` FAILED.
247 #
248 # Test for when an index is implicitly promoted to primary key index.
249 # The first NOT NULL UNIQUE index is candidate for promotion.
250 # These indexes can't be invisible, either.
251 CREATE TABLE t1 ( a INT NOT NULL, UNIQUE KEY (a) );
252 ALTER TABLE t1 ALTER INDEX a INVISIBLE;
253 ERROR HY000: A primary key index cannot be invisible
254 EXPLAIN
255 SELECT * FROM t1;
256 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
257 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ALL NULL NULL NULL NULL 2 100.00 NULL
258 Warnings:
259 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1`
260 SELECT * FROM t1;
261 a
262 DROP TABLE t1;
263 # The first NOT NULL UNIQUE index may of course be invisible if it is
264 # not promoted.
265 CREATE TABLE t1 (
266 a INT NOT NULL,
267 b INT NOT NULL PRIMARY KEY,
268 UNIQUE KEY (a) INVISIBLE
269 );
270 SHOW INDEXES FROM t1;
271 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
272 t1 0 PRIMARY 1 b A 0 NULL NULL BTREE YES NULL
273 t1 0 a 1 a A 0 NULL NULL BTREE NO NULL
274 DROP TABLE t1;
275 # The check above applies only to the first NOT NULL UNIQUE index.
276 CREATE TABLE t1 (
277 a INT NOT NULL,
278 b INT NOT NULL,
279 UNIQUE KEY (a),
280 UNIQUE KEY (b) INVISIBLE
281 );
282 SHOW INDEXES FROM t1;
283 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
284 t1 0 a 1 a A 0 NULL NULL BTREE YES NULL
285 t1 0 b 1 b A 0 NULL NULL BTREE NO NULL
286 DROP TABLE t1;
287 CREATE TABLE t1 ( a INT NOT NULL, UNIQUE KEY (a) INVISIBLE );
288 ERROR HY000: A primary key index cannot be invisible
289 #
290 # Bug#23264435: WL#8697: FULLTEXT INDEXES CANNOT BE MADE INVISIBLE
291 #
292 CREATE TABLE t1 ( a INT NOT NULL, KEY (a) INVISIBLE, b INT NOT NULL UNIQUE );
293 CREATE TABLE t2 ( a INT PRIMARY KEY, b INT, INDEX(b) INVISIBLE);
294 ALTER TABLE t2 ALTER INDEX b VISIBLE;
295 DROP TABLE t1, t2;
296 CREATE TEMPORARY TABLE t1 ( a INT, KEY (a) INVISIBLE );
297 SHOW INDEXES FROM t1;
298 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
299 t1 1 a 1 a A 0 NULL NULL YES BTREE NO NULL
300 EXPLAIN SELECT a FROM t1;
301 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
302 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
303 Warnings:
304 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1`
305 DROP TABLE t1;
306 CREATE TABLE t1 (
307 fid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
308 g GEOMETRY NOT NULL SRID 0,
309 SPATIAL KEY(g)
310 );
311 ERROR HY000: The used table type doesn't support SPATIAL indexes
312 CREATE TABLE t1 ( a INT AUTO_INCREMENT, KEY ( a ) );
313 ERROR HY000: Can't create table 't1' (use SHOW WARNINGS for more info).
314 #
315 # Bug#24660093: REMOVING AN INVISIBLE INDEX BREAKS EXPLAIN
316 #
317 CREATE TABLE t1 ( a INT, KEY(a) INVISIBLE );
318 SELECT * FROM t1 FORCE INDEX(a);
319 ERROR 42000: Key 'a' doesn't exist in table 't1'
320 SELECT * FROM t1 FORCE INDEX FOR JOIN (a);
321 ERROR 42000: Key 'a' doesn't exist in table 't1'
322 SELECT * FROM t1 FORCE INDEX FOR ORDER BY (a);
323 ERROR 42000: Key 'a' doesn't exist in table 't1'
324 SELECT * FROM t1 FORCE INDEX FOR GROUP BY (a);
325 ERROR 42000: Key 'a' doesn't exist in table 't1'
326 SELECT * FROM t1 USE INDEX(a);
327 ERROR 42000: Key 'a' doesn't exist in table 't1'
328 SELECT * FROM t1 USE INDEX FOR JOIN (a);
329 ERROR 42000: Key 'a' doesn't exist in table 't1'
330 SELECT * FROM t1 USE INDEX FOR ORDER BY (a);
331 ERROR 42000: Key 'a' doesn't exist in table 't1'
332 SELECT * FROM t1 USE INDEX FOR GROUP BY (a);
333 ERROR 42000: Key 'a' doesn't exist in table 't1'
334 SELECT * FROM t1 IGNORE INDEX(a);
335 ERROR 42000: Key 'a' doesn't exist in table 't1'
336 SELECT * FROM t1 IGNORE INDEX FOR JOIN (a);
337 ERROR 42000: Key 'a' doesn't exist in table 't1'
338 SELECT * FROM t1 IGNORE INDEX FOR ORDER BY (a);
339 ERROR 42000: Key 'a' doesn't exist in table 't1'
340 SELECT * FROM t1 IGNORE INDEX FOR GROUP BY (a);
341 ERROR 42000: Key 'a' doesn't exist in table 't1'
342 DROP TABLE t1;
343 #
344 # Tests Specific to ndbcluster
345 #
346 CREATE TABLE t1 (
347 a INT, KEY (a),
348 b INT, KEY (b) INVISIBLE
349 ) engine=NDB;
350 INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3);
351 ALTER TABLE t1 ALGORITHM=INPLACE, RENAME INDEX a TO x, ALTER INDEX b VISIBLE;
352 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Rename index can not be performed inplace. Try ALGORITHM=COPY.
353 DROP TABLE t1;