# # Test of refering to old values # --disable_warnings drop table if exists t1,t2,t3; --enable_warnings create table t1 (a int not null); insert into t1 values (1); insert into t1 values (a+2); insert into t1 values (a+3); insert into t1 values (4),(a+5); select * from t1; drop table t1; # # Test of duplicate key values with packed keys # create table t1 (id int not null auto_increment primary key, username varchar(32) not null, unique (username)); insert into t1 values (0,"mysql"); insert into t1 values (0,"mysql ab"); insert into t1 values (0,"mysql a"); insert into t1 values (0,"r1manic"); insert into t1 values (0,"r1man"); drop table t1; # # Test insert syntax # create table t1 (a int not null auto_increment, primary key (a), t timestamp, c char(10) default "hello", i int); insert into t1 values (default,default,default,default), (default,default,default,default), (4,0,"a",5),(default,default,default,default); select a,t>0,c,i from t1; truncate table t1; insert into t1 set a=default,t=default,c=default; insert into t1 set a=default,t=default,c=default,i=default; insert into t1 set a=4,t=0,c="a",i=5; insert into t1 set a=5,t=0,c="a",i=null; insert into t1 set a=default,t=default,c=default,i=default; select a,t>0,c,i from t1; drop table t1; # # Test problem with bulk insert and auto_increment on second part keys # create table t1 (sid char(20), id int(2) NOT NULL auto_increment, key(sid, id)); insert into t1 values ('skr',NULL),('skr',NULL),('test',NULL); select * from t1; insert into t1 values ('rts',NULL),('rts',NULL),('test',NULL); select * from t1; drop table t1; # #Test of behaviour with INSERT VALUES (NULL) # create table t1 (id int NOT NULL DEFAULT 8); -- error 1048 insert into t1 values(NULL); insert into t1 values (1), (NULL), (2); select * from t1; drop table t1; # # Test if insert ... select distinct # create table t1 (email varchar(50)); insert into t1 values ('sasha@mysql.com'),('monty@mysql.com'),('foo@hotmail.com'),('foo@aol.com'),('bar@aol.com'); create table t2(id int not null auto_increment primary key, t2 varchar(50), unique(t2)); insert delayed into t2 (t2) select distinct substring(email, locate('@', email)+1) from t1; select * from t2; drop table t1,t2; # # Test of mysqld crash with fully qualified column names # --disable_warnings drop database if exists mysqltest; --enable_warnings create database mysqltest; use mysqltest; create table t1 (c int); insert into mysqltest.t1 set mysqltest.t1.c = '1'; drop database mysqltest; use test; # # Test of wrong values for float data (bug #2082) # # PS gives sligthly different numbers for max-float/max-double --disable_ps_protocol create table t1(number int auto_increment primary key, original_value varchar(50), f_double double, f_float float, f_double_7_2 double(7,2), f_float_4_3 float (4,3), f_double_u double unsigned, f_float_u float unsigned, f_double_15_1_u double(15,1) unsigned, f_float_3_1_u float (3,1) unsigned); set @value= "aa"; insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value); --query_vertical select * from t1 where number =last_insert_id() set @value= "1aa"; insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value); --query_vertical select * from t1 where number =last_insert_id() set @value= "aa1"; insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value); --query_vertical select * from t1 where number =last_insert_id() set @value= "1e+1111111111a"; insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value); --query_vertical select * from t1 where number =last_insert_id() set @value= "-1e+1111111111a"; insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value); --query_vertical select * from t1 where number =last_insert_id() --error 1367 set @value= 1e+1111111111; --error 1367 set @value= -1e+1111111111; set @value= 1e+111; insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value); --query_vertical select * from t1 where number =last_insert_id() set @value= -1e+111; insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value); --query_vertical select * from t1 where number =last_insert_id() set @value= 1; insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value); --query_vertical select * from t1 where number =last_insert_id() set @value= -1; insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value); --query_vertical select * from t1 where number =last_insert_id() drop table t1; --enable_ps_protocol # End of 4.1 tests # # Test automatic result buffering with INSERT INTO t1 ... SELECT ... FROM t1 # create table t1(id1 int not null auto_increment primary key, t char(12)); create table t2(id2 int not null, t char(12)); create table t3(id3 int not null, t char(12), index(id3)); disable_query_log; let $1 = 100; while ($1) { let $2 = 5; eval insert into t1(t) values ('$1'); while ($2) { eval insert into t2(id2,t) values ($1,'$2'); let $3 = 10; while ($3) { eval insert into t3(id3,t) values ($1,'$2'); dec $3; } dec $2; } dec $1; } enable_query_log; select count(*) from t2; insert into t2 select t1.* from t1, t2 t, t3 where t1.id1 = t.id2 and t.id2 = t3.id3; select count(*) from t2; drop table t1,t2,t3; # # Test for values returned by ROW_COUNT() function # (and thus for values returned by mysql_affected_rows()) # for various forms of INSERT # create table t1 (id int primary key, data int); insert into t1 values (1, 1), (2, 2), (3, 3); select row_count(); insert ignore into t1 values (1, 1); select row_count(); # Reports that 2 rows are affected (1 deleted + 1 inserted) replace into t1 values (1, 11); select row_count(); replace into t1 values (4, 4); select row_count(); # Reports that 2 rows are affected. This conforms to documentation. # (Useful for differentiating inserts from updates). insert into t1 values (2, 2) on duplicate key update data= data + 10; select row_count(); insert into t1 values (5, 5) on duplicate key update data= data + 10; select row_count(); drop table t1;