~drizzle-trunk/drizzle/development

#

# Test of auto_increment;  The test for BDB tables is in bdb.test

#

--disable_warnings

drop table if exists t1;

drop table if exists t2;

--enable_warnings

SET SQL_WARNINGS=1;

create table t1 (a int not null auto_increment,b int, primary key (a)) engine=myisam 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;

delete from t1 where a=6;

#show table status like "t1";

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;

drop table t1;

create table t1 (

  skey int NOT NULL auto_increment PRIMARY KEY,

  sval char(20)

);

insert into t1 values (NULL, "hello");

insert into t1 values (NULL, "hey");

select * from t1;

select _rowid,t1._rowid,skey,sval from t1;

drop table t1;

#

# Test auto_increment on sub key

#

#Drizzle does not support auto_increment on sub key.

#create table t1 (a char(10) not null, b int not null auto_increment, primary key(b));

#insert into t1 values ("a",1),("b",2),("a",2),("c",1);

#insert into t1 values ("a",NULL),("b",NULL),("c",NULL),("e",NULL);

#insert into t1 (a) values ("a"),("b"),("c"),("d");

#insert into t1 (a) values ('k'),('d');

#insert into t1 (a) values ("a");

#insert into t1 values ("d",last_insert_id());

#select * from t1;

#drop table t1;

#create table t1 (ordid int(8) not null auto_increment, ord  varchar(50) not null, primary key (ordid), index(ord,ordid)); 

#insert into t1 (ordid,ord) values (NULL,'sdj'),(NULL,'sdj');

#select * from t1;

#drop table t1;

#create table t1 (ordid int(8) not null auto_increment, ord  varchar(50) not null, primary key (ord,ordid));

#insert into t1 values (NULL,'sdj'),(NULL,'sdj'),(NULL,"abc"),(NULL,'abc'),(NULL,'zzz'),(NULL,'sdj'),(NULL,'abc');

#select * from t1;

#drop table t1;

#create table t1 (sid char(5), id int(2) NOT NULL auto_increment, key(sid,  id));

#create table t2 (sid char(20), id int(2));

#insert into t2 values ('skr',NULL),('skr',NULL),('test',NULL);

#insert into t1 select * from t2;

#select * from t1;

#drop table t1,t2;

#

# Test of auto_increment columns when they are set to 0

#

create table t1 (a int not null primary key auto_increment);

insert into t1 values (0);

update t1 set a=0;

select * from t1;

check table t1;

drop table t1;

#

# Test negative values (Bug #1366)

#

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();

insert into t1 values (NULL);

select * from t1;

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();

insert into t1 values (NULL);

select * from t1;

drop table t1;

#

# last_insert_id() madness

# Bug 314554 - Autoincrement succeeds where it should have failed

#at line 109

#create table t1 (i int not null auto_increment primary key);

#insert into t1 set i = 254;

#insert into t1 set i = null;

#select last_insert_id();

#explain extended select last_insert_id();

#--error ER_DUP_ENTRY

#insert into t1 set i = 254;

#select last_insert_id();

#--error ER_DUP_ENTRY

#insert into t1 set i = null;

#select last_insert_id();

#drop table t1;

create table t1 (i int not null auto_increment, key (i));

insert into t1 set i = 254;

insert into t1 set i = null;

select last_insert_id();

insert into t1 set i = null;

select last_insert_id();

drop table t1;

create table t1 (i int not null auto_increment primary key, b int, unique (b));

insert into t1 values (NULL, 10);

select last_insert_id();

insert into t1 values (NULL, 15);

select last_insert_id();

--error ER_DUP_ENTRY

insert into t1 values (NULL, 10);

select last_insert_id();

drop table t1;

#SQL_MODE was removed from Drizzle.

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;

#mediumint is not supported. changed to b int.

alter table t1 modify b int;

select * from t1 order by b;

create table t2 (a int);

insert t2 values (1),(2);

alter table t2 add b int auto_increment primary key;

select * from t2;

drop table t2;

delete from t1 where a=0;

update t1 set a=0 where b=5;

select * from t1 order by b;

delete from t1 where a=0;

--error 1048

update t1 set a=NULL where b=6;

update t1 set a=300 where b=7;

#SQL_MODE var is not supported.

