526 lines
13 KiB
Plaintext
526 lines
13 KiB
Plaintext
drop table if exists t1;
|
|
drop table if exists t2;
|
|
SET SQL_WARNINGS=1;
|
|
create table t1 (a int not null auto_increment,b int, primary key (a)) auto_increment=3;
|
|
insert into t1 values (1,1),(NULL,3),(NULL,4);
|
|
delete from t1 where a=4;
|
|
insert into t1 values (NULL,5),(NULL,6);
|
|
select * from t1;
|
|
a b
|
|
1 1
|
|
3 3
|
|
5 5
|
|
6 6
|
|
delete from t1 where a=6;
|
|
replace t1 values (3,1);
|
|
ALTER TABLE t1 add c int;
|
|
replace t1 values (3,3,3);
|
|
insert into t1 values (NULL,7,7);
|
|
update t1 set a=8,b=b+1,c=c+1 where a=7;
|
|
insert into t1 values (NULL,9,9);
|
|
select * from t1;
|
|
a b c
|
|
1 1 NULL
|
|
3 3 3
|
|
5 5 NULL
|
|
8 8 8
|
|
9 9 9
|
|
drop table t1;
|
|
create table t1 (
|
|
skey tinyint unsigned NOT NULL auto_increment PRIMARY KEY,
|
|
sval char(20)
|
|
);
|
|
insert into t1 values (NULL, "hello");
|
|
insert into t1 values (NULL, "hey");
|
|
select * from t1;
|
|
skey sval
|
|
1 hello
|
|
2 hey
|
|
select _rowid,t1._rowid,skey,sval from t1;
|
|
_rowid _rowid skey sval
|
|
1 1 1 hello
|
|
2 2 2 hey
|
|
drop table t1;
|
|
create table t1 (a int not null primary key auto_increment);
|
|
insert into t1 values (0);
|
|
update t1 set a=0;
|
|
select * from t1;
|
|
a
|
|
0
|
|
check table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
drop table t1;
|
|
create table t1 (a int not null auto_increment primary key);
|
|
insert into t1 values (NULL);
|
|
insert into t1 values (-1);
|
|
select last_insert_id();
|
|
last_insert_id()
|
|
1
|
|
insert into t1 values (NULL);
|
|
select * from t1;
|
|
a
|
|
-1
|
|
1
|
|
2
|
|
drop table t1;
|
|
create table t1 (a int not null auto_increment primary key) /*!40102 engine=heap */;
|
|
insert into t1 values (NULL);
|
|
insert into t1 values (-1);
|
|
select last_insert_id();
|
|
last_insert_id()
|
|
1
|
|
insert into t1 values (NULL);
|
|
select * from t1;
|
|
a
|
|
1
|
|
-1
|
|
2
|
|
drop table t1;
|
|
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
|
|
create table t1 (i tinyint unsigned not null auto_increment primary key);
|
|
insert into t1 set i = 254;
|
|
insert into t1 set i = null;
|
|
select last_insert_id();
|
|
last_insert_id()
|
|
255
|
|
explain select last_insert_id();
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select last_insert_id() AS `last_insert_id()`
|
|
insert into t1 set i = 254;
|
|
ERROR 23000: Duplicate entry '254' for key 'PRIMARY'
|
|
select last_insert_id();
|
|
last_insert_id()
|
|
255
|
|
insert into t1 set i = null;
|
|
ERROR 23000: Duplicate entry '255' for key 'PRIMARY'
|
|
select last_insert_id();
|
|
last_insert_id()
|
|
255
|
|
drop table t1;
|
|
create table t1 (i tinyint unsigned not null auto_increment, key (i));
|
|
insert into t1 set i = 254;
|
|
insert into t1 set i = null;
|
|
select last_insert_id();
|
|
last_insert_id()
|
|
255
|
|
insert into t1 set i = null;
|
|
select last_insert_id();
|
|
last_insert_id()
|
|
255
|
|
drop table t1;
|
|
create table t1 (i tinyint unsigned not null auto_increment primary key, b int, unique (b));
|
|
insert into t1 values (NULL, 10);
|
|
select last_insert_id();
|
|
last_insert_id()
|
|
1
|
|
insert into t1 values (NULL, 15);
|
|
select last_insert_id();
|
|
last_insert_id()
|
|
2
|
|
insert into t1 values (NULL, 10);
|
|
ERROR 23000: Duplicate entry '10' for key 'b'
|
|
select last_insert_id();
|
|
last_insert_id()
|
|
2
|
|
drop table t1;
|
|
SET sql_mode = default;
|
|
create table t1(a int auto_increment,b int null,primary key(a));
|
|
SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
|
|
insert into t1(a,b)values(NULL,1);
|
|
insert into t1(a,b)values(200,2);
|
|
insert into t1(a,b)values(0,3);
|
|
insert into t1(b)values(4);
|
|
insert into t1(b)values(5);
|
|
insert into t1(b)values(6);
|
|
insert into t1(b)values(7);
|
|
select * from t1 order by b;
|
|
a b
|
|
1 1
|
|
200 2
|
|
0 3
|
|
201 4
|
|
202 5
|
|
203 6
|
|
204 7
|
|
alter table t1 modify b mediumint;
|
|
select * from t1 order by b;
|
|
a b
|
|
1 1
|
|
200 2
|
|
0 3
|
|
201 4
|
|
202 5
|
|
203 6
|
|
204 7
|
|
create table t2 (a int);
|
|
insert t2 values (1),(2);
|
|
alter table t2 add b int auto_increment primary key;
|
|
select * from t2;
|
|
a b
|
|
1 1
|
|
2 2
|
|
drop table t2;
|
|
delete from t1 where a=0;
|
|
update t1 set a=0 where b=5;
|
|
select * from t1 order by b;
|
|
a b
|
|
1 1
|
|
200 2
|
|
201 4
|
|
0 5
|
|
203 6
|
|
204 7
|
|
delete from t1 where a=0;
|
|
update t1 set a=NULL where b=6;
|
|
Warnings:
|
|
Warning 1048 Column 'a' cannot be null
|
|
update t1 set a=300 where b=7;
|
|
SET SQL_MODE='';
|
|
insert into t1(a,b)values(NULL,8);
|
|
insert into t1(a,b)values(400,9);
|
|
insert into t1(a,b)values(0,10);
|
|
insert into t1(b)values(11);
|
|
insert into t1(b)values(12);
|
|
insert into t1(b)values(13);
|
|
insert into t1(b)values(14);
|
|
select * from t1 order by b;
|
|
a b
|
|
1 1
|
|
200 2
|
|
201 4
|
|
0 6
|
|
300 7
|
|
301 8
|
|
400 9
|
|
401 10
|
|
402 11
|
|
403 12
|
|
404 13
|
|
405 14
|
|
delete from t1 where a=0;
|
|
update t1 set a=0 where b=12;
|
|
select * from t1 order by b;
|
|
a b
|
|
1 1
|
|
200 2
|
|
201 4
|
|
300 7
|
|
301 8
|
|
400 9
|
|
401 10
|
|
402 11
|
|
0 12
|
|
404 13
|
|
405 14
|
|
delete from t1 where a=0;
|
|
update t1 set a=NULL where b=13;
|
|
Warnings:
|
|
Warning 1048 Column 'a' cannot be null
|
|
update t1 set a=500 where b=14;
|
|
select * from t1 order by b;
|
|
a b
|
|
1 1
|
|
200 2
|
|
201 4
|
|
300 7
|
|
301 8
|
|
400 9
|
|
401 10
|
|
402 11
|
|
0 13
|
|
500 14
|
|
drop table t1;
|
|
create table t1 (a bigint);
|
|
insert into t1 values (1), (2), (3), (NULL), (NULL);
|
|
alter table t1 modify a bigint not null auto_increment primary key;
|
|
select * from t1;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
drop table t1;
|
|
create table t1 (a bigint);
|
|
insert into t1 values (1), (2), (3), (0), (0);
|
|
alter table t1 modify a bigint not null auto_increment primary key;
|
|
select * from t1;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
drop table t1;
|
|
create table t1 (a bigint);
|
|
insert into t1 values (0), (1), (2), (3);
|
|
set sql_mode=NO_AUTO_VALUE_ON_ZERO;
|
|
alter table t1 modify a bigint not null auto_increment primary key;
|
|
set sql_mode= '';
|
|
select * from t1;
|
|
a
|
|
0
|
|
1
|
|
2
|
|
3
|
|
drop table t1;
|
|
create table t1 (a int auto_increment primary key , b int null);
|
|
set sql_mode=NO_AUTO_VALUE_ON_ZERO;
|
|
insert into t1 values (0,1),(1,2),(2,3);
|
|
select * from t1;
|
|
a b
|
|
0 1
|
|
1 2
|
|
2 3
|
|
set sql_mode= '';
|
|
alter table t1 modify b varchar(255);
|
|
insert into t1 values (0,4);
|
|
select * from t1;
|
|
a b
|
|
0 1
|
|
1 2
|
|
2 3
|
|
3 4
|
|
drop table t1;
|
|
CREATE TABLE t1 ( a INT AUTO_INCREMENT, b BLOB, PRIMARY KEY (a,b(10)));
|
|
INSERT INTO t1 (b) VALUES ('aaaa');
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
INSERT INTO t1 (b) VALUES ('');
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
INSERT INTO t1 (b) VALUES ('bbbb');
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
DROP TABLE IF EXISTS t1;
|
|
CREATE TABLE `t1` (
|
|
t1_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
t1_name VARCHAR(255) DEFAULT NULL,
|
|
KEY (t1_name),
|
|
PRIMARY KEY (t1_id)
|
|
) AUTO_INCREMENT = 1000;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
INSERT INTO t1 (t1_name) VALUES('MySQL');
|
|
INSERT INTO t1 (t1_name) VALUES('MySQL');
|
|
INSERT INTO t1 (t1_name) VALUES('MySQL');
|
|
SELECT * from t1;
|
|
t1_id t1_name
|
|
1000 MySQL
|
|
1001 MySQL
|
|
1002 MySQL
|
|
SHOW CREATE TABLE `t1`;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`t1_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
|
`t1_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
|
|
PRIMARY KEY (`t1_id`),
|
|
KEY `t1_name` (`t1_name`)
|
|
) ENGINE=XENGINE AUTO_INCREMENT=1003 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
DROP TABLE `t1`;
|
|
create table t1(a int not null auto_increment primary key);
|
|
create table t2(a int not null auto_increment primary key, t1a int);
|
|
insert into t1 values(NULL);
|
|
insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID());
|
|
insert into t1 values (NULL);
|
|
insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()),
|
|
(NULL, LAST_INSERT_ID());
|
|
insert into t1 values (NULL);
|
|
insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()),
|
|
(NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID());
|
|
select * from t2;
|
|
a t1a
|
|
1 1
|
|
2 1
|
|
3 2
|
|
4 2
|
|
5 2
|
|
6 3
|
|
7 3
|
|
8 3
|
|
9 3
|
|
drop table t1, t2;
|
|
End of 4.1 tests
|
|
CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`));
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
insert into t1 (b) values (1);
|
|
replace into t1 (b) values (2), (1), (3);
|
|
select * from t1;
|
|
a b
|
|
2 2
|
|
3 1
|
|
4 3
|
|
truncate table t1;
|
|
insert into t1 (b) values (1);
|
|
replace into t1 (b) values (2);
|
|
replace into t1 (b) values (1);
|
|
replace into t1 (b) values (3);
|
|
select * from t1;
|
|
a b
|
|
2 2
|
|
3 1
|
|
4 3
|
|
drop table t1;
|
|
create table t1 (rowid int not null auto_increment, val int not null,primary
|
|
key (rowid), unique(val));
|
|
replace into t1 (val) values ('1'),('2');
|
|
replace into t1 (val) values ('1'),('2');
|
|
insert into t1 (val) values ('1'),('2');
|
|
ERROR 23000: Duplicate entry '1' for key 'val'
|
|
select * from t1;
|
|
rowid val
|
|
3 1
|
|
4 2
|
|
drop table t1;
|
|
create table t1 (a int not null auto_increment primary key, val int);
|
|
insert into t1 (val) values (1);
|
|
update t1 set a=2 where a=1;
|
|
insert into t1 (val) values (1);
|
|
select * from t1;
|
|
a val
|
|
2 1
|
|
3 1
|
|
drop table t1;
|
|
CREATE TABLE t1 (t1 INT(10) PRIMARY KEY, t2 INT(10));
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
INSERT INTO t1 VALUES(0, 0);
|
|
INSERT INTO t1 VALUES(1, 1);
|
|
ALTER TABLE t1 CHANGE t1 t1 INT(10) auto_increment;
|
|
ERROR 23000: ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'
|
|
DROP TABLE t1;
|
|
create table t1 (a int primary key auto_increment, b int, c int, d timestamp default current_timestamp, unique(b),unique(c));
|
|
insert into t1 values(null,1,1,now());
|
|
insert into t1 values(null,0,0,null);
|
|
replace into t1 values(null,1,0,null);
|
|
select last_insert_id();
|
|
last_insert_id()
|
|
3
|
|
drop table t1;
|
|
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));
|
|
insert into t1 values(null,1,1,1,now());
|
|
insert into t1 values(null,0,0,0,null);
|
|
replace into t1 values(null,1,0,2,null);
|
|
select last_insert_id();
|
|
last_insert_id()
|
|
3
|
|
drop table t1;
|
|
#
|
|
# Bug#46616: Assertion `!table->auto_increment_field_not_null' on view
|
|
# manipulations
|
|
#
|
|
CREATE TABLE t1 ( a INT );
|
|
INSERT INTO t1 VALUES (1), (1);
|
|
CREATE TABLE t2 ( a INT AUTO_INCREMENT KEY );
|
|
INSERT INTO t2 SELECT a FROM t1;
|
|
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
|
|
UPDATE t2 SET a = 2;
|
|
SELECT a FROM t2;
|
|
a
|
|
DROP TABLE t1, t2;
|
|
#
|
|
# Bug#39828 autoinc wraps around when offset and increment > 1
|
|
#
|
|
CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1));
|
|
INSERT INTO t1 VALUES(1);
|
|
INSERT INTO t1 VALUES (18446744073709551601);
|
|
SET @@SESSION.AUTO_INCREMENT_INCREMENT=10;
|
|
SELECT @@SESSION.AUTO_INCREMENT_OFFSET;
|
|
@@SESSION.AUTO_INCREMENT_OFFSET
|
|
1
|
|
INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
|
|
ERROR 22003: Out of range value for column 't1' at row 167
|
|
SELECT * FROM t1;
|
|
c1
|
|
1
|
|
18446744073709551601
|
|
SET @@SESSION.AUTO_INCREMENT_INCREMENT=default;
|
|
SET @@SESSION.AUTO_INCREMENT_OFFSET=default;
|
|
DROP TABLE t1;
|
|
End of 5.1 tests
|
|
#
|
|
# Bug#50619 assert in handler::update_auto_increment
|
|
#
|
|
CREATE TABLE t1 (pk INT AUTO_INCREMENT, PRIMARY KEY (pk));
|
|
INSERT INTO t1 VALUES (NULL), (-1), (NULL);
|
|
SELECT * FROM t1;
|
|
pk
|
|
-1
|
|
1
|
|
2
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (pk BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY (pk));
|
|
INSERT INTO t1 VALUES (NULL), (18446744073709551615-1), (NULL);
|
|
ERROR HY000: Failed to read auto-increment value from storage engine
|
|
SELECT * FROM t1;
|
|
pk
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug #27041393: ASSERTION `HAS_ERROR == THD->GET_STMT_DA()->IS_ERROR()'
|
|
# FAILED.
|
|
#
|
|
USE test;
|
|
SET sql_mode = 'STRICT_TRANS_TABLES';
|
|
Warnings:
|
|
Warning 3135 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
|
|
CREATE TABLE t1 (c1 DOUBLE NOT NULL AUTO_INCREMENT, c2 INT, PRIMARY KEY (c1))
|
|
ENGINE=XENGINE;
|
|
Warnings:
|
|
Warning 3856 AUTO_INCREMENT support for FLOAT/DOUBLE columns is deprecated and will be removed in a future release. Consider removing AUTO_INCREMENT from column 'c1'.
|
|
# Single row insert:
|
|
INSERT INTO t1 VALUES (0x7FFFFFFFFFFFFFFD, 2);
|
|
ERROR 22007: Truncated incorrect INTEGER value: '9.223372036854776e18'
|
|
SELECT * FROM t1;
|
|
c1 c2
|
|
INSERT INTO t1 (c2) VALUES (3);
|
|
SELECT * FROM t1;
|
|
c1 c2
|
|
1 3
|
|
DELETE FROM t1;
|
|
# Multi row insert:
|
|
INSERT INTO t1 VALUES (0x7FFFFFFFFFFFFFFD, 2), (NULL, 3);
|
|
ERROR 22007: Truncated incorrect INTEGER value: '9.223372036854776e18'
|
|
SELECT * FROM t1;
|
|
c1 c2
|
|
DROP TABLE t1;
|
|
SET sql_mode = '';
|
|
CREATE TABLE t1 (c1 DOUBLE NOT NULL AUTO_INCREMENT, c2 INT, PRIMARY KEY (c1))
|
|
ENGINE=XENGINE;
|
|
Warnings:
|
|
Warning 3856 AUTO_INCREMENT support for FLOAT/DOUBLE columns is deprecated and will be removed in a future release. Consider removing AUTO_INCREMENT from column 'c1'.
|
|
# Single row insert:
|
|
INSERT INTO t1 VALUES (0x7FFFFFFFFFFFFFFD, 2);
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect INTEGER value: '9.223372036854776e18'
|
|
Warning 1292 Truncated incorrect INTEGER value: '9.223372036854776e18'
|
|
Warning 1292 Truncated incorrect INTEGER value: '9.223372036854776e18'
|
|
SELECT * FROM t1;
|
|
c1 c2
|
|
9.223372036854776e18 2
|
|
INSERT INTO t1 (c2) VALUES (3);
|
|
SELECT * FROM t1;
|
|
c1 c2
|
|
1 3
|
|
9.223372036854776e18 2
|
|
DELETE FROM t1;
|
|
# Multi row insert:
|
|
INSERT INTO t1 VALUES (0x7FFFFFFFFFFFFFFD, 2), (NULL, 3);
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect INTEGER value: '9.223372036854776e18'
|
|
Warning 1292 Truncated incorrect INTEGER value: '9.223372036854776e18'
|
|
SELECT * FROM t1;
|
|
c1 c2
|
|
2 3
|
|
9.223372036854776e18 2
|
|
DROP TABLE t1;
|
|
SET sql_mode = DEFAULT;
|