Create 'main' test directory and move 't' and 'r' there

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 00000000000..10315988708
--- /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;