summaryrefslogtreecommitdiff
path: root/mysql-test/r/trigger_null-8605.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r/trigger_null-8605.result')
-rw-r--r--mysql-test/r/trigger_null-8605.result366
1 files changed, 0 insertions, 366 deletions
diff --git a/mysql-test/r/trigger_null-8605.result b/mysql-test/r/trigger_null-8605.result
deleted file mode 100644
index 10315988708..00000000000
--- a/mysql-test/r/trigger_null-8605.result
+++ /dev/null
@@ -1,366 +0,0 @@
-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;
View Lorry Controller Status