insert.test
上传用户:romrleung
上传日期:2022-05-23
资源大小:18897k
文件大小:5k
- #
- # Test of refering to old values
- #
- --disable_warnings
- drop table if exists t1,t2;
- --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;
- #
- # Test of wrong values for float data (bug #2082)
- #
- # PS gives sligthly different numbers for max-float/max-double
- --disable_ps_protocol
- use test;
- 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);
- --replace_result e-0 e- e+0 e+
- --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);
- --replace_result e-0 e- e+0 e+
- --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);
- --replace_result e-0 e- e+0 e+
- --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);
- --replace_result e-0 e- e+0 e+
- --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);
- --replace_result e-0 e- e+0 e+
- --query_vertical select * from t1 where number =last_insert_id()
- set @value= 1e+1111111111;
- insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value);
- --replace_result e-0 e- e+0 e+
- --query_vertical select * from t1 where number =last_insert_id()
- set @value= -1e+1111111111;
- insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value);
- --replace_result e-0 e- e+0 e+
- --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);
- --replace_result e-0 e- e+0 e+
- --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);
- --replace_result e-0 e- e+0 e+
- --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);
- --replace_result e-0 e- e+0 e+
- --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);
- --replace_result e-0 e- e+0 e+
- --query_vertical select * from t1 where number =last_insert_id()
- drop table t1;
- --enable_ps_protocol
- # End of 4.1 tests