Edit File: insert.result
drop table if exists t1,t2,t3; create table t1 (a int not null); insert into t1 values (1); insert into t1 values (a+2); insert into t1 values (a+3),(a+4); insert into t1 values (5),(a+6); select * from t1; a 1 2 3 4 5 6 drop table t1; 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; 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; a t>0 c i 1 1 hello NULL 2 1 hello NULL 4 0 a 5 5 1 hello NULL 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; a t>0 c i 1 1 hello NULL 2 1 hello NULL 4 0 a 5 5 0 a NULL 6 1 hello NULL drop table t1; 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; sid id skr 1 skr 2 test 1 insert into t1 values ('rts',NULL),('rts',NULL),('test',NULL); select * from t1; sid id rts 1 rts 2 skr 1 skr 2 test 1 test 2 drop table t1; create table t1 (id int NOT NULL DEFAULT 8); insert into t1 values(NULL); ERROR 23000: Column 'id' cannot be null insert into t1 values (1), (NULL), (2); Warnings: Warning 1048 Column 'id' cannot be null select * from t1; id 1 0 2 drop table t1; 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; id t2 1 mysql.com 2 hotmail.com 3 aol.com drop table t1,t2; drop database if exists mysqltest; create database mysqltest; use mysqltest; create table t1 (c int); insert into mysqltest.t1 set mysqltest.t1.c = '1'; drop database mysqltest; 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); Warnings: Warning 1265 Data truncated for column 'f_double' at row 1 Warning 1265 Data truncated for column 'f_float' at row 1 Warning 1265 Data truncated for column 'f_double_7_2' at row 1 Warning 1265 Data truncated for column 'f_float_4_3' at row 1 Warning 1265 Data truncated for column 'f_double_u' at row 1 Warning 1265 Data truncated for column 'f_float_u' at row 1 Warning 1265 Data truncated for column 'f_double_15_1_u' at row 1 Warning 1265 Data truncated for column 'f_float_3_1_u' at row 1 select * from t1 where number =last_insert_id(); number 1 original_value aa f_double 0 f_float 0 f_double_7_2 0.00 f_float_4_3 0.000 f_double_u 0 f_float_u 0 f_double_15_1_u 0.0 f_float_3_1_u 0.0 set @value= "1aa"; insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value); Warnings: Warning 1265 Data truncated for column 'f_double' at row 1 Warning 1265 Data truncated for column 'f_float' at row 1 Warning 1265 Data truncated for column 'f_double_7_2' at row 1 Warning 1265 Data truncated for column 'f_float_4_3' at row 1 Warning 1265 Data truncated for column 'f_double_u' at row 1 Warning 1265 Data truncated for column 'f_float_u' at row 1 Warning 1265 Data truncated for column 'f_double_15_1_u' at row 1 Warning 1265 Data truncated for column 'f_float_3_1_u' at row 1 select * from t1 where number =last_insert_id(); number 2 original_value 1aa f_double 1 f_float 1 f_double_7_2 1.00 f_float_4_3 1.000 f_double_u 1 f_float_u 1 f_double_15_1_u 1.0 f_float_3_1_u 1.0 set @value= "aa1"; insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value); Warnings: Warning 1265 Data truncated for column 'f_double' at row 1 Warning 1265 Data truncated for column 'f_float' at row 1 Warning 1265 Data truncated for column 'f_double_7_2' at row 1 Warning 1265 Data truncated for column 'f_float_4_3' at row 1 Warning 1265 Data truncated for column 'f_double_u' at row 1 Warning 1265 Data truncated for column 'f_float_u' at row 1 Warning 1265 Data truncated for column 'f_double_15_1_u' at row 1 Warning 1265 Data truncated for column 'f_float_3_1_u' at row 1 select * from t1 where number =last_insert_id(); number 3 original_value aa1 f_double 0 f_float 0 f_double_7_2 0.00 f_float_4_3 0.000 f_double_u 0 f_float_u 0 f_double_15_1_u 0.0 f_float_3_1_u 0.0 set @value= "1e+1111111111a"; insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value); Warnings: Warning 1264 Out of range value for column 'f_double' at row 1 Warning 1264 Out of range value for column 'f_float' at row 1 Warning 1264 Out of range value for column 'f_float' at row 1 Warning 1264 Out of range value for column 'f_double_7_2' at row 1 Warning 1264 Out of range value for column 'f_double_7_2' at row 1 Warning 1264 Out of range value for column 'f_float_4_3' at row 1 Warning 1264 Out of range value for column 'f_float_4_3' at row 1 Warning 1264 Out of range value for column 'f_double_u' at row 1 Warning 1264 Out of range value for column 'f_float_u' at row 1 Warning 1264 Out of range value for column 'f_float_u' at row 1 Warning 1264 Out of range value for column 'f_double_15_1_u' at row 1 Warning 1264 Out of range value for column 'f_double_15_1_u' at row 1 Warning 1264 Out of range value for column 'f_float_3_1_u' at row 1 Warning 1264 Out of range value for column 'f_float_3_1_u' at row 1 select * from t1 where number =last_insert_id(); number 4 original_value 1e+1111111111a f_double 1.7976931348623157e308 f_float 3.40282e38 f_double_7_2 99999.99 f_float_4_3 9.999 f_double_u 1.7976931348623157e308 f_float_u 3.40282e38 f_double_15_1_u 99999999999999.9 f_float_3_1_u 99.9 set @value= "-1e+1111111111a"; insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value); Warnings: Warning 1264 Out of range value for column 'f_double' at row 1 Warning 1264 Out of range value for column 'f_float' at row 1 Warning 1264 Out of range value for column 'f_float' at row 1 Warning 1264 Out of range value for column 'f_double_7_2' at row 1 Warning 1264 Out of range value for column 'f_double_7_2' at row 1 Warning 1264 Out of range value for column 'f_float_4_3' at row 1 Warning 1264 Out of range value for column 'f_float_4_3' at row 1 Warning 1264 Out of range value for column 'f_double_u' at row 1 Warning 1264 Out of range value for column 'f_double_u' at row 1 Warning 1264 Out of range value for column 'f_float_u' at row 1 Warning 1264 Out of range value for column 'f_float_u' at row 1 Warning 1264 Out of range value for column 'f_double_15_1_u' at row 1 Warning 1264 Out of range value for column 'f_double_15_1_u' at row 1 Warning 1264 Out of range value for column 'f_float_3_1_u' at row 1 Warning 1264 Out of range value for column 'f_float_3_1_u' at row 1 select * from t1 where number =last_insert_id(); number 5 original_value -1e+1111111111a f_double -1.7976931348623157e308 f_float -3.40282e38 f_double_7_2 -99999.99 f_float_4_3 -9.999 f_double_u 0 f_float_u 0 f_double_15_1_u 0.0 f_float_3_1_u 0.0 set @value= 1e+1111111111; ERROR 22007: Illegal double '1e+1111111111' value found during parsing set @value= -1e+1111111111; ERROR 22007: Illegal double '1e+1111111111' value found during parsing set @value= 1e+111; insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value); Warnings: Warning 1264 Out of range value for column 'f_float' at row 1 Warning 1264 Out of range value for column 'f_double_7_2' at row 1 Warning 1264 Out of range value for column 'f_float_4_3' at row 1 Warning 1264 Out of range value for column 'f_float_u' at row 1 Warning 1264 Out of range value for column 'f_double_15_1_u' at row 1 Warning 1264 Out of range value for column 'f_float_3_1_u' at row 1 select * from t1 where number =last_insert_id(); number 6 original_value 1e111 f_double 1e111 f_float 3.40282e38 f_double_7_2 99999.99 f_float_4_3 9.999 f_double_u 1e111 f_float_u 3.40282e38 f_double_15_1_u 99999999999999.9 f_float_3_1_u 99.9 set @value= -1e+111; insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value); Warnings: Warning 1264 Out of range value for column 'f_float' at row 1 Warning 1264 Out of range value for column 'f_double_7_2' at row 1 Warning 1264 Out of range value for column 'f_float_4_3' at row 1 Warning 1264 Out of range value for column 'f_double_u' at row 1 Warning 1264 Out of range value for column 'f_float_u' at row 1 Warning 1264 Out of range value for column 'f_double_15_1_u' at row 1 Warning 1264 Out of range value for column 'f_float_3_1_u' at row 1 select * from t1 where number =last_insert_id(); number 7 original_value -1e111 f_double -1e111 f_float -3.40282e38 f_double_7_2 -99999.99 f_float_4_3 -9.999 f_double_u 0 f_float_u 0 f_double_15_1_u 0.0 f_float_3_1_u 0.0 set @value= 1; insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value); select * from t1 where number =last_insert_id(); number 8 original_value 1 f_double 1 f_float 1 f_double_7_2 1.00 f_float_4_3 1.000 f_double_u 1 f_float_u 1 f_double_15_1_u 1.0 f_float_3_1_u 1.0 set @value= -1; insert into t1 values(null,@value,@value,@value,@value,@value,@value,@value,@value,@value); Warnings: Warning 1264 Out of range value for column 'f_double_u' at row 1 Warning 1264 Out of range value for column 'f_float_u' at row 1 Warning 1264 Out of range value for column 'f_double_15_1_u' at row 1 Warning 1264 Out of range value for column 'f_float_3_1_u' at row 1 select * from t1 where number =last_insert_id(); number 9 original_value -1 f_double -1 f_float -1 f_double_7_2 -1.00 f_float_4_3 -1.000 f_double_u 0 f_float_u 0 f_double_15_1_u 0.0 f_float_3_1_u 0.0 drop table 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)); select count(*) from t2; count(*) 500 insert into t2 select t1.* from t1, t2 t, t3 where t1.id1 = t.id2 and t.id2 = t3.id3; select count(*) from t2; count(*) 25500 drop table t1,t2,t3; create table t1 (a int, b int); insert into t1 (a,b) values (a,b); insert into t1 SET a=1, b=a+1; insert into t1 (a,b) select 1,2; INSERT INTO t1 ( a ) SELECT 0 ON DUPLICATE KEY UPDATE a = a + VALUES (a); prepare stmt1 from ' replace into t1 (a,a) select 100, ''hundred'' '; execute stmt1; ERROR 42000: Column 'a' specified twice insert into t1 (a,b,b) values (1,1,1); ERROR 42000: Column 'b' specified twice insert into t1 (a,a) values (1,1,1); ERROR 21S01: Column count doesn't match value count at row 1 insert into t1 (a,a) values (1,1); ERROR 42000: Column 'a' specified twice insert into t1 SET a=1,b=2,a=1; ERROR 42000: Column 'a' specified twice insert into t1 (b,b) select 1,2; ERROR 42000: Column 'b' specified twice INSERT INTO t1 (b,b) SELECT 0,0 ON DUPLICATE KEY UPDATE a = a + VALUES (a); ERROR 42000: Column 'b' specified twice drop table t1; create table t1 (id int primary key, data int); insert into t1 values (1, 1), (2, 2), (3, 3); select row_count(); row_count() 3 insert ignore into t1 values (1, 1); select row_count(); row_count() 0 replace into t1 values (1, 11); select row_count(); row_count() 2 replace into t1 values (4, 4); select row_count(); row_count() 1 insert into t1 values (2, 2) on duplicate key update data= data + 10; select row_count(); row_count() 2 insert into t1 values (5, 5) on duplicate key update data= data + 10; select row_count(); row_count() 1 drop table t1; create table t1 (f1 int unique, f2 int); create table t2 (f3 int, f4 int); create view v1 as select * from t1, t2 where f1= f3; insert into t1 values (1,11), (2,22); insert into t2 values (1,12), (2,24); insert into v1 (f1) values (3) on duplicate key update f3= f3 + 10; ERROR HY000: Can not modify more than one base table through a join view 'test.v1' insert into v1 (f1) values (3) on duplicate key update f1= f3 + 10; ERROR HY000: Can not modify more than one base table through a join view 'test.v1' select * from t1; f1 f2 1 11 2 22 insert into v1 (f1) values (3) on duplicate key update f1= f3 + 10; ERROR HY000: Can not modify more than one base table through a join view 'test.v1' select * from t1; f1 f2 1 11 2 22 drop view v1; drop table t1,t2; create table t1 (id int primary key auto_increment, data int, unique(data)); insert ignore into t1 values(NULL,100),(NULL,110),(NULL,120); insert ignore into t1 values(NULL,10),(NULL,20),(NULL,110),(NULL,120),(NULL,100),(NULL,90); insert ignore into t1 values(NULL,130),(NULL,140),(500,110),(550,120),(450,100),(NULL,150); select * from t1 order by id; id data 1 100 2 110 3 120 4 10 5 20 6 90 7 130 8 140 9 150 drop table t1; DROP TABLE IF EXISTS t1; DROP FUNCTION IF EXISTS f1; DROP FUNCTION IF EXISTS f2; CREATE TABLE t1 (i INT); CREATE FUNCTION f1() RETURNS INT BEGIN INSERT INTO t1 VALUES (1); RETURN 1; END | CREATE FUNCTION f2() RETURNS INT BEGIN INSERT DELAYED INTO t1 VALUES (2); RETURN 1; END | SELECT f1(); f1() 1 SELECT f2(); f2() 1 INSERT INTO t1 VALUES (3); INSERT DELAYED INTO t1 VALUES (4); INSERT INTO t1 VALUES (f1()); ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. INSERT DELAYED INTO t1 VALUES (f1()); ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. INSERT INTO t1 VALUES (f2()); ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. INSERT DELAYED INTO t1 VALUES (f2()); ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t1 VALUES (NEW.i); INSERT INTO t1 VALUES (1); ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. INSERT DELAYED INTO t1 VALUES (1); ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. SELECT * FROM t1; i 1 2 3 4 DROP FUNCTION f2; DROP FUNCTION f1; DROP TABLE t1; DROP TABLE IF EXISTS t1, t2; CREATE TABLE t1 (i INT); CREATE TABLE t2 (i INT); CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT DELAYED INTO t2 VALUES (NEW.i); CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW INSERT DELAYED INTO t2 VALUES (NEW.i); CREATE TRIGGER t1_bd BEFORE DELETE ON t1 FOR EACH ROW INSERT DELAYED INTO t2 VALUES (OLD.i); INSERT INTO t1 VALUES (1); INSERT DELAYED INTO t1 VALUES (2); SELECT * FROM t1; i 1 2 UPDATE t1 SET i = 3 WHERE i = 1; SELECT * FROM t1; i 3 2 DELETE FROM t1 WHERE i = 3; SELECT * FROM t1; i 2 SELECT * FROM t2; i 1 2 3 3 DROP TABLE t1, t2; DROP TABLE IF EXISTS t1, t2; CREATE TABLE t1 (i INT); CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a= NEW.i; SET @a= 0; INSERT DELAYED INTO t1 VALUES (1); SELECT @a; @a 1 INSERT DELAYED INTO t1 VALUES (2); SELECT @a; @a 2 DROP TABLE t1; CREATE TABLE t1 (i INT); CREATE TABLE t2 (i INT); CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (NEW.i); CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW INSERT DELAYED INTO t2 VALUES (NEW.i); CREATE TRIGGER t1_ad AFTER DELETE ON t1 FOR EACH ROW INSERT DELAYED INTO t2 VALUES (OLD.i); INSERT DELAYED INTO t1 VALUES (1); SELECT * FROM t1; i 1 UPDATE t1 SET i = 2 WHERE i = 1; SELECT * FROM t1; i 2 DELETE FROM t1 WHERE i = 2; SELECT * FROM t1; i SELECT * FROM t2; i 1 2 2 DROP TABLE t1, t2; CREATE TABLE t1 ( a char(20) NOT NULL, b char(7) DEFAULT NULL, c char(4) DEFAULT NULL ); INSERT INTO t1(a,b,c) VALUES (9.999999e+0, 9.999999e+0, 9.999e+0); INSERT INTO t1(a,b,c) VALUES (1.225e-05, 1.225e-05, 1.225e-05); Warnings: Warning 1265 Data truncated for column 'c' at row 1 INSERT INTO t1(a,b) VALUES (1.225e-04, 1.225e-04); INSERT INTO t1(a,b) VALUES (1.225e-01, 1.225e-01); INSERT INTO t1(a,b) VALUES (1.225877e-01, 1.225877e-01); INSERT INTO t1(a,b) VALUES (1.225e+01, 1.225e+01); INSERT INTO t1(a,b,c) VALUES (1.225e+01, 1.225e+01, 1.225e+01); INSERT INTO t1(a,b) VALUES (1.225e+05, 1.225e+05); INSERT INTO t1(a,b) VALUES (1.225e+10, 1.225e+10); INSERT INTO t1(a,b) VALUES (1.225e+15, 1.225e+15); INSERT INTO t1(a,b) VALUES (5000000e+0, 5000000e+0); INSERT INTO t1(a,b) VALUES (1.25e+78, 1.25e+78); INSERT INTO t1(a,b) VALUES (1.25e-94, 1.25e-94); INSERT INTO t1(a,b) VALUES (1.25e+203, 1.25e+203); INSERT INTO t1(a,b) VALUES (1.25e-175, 1.25e-175); INSERT INTO t1(a,c) VALUES (1.225e+0, 1.225e+0); INSERT INTO t1(a,c) VALUES (1.37e+0, 1.37e+0); INSERT INTO t1(a,c) VALUES (-1.37e+0, -1.37e+0); INSERT INTO t1(a,c) VALUES (1.87e-3, 1.87e-3); Warnings: Warning 1265 Data truncated for column 'c' at row 1 INSERT INTO t1(a,c) VALUES (-1.87e-2, -1.87e-2); INSERT INTO t1(a,c) VALUES (5000e+0, 5000e+0); INSERT INTO t1(a,c) VALUES (-5000e+0, -5000e+0); SELECT * FROM t1; a b c 9.999999 10 10 0.00001225 1.22e-5 1e-5 0.0001225 1.22e-4 NULL 0.1225 0.1225 NULL 0.1225877 0.12259 NULL 12.25 12.25 NULL 12.25 12.25 12.2 122500 122500 NULL 12250000000 1.22e10 NULL 1.225e15 1.22e15 NULL 5000000 5000000 NULL 1.25e78 1.25e78 NULL 1.25e-94 1.2e-94 NULL 1.25e203 1.2e203 NULL 1.25e-175 1e-175 NULL 1.225 NULL 1.23 1.37 NULL 1.37 -1.37 NULL -1.4 0.00187 NULL 2e-3 -0.0187 NULL 0 5000 NULL 5000 -5000 NULL -5e3 DROP TABLE t1; CREATE TABLE t1 ( a char(20) NOT NULL, b char(7) DEFAULT NULL, c char(5) ); INSERT INTO t1(a,b,c) VALUES (9.999999e+0, 9.999999e+0, 9.999e+0); INSERT INTO t1(a,b,c) VALUES (1.225e-05, 1.225e-05, 1.225e-05); INSERT INTO t1(a,b) VALUES (1.225e-04, 1.225e-04); INSERT INTO t1(a,b) VALUES (1.225e-01, 1.225e-01); INSERT INTO t1(a,b) VALUES (1.225877e-01, 1.225877e-01); INSERT INTO t1(a,b) VALUES (1.225e+01, 1.225e+01); INSERT INTO t1(a,b,c) VALUES (1.225e+01, 1.225e+01, 1.225e+01); INSERT INTO t1(a,b) VALUES (1.225e+05, 1.225e+05); INSERT INTO t1(a,b) VALUES (1.225e+10, 1.225e+10); INSERT INTO t1(a,b) VALUES (1.225e+15, 1.225e+15); INSERT INTO t1(a,b) VALUES (5000000e+0, 5000000e+0); INSERT INTO t1(a,b) VALUES (1.25e+78, 1.25e+78); INSERT INTO t1(a,b) VALUES (1.25e-94, 1.25e-94); INSERT INTO t1(a,b) VALUES (1.25e+203, 1.25e+203); INSERT INTO t1(a,b) VALUES (1.25e-175, 1.25e-175); INSERT INTO t1(a,c) VALUES (1.225e+0, 1.225e+0); INSERT INTO t1(a,c) VALUES (1.37e+0, 1.37e+0); INSERT INTO t1(a,c) VALUES (-1.37e+0, -1.37e+0); INSERT INTO t1(a,c) VALUES (1.87e-3, 1.87e-3); INSERT INTO t1(a,c) VALUES (-1.87e-2, -1.87e-2); INSERT INTO t1(a,c) VALUES (5000e+0, 5000e+0); INSERT INTO t1(a,c) VALUES (-5000e+0, -5000e+0); SELECT * FROM t1; a b c 9.999999 10 9.999 0.00001225 1.22e-5 1e-5 0.0001225 1.22e-4 NULL 0.1225 0.1225 NULL 0.1225877 0.12259 NULL 12.25 12.25 NULL 12.25 12.25 12.25 122500 122500 NULL 12250000000 1.22e10 NULL 1.225e15 1.22e15 NULL 5000000 5000000 NULL 1.25e78 1.25e78 NULL 1.25e-94 1.2e-94 NULL 1.25e203 1.2e203 NULL 1.25e-175 1e-175 NULL 1.225 NULL 1.225 1.37 NULL 1.37 -1.37 NULL -1.37 0.00187 NULL 0.002 -0.0187 NULL -0.02 5000 NULL 5000 -5000 NULL -5000 DROP TABLE t1; CREATE TABLE t (a CHAR(10),b INT); INSERT INTO t VALUES (),(),(); INSERT INTO t(a) SELECT rand() FROM t; DROP TABLE t; CREATE TABLE t1 (c1 INT NOT NULL); INSERT INTO t1 VALUES(4188.32999999999992724042385816574096679687500), ('4188.32999999999992724042385816574096679687500'), (4188); SELECT * FROM t1; c1 4188 4188 4188 CREATE TABLE t2 (c1 BIGINT); INSERT INTO t2 VALUES('15449237462.0000000000'); SELECT * FROM t2; c1 15449237462 DROP TABLE t1, t2; CREATE TABLE t1(f1 FLOAT); INSERT INTO t1 VALUES (1.23); CREATE TABLE t2(f1 CHAR(1)); INSERT INTO t2 SELECT f1 FROM t1; DROP TABLE t1, t2; End of 5.0 tests. # # Bug#34898 "mysql_info() reports 0 warnings while # mysql_warning_count() reports 1" # Check that the number of warnings reported by # mysql_info() is correct. # drop table if exists t1; create table t1 (data varchar(4) not null); set sql_mode='error_for_division_by_zero'; # # Demonstrate that the number of warnings matches # the information in mysql_info(). # insert t1 (data) values ('letter'), (1/0); affected rows: 2 info: Records: 2 Duplicates: 0 Warnings: 3 Warnings: Warning 1265 Data truncated for column 'data' at row 1 Warning 1365 Division by 0 Warning 1048 Column 'data' cannot be null update t1 set data='envelope' where 1/0 or 1; affected rows: 2 info: Rows matched: 2 Changed: 2 Warnings: 3 Warnings: Warning 1365 Division by 0 Warning 1265 Data truncated for column 'data' at row 1 Warning 1265 Data truncated for column 'data' at row 2 insert t1 (data) values (default), (1/0), ('dead beef'); affected rows: 3 info: Records: 3 Duplicates: 0 Warnings: 4 Warnings: Warning 1364 Field 'data' doesn't have a default value Warning 1365 Division by 0 Warning 1048 Column 'data' cannot be null Warning 1265 Data truncated for column 'data' at row 3 set sql_mode=default; drop table t1; # # End of 5.4 tests # # # Bug#54106 assert in Protocol::end_statement, # INSERT IGNORE ... SELECT ... UNION SELECT ... # DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a INT); INSERT INTO t1 (a, a) VALUES (1, 1); ERROR 42000: Column 'a' specified twice INSERT IGNORE t1 (a, a) VALUES (1, 1); ERROR 42000: Column 'a' specified twice INSERT IGNORE t1 (a, a) SELECT 1,1; ERROR 42000: Column 'a' specified twice INSERT IGNORE t1 (a, a) SELECT 1,1 UNION SELECT 2,2; ERROR 42000: Column 'a' specified twice DROP TABLE t1; # # BUG#22037930: INSERT IGNORE FAILS TO IGNORE # FOREIGN KEY CONSTRAINT # Setup. CREATE TABLE t1 (fld1 INT PRIMARY KEY) ENGINE=INNODB; CREATE TABLE t2 (fld2 INT, FOREIGN KEY (fld2) REFERENCES t1 (fld1)) ENGINE=INNODB; INSERT INTO t1 VALUES(0); INSERT INTO t2 VALUES(0); # Without fix, an error is reported. INSERT IGNORE INTO t2 VALUES(1); Warnings: Warning 1452 `test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`) UPDATE IGNORE t2 SET fld2=20 WHERE fld2=0; Warnings: Warning 1452 `test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`) UPDATE IGNORE t1 SET fld1=20 WHERE fld1=0; Warnings: Warning 1451 `test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`) # Test for multi update. UPDATE IGNORE t1, t2 SET t2.fld2= t2.fld2 + 3; Warnings: Warning 1452 `test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`) UPDATE IGNORE t1, t2 SET t1.fld1= t1.fld1 + 3; Warnings: Warning 1451 `test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`) # Reports an error since IGNORE is not used. INSERT INTO t2 VALUES(1); ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`)) UPDATE t2 SET fld2=20 WHERE fld2=0; ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`)) UPDATE t1 SET fld1=20 WHERE fld1=0; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`)) UPDATE t1, t2 SET t2.fld2= t2.fld2 + 3; ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`)) UPDATE t1, t2 SET t1.fld1= t1.fld1 + 3; ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`)) DROP TABLE t2, t1; # # BUG#22037930: INSERT IGNORE FAILS TO IGNORE FOREIGN # KEY CONSTRAINT CREATE TABLE t1 (fld1 INT PRIMARY KEY) ENGINE= INNODB; CREATE TABLE t2 (fld1 VARCHAR(10), fld2 INT NOT NULL, CONSTRAINT fk FOREIGN KEY (fld2) REFERENCES t1(fld1)) ENGINE= INNODB; # Without patch, reports incorrect error. INSERT INTO t2 VALUES('abc', 2) ON DUPLICATE KEY UPDATE fld1= 'def'; ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`)) REPLACE INTO t2 VALUES('abc', 2); ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`)) INSERT IGNORE INTO t2 VALUES('abc', 2) ON DUPLICATE KEY UPDATE fld1= 'def'; Warnings: Warning 1452 `test`.`t2`, CONSTRAINT `fk` FOREIGN KEY (`fld2`) REFERENCES `t1` (`fld1`) DROP TABLE t2, t1;
Back to File Manager