release/src/router/mysql/mysql-test/t/auto_increment.test

   1 #
   2 # Test of auto_increment;  The test for BDB tables is in bdb.test
   3 #
   4 --disable_warnings
   5 drop table if exists t1;
   6 drop table if exists t2;
   7 --enable_warnings
   8 SET SQL_WARNINGS=1;
   9
  10 create table t1 (a int not null auto_increment,b int, primary key (a)) engine=myisam auto_increment=3;
  11 insert into t1 values (1,1),(NULL,3),(NULL,4);
  12 delete from t1 where a=4;
  13 insert into t1 values (NULL,5),(NULL,6);
  14 select * from t1;
  15 delete from t1 where a=6;
  16 #show table status like "t1";
  17 replace t1 values (3,1);
  18 ALTER TABLE t1 add c int;
  19 replace t1 values (3,3,3);
  20 insert into t1 values (NULL,7,7);
  21 update t1 set a=8,b=b+1,c=c+1 where a=7;
  22 insert into t1 values (NULL,9,9);
  23 select * from t1;
  24 drop table t1;
  25
  26 create table t1 (
  27   skey tinyint unsigned NOT NULL auto_increment PRIMARY KEY,
  28   sval char(20)
  29 );
  30 insert into t1 values (NULL, "hello");
  31 insert into t1 values (NULL, "hey");
  32 select * from t1;
  33 select _rowid,t1._rowid,skey,sval from t1;
  34 drop table t1;
  35
  36 #
  37 # Test auto_increment on sub key
  38 #
  39 create table t1 (a char(10) not null, b int not null auto_increment, primary key(a,b));
  40 insert into t1 values ("a",1),("b",2),("a",2),("c",1);
  41 insert into t1 values ("a",NULL),("b",NULL),("c",NULL),("e",NULL);
  42 insert into t1 (a) values ("a"),("b"),("c"),("d");
  43 insert into t1 (a) values ('k'),('d');
  44 insert into t1 (a) values ("a");
  45 insert into t1 values ("d",last_insert_id());
  46 select * from t1;
  47 drop table t1;
  48
  49 create table t1 (ordid int(8) not null auto_increment, ord  varchar(50) not null, primary key (ordid), index(ord,ordid));
  50 insert into t1 (ordid,ord) values (NULL,'sdj'),(NULL,'sdj');
  51 select * from t1;
  52 drop table t1;
  53
  54 create table t1 (ordid int(8) not null auto_increment, ord  varchar(50) not null, primary key (ord,ordid));
  55 insert into t1 values (NULL,'sdj'),(NULL,'sdj'),(NULL,"abc"),(NULL,'abc'),(NULL,'zzz'),(NULL,'sdj'),(NULL,'abc');
  56 select * from t1;
  57 drop table t1;
  58
  59 create table t1 (sid char(5), id int(2) NOT NULL auto_increment, key(sid,  id));
  60 create table t2 (sid char(20), id int(2));
  61 insert into t2 values ('skr',NULL),('skr',NULL),('test',NULL);
  62 insert into t1 select * from t2;
  63 select * from t1;
  64 drop table t1,t2;
  65
  66 #
  67 # Test of auto_increment columns when they are set to 0
  68 #
  69
  70 create table t1 (a int not null primary key auto_increment);
  71 insert into t1 values (0);
  72 update t1 set a=0;
  73 select * from t1;
  74 check table t1;
  75 drop table t1;
  76
  77 #
  78 # Test negative values (Bug #1366)
  79 #
  80
  81 create table t1 (a int not null auto_increment primary key);
  82 insert into t1 values (NULL);
  83 insert into t1 values (-1);
  84 select last_insert_id();
  85 insert into t1 values (NULL);
  86 select * from t1;
  87 drop table t1;
  88
  89 create table t1 (a int not null auto_increment primary key) /*!40102 engine=heap */;
  90 insert into t1 values (NULL);
  91 insert into t1 values (-1);
  92 select last_insert_id();
  93 insert into t1 values (NULL);
  94 select * from t1;
  95 drop table t1;
  96 #
  97 # last_insert_id() madness
  98 #
  99 create table t1 (i tinyint unsigned not null auto_increment primary key);
 100 insert into t1 set i = 254;
 101 insert into t1 set i = null;
 102 select last_insert_id();
 103 explain extended select last_insert_id();
 104 --error ER_DUP_ENTRY
 105 insert into t1 set i = 254;
 106 select last_insert_id();
 107 --error ER_DUP_ENTRY
 108 insert into t1 set i = null;
 109 select last_insert_id();
 110 drop table t1;
 111
 112 create table t1 (i tinyint unsigned not null auto_increment, key (i));
 113 insert into t1 set i = 254;
 114 insert into t1 set i = null;
 115 select last_insert_id();
 116 insert into t1 set i = null;
 117 select last_insert_id();
 118 drop table t1;
 119
 120 create table t1 (i tinyint unsigned not null auto_increment primary key, b int, unique (b));
 121 insert into t1 values (NULL, 10);
 122 select last_insert_id();
 123 insert into t1 values (NULL, 15);
 124 select last_insert_id();
 125 --error ER_DUP_ENTRY
 126 insert into t1 values (NULL, 10);
 127 select last_insert_id();
 128
 129 drop table t1;
 130
 131 create table t1(a int auto_increment,b int null,primary key(a));
 132 SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
 133 insert into t1(a,b)values(NULL,1);
 134 insert into t1(a,b)values(200,2);
 135 insert into t1(a,b)values(0,3);
 136 insert into t1(b)values(4);
 137 insert into t1(b)values(5);
 138 insert into t1(b)values(6);
 139 insert into t1(b)values(7);
 140 select * from t1 order by b;
 141 alter table t1 modify b mediumint;
 142 select * from t1 order by b;
 143 create table t2 (a int);
 144 insert t2 values (1),(2);
 145 alter table t2 add b int auto_increment primary key;
 146 select * from t2;
 147 drop table t2;
 148 delete from t1 where a=0;
 149 update t1 set a=0 where b=5;
 150 select * from t1 order by b;
 151 delete from t1 where a=0;
 152 update t1 set a=NULL where b=6;
 153 update t1 set a=300 where b=7;
 154 SET SQL_MODE='';
 155 insert into t1(a,b)values(NULL,8);
 156 insert into t1(a,b)values(400,9);
 157 insert into t1(a,b)values(0,10);
 158 insert into t1(b)values(11);
 159 insert into t1(b)values(12);
 160 insert into t1(b)values(13);
 161 insert into t1(b)values(14);
 162 select * from t1 order by b;
 163 delete from t1 where a=0;
 164 update t1 set a=0 where b=12;
 165 select * from t1 order by b;
 166 delete from t1 where a=0;
 167 update t1 set a=NULL where b=13;
 168 update t1 set a=500 where b=14;
 169 select * from t1 order by b;
 170 drop table t1;
 171
 172 #
 173 # Test of behavior of ALTER TABLE when coulmn containing NULL or zeroes is
 174 # converted to AUTO_INCREMENT column
 175 #
 176 create table t1 (a bigint);
 177 insert into t1 values (1), (2), (3), (NULL), (NULL);
 178 alter table t1 modify a bigint not null auto_increment primary key;
 179 select * from t1;
 180 drop table t1;
 181
 182 create table t1 (a bigint);
 183 insert into t1 values (1), (2), (3), (0), (0);
 184 alter table t1 modify a bigint not null auto_increment primary key;
 185 select * from t1;
 186 drop table t1;
 187
 188 # We still should be able to preserve zero in NO_AUTO_VALUE_ON_ZERO mode
 189 create table t1 (a bigint);
 190 insert into t1 values (0), (1), (2), (3);
 191 set sql_mode=NO_AUTO_VALUE_ON_ZERO;
 192 alter table t1 modify a bigint not null auto_increment primary key;
 193 set sql_mode= '';
 194 select * from t1;
 195 drop table t1;
 196
 197 # It also sensible to preserve zeroes if we are converting auto_increment
 198 # column to auto_increment column (or not touching it at all, which is more
 199 # common case probably)
 200 create table t1 (a int auto_increment primary key , b int null);
 201 set sql_mode=NO_AUTO_VALUE_ON_ZERO;
 202 insert into t1 values (0,1),(1,2),(2,3);
 203 select * from t1;
 204 set sql_mode= '';
 205 alter table t1 modify b varchar(255);
 206 insert into t1 values (0,4);
 207 select * from t1;
 208 drop table t1;
 209
 210 #
 211 # BUG #10045: Problem with composite AUTO_INCREMENT + BLOB key
 212
 213 CREATE TABLE t1 ( a INT AUTO_INCREMENT, b BLOB, PRIMARY KEY (a,b(10)));
 214 INSERT INTO t1 (b) VALUES ('aaaa');
 215 CHECK TABLE t1;
 216 INSERT INTO t1 (b) VALUES ('');
 217 CHECK TABLE t1;
 218 INSERT INTO t1 (b) VALUES ('bbbb');
 219 CHECK TABLE t1;
 220 DROP TABLE IF EXISTS t1;
 221
 222 # BUG #19025:
 223
 224 CREATE TABLE `t1` (
 225     t1_name VARCHAR(255) DEFAULT NULL,
 226     t1_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 227     KEY (t1_name),
 228     PRIMARY KEY (t1_id)
 229 ) AUTO_INCREMENT = 1000;
 230
 231 INSERT INTO t1 (t1_name) VALUES('MySQL');
 232 INSERT INTO t1 (t1_name) VALUES('MySQL');
 233 INSERT INTO t1 (t1_name) VALUES('MySQL');
 234
 235 SELECT * from t1;
 236
 237 SHOW CREATE TABLE `t1`;
 238
 239 DROP TABLE `t1`;
 240
 241 #
 242 # Bug #6880: LAST_INSERT_ID() within a statement
 243 #
 244
 245 create table t1(a int not null auto_increment primary key);
 246 create table t2(a int not null auto_increment primary key, t1a int);
 247 insert into t1 values(NULL);
 248 insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID());
 249 insert into t1 values (NULL);
 250 insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()),
 251 (NULL, LAST_INSERT_ID());
 252 insert into t1 values (NULL);
 253 insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()),
 254 (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID());
 255 select * from t2;
 256 drop table t1, t2;
 257
 258 --echo End of 4.1 tests
 259
 260 #
 261 # Bug #11080 & #11005  Multi-row REPLACE fails on a duplicate key error
 262 #
 263
 264 CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY  (`a`),UNIQUE KEY `b` (`b`));
 265 insert into t1 (b) values (1);
 266 replace into t1 (b) values (2), (1), (3);
 267 select * from t1;
 268 truncate table t1;
 269 insert into t1 (b) values (1);
 270 replace into t1 (b) values (2);
 271 replace into t1 (b) values (1);
 272 replace into t1 (b) values (3);
 273 select * from t1;
 274 drop table t1;
 275
 276 create table t1 (rowid int not null auto_increment, val int not null,primary
 277 key (rowid), unique(val));
 278 replace into t1 (val) values ('1'),('2');
 279 replace into t1 (val) values ('1'),('2');
 280 --error ER_DUP_ENTRY
 281 insert into t1 (val) values ('1'),('2');
 282 select * from t1;
 283 drop table t1;
 284
 285 #
 286 # Test that update changes internal auto-increment value
 287 #
 288
 289 create table t1 (a int not null auto_increment primary key, val int);
 290 insert into t1 (val) values (1);
 291 update t1 set a=2 where a=1;
 292 insert into t1 (val) values (1);
 293 select * from t1;
 294 drop table t1;
 295
 296 #
 297 # Test key duplications with auto-increment in ALTER TABLE
 298 # bug #14573
 299 #
 300 CREATE TABLE t1 (t1 INT(10) PRIMARY KEY, t2 INT(10));
 301 INSERT INTO t1 VALUES(0, 0);
 302 INSERT INTO t1 VALUES(1, 1);
 303 --error ER_DUP_ENTRY
 304 ALTER TABLE t1 CHANGE t1 t1 INT(10) auto_increment;
 305 DROP TABLE t1;
 306
 307 # Test of REPLACE when it does INSERT+DELETE and not UPDATE:
 308 # see if it sets LAST_INSERT_ID() ok
 309 create table t1 (a int primary key auto_increment, b int, c int, d timestamp default current_timestamp, unique(b),unique(c));
 310 insert into t1 values(null,1,1,now());
 311 insert into t1 values(null,0,0,null);
 312 # this will delete two rows
 313 replace into t1 values(null,1,0,null);
 314 select last_insert_id();
 315 drop table t1;
 316
 317 # Test of REPLACE when it does a INSERT+DELETE for all the conflicting rows
 318 # (i.e.) when there are three rows conflicting in unique key columns with
 319 # a row that is being inserted, all the three rows will be deleted and then
 320 # the new rows will be inserted.
 321 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));
 322 insert into t1 values(null,1,1,1,now());
 323 insert into t1 values(null,0,0,0,null);
 324 replace into t1 values(null,1,0,2,null);
 325 select last_insert_id();
 326 drop table t1;
 327
 328 --echo #
 329 --echo # Bug#46616: Assertion `!table->auto_increment_field_not_null' on view
 330 --echo # manipulations
 331 --echo #
 332 CREATE TABLE t1 ( a INT );
 333 INSERT INTO t1 VALUES (1), (1);
 334
 335 CREATE TABLE t2 ( a INT AUTO_INCREMENT KEY );
 336 --error ER_DUP_ENTRY
 337 CREATE TABLE IF NOT EXISTS t2 AS SELECT a FROM t1;
 338
 339 UPDATE t2 SET a = 2;
 340
 341 SELECT a FROM t2;
 342
 343 DROP TABLE t1, t2;
 344
 345 --echo #
 346 --echo # Bug#39828 autoinc wraps around when offset and increment > 1
 347 --echo #
 348
 349 CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) engine=MyISAM;
 350 INSERT INTO t1 VALUES(1);
 351 INSERT INTO t1 VALUES (18446744073709551601);
 352
 353 SET @@SESSION.AUTO_INCREMENT_INCREMENT=10;
 354
 355 SELECT @@SESSION.AUTO_INCREMENT_OFFSET;
 356 --error ER_WARN_DATA_OUT_OF_RANGE
 357 INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
 358 SELECT * FROM t1;
 359
 360 SET @@SESSION.AUTO_INCREMENT_INCREMENT=default;
 361 SET @@SESSION.AUTO_INCREMENT_OFFSET=default;
 362
 363 DROP TABLE t1;
 364
 365 --echo End of 5.1 tests
 366
 367 --echo #
 368 --echo # Bug#50619 assert in handler::update_auto_increment
 369 --echo #
 370
 371 CREATE TABLE t1 (pk INT AUTO_INCREMENT, PRIMARY KEY (pk));
 372 # This triggered the assert
 373 INSERT INTO t1 VALUES (NULL), (-1), (NULL);
 374 SELECT * FROM t1;
 375 DROP TABLE t1;
 376
 377 # Check that that true overflow still gives error
 378 CREATE TABLE t1 (pk BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY (pk));
 379 --error ER_AUTOINC_READ_FAILED
 380 INSERT INTO t1 VALUES (NULL), (18446744073709551615-1), (NULL);
 381 SELECT * FROM t1;
 382 DROP TABLE t1;