Debian Codegraph @

mysql-test/suite/vcol/r/vcol_keys_myisam.result

raw
1 SET @@session.default_storage_engine = 'MyISAM';
2 # - UNIQUE KEY
3 # - INDEX
4 # - FULLTEXT INDEX
5 # - SPATIAL INDEX (not supported)
6 # - FOREIGN INDEX (partially supported)
7 # - CHECK (allowed but not used)
8 # UNIQUE
9 create table t1 (a int, b int as (a*2) unique);
10 drop table t1;
11 create table t1 (a int, b int as (a*2) persistent unique);
12 show create table t1;
13 Table Create Table
14 t1 CREATE TABLE `t1` (
15 `a` int(11) DEFAULT NULL,
16 `b` int(11) GENERATED ALWAYS AS (`a` * 2) STORED,
17 UNIQUE KEY `b` (`b`)
18 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
19 describe t1;
20 Field Type Null Key Default Extra
21 a int(11) YES NULL
22 b int(11) YES UNI NULL STORED GENERATED
23 drop table t1;
24 create table t1 (a int, b int as (a*2), unique key (b));
25 drop table t1;
26 create table t1 (a int, b int as (a*2) persistent, unique (b));
27 show create table t1;
28 Table Create Table
29 t1 CREATE TABLE `t1` (
30 `a` int(11) DEFAULT NULL,
31 `b` int(11) GENERATED ALWAYS AS (`a` * 2) STORED,
32 UNIQUE KEY `b` (`b`)
33 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
34 describe t1;
35 Field Type Null Key Default Extra
36 a int(11) YES NULL
37 b int(11) YES UNI NULL STORED GENERATED
38 drop table t1;
39 create table t1 (a int, b int as (a*2));
40 alter table t1 add unique key (b);
41 drop table t1;
42 create table t1 (a int, b int as (a*2) persistent);
43 alter table t1 add unique key (b);
44 drop table t1;
45 # Testing data manipulation operations involving UNIQUE keys
46 # on virtual columns can be found in:
47 # - vcol_ins_upd.inc
48 # - vcol_select.inc
49 #
50 # INDEX
51 create table t1 (a int, b int as (a*2), index (b));
52 drop table t1;
53 create table t1 (a int, b int as (a*2), index (a,b));
54 drop table t1;
55 create table t1 (a int, b int as (a*2) persistent, index (b));
56 show create table t1;
57 Table Create Table
58 t1 CREATE TABLE `t1` (
59 `a` int(11) DEFAULT NULL,
60 `b` int(11) GENERATED ALWAYS AS (`a` * 2) STORED,
61 KEY `b` (`b`)
62 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
63 describe t1;
64 Field Type Null Key Default Extra
65 a int(11) YES NULL
66 b int(11) YES MUL NULL STORED GENERATED
67 drop table t1;
68 create table t1 (a int, b int as (a*2) persistent, index (a,b));
69 show create table t1;
70 Table Create Table
71 t1 CREATE TABLE `t1` (
72 `a` int(11) DEFAULT NULL,
73 `b` int(11) GENERATED ALWAYS AS (`a` * 2) STORED,
74 KEY `a` (`a`,`b`)
75 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
76 describe t1;
77 Field Type Null Key Default Extra
78 a int(11) YES MUL NULL
79 b int(11) YES NULL STORED GENERATED
80 drop table t1;
81 create table t1 (a int, b int as (a*2));
82 alter table t1 add index (b);
83 alter table t1 add index (a,b);
84 drop table t1;
85 create table t1 (a int, b int as (a*2) persistent);
86 alter table t1 add index (b);
87 drop table t1;
88 create table t1 (a int, b int as (a*2) persistent);
89 alter table t1 add index (a,b);
90 create table t2 like t1;
91 drop table t2;
92 drop table t1;
93 # Testing data manipulation operations involving INDEX
94 # on virtual columns can be found in:
95 # - vcol_select.inc
96 #
97 # TODO: FULLTEXT INDEX
98 # SPATIAL INDEX
99 # Error "All parts of a SPATIAL index must be NOT NULL"
100 create table t1 (a int, b geometry as (a+1) persistent, spatial index (b));
101 ERROR 42000: All parts of a SPATIAL index must be NOT NULL
102 create table t1 (a int, b int as (a+1) persistent);
103 alter table t1 add spatial index (b);
104 ERROR HY000: Incorrect arguments to SPATIAL INDEX
105 drop table t1;
106 # FOREIGN KEY
107 # Rejected FK options.
108 create table t1 (a int, b int as (a+1) persistent,
109 foreign key (b) references t2(a) on update set null);
110 ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column
111 create table t1 (a int, b int as (a+1) persistent,
112 foreign key (b) references t2(a) on update cascade);
113 ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column
114 create table t1 (a int, b int as (a+1) persistent,
115 foreign key (b) references t2(a) on delete set null);
116 ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column
117 create table t1 (a int, b int as (a+1) persistent);
118 alter table t1 add foreign key (b) references t2(a) on update set null;
119 ERROR HY000: Cannot define foreign key with ON UPDATE SET NULL clause on a generated column
120 alter table t1 add foreign key (b) references t2(a) on update cascade;
121 ERROR HY000: Cannot define foreign key with ON UPDATE CASCADE clause on a generated column
122 alter table t1 add foreign key (b) references t2(a) on delete set null;
123 ERROR HY000: Cannot define foreign key with ON DELETE SET NULL clause on a generated column
124 drop table t1;
125 # Allowed FK options.
126 create table t2 (a int primary key, b char(5));
127 create table t1 (a int, b int as (a % 10) persistent,
128 foreign key (b) references t2(a) on update restrict);
129 drop table t1;
130 create table t1 (a int, b int as (a % 10) persistent,
131 foreign key (b) references t2(a) on update no action);
132 drop table t1;
133 create table t1 (a int, b int as (a % 10) persistent,
134 foreign key (b) references t2(a) on delete restrict);
135 drop table t1;
136 create table t1 (a int, b int as (a % 10) persistent,
137 foreign key (b) references t2(a) on delete cascade);
138 drop table t1;
139 create table t1 (a int, b int as (a % 10) persistent,
140 foreign key (b) references t2(a) on delete no action);
141 drop table t1;
142
143 # Testing data manipulation operations involving FOREIGN KEY
144 # on virtual columns can be found in:
145 # - vcol_ins_upd.inc
146 # - vcol_select.inc
147 create table t1 (a int, b timestamp as (now()), key (b));
148 ERROR HY000: Function or expression 'current_timestamp()' cannot be used in the GENERATED ALWAYS AS clause of `b`
149 create table t1 (a int, b timestamp as (now()));
150 alter table t1 add index (b);
151 ERROR HY000: Function or expression 'current_timestamp()' cannot be used in the GENERATED ALWAYS AS clause of `b`
152 drop table t1;
153 create table t1 (a int, b varchar(100) as (user()), key (b));
154 ERROR HY000: Function or expression 'user()' cannot be used in the GENERATED ALWAYS AS clause of `b`
155 create table t1 (a int, b varchar(100) as (user()));
156 alter table t1 add index (b);
157 ERROR HY000: Function or expression 'user()' cannot be used in the GENERATED ALWAYS AS clause of `b`
158 drop table t1;
159 create table t1 (a int, b double as (rand()), key (b));
160 ERROR HY000: Function or expression 'rand()' cannot be used in the GENERATED ALWAYS AS clause of `b`
161 create table t1 (a int, b double as (rand()));
162 alter table t1 add index (b);
163 ERROR HY000: Function or expression 'rand()' cannot be used in the GENERATED ALWAYS AS clause of `b`
164 drop table t1;
165 CREATE OR REPLACE TABLE t1 (
166 f2 DOUBLE NOT NULL DEFAULT '0',
167 f3 DOUBLE NOT NULL DEFAULT '0',
168 f4 DOUBLE,
169 f5 DOUBLE DEFAULT '0',
170 v4 DOUBLE AS (IF(f4,f3,f2)) VIRTUAL,
171 KEY (f5),
172 KEY (v4)
173 );
174 INSERT INTO t1 (f2,f3,f4,f5) VALUES (5,4,1,0),(5,7,NULL,0);
175 INSERT INTO t1 (f2,f3,f4,f5) SELECT f2, f3, f5, f3 FROM t1;
176 INSERT INTO t1 (f2,f3,f4,f5) VALUES (5,0,NULL,1);
177 INSERT INTO t1 (f2,f3,f4,f5) SELECT f2, f5, f5, f3 FROM t1;
178 DELETE FROM t1 WHERE f5 = 1 OR v4 = 4 ORDER BY f5,v4 LIMIT 9;
179 SELECT * from t1;
180 f2 f3 f4 f5 v4
181 5 7 NULL 0 5
182 5 4 0 4 5
183 5 7 0 7 5
184 5 0 0 4 5
185 5 0 0 7 5
186 5 7 7 7 7
187 5 1 1 0 1
188 DROP TABLE t1;
189 CREATE TABLE t1 (
190 d DECIMAL(63,0) NOT NULL DEFAULT 0,
191 c VARCHAR(64) NOT NULL DEFAULT '',
192 vd DECIMAL(63,0) AS (d) VIRTUAL,
193 vc VARCHAR(2048) AS (c) VIRTUAL,
194 pk BIGINT AUTO_INCREMENT,
195 PRIMARY KEY(pk));
196 INSERT INTO t1 (d,c) VALUES (0.5,'foo');
197 Warnings:
198 Note 1265 Data truncated for column 'd' at row 1
199 SELECT * FROM t1 WHERE vc != 'bar' ORDER BY vd;
200 d c vd vc pk
201 1 foo 1 foo 1
202 DROP TABLE t1;
203 CREATE TABLE t1 (
204 pk BIGINT,
205 c CHAR(64) NOT NULL DEFAULT '',
206 vc CHAR(64) AS (c) VIRTUAL,
207 PRIMARY KEY(pk),
208 INDEX(vc(32))
209 );
210 DELETE FROM t1 WHERE vc IS NULL ORDER BY pk;
211 DROP TABLE t1;
212 SET sql_mode='';
213 CREATE TABLE t1 (a INT GENERATED ALWAYS AS (1) VIRTUAL,KEY(a)) ENGINE=MyISAM;
214 INSERT INTO t1 SELECT 1 FROM seq_1_to_2 ;
215 Warnings:
216 Warning 1906 The value specified for generated column 'a' in table 't1' has been ignored
217 Warning 1906 The value specified for generated column 'a' in table 't1' has been ignored
218 SELECT * FROM t1;
219 a
220 1
221 1
222 DROP TABLE t1;
223 CREATE TABLE t1 (a INT GENERATED ALWAYS AS (1) VIRTUAL,KEY(a)) ENGINE=MyISAM;
224 INSERT INTO t1 SELECT 1 UNION SELECT 1;
225 Warnings:
226 Warning 1906 The value specified for generated column 'a' in table 't1' has been ignored
227 SELECT * FROM t1;
228 a
229 1
230 DROP TABLE t1;
231 #
232 # Original test
233 #
234 create table t1 (a int, b int as (a+1), c int, index(b));
235 insert t1 (a,c) values (0x7890abcd, 0x76543210);
236 insert t1 (a,c) select seq, sin(seq)*10000 from seq_1_to_1000;
237 explain select * from t1 where b=10;
238 id select_type table type possible_keys key key_len ref rows Extra
239 1 SIMPLE t1 ref b b 5 const 1
240 select * from t1 where b=10;
241 a b c
242 9 10 4121
243
244 MyISAM file: datadir/test/t1
245 Record format: Fixed length
246 Character set: ? (0)
247 Data records: 1001 Deleted blocks: 0
248 Recordlength: 9
249
250 table description:
251 Key Start Len Index Type
252 1 10 4 multip. long NULL
253 update t1 set a=20 where b=10;
254 select * from t1 where b=10;
255 a b c
256 select * from t1 where b=21;
257 a b c
258 20 21 4121
259 20 21 9129
260 delete from t1 where b=21;
261 select * from t1 where b=21;
262 a b c
263 alter table t1 add column d char(20) as (concat(a,c));
264 select * from t1 where b=11;
265 a b c d
266 10 11 -5440 10-5440
267 create index i on t1 (d);
268 check table t1;
269 Table Op Msg_type Msg_text
270 test.t1 check status OK
271 select * from t1 where b=11;
272 a b c d
273 10 11 -5440 10-5440
274 check table t1 quick;
275 Table Op Msg_type Msg_text
276 test.t1 check status OK
277 select * from t1 where b=11;
278 a b c d
279 10 11 -5440 10-5440
280 check table t1 medium;
281 Table Op Msg_type Msg_text
282 test.t1 check status OK
283 select * from t1 where b=11;
284 a b c d
285 10 11 -5440 10-5440
286 check table t1 extended;
287 Table Op Msg_type Msg_text
288 test.t1 check status OK
289 show keys from t1;
290 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
291 t1 1 b 1 b A 999 NULL NULL YES BTREE NO
292 t1 1 i 1 d A 999 NULL NULL YES BTREE NO
293 select * from t1 where b=11;
294 a b c d
295 10 11 -5440 10-5440
296 delete from t1 where b=12;
297 analyze table t1;
298 Table Op Msg_type Msg_text
299 test.t1 analyze status Engine-independent statistics collected
300 test.t1 analyze status OK
301 show keys from t1;
302 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
303 t1 1 b 1 b A 998 NULL NULL YES BTREE NO
304 t1 1 i 1 d A 998 NULL NULL YES BTREE NO
305 select * from t1 where b=11;
306 a b c d
307 10 11 -5440 10-5440
308 optimize table t1;
309 Table Op Msg_type Msg_text
310 test.t1 optimize status OK
311 show keys from t1;
312 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
313 t1 1 b 1 b A 998 NULL NULL YES BTREE NO
314 t1 1 i 1 d A 998 NULL NULL YES BTREE NO
315 select * from t1 where b=11;
316 a b c d
317 10 11 -5440 10-5440
318 repair table t1;
319 Table Op Msg_type Msg_text
320 test.t1 repair status OK
321 select * from t1 where b=11;
322 a b c d
323 10 11 -5440 10-5440
324 repair table t1 quick;
325 Table Op Msg_type Msg_text
326 test.t1 repair status OK
327 select * from t1 where b=11;
328 a b c d
329 10 11 -5440 10-5440
330 repair table t1 extended;
331 Table Op Msg_type Msg_text
332 test.t1 repair status OK
333 select * from t1 where b=11;
334 a b c d
335 10 11 -5440 10-5440
336 repair table t1 use_frm;
337 Table Op Msg_type Msg_text
338 test.t1 repair warning Number of rows changed from 0 to 998
339 test.t1 repair status OK
340 select * from t1 where b=11;
341 a b c d
342 10 11 -5440 10-5440
343 update t1 set a=30 where b=11;
344 select * from t1 where b=11;
345 a b c d
346 select * from t1 where b=31;
347 a b c d
348 30 31 -5440 30-5440
349 30 31 -9880 30-9880
350 drop table t1;
351 #
352 # MDEV-11606 Server crashes in mi_make_key / sort_key_read
353 #
354 CREATE TABLE t1 (
355 pk BIGINT AUTO_INCREMENT,
356 col_date DATE NULL,
357 col_datetime DATETIME(1) NULL,
358 col_int TINYINT(13) UNSIGNED ZEROFILL NULL,
359 col_varchar VARBINARY(2222) NULL,
360 col_timestamp TIMESTAMP(2) NULL,
361 col_bit BIT(64) NOT NULL DEFAULT 0,
362 col_blob MEDIUMBLOB NULL,
363 col_dec DECIMAL(10,9) ZEROFILL NOT NULL DEFAULT 0,
364 col_time TIME(4) NULL,
365 col_year YEAR NOT NULL DEFAULT '1970',
366 col_char CHAR(129) NULL,
367 col_enum SET('','a','b','c','d','e','f','foo','bar') NULL,
368 vcol_dec DECIMAL(50,18) ZEROFILL AS (col_dec) VIRTUAL,
369 vcol_bit BIT(48) AS (col_bit) VIRTUAL,
370 vcol_char CHAR(224) AS (col_char) VIRTUAL,
371 vcol_datetime DATETIME(4) AS (col_datetime) VIRTUAL,
372 vcol_year YEAR AS (col_year) VIRTUAL,
373 vcol_varchar VARBINARY(356) AS (col_varchar) VIRTUAL,
374 vcol_blob MEDIUMBLOB AS (col_blob) VIRTUAL,
375 vcol_timestamp TIMESTAMP(5) AS (col_timestamp) VIRTUAL,
376 vcol_int BIGINT(46) AS (col_int) VIRTUAL,
377 vcol_time TIME(1) AS (col_time) VIRTUAL,
378 vcol_date DATE AS (col_date) VIRTUAL,
379 vcol_enum SET('','a','b','c','d','e','f','foo','bar') AS (col_enum) VIRTUAL,
380 UNIQUE(pk),
381 PRIMARY KEY(pk)
382 ) ENGINE=MyISAM;
383 SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
384 ALTER TABLE t1 ADD INDEX(col_enum,vcol_int);
385 SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
386 ALTER TABLE t1 ADD INDEX(col_year);
387 DROP TABLE t1;
388 create table t1 (
389 pk int primary key auto_increment,
390 b bit default null,
391 key(b)
392 ) engine=myisam;
393 insert into t1 values (null, 0);
394 repair table t1 extended;
395 Table Op Msg_type Msg_text
396 test.t1 repair status OK
397 drop table t1;
398 create table t1 ( id int primary key,
399 hexid varchar(10) generated always as (hex(id)) stored,
400 key (hexid)) engine=myisam;
401 insert into t1 (id) select 100;
402 select * from t1;
403 id hexid
404 100 64
405 drop table t1;
406 #
407 # MDEV-15881 Assertion `is_valid_value_slow()' failed in Datetime::Datetime or corrupt data after ALTER with indexed persistent column
408 #
409 CREATE TABLE t1 (i INT, d1 DATE, d2 DATE NOT NULL, t TIMESTAMP, KEY(t)) ENGINE=MyISAM;
410 INSERT INTO t1 VALUES (1,'2023-03-16','2023-03-15','2012-12-12 12:12:12');
411 ALTER TABLE t1 MODIFY t FLOAT AS (i) PERSISTENT;
412 SELECT i, d1, d2 INTO OUTFILE 'load_t1' FROM t1;
413 DELETE FROM t1;
414 LOAD DATA INFILE 'load_t1' INTO TABLE t1 (i,d1,d2);
415 SELECT * FROM t1 WHERE d2 < d1;
416 i d1 d2 t
417 1 2023-03-16 2023-03-15 1
418 DROP TABLE t1;
419 CREATE TABLE t1 (
420 i INT DEFAULT NULL,
421 d1 DATE DEFAULT NULL,
422 d2 DATE NOT NULL,
423 t FLOAT GENERATED ALWAYS AS (i) STORED,
424 KEY (t)
425 ) ENGINE=MyISAM;
426 LOAD DATA INFILE 'load_t1' INTO TABLE t1 (i,d1,d2);
427 SELECT * FROM t1 WHERE d2 < d1;
428 i d1 d2 t
429 1 2023-03-16 2023-03-15 1
430 DROP TABLE t1;
431 #
432 # MDEV-20015 Assertion `!in_use->is_error()' failed in TABLE::update_virtual_field
433 #
434 create or replace table t1 (a int);
435 insert into t1 (a) values (1), (1);
436 create or replace table t2 (pk int, b int, c int as (b) virtual, primary key (pk), key(c));
437 insert into t2 (pk) select a from t1;
438 ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
439 drop tables t1, t2;
440 #
441 # MDEV-23294 Segfault or assertion upon MyISAM repair
442 #
443 set @old_mode= @@sql_mode;
444 set @old_myisam_repair_threads= @@myisam_repair_threads;
445 set sql_mode='', myisam_repair_threads=2;
446 create table t (a binary,b blob,c blob as (concat (a,b)),h char,index (c)) engine=innodb;
447 Warnings:
448 Warning 1286 Unknown storage engine 'innodb'
449 Warning 1266 Using storage engine MyISAM for table 't'
450 Note 1071 Specified key was too long; max key length is 1000 bytes
451 insert into t values (0,0,default,0);
452 create table ti like t;
453 alter table ti engine=myisam;
454 insert into ti select * from t;
455 Warnings:
456 Warning 1906 The value specified for generated column 'c' in table 'ti' has been ignored
457 drop tables ti, t;
458 create table t (id int,a varchar(1),b varchar(1),c varchar(1) generated always as (concat (a,b)),key(c)) engine=myisam;
459 insert into t values (0,0,9687,0);
460 Warnings:
461 Warning 1265 Data truncated for column 'b' at row 1
462 Warning 1906 The value specified for generated column 'c' in table 't' has been ignored
463 Warning 1265 Data truncated for column 'c' at row 1
464 repair table t quick;
465 Table Op Msg_type Msg_text
466 test.t repair status OK
467 drop table t;
468 create table t1 (b varchar(1024), c char(3), unique(b,c)) engine=myisam;
469 insert into t1 values ('foo','baz');
470 alter table t1 disable keys;
471 set session myisam_repair_threads= 2;
472 insert into t1 select 'qux';
473 ERROR 21S01: Column count doesn't match value count at row 1
474 drop table t1;
475 set sql_mode= @old_mode;
476 set myisam_repair_threads= @old_myisam_repair_threads;