#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;

delete from t1 where a=0;

update t1 set a=0 where b=12;

select * from t1 order by b;

delete from t1 where a=0;

--error 1048

update t1 set a=NULL where b=13;

update t1 set a=500 where b=14;

select * from t1 order by b;

drop table t1;

#

# Test of behavior of ALTER TABLE when coulmn containing NULL or zeroes is

# converted to AUTO_INCREMENT column

#

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;

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;

drop table t1;

# We still should be able to preserve zero in NO_AUTO_VALUE_ON_ZERO mode

#create table t1 (a bigint);

#insert into t1 values (0), (1), (2), (3);

#sql_mode not supported

#set sql_mode=NO_AUTO_VALUE_ON_ZERO;

# Bug314567 -  ALTER TABLE causes auto_increment resequencing,

# resulting in duplicate entry since sql_mode=NO_AUTO_VALUE_ON_ZERO

#is not supported.

#alter table t1 modify a bigint not null auto_increment primary key; 

#set sql_mode= '';

#select * from t1;

#drop table t1;

# It also sensible to preserve zeroes if we are converting auto_increment

# column to auto_increment column (or not touching it at all, which is more

# common case probably)

create table t1 (a int auto_increment primary key , b int null);

#sql_mode is not supported.

#set sql_mode=NO_AUTO_VALUE_ON_ZERO;

#insert into t1 values (0,1),(1,2),(2,3);

#select * from t1;

#set sql_mode= '';

alter table t1 modify b varchar(255);

insert into t1 values (0,4);

select * from t1;

drop table t1;

#

# BUG #10045: Problem with composite AUTO_INCREMENT + BLOB key

CREATE TABLE t1 ( a INT AUTO_INCREMENT, b BLOB, PRIMARY KEY (a,b(10)));

INSERT INTO t1 (b) VALUES ('aaaa');

CHECK TABLE t1;

INSERT INTO t1 (b) VALUES ('');

CHECK TABLE t1;

INSERT INTO t1 (b) VALUES ('bbbb');

CHECK TABLE t1;

DROP TABLE IF EXISTS t1;

# BUG #19025:

#changed syntax of int(10) to int

CREATE TABLE t1 (

    t1_name VARCHAR(255) DEFAULT NULL,

    t1_id INT not null AUTO_INCREMENT,

    KEY (t1_name),

    PRIMARY KEY (t1_id)

) AUTO_INCREMENT = 1000;

INSERT INTO t1 (t1_name) VALUES('MySQL');

INSERT INTO t1 (t1_name) VALUES('MySQL');

INSERT INTO t1 (t1_name) VALUES('MySQL');

SELECT * from t1;

SHOW CREATE TABLE `t1`;

DROP TABLE `t1`;

#

# Bug #6880: LAST_INSERT_ID() within a statement

#

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;

drop table t1, t2;

--echo End of 4.1 tests

#

# Bug #11080 & #11005  Multi-row REPLACE fails on a duplicate key error

# 

#changed syntax of int (11) to int

CREATE TABLE t1 ( `a` int NOT NULL auto_increment, `b` int default NULL,PRIMARY KEY  (`a`),UNIQUE KEY `b` (`b`));

insert into t1 (b) values (1);

replace into t1 (b) values (2), (1), (3);

select * from t1;

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;

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');

--error ER_DUP_ENTRY

insert into t1 (val) values ('1'),('2');

select * from t1;

drop table t1;

#

# Test that update changes internal auto-increment value

# Bug 314570 

#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;

#drop table t1;

#

# Test key duplications with auto-increment in ALTER TABLE

# bug #14573

#

#changed syntax of int(11) to int.

CREATE TABLE t1 (t1 INT PRIMARY KEY, t2 INT);

INSERT INTO t1 VALUES(0, 0);

INSERT INTO t1 VALUES(1, 1);

--error ER_DUP_ENTRY

ALTER TABLE t1 CHANGE t1 t1 INT auto_increment;

DROP TABLE t1;

# Test of REPLACE when it does INSERT+DELETE and not UPDATE:

# see if it sets LAST_INSERT_ID() ok

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);

# this will delete two rows

replace into t1 values(null,1,0,null);

select last_insert_id();

drop table t1;