diff options
Diffstat (limited to 'mysql-test/main/trigger_null-8605.result')
-rw-r--r-- | mysql-test/main/trigger_null-8605.result | 366 |
1 files changed, 366 insertions, 0 deletions
diff --git a/mysql-test/main/trigger_null-8605.result b/mysql-test/main/trigger_null-8605.result new file mode 100644 index 00000000..10315988 --- /dev/null +++ b/mysql-test/main/trigger_null-8605.result @@ -0,0 +1,366 @@ +set sql_mode=strict_all_tables; +set time_zone="+02:00"; +create table t1 (a int not null, b int, c int); +create trigger trgi before insert on t1 for each row set new.a=if(new.a is null,new.b,new.c); +insert t1 values (10, NULL, 1); +insert t1 values (NULL, 2, NULL); +insert t1 values (NULL, NULL, 20); +ERROR 23000: Column 'a' cannot be null +insert t1 values (1, 2, NULL); +ERROR 23000: Column 'a' cannot be null +select * from t1; +a b c +1 NULL 1 +2 2 NULL +insert ignore t1 values (NULL, NULL, 30); +Warnings: +Warning 1048 Column 'a' cannot be null +insert ignore t1 values (1, 3, NULL); +Warnings: +Warning 1048 Column 'a' cannot be null +select * from t1; +a b c +1 NULL 1 +2 2 NULL +0 NULL 30 +0 3 NULL +insert t1 set a=NULL, b=4, c=a; +select * from t1; +a b c +1 NULL 1 +2 2 NULL +0 NULL 30 +0 3 NULL +4 4 NULL +delete from t1; +insert t1 (a,c) values (10, 1); +insert t1 (a,b) values (NULL, 2); +insert t1 (a,c) values (NULL, 20); +ERROR 23000: Column 'a' cannot be null +insert t1 (a,b) values (1, 2); +ERROR 23000: Column 'a' cannot be null +select * from t1; +a b c +1 NULL 1 +2 2 NULL +delete from t1; +insert t1 select 10, NULL, 1; +insert t1 select NULL, 2, NULL; +insert t1 select NULL, NULL, 20; +ERROR 23000: Column 'a' cannot be null +insert t1 select 1, 2, NULL; +ERROR 23000: Column 'a' cannot be null +insert ignore t1 select NULL, NULL, 30; +Warnings: +Warning 1048 Column 'a' cannot be null +insert ignore t1 select 1, 3, NULL; +Warnings: +Warning 1048 Column 'a' cannot be null +select * from t1; +a b c +1 NULL 1 +2 2 NULL +0 NULL 30 +0 3 NULL +delete from t1; +insert delayed t1 values (10, NULL, 1); +insert delayed t1 values (NULL, 2, NULL); +insert delayed t1 values (NULL, NULL, 20); +ERROR 23000: Column 'a' cannot be null +insert delayed t1 values (1, 2, NULL); +ERROR 23000: Column 'a' cannot be null +select * from t1; +a b c +1 NULL 1 +2 2 NULL +insert delayed ignore t1 values (NULL, NULL, 30); +Warnings: +Warning 1048 Column 'a' cannot be null +insert delayed ignore t1 values (1, 3, NULL); +Warnings: +Warning 1048 Column 'a' cannot be null +flush table t1; +select * from t1; +a b c +1 NULL 1 +2 2 NULL +0 NULL 30 +0 3 NULL +delete from t1; +alter table t1 add primary key (a); +create trigger trgu before update on t1 for each row set new.a=if(new.a is null,new.b,new.c); +insert t1 values (100,100,100), (200,200,200), (300,300,300); +insert t1 values (100,100,100) on duplicate key update a=10, b=NULL, c=1; +insert t1 values (200,200,200) on duplicate key update a=NULL, b=2, c=NULL; +insert t1 values (300,300,300) on duplicate key update a=NULL, b=NULL, c=20; +ERROR 23000: Column 'a' cannot be null +insert t1 values (300,300,300) on duplicate key update a=1, b=2, c=NULL; +ERROR 23000: Column 'a' cannot be null +select * from t1; +a b c +1 NULL 1 +2 2 NULL +300 300 300 +delete from t1; +insert t1 values (1,100,1), (2,200,2); +replace t1 values (10, NULL, 1); +replace t1 values (NULL, 2, NULL); +replace t1 values (NULL, NULL, 30); +ERROR 23000: Column 'a' cannot be null +replace t1 values (1, 3, NULL); +ERROR 23000: Column 'a' cannot be null +select * from t1; +a b c +1 NULL 1 +2 2 NULL +delete from t1; +insert t1 values (100,100,100), (200,200,200), (300,300,300); +update t1 set a=10, b=NULL, c=1 where a=100; +update t1 set a=NULL, b=2, c=NULL where a=200; +update t1 set a=NULL, b=NULL, c=20 where a=300; +ERROR 23000: Column 'a' cannot be null +update t1 set a=1, b=2, c=NULL where a=300; +ERROR 23000: Column 'a' cannot be null +select * from t1; +a b c +1 NULL 1 +2 2 NULL +300 300 300 +set statement sql_mode='' for update t1 set a=1, b=2, c=NULL where a > 1; +ERROR 23000: Duplicate entry '0' for key 'PRIMARY' +select * from t1; +a b c +1 NULL 1 +0 2 NULL +300 300 300 +update t1 set a=NULL, b=4, c=a where a=300; +select * from t1; +a b c +1 NULL 1 +0 2 NULL +4 4 NULL +delete from t1; +create table t2 (d int, e int); +insert t1 values (100,100,100), (200,200,200), (300,300,300); +insert t2 select a,b from t1; +update t1,t2 set a=10, b=NULL, c=1 where b=d and e=100; +update t1,t2 set a=NULL, b=2, c=NULL where b=d and e=200; +update t1,t2 set a=NULL, b=NULL, c=20 where b=d and e=300; +ERROR 23000: Column 'a' cannot be null +update t1,t2 set a=1, b=2, c=NULL where b=d and e=300; +ERROR 23000: Column 'a' cannot be null +select * from t1; +a b c +1 NULL 1 +2 2 NULL +300 300 300 +update t1,t2 set a=NULL, b=4, c=a where b=d and e=300; +select * from t1; +a b c +1 NULL 1 +2 2 NULL +4 4 300 +delete from t1; +insert t2 values (2,2); +create view v1 as select * from t1, t2 where d=2; +insert v1 (a,c) values (10, 1); +insert v1 (a,b) values (NULL, 2); +insert v1 (a,c) values (NULL, 20); +ERROR 23000: Column 'a' cannot be null +insert v1 (a,b) values (1, 2); +ERROR 23000: Column 'a' cannot be null +select * from v1; +a b c d e +1 NULL 1 2 2 +2 2 NULL 2 2 +delete from t1; +drop view v1; +drop table t2; +load data infile 'mdev8605.txt' into table t1 fields terminated by ','; +ERROR 23000: Column 'a' cannot be null +select * from t1; +a b c +1 NULL 1 +2 2 NULL +drop table t1; +create table t1 (a timestamp, b int auto_increment primary key); +create trigger trgi before insert on t1 for each row set new.a=if(new.a is null, '2000-10-20 10:20:30', NULL); +set statement timestamp=777777777 for insert t1 (a) values (NULL); +set statement timestamp=888888888 for insert t1 (a) values ('1999-12-11 10:9:8'); +select b, a, unix_timestamp(a) from t1; +b a unix_timestamp(a) +1 2000-10-20 10:20:30 972030030 +2 1998-03-03 03:34:48 888888888 +set statement timestamp=999999999 for update t1 set b=3 where b=2; +select b, a, unix_timestamp(a) from t1; +b a unix_timestamp(a) +1 2000-10-20 10:20:30 972030030 +3 2001-09-09 03:46:39 999999999 +create trigger trgu before update on t1 for each row set new.a='2011-11-11 11:11:11'; +update t1 set b=4 where b=3; +select b, a, unix_timestamp(a) from t1; +b a unix_timestamp(a) +1 2000-10-20 10:20:30 972030030 +4 2011-11-11 11:11:11 1321002671 +drop table t1; +create table t1 (a int auto_increment primary key); +create trigger trgi before insert on t1 for each row set new.a=if(new.a is null, 5, NULL); +insert t1 values (NULL); +insert t1 values (10); +select a from t1; +a +5 +6 +drop table t1; +create table t1 (a int, b int, c int); +create trigger trgi before insert on t1 for each row set new.a=if(new.a is null,new.b,new.c); +insert t1 values (10, NULL, 1); +insert t1 values (NULL, 2, NULL); +insert t1 values (NULL, NULL, 20); +insert t1 values (1, 2, NULL); +select * from t1; +a b c +1 NULL 1 +2 2 NULL +NULL NULL 20 +NULL 2 NULL +drop table t1; +create table t1 (a1 tinyint not null, a2 timestamp not null, +a3 tinyint not null auto_increment primary key, +b tinyint, c int not null); +create trigger trgi before insert on t1 for each row +begin +if new.b=1 then set new.a1=if(new.c,new.c,null); end if; +if new.b=2 then set new.a2=if(new.c,new.c,null); end if; +if new.b=3 then set new.a3=if(new.c,new.c,null); end if; +end| +set statement timestamp=777777777 for +load data infile 'sep8605.txt' into table t1 fields terminated by ','; +ERROR 23000: Column 'a1' cannot be null +select * from t1; +a1 a2 a3 b c +1 2010-11-12 01:02:03 10 0 0 +2 2010-11-12 01:02:03 11 1 2 +3 1994-08-25 03:22:57 12 0 0 +4 2000-09-08 07:06:05 13 2 908070605 +5 1994-08-25 03:22:57 14 2 0 +6 2010-11-12 01:02:03 15 0 0 +7 2010-11-12 01:02:03 20 3 20 +8 2010-11-12 01:02:03 21 3 0 +delete from t1; +set statement timestamp=777777777 for +load data infile 'sep8605.txt' into table t1 fields terminated by ',' + (@a,a2,a3,b,c) set a1=100-@a; +ERROR 23000: Column 'a1' cannot be null +select 100-a1,a2,a3,b,c from t1; +100-a1 a2 a3 b c +1 2010-11-12 01:02:03 10 0 0 +98 2010-11-12 01:02:03 11 1 2 +3 1994-08-25 03:22:57 12 0 0 +4 2000-09-08 07:06:05 13 2 908070605 +5 1994-08-25 03:22:57 14 2 0 +6 2010-11-12 01:02:03 22 0 0 +7 2010-11-12 01:02:03 20 3 20 +8 2010-11-12 01:02:03 23 3 0 +delete from t1; +set statement timestamp=777777777 for +load data infile 'fix8605.txt' into table t1 fields terminated by ''; +ERROR 23000: Column 'a1' cannot be null +select * from t1; +a1 a2 a3 b c +1 2010-11-12 01:02:03 10 0 0 +5 1994-08-25 03:22:57 14 2 0 +8 2010-11-12 01:02:03 24 3 0 +delete from t1; +set statement timestamp=777777777 for +load xml infile 'xml8605.txt' into table t1 rows identified by '<row>'; +ERROR 23000: Column 'a1' cannot be null +select * from t1; +a1 a2 a3 b c +1 2010-11-12 01:02:03 10 0 0 +2 2010-11-12 01:02:03 11 1 2 +3 1994-08-25 03:22:57 12 0 0 +4 2000-09-08 07:06:05 13 2 908070605 +5 1994-08-25 03:22:57 14 2 0 +6 2010-11-12 01:02:03 25 0 0 +7 2010-11-12 01:02:03 20 3 20 +8 2010-11-12 01:02:03 26 3 0 +drop table t1; +create table t1 (a int not null default 5, b int, c int); +create trigger trgi before insert on t1 for each row set new.b=new.c; +insert t1 values (DEFAULT,2,1); +select * from t1; +a b c +5 1 1 +drop table t1; +create table t1 (a int not null, b int not null default 5, c int); +create trigger trgi before insert on t1 for each row +begin +if new.c=1 then set new.a=1, new.b=1; end if; +if new.c=2 then set new.a=NULL, new.b=NULL; end if; +if new.c=3 then set new.a=2; end if; +end| +insert t1 values (9, 9, 1); +insert t1 values (9, 9, 2); +ERROR 23000: Column 'a' cannot be null +insert t1 (a,c) values (9, 3); +select * from t1; +a b c +1 1 1 +2 5 3 +drop table t1; +set session sql_mode ='no_auto_value_on_zero'; +create table t1 (id int unsigned auto_increment primary key); +insert t1 values (0); +select * from t1; +id +0 +delete from t1; +create trigger t1_bi before insert on t1 for each row begin end; +insert t1 values (0); +insert t1 (id) values (0); +ERROR 23000: Duplicate entry '0' for key 'PRIMARY' +drop table t1; +create table t1 (a int not null, b int); +create trigger trgi before update on t1 for each row do 1; +insert t1 values (1,1),(2,2),(3,3),(1,4); +create table t2 select a as c, b as d from t1; +update t1 set a=(select count(c) from t2 where c+1=a+1 group by a); +select * from t1; +a b +2 1 +1 2 +1 3 +2 4 +drop table t1, t2; +create table t1 (a int not null); +create table t2 (f1 int unsigned not null, f2 int); +insert into t2 values (1, null); +create trigger tr1 before update on t1 for each row do 1; +create trigger tr2 after update on t2 for each row update t1 set a=new.f2; +update t2 set f2=1 where f1=1; +drop table t1, t2; +create table t1 (a int not null, primary key (a)); +insert into t1 (a) values (1); +show columns from t1; +Field Type Null Key Default Extra +a int(11) NO PRI NULL +create trigger t1bu before update on t1 for each row begin end; +show columns from t1; +Field Type Null Key Default Extra +a int(11) NO PRI NULL +insert into t1 (a) values (3); +show columns from t1; +Field Type Null Key Default Extra +a int(11) NO PRI NULL +drop table t1; +create table t1 ( +pk int primary key, +i int, +v1 int as (i) virtual, +v2 int as (i) virtual +); +create trigger tr before update on t1 for each row set @a = 1; +insert into t1 (pk, i) values (null, null); +ERROR 23000: Column 'pk' cannot be null +drop table t1; |