| blob | edd23050294cf657797fc0ecf14bd56ad4732fe6 |
1 drop table if exists t1;
2 drop table if exists t2;
3 SET SQL_WARNINGS=1;
4 create table t1 (a int not null auto_increment,b int, primary key (a)) engine=myisam auto_increment=3;
5 insert into t1 values (1,1),(NULL,3),(NULL,4);
6 delete from t1 where a=4;
7 insert into t1 values (NULL,5),(NULL,6);
8 select * from t1;
9 a b
10 1 1
11 3 3
12 5 5
13 6 6
14 delete from t1 where a=6;
15 replace t1 values (3,1);
16 ALTER TABLE t1 add c int;
17 replace t1 values (3,3,3);
18 insert into t1 values (NULL,7,7);
19 update t1 set a=8,b=b+1,c=c+1 where a=7;
20 insert into t1 values (NULL,9,9);
21 select * from t1;
22 a b c
23 1 1 NULL
24 3 3 3
25 5 5 NULL
26 8 8 8
27 9 9 9
28 drop table t1;
29 create table t1 (
30 skey tinyint unsigned NOT NULL auto_increment PRIMARY KEY,
31 sval char(20)
32 );
33 insert into t1 values (NULL, "hello");
34 insert into t1 values (NULL, "hey");
35 select * from t1;
36 skey sval
37 1 hello
38 2 hey
39 select _rowid,t1._rowid,skey,sval from t1;
40 _rowid _rowid skey sval
41 1 1 1 hello
42 2 2 2 hey
43 drop table t1;
44 create table t1 (a char(10) not null, b int not null auto_increment, primary key(a,b));
45 insert into t1 values ("a",1),("b",2),("a",2),("c",1);
46 insert into t1 values ("a",NULL),("b",NULL),("c",NULL),("e",NULL);
47 insert into t1 (a) values ("a"),("b"),("c"),("d");
48 insert into t1 (a) values ('k'),('d');
49 insert into t1 (a) values ("a");
50 insert into t1 values ("d",last_insert_id());
51 select * from t1;
52 a b
53 a 1
54 a 2
55 a 3
56 a 4
57 a 5
58 b 2
59 b 3
60 b 4
61 c 1
62 c 2
63 c 3
64 d 1
65 d 2
66 d 5
67 e 1
68 k 1
69 drop table t1;
70 create table t1 (ordid int(8) not null auto_increment, ord varchar(50) not null, primary key (ordid), index(ord,ordid));
71 insert into t1 (ordid,ord) values (NULL,'sdj'),(NULL,'sdj');
72 select * from t1;
73 ordid ord
74 1 sdj
75 2 sdj
76 drop table t1;
77 create table t1 (ordid int(8) not null auto_increment, ord varchar(50) not null, primary key (ord,ordid));
78 insert into t1 values (NULL,'sdj'),(NULL,'sdj'),(NULL,"abc"),(NULL,'abc'),(NULL,'zzz'),(NULL,'sdj'),(NULL,'abc');
79 select * from t1;
80 ordid ord
81 1 abc
82 2 abc
83 3 abc
84 1 sdj
85 2 sdj
86 3 sdj
87 1 zzz
88 drop table t1;
89 create table t1 (sid char(5), id int(2) NOT NULL auto_increment, key(sid, id));
90 create table t2 (sid char(20), id int(2));
91 insert into t2 values ('skr',NULL),('skr',NULL),('test',NULL);
92 insert into t1 select * from t2;
93 select * from t1;
94 sid id
95 skr 1
96 skr 2
97 test 1
98 drop table t1,t2;
99 create table t1 (a int not null primary key auto_increment);
100 insert into t1 values (0);
101 update t1 set a=0;
102 select * from t1;
103 a
104 0
105 check table t1;
106 Table Op Msg_type Msg_text
107 test.t1 check warning Found row where the auto_increment column has the value 0
108 test.t1 check status OK
109 drop table t1;
110 create table t1 (a int not null auto_increment primary key);
111 insert into t1 values (NULL);
112 insert into t1 values (-1);
113 select last_insert_id();
114 last_insert_id()
115 1
116 insert into t1 values (NULL);
117 select * from t1;
118 a
119 -1
120 1
121 2
122 drop table t1;
123 create table t1 (a int not null auto_increment primary key) /*!40102 engine=heap */;
124 insert into t1 values (NULL);
125 insert into t1 values (-1);
126 select last_insert_id();
127 last_insert_id()
128 1
129 insert into t1 values (NULL);
130 select * from t1;
131 a
132 1
133 -1
134 2
135 drop table t1;
136 create table t1 (i tinyint unsigned not null auto_increment primary key);
137 insert into t1 set i = 254;
138 insert into t1 set i = null;
139 select last_insert_id();
140 last_insert_id()
141 255
142 explain extended select last_insert_id();
143 id select_type table type possible_keys key key_len ref rows filtered Extra
144 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
145 Warnings:
146 Note 1003 select last_insert_id() AS `last_insert_id()`
147 insert into t1 set i = 254;
148 ERROR 23000: Duplicate entry '254' for key 'PRIMARY'
149 select last_insert_id();
150 last_insert_id()
151 255
152 insert into t1 set i = null;
153 ERROR 23000: Duplicate entry '255' for key 'PRIMARY'
154 select last_insert_id();
155 last_insert_id()
156 255
157 drop table t1;
158 create table t1 (i tinyint unsigned not null auto_increment, key (i));
159 insert into t1 set i = 254;
160 insert into t1 set i = null;
161 select last_insert_id();
162 last_insert_id()
163 255
164 insert into t1 set i = null;
165 Warnings:
166 Warning 1264 Out of range value for column 'i' at row 1
167 select last_insert_id();
168 last_insert_id()
169 255
170 drop table t1;
171 create table t1 (i tinyint unsigned not null auto_increment primary key, b int, unique (b));
172 insert into t1 values (NULL, 10);
173 select last_insert_id();
174 last_insert_id()
175 1
176 insert into t1 values (NULL, 15);
177 select last_insert_id();
178 last_insert_id()
179 2
180 insert into t1 values (NULL, 10);
181 ERROR 23000: Duplicate entry '10' for key 'b'
182 select last_insert_id();
183 last_insert_id()
184 2
185 drop table t1;
186 create table t1(a int auto_increment,b int null,primary key(a));
187 SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
188 insert into t1(a,b)values(NULL,1);
189 insert into t1(a,b)values(200,2);
190 insert into t1(a,b)values(0,3);
191 insert into t1(b)values(4);
192 insert into t1(b)values(5);
193 insert into t1(b)values(6);
194 insert into t1(b)values(7);
195 select * from t1 order by b;
196 a b
197 1 1
198 200 2
199 0 3
200 201 4
201 202 5
202 203 6
203 204 7
204 alter table t1 modify b mediumint;
205 select * from t1 order by b;
206 a b
207 1 1
208 200 2
209 0 3
210 201 4
211 202 5
212 203 6
213 204 7
214 create table t2 (a int);
215 insert t2 values (1),(2);
216 alter table t2 add b int auto_increment primary key;
217 select * from t2;
218 a b
219 1 1
220 2 2
221 drop table t2;
222 delete from t1 where a=0;
223 update t1 set a=0 where b=5;
224 select * from t1 order by b;
225 a b
226 1 1
227 200 2
228 201 4
229 0 5
230 203 6
231 204 7
232 delete from t1 where a=0;
233 update t1 set a=NULL where b=6;
234 Warnings:
235 Warning 1048 Column 'a' cannot be null
236 update t1 set a=300 where b=7;
237 SET SQL_MODE='';
238 insert into t1(a,b)values(NULL,8);
239 insert into t1(a,b)values(400,9);
240 insert into t1(a,b)values(0,10);
241 insert into t1(b)values(11);
242 insert into t1(b)values(12);
243 insert into t1(b)values(13);
244 insert into t1(b)values(14);
245 select * from t1 order by b;
246 a b
247 1 1
248 200 2
249 201 4
250 0 6
251 300 7
252 301 8
253 400 9
254 401 10
255 402 11
256 403 12
257 404 13
258 405 14
259 delete from t1 where a=0;
260 update t1 set a=0 where b=12;
261 select * from t1 order by b;
262 a b
263 1 1
264 200 2
265 201 4
266 300 7
267 301 8
268 400 9
269 401 10
270 402 11
271 0 12
272 404 13
273 405 14
274 delete from t1 where a=0;
275 update t1 set a=NULL where b=13;
276 Warnings:
277 Warning 1048 Column 'a' cannot be null
278 update t1 set a=500 where b=14;
279 select * from t1 order by b;
280 a b
281 1 1
282 200 2
283 201 4
284 300 7
285 301 8
286 400 9
287 401 10
288 402 11
289 0 13
290 500 14
291 drop table t1;
292 create table t1 (a bigint);
293 insert into t1 values (1), (2), (3), (NULL), (NULL);
294 alter table t1 modify a bigint not null auto_increment primary key;
295 select * from t1;
296 a
297 1
298 2
299 3
300 4
301 5
302 drop table t1;
303 create table t1 (a bigint);
304 insert into t1 values (1), (2), (3), (0), (0);
305 alter table t1 modify a bigint not null auto_increment primary key;
306 select * from t1;
307 a
308 1
309 2
310 3
311 4
312 5
313 drop table t1;
314 create table t1 (a bigint);
315 insert into t1 values (0), (1), (2), (3);
316 set sql_mode=NO_AUTO_VALUE_ON_ZERO;
317 alter table t1 modify a bigint not null auto_increment primary key;
318 set sql_mode= '';
319 select * from t1;
320 a
321 0
322 1
323 2
324 3
325 drop table t1;
326 create table t1 (a int auto_increment primary key , b int null);
327 set sql_mode=NO_AUTO_VALUE_ON_ZERO;
328 insert into t1 values (0,1),(1,2),(2,3);
329 select * from t1;
330 a b
331 0 1
332 1 2
333 2 3
334 set sql_mode= '';
335 alter table t1 modify b varchar(255);
336 insert into t1 values (0,4);
337 select * from t1;
338 a b
339 0 1
340 1 2
341 2 3
342 3 4
343 drop table t1;
344 CREATE TABLE t1 ( a INT AUTO_INCREMENT, b BLOB, PRIMARY KEY (a,b(10)));
345 INSERT INTO t1 (b) VALUES ('aaaa');
346 CHECK TABLE t1;
347 Table Op Msg_type Msg_text
348 test.t1 check status OK
349 INSERT INTO t1 (b) VALUES ('');
350 CHECK TABLE t1;
351 Table Op Msg_type Msg_text
352 test.t1 check status OK
353 INSERT INTO t1 (b) VALUES ('bbbb');
354 CHECK TABLE t1;
355 Table Op Msg_type Msg_text
356 test.t1 check status OK
357 DROP TABLE IF EXISTS t1;
358 CREATE TABLE `t1` (
359 t1_name VARCHAR(255) DEFAULT NULL,
360 t1_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
361 KEY (t1_name),
362 PRIMARY KEY (t1_id)
363 ) AUTO_INCREMENT = 1000;
364 INSERT INTO t1 (t1_name) VALUES('MySQL');
365 INSERT INTO t1 (t1_name) VALUES('MySQL');
366 INSERT INTO t1 (t1_name) VALUES('MySQL');
367 SELECT * from t1;
368 t1_name t1_id
369 MySQL 1000
370 MySQL 1001
371 MySQL 1002
372 SHOW CREATE TABLE `t1`;
373 Table Create Table
374 t1 CREATE TABLE `t1` (
375 `t1_name` varchar(255) DEFAULT NULL,
376 `t1_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
377 PRIMARY KEY (`t1_id`),
378 KEY `t1_name` (`t1_name`)
379 ) ENGINE=MyISAM AUTO_INCREMENT=1003 DEFAULT CHARSET=latin1
380 DROP TABLE `t1`;
381 create table t1(a int not null auto_increment primary key);
382 create table t2(a int not null auto_increment primary key, t1a int);
383 insert into t1 values(NULL);
384 insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID());
385 insert into t1 values (NULL);
386 insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()),
387 (NULL, LAST_INSERT_ID());
388 insert into t1 values (NULL);
389 insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()),
390 (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID());
391 select * from t2;
392 a t1a
393 1 1
394 2 1
395 3 2
396 4 2
397 5 2
398 6 3
399 7 3
400 8 3
401 9 3
402 drop table t1, t2;
403 End of 4.1 tests
404 CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`));
405 insert into t1 (b) values (1);
406 replace into t1 (b) values (2), (1), (3);
407 select * from t1;
408 a b
409 3 1
410 2 2
411 4 3
412 truncate table t1;
413 insert into t1 (b) values (1);
414 replace into t1 (b) values (2);
415 replace into t1 (b) values (1);
416 replace into t1 (b) values (3);
417 select * from t1;
418 a b
419 3 1
420 2 2
421 4 3
422 drop table t1;
423 create table t1 (rowid int not null auto_increment, val int not null,primary
424 key (rowid), unique(val));
425 replace into t1 (val) values ('1'),('2');
426 replace into t1 (val) values ('1'),('2');
427 insert into t1 (val) values ('1'),('2');
428 ERROR 23000: Duplicate entry '1' for key 'val'
429 select * from t1;
430 rowid val
431 3 1
432 4 2
433 drop table t1;
434 create table t1 (a int not null auto_increment primary key, val int);
435 insert into t1 (val) values (1);
436 update t1 set a=2 where a=1;
437 insert into t1 (val) values (1);
438 select * from t1;
439 a val
440 2 1
441 3 1
442 drop table t1;
443 CREATE TABLE t1 (t1 INT(10) PRIMARY KEY, t2 INT(10));
444 INSERT INTO t1 VALUES(0, 0);
445 INSERT INTO t1 VALUES(1, 1);
446 ALTER TABLE t1 CHANGE t1 t1 INT(10) auto_increment;
447 ERROR 23000: ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'
448 DROP TABLE t1;
449 create table t1 (a int primary key auto_increment, b int, c int, d timestamp default current_timestamp, unique(b),unique(c));
450 insert into t1 values(null,1,1,now());
451 insert into t1 values(null,0,0,null);
452 replace into t1 values(null,1,0,null);
453 select last_insert_id();
454 last_insert_id()
455 3
456 drop table t1;
457 create table t1 (a int primary key auto_increment, b int, c int, e int, d timestamp default current_timestamp, unique(b),unique(c),unique(e));
458 insert into t1 values(null,1,1,1,now());
459 insert into t1 values(null,0,0,0,null);
460 replace into t1 values(null,1,0,2,null);
461 select last_insert_id();
462 last_insert_id()
463 3
464 drop table t1;
465 #
466 # Bug#46616: Assertion `!table->auto_increment_field_not_null' on view
467 # manipulations
468 #
469 CREATE TABLE t1 ( a INT );
470 INSERT INTO t1 VALUES (1), (1);
471 CREATE TABLE t2 ( a INT AUTO_INCREMENT KEY );
472 CREATE TABLE IF NOT EXISTS t2 AS SELECT a FROM t1;
473 ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
474 UPDATE t2 SET a = 2;
475 SELECT a FROM t2;
476 a
477 2
478 DROP TABLE t1, t2;
479 #
480 # Bug#39828 autoinc wraps around when offset and increment > 1
481 #
482 CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) engine=MyISAM;
483 INSERT INTO t1 VALUES(1);
484 INSERT INTO t1 VALUES (18446744073709551601);
485 SET @@SESSION.AUTO_INCREMENT_INCREMENT=10;
486 SELECT @@SESSION.AUTO_INCREMENT_OFFSET;
487 @@SESSION.AUTO_INCREMENT_OFFSET
488 1
489 INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
490 ERROR 22003: Out of range value for column 't1' at row 167
491 SELECT * FROM t1;
492 c1
493 1
494 18446744073709551601
495 18446744073709551611
496 SET @@SESSION.AUTO_INCREMENT_INCREMENT=default;
497 SET @@SESSION.AUTO_INCREMENT_OFFSET=default;
498 DROP TABLE t1;
499 End of 5.1 tests
500 #
501 # Bug#50619 assert in handler::update_auto_increment
502 #
503 CREATE TABLE t1 (pk INT AUTO_INCREMENT, PRIMARY KEY (pk));
504 INSERT INTO t1 VALUES (NULL), (-1), (NULL);
505 SELECT * FROM t1;
506 pk
507 -1
508 1
509 2
510 DROP TABLE t1;
511 CREATE TABLE t1 (pk BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY (pk));
512 INSERT INTO t1 VALUES (NULL), (18446744073709551615-1), (NULL);
513 ERROR HY000: Failed to read auto-increment value from storage engine
514 SELECT * FROM t1;
515 pk
516 1
517 18446744073709551614
518 DROP TABLE t1;