summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/trigger.result4
-rw-r--r--mysql-test/r/trigger_null-8605.result311
-rw-r--r--mysql-test/suite/funcs_1/r/innodb_trig_09.result4
-rw-r--r--mysql-test/suite/funcs_1/r/memory_trig_09.result4
-rw-r--r--mysql-test/suite/funcs_1/r/myisam_trig_09.result4
-rw-r--r--mysql-test/suite/funcs_1/triggers/triggers_09.inc3
-rw-r--r--mysql-test/suite/rpl/r/rpl_stm_maria.result2
-rw-r--r--mysql-test/suite/rpl/r/rpl_trigger.result4
-rw-r--r--mysql-test/t/trigger.test1
-rw-r--r--mysql-test/t/trigger_null-8605.test327
-rw-r--r--sql/field.h20
-rw-r--r--sql/field_conv.cc51
-rw-r--r--sql/item.cc24
-rw-r--r--sql/item.h3
-rw-r--r--sql/sql_base.cc75
-rw-r--r--sql/sql_base.h1
-rw-r--r--sql/sql_insert.cc13
-rw-r--r--sql/sql_load.cc3
-rw-r--r--sql/sql_trigger.cc88
-rw-r--r--sql/sql_trigger.h27
-rw-r--r--sql/sql_update.cc11
-rw-r--r--sql/table.h1
22 files changed, 897 insertions, 84 deletions
diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result
index c1780819c68..86219875bed 100644
--- a/mysql-test/r/trigger.result
+++ b/mysql-test/r/trigger.result
@@ -214,12 +214,14 @@ end if;
end|
insert into t3 values (1);
insert into t1 values (4, "four", 1), (5, "five", 2);
-ERROR 23000: Column 'id' cannot be null
+Warnings:
+Warning 1048 Column 'id' cannot be null
select * from t1;
id data fk
1 one NULL
2 two NULL
4 four 1
+0 five 2
select * from t2;
event
INSERT INTO t1 id=1 data='one'
diff --git a/mysql-test/r/trigger_null-8605.result b/mysql-test/r/trigger_null-8605.result
new file mode 100644
index 00000000000..79627eac455
--- /dev/null
+++ b/mysql-test/r/trigger_null-8605.result
@@ -0,0 +1,311 @@
+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;
diff --git a/mysql-test/suite/funcs_1/r/innodb_trig_09.result b/mysql-test/suite/funcs_1/r/innodb_trig_09.result
index 986506b4e71..e6a10592424 100644
--- a/mysql-test/suite/funcs_1/r/innodb_trig_09.result
+++ b/mysql-test/suite/funcs_1/r/innodb_trig_09.result
@@ -189,8 +189,6 @@ Update tb3 Set f122='Test 3.5.9.4-trig', f136=NULL, f151=DEFAULT, f163=NULL
where f122='Test 3.5.9.4';
Warnings:
Warning 1048 Column 'f136' cannot be null
-Update tb3 Set f122='Test 3.5.9.4-trig', f136=0, f151=DEFAULT, f163=NULL
-where f122='Test 3.5.9.4';
select f118, f121, f122, f136, f151, f163 from tb3
where f122 like 'Test 3.5.9.4-trig' order by f163;
f118 f121 f122 f136 f151 f163
@@ -198,7 +196,7 @@ a NULL Test 3.5.9.4-trig 00000 999 NULL
select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122,
@tr_var_b4_136, @tr_var_b4_151, @tr_var_b4_163;
@tr_var_b4_118 @tr_var_b4_121 @tr_var_b4_122 @tr_var_b4_136 @tr_var_b4_151 @tr_var_b4_163
-a NULL Test 3.5.9.4-trig 0 999 NULL
+a NULL Test 3.5.9.4-trig NULL 999 NULL
select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122,
@tr_var_af_136, @tr_var_af_151, @tr_var_af_163;
@tr_var_af_118 @tr_var_af_121 @tr_var_af_122 @tr_var_af_136 @tr_var_af_151 @tr_var_af_163
diff --git a/mysql-test/suite/funcs_1/r/memory_trig_09.result b/mysql-test/suite/funcs_1/r/memory_trig_09.result
index 0795c3be36f..5394aa01bf6 100644
--- a/mysql-test/suite/funcs_1/r/memory_trig_09.result
+++ b/mysql-test/suite/funcs_1/r/memory_trig_09.result
@@ -190,8 +190,6 @@ Update tb3 Set f122='Test 3.5.9.4-trig', f136=NULL, f151=DEFAULT, f163=NULL
where f122='Test 3.5.9.4';
Warnings:
Warning 1048 Column 'f136' cannot be null
-Update tb3 Set f122='Test 3.5.9.4-trig', f136=0, f151=DEFAULT, f163=NULL
-where f122='Test 3.5.9.4';
select f118, f121, f122, f136, f151, f163 from tb3
where f122 like 'Test 3.5.9.4-trig' order by f163;
f118 f121 f122 f136 f151 f163
@@ -199,7 +197,7 @@ a NULL Test 3.5.9.4-trig 00000 999 NULL
select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122,
@tr_var_b4_136, @tr_var_b4_151, @tr_var_b4_163;
@tr_var_b4_118 @tr_var_b4_121 @tr_var_b4_122 @tr_var_b4_136 @tr_var_b4_151 @tr_var_b4_163
-a NULL Test 3.5.9.4-trig 0 999 NULL
+a NULL Test 3.5.9.4-trig NULL 999 NULL
select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122,
@tr_var_af_136, @tr_var_af_151, @tr_var_af_163;
@tr_var_af_118 @tr_var_af_121 @tr_var_af_122 @tr_var_af_136 @tr_var_af_151 @tr_var_af_163
diff --git a/mysql-test/suite/funcs_1/r/myisam_trig_09.result b/mysql-test/suite/funcs_1/r/myisam_trig_09.result
index 0795c3be36f..5394aa01bf6 100644
--- a/mysql-test/suite/funcs_1/r/myisam_trig_09.result
+++ b/mysql-test/suite/funcs_1/r/myisam_trig_09.result
@@ -190,8 +190,6 @@ Update tb3 Set f122='Test 3.5.9.4-trig', f136=NULL, f151=DEFAULT, f163=NULL
where f122='Test 3.5.9.4';
Warnings:
Warning 1048 Column 'f136' cannot be null
-Update tb3 Set f122='Test 3.5.9.4-trig', f136=0, f151=DEFAULT, f163=NULL
-where f122='Test 3.5.9.4';
select f118, f121, f122, f136, f151, f163 from tb3
where f122 like 'Test 3.5.9.4-trig' order by f163;
f118 f121 f122 f136 f151 f163
@@ -199,7 +197,7 @@ a NULL Test 3.5.9.4-trig 00000 999 NULL
select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122,
@tr_var_b4_136, @tr_var_b4_151, @tr_var_b4_163;
@tr_var_b4_118 @tr_var_b4_121 @tr_var_b4_122 @tr_var_b4_136 @tr_var_b4_151 @tr_var_b4_163
-a NULL Test 3.5.9.4-trig 0 999 NULL
+a NULL Test 3.5.9.4-trig NULL 999 NULL
select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122,
@tr_var_af_136, @tr_var_af_151, @tr_var_af_163;
@tr_var_af_118 @tr_var_af_121 @tr_var_af_122 @tr_var_af_136 @tr_var_af_151 @tr_var_af_163
diff --git a/mysql-test/suite/funcs_1/triggers/triggers_09.inc b/mysql-test/suite/funcs_1/triggers/triggers_09.inc
index 47277afb63c..e9e6af92994 100644
--- a/mysql-test/suite/funcs_1/triggers/triggers_09.inc
+++ b/mysql-test/suite/funcs_1/triggers/triggers_09.inc
@@ -186,9 +186,6 @@ let $message= Testcase 3.5.9.4:;
Update tb3 Set f122='Test 3.5.9.4-trig', f136=NULL, f151=DEFAULT, f163=NULL
where f122='Test 3.5.9.4';
- Update tb3 Set f122='Test 3.5.9.4-trig', f136=0, f151=DEFAULT, f163=NULL
- where f122='Test 3.5.9.4';
-
select f118, f121, f122, f136, f151, f163 from tb3
where f122 like 'Test 3.5.9.4-trig' order by f163;
select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122,
diff --git a/mysql-test/suite/rpl/r/rpl_stm_maria.result b/mysql-test/suite/rpl/r/rpl_stm_maria.result
index 08d202b61c0..7a9dfcd6061 100644
--- a/mysql-test/suite/rpl/r/rpl_stm_maria.result
+++ b/mysql-test/suite/rpl/r/rpl_stm_maria.result
@@ -39,7 +39,7 @@ select a,name, old_a, old_b, truncate(rand_value,4) from t3;
a name old_a old_b truncate(rand_value,4)
100 log 0 0 0.0000
101 t1 1 1 0.3203
-102 t1 0 2 0.5666
+102 t1 NULL 2 0.5666
103 t2 1 2 0.9164
104 t2 3 0 0.8826
105 t2 4 0 0.6635
diff --git a/mysql-test/suite/rpl/r/rpl_trigger.result b/mysql-test/suite/rpl/r/rpl_trigger.result
index c0e35274594..6b63d8cda31 100644
--- a/mysql-test/suite/rpl/r/rpl_trigger.result
+++ b/mysql-test/suite/rpl/r/rpl_trigger.result
@@ -35,7 +35,7 @@ select a,name, old_a, old_b, truncate(rand_value,4) from t3;
a name old_a old_b truncate(rand_value,4)
100 log 0 0 0.0000
101 t1 1 1 0.3203
-102 t1 0 2 0.5666
+102 t1 NULL 2 0.5666
103 t2 1 2 0.9164
104 t2 3 0 0.8826
105 t2 4 0 0.6635
@@ -58,7 +58,7 @@ select a,name, old_a, old_b, truncate(rand_value,4) from t3;
a name old_a old_b truncate(rand_value,4)
100 log 0 0 0.0000
101 t1 1 1 0.3203
-102 t1 0 2 0.5666
+102 t1 NULL 2 0.5666
103 t2 1 2 0.9164
104 t2 3 0 0.8826
105 t2 4 0 0.6635
diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test
index 033a197fe10..a02dce34837 100644
--- a/mysql-test/t/trigger.test
+++ b/mysql-test/t/trigger.test
@@ -241,7 +241,6 @@ begin
end|
delimiter ;|
insert into t3 values (1);
---error ER_BAD_NULL_ERROR
insert into t1 values (4, "four", 1), (5, "five", 2);
select * from t1;
select * from t2;
diff --git a/mysql-test/t/trigger_null-8605.test b/mysql-test/t/trigger_null-8605.test
new file mode 100644
index 00000000000..6861676145f
--- /dev/null
+++ b/mysql-test/t/trigger_null-8605.test
@@ -0,0 +1,327 @@
+#
+# MDEV-8605 MariaDB not use DEFAULT value even when inserted NULL for NOT NULLABLE column.
+#
+
+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
+insert t1 values (10, NULL, 1);
+insert t1 values (NULL, 2, NULL);
+--error ER_BAD_NULL_ERROR
+insert t1 values (NULL, NULL, 20);
+--error ER_BAD_NULL_ERROR
+insert t1 values (1, 2, NULL);
+select * from t1;
+
+# INSERT IGNORE
+insert ignore t1 values (NULL, NULL, 30);
+insert ignore t1 values (1, 3, NULL);
+select * from t1;
+
+# fields in the value list
+insert t1 set a=NULL, b=4, c=a;
+select * from t1;
+delete from t1;
+
+#insert (column list)
+insert t1 (a,c) values (10, 1);
+insert t1 (a,b) values (NULL, 2);
+--error ER_BAD_NULL_ERROR
+insert t1 (a,c) values (NULL, 20);
+--error ER_BAD_NULL_ERROR
+insert t1 (a,b) values (1, 2);
+select * from t1;
+delete from t1;
+
+# insert select
+
+insert t1 select 10, NULL, 1;
+insert t1 select NULL, 2, NULL;
+--error ER_BAD_NULL_ERROR
+insert t1 select NULL, NULL, 20;
+--error ER_BAD_NULL_ERROR
+insert t1 select 1, 2, NULL;
+insert ignore t1 select NULL, NULL, 30;
+insert ignore t1 select 1, 3, NULL;
+select * from t1;
+delete from t1;
+
+# insert delayed
+insert delayed t1 values (10, NULL, 1);
+insert delayed t1 values (NULL, 2, NULL);
+--error ER_BAD_NULL_ERROR
+insert delayed t1 values (NULL, NULL, 20);
+--error ER_BAD_NULL_ERROR
+insert delayed t1 values (1, 2, NULL);
+select * from t1;
+insert delayed ignore t1 values (NULL, NULL, 30);
+insert delayed ignore t1 values (1, 3, NULL);
+flush table t1; # wait for inserts to finish
+select * from t1;
+delete from t1;
+
+# insert on dup key update
+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;
+--error ER_BAD_NULL_ERROR
+insert t1 values (300,300,300) on duplicate key update a=NULL, b=NULL, c=20;
+--error ER_BAD_NULL_ERROR
+insert t1 values (300,300,300) on duplicate key update a=1, b=2, c=NULL;
+select * from t1;
+delete from t1;
+
+# replace
+insert t1 values (1,100,1), (2,200,2);
+replace t1 values (10, NULL, 1);
+replace t1 values (NULL, 2, NULL);
+--error ER_BAD_NULL_ERROR
+replace t1 values (NULL, NULL, 30);
+--error ER_BAD_NULL_ERROR
+replace t1 values (1, 3, NULL);
+select * from t1;
+delete from t1;
+
+# update
+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;
+--error ER_BAD_NULL_ERROR
+update t1 set a=NULL, b=NULL, c=20 where a=300;
+--error ER_BAD_NULL_ERROR
+update t1 set a=1, b=2, c=NULL where a=300;
+select * from t1;
+# Test UPDATE with NULL violation in many rows
+--error ER_DUP_ENTRY
+set statement sql_mode='' for update t1 set a=1, b=2, c=NULL where a > 1;
+select * from t1;
+# fields in the value list
+update t1 set a=NULL, b=4, c=a where a=300;
+select * from t1;
+delete from t1;
+
+# multi-update
+
+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;
+--error ER_BAD_NULL_ERROR
+update t1,t2 set a=NULL, b=NULL, c=20 where b=d and e=300;
+--error ER_BAD_NULL_ERROR
+update t1,t2 set a=1, b=2, c=NULL where b=d and e=300;
+select * from t1;
+# fields in the value list
+update t1,t2 set a=NULL, b=4, c=a where b=d and e=300;
+select * from t1;
+delete from t1;
+
+# view
+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);
+--error ER_BAD_NULL_ERROR
+insert v1 (a,c) values (NULL, 20);
+--error ER_BAD_NULL_ERROR
+insert v1 (a,b) values (1, 2);
+select * from v1;
+delete from t1;
+drop view v1;
+drop table t2;
+
+# load data
+let $datadir=`select @@datadir`;
+--write_file $datadir/test/mdev8605.txt
+10,\N,1
+\N,2,\N
+\N,\N,20
+EOF
+--error ER_BAD_NULL_ERROR
+load data infile 'mdev8605.txt' into table t1 fields terminated by ',';
+select * from t1;
+drop table t1;
+
+# timestamps (on NULL = NOW())
+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;
+set statement timestamp=999999999 for update t1 set b=3 where b=2;
+select b, a, unix_timestamp(a) from t1;
+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;
+drop table t1;
+
+# auto-increment (on NULL = int)
+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;
+drop table t1;
+
+# insert, all columns NULLable
+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;
+drop table t1;
+
+# more load data: autoinc and timestamp, different load formats
+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);
+delimiter |;
+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|
+delimiter ;|
+
+--write_file $datadir/test/sep8605.txt
+1,2010-11-12 1:2:3,10,0,0
+\N,2010-11-12 1:2:3,11,1,2
+3,\N,12,0,0
+4,\N,13,2,908070605
+5,2010-11-12 1:2:3,14,2,0
+6,2010-11-12 1:2:3,\N,0,0
+7,2010-11-12 1:2:3,\N,3,20
+8,2010-11-12 1:2:3,30,3,0
+99,2010-11-12 1:2:3,0,1,0
+EOF
+
+--error ER_BAD_NULL_ERROR
+set statement timestamp=777777777 for
+load data infile 'sep8605.txt' into table t1 fields terminated by ',';
+select * from t1;
+delete from t1;
+
+--error ER_BAD_NULL_ERROR
+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;
+select 100-a1,a2,a3,b,c from t1;
+delete from t1;
+
+--write_file $datadir/test/fix8605.txt
+00012010-11-12 01:02:030010000000000000000
+00052010-11-12 01:02:030014000200000000000
+00082010-11-12 01:02:030030000300000000000
+00992010-11-12 01:02:030000000100000000000
+EOF
+
+--error ER_BAD_NULL_ERROR
+set statement timestamp=777777777 for
+load data infile 'fix8605.txt' into table t1 fields terminated by '';
+select * from t1;
+delete from t1;
+
+--write_file $datadir/test/xml8605.txt
+<data>
+ <row>
+ <field name="a1">1</field>
+ <field name="a2">2010-11-12 1:2:3</field>
+ <field name="a3">10</field>
+ <field name="b">0</field>
+ <field name="c">0</field>
+ </row>
+ <row>
+ <field name="a2">2010-11-12 1:2:3</field>
+ <field name="a3">11</field>
+ <field name="b">1</field>
+ <field name="c">2</field>
+ </row>
+ <row>
+ <field name="a1">3</field>
+ <field name="a3">12</field>
+ <field name="b">0</field>
+ <field name="c">0</field>
+ </row>
+ <row>
+ <field name="a1">4</field>
+ <field name="a3">13</field>
+ <field name="b">2</field>
+ <field name="c">908070605</field>
+ </row>
+ <row>
+ <field name="a1">5</field>
+ <field name="a2">2010-11-12 1:2:3</field>
+ <field name="a3">14</field>
+ <field name="b">2</field>
+ <field name="c">0</field>
+ </row>
+ <row>
+ <field name="a1">6</field>
+ <field name="a2">2010-11-12 1:2:3</field>
+ <field name="b">0</field>
+ <field name="c">0</field>
+ </row>
+ <row>
+ <field name="a1">7</field>
+ <field name="a2">2010-11-12 1:2:3</field>
+ <field name="b">3</field>
+ <field name="c">20</field>
+ </row>
+ <row>
+ <field name="a1">8</field>
+ <field name="a2">2010-11-12 1:2:3</field>
+ <field name="a3">30</field>
+ <field name="b">3</field>
+ <field name="c">0</field>
+ </row>
+ <row>
+ <field name="a1">99</field>
+ <field name="a2">2010-11-12 1:2:3</field>
+ <field name="a3">0</field>
+ <field name="b">1</field>
+ <field name="c">0</field>
+ </row>
+</data>
+EOF
+
+--error ER_BAD_NULL_ERROR
+set statement timestamp=777777777 for
+load xml infile 'xml8605.txt' into table t1 rows identified by '<row>';
+select * from t1;
+drop table t1;
+
+# explicit DEFAULT
+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;
+drop table t1;
+
+# Two statements, first fails, second uses an implicit default
+create table t1 (a int not null, b int not null default 5, c int);
+delimiter |;
+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|
+delimiter ;|
+
+insert t1 values (9, 9, 1);
+--error ER_BAD_NULL_ERROR
+insert t1 values (9, 9, 2);
+insert t1 (a,c) values (9, 3);
+select * from t1;
+drop table t1;
diff --git a/sql/field.h b/sql/field.h
index cb7f94b6437..7199e40c173 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -844,7 +844,7 @@ public:
my_ptrdiff_t l_offset= (my_ptrdiff_t) (table->s->default_values -
table->record[0]);
memcpy(ptr, ptr + l_offset, pack_length());
- if (null_ptr)
+ if (maybe_null_in_table())
*null_ptr= ((*null_ptr & (uchar) ~null_bit) |
(null_ptr[l_offset] & null_bit));
}
@@ -1024,9 +1024,9 @@ public:
{ return null_ptr && (null_ptr[row_offset] & null_bit); }
inline bool is_null_in_record(const uchar *record) const
{
- if (!null_ptr)
- return 0;
- return record[(uint) (null_ptr - table->record[0])] & null_bit;
+ if (maybe_null_in_table())
+ return record[(uint) (null_ptr - table->record[0])] & null_bit;
+ return 0;
}
inline void set_null(my_ptrdiff_t row_offset= 0)
{ if (null_ptr) null_ptr[row_offset]|= null_bit; }
@@ -1035,10 +1035,19 @@ public:
inline bool maybe_null(void) const
{ return null_ptr != 0 || table->maybe_null; }
- /* @return true if this field is NULL-able, false otherwise. */
+ /* @return true if this field is NULL-able (even if temporarily) */
inline bool real_maybe_null(void) const { return null_ptr != 0; }
uint null_offset(const uchar *record) const
{ return (uint) (null_ptr - record); }
+ /*
+ For a NULL-able field (that can actually store a NULL value in a table)
+ null_ptr points to the "null bitmap" in the table->record[0] header. For
+ NOT NULL fields it is either 0 or points outside table->record[0] into the
+ table->triggers->extra_null_bitmap (so that the field can store a NULL
+ value temporarily, only in memory)
+ */
+ bool maybe_null_in_table() const
+ { return null_ptr >= table->record[0] && null_ptr <= ptr; }
uint null_offset() const
{ return null_offset(table->record[0]); }
@@ -3600,6 +3609,7 @@ enum_field_types get_blob_type_from_length(ulong length);
uint32 calc_pack_length(enum_field_types type,uint32 length);
int set_field_to_null(Field *field);
int set_field_to_null_with_conversions(Field *field, bool no_conversions);
+int convert_null_to_field_value_or_error(Field *field);
/*
The following are for the interface with the .frm file
diff --git a/sql/field_conv.cc b/sql/field_conv.cc
index df4730a50ce..0f6c85f50e8 100644
--- a/sql/field_conv.cc
+++ b/sql/field_conv.cc
@@ -153,6 +153,36 @@ int set_field_to_null(Field *field)
/**
+ Set TIMESTAMP to NOW(), AUTO_INCREMENT to the next number, or report an error
+
+ @param field Field to update
+
+ @retval
+ 0 Field could take 0 or an automatic conversion was used
+ @retval
+ -1 Field could not take NULL and no conversion was used.
+ If no_conversion was not set, an error message is printed
+*/
+
+int convert_null_to_field_value_or_error(Field *field)
+{
+ if (field->type() == MYSQL_TYPE_TIMESTAMP)
+ {
+ ((Field_timestamp*) field)->set_time();
+ return 0;
+ }
+
+ field->reset(); // Note: we ignore any potential failure of reset() here.
+
+ if (field == field->table->next_number_field)
+ {
+ field->table->auto_increment_field_not_null= FALSE;
+ return 0; // field is set in fill_record()
+ }
+ return set_bad_null_error(field, ER_BAD_NULL_ERROR);
+}
+
+/**
Set field to NULL or TIMESTAMP or to next auto_increment number.
@param field Field to update
@@ -186,26 +216,7 @@ set_field_to_null_with_conversions(Field *field, bool no_conversions)
if (no_conversions)
return -1;
- /*
- Check if this is a special type, which will get a special walue
- when set to NULL (TIMESTAMP fields which allow setting to NULL
- are handled by first check).
- */
- if (field->type() == MYSQL_TYPE_TIMESTAMP)
- {
- ((Field_timestamp*) field)->set_time();
- return 0; // Ok to set time to NULL
- }
-
- // Note: we ignore any potential failure of reset() here.
- field->reset();
-
- if (field == field->table->next_number_field)
- {
- field->table->auto_increment_field_not_null= FALSE;
- return 0; // field is set in fill_record()
- }
- return set_bad_null_error(field, ER_BAD_NULL_ERROR);
+ return convert_null_to_field_value_or_error(field);
}
diff --git a/sql/item.cc b/sql/item.cc
index e3c93a8da61..552069ebd9f 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -2379,6 +2379,24 @@ bool Item_field::update_table_bitmaps_processor(uchar *arg)
return FALSE;
}
+static inline void set_field_to_new_field(Field **field, Field **new_field)
+{
+ if (*field)
+ {
+ Field *newf= new_field[(*field)->field_index];
+ if ((*field)->ptr == newf->ptr)
+ *field= newf;
+ }
+}
+
+bool Item_field::switch_to_nullable_fields_processor(uchar *arg)
+{
+ Field **new_fields= (Field **)arg;
+ set_field_to_new_field(&field, new_fields);
+ set_field_to_new_field(&result_field, new_fields);
+ return 0;
+}
+
const char *Item_ident::full_name() const
{
char *tmp;
@@ -8191,9 +8209,8 @@ int Item_default_value::save_in_field(Field *field_arg, bool no_conversions)
}
field_arg->set_default();
return
- !field_arg->is_null_in_record(field_arg->table->s->default_values) &&
- field_arg->validate_value_in_record_with_warn(thd,
- field_arg->table->s->default_values) &&
+ !field_arg->is_null() &&
+ field_arg->validate_value_in_record_with_warn(thd, table->record[0]) &&
thd->is_error() ? -1 : 0;
}
return Item_field::save_in_field(field_arg, no_conversions);
@@ -8387,6 +8404,7 @@ bool Item_trigger_field::set_value(THD *thd, sp_rcontext * /*ctx*/, Item **it)
int err_code= item->save_in_field(field, 0);
field->table->copy_blobs= copy_blobs_saved;
+ field->set_explicit_default(item);
return err_code < 0;
}
diff --git a/sql/item.h b/sql/item.h
index e262ce9d12d..d5b6463d91a 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -1605,6 +1605,8 @@ public:
virtual bool check_inner_refs_processor(uchar *arg) { return FALSE; }
+ virtual bool switch_to_nullable_fields_processor(uchar *arg) { return FALSE; }
+
/*
For SP local variable returns pointer to Item representing its
current value and pointer to current Item otherwise.
@@ -2464,6 +2466,7 @@ public:
bool vcol_in_partition_func_processor(uchar *bool_arg);
bool enumerate_field_refs_processor(uchar *arg);
bool update_table_bitmaps_processor(uchar *arg);
+ bool switch_to_nullable_fields_processor(uchar *arg);
void cleanup();
Item_equal *get_item_equal() { return item_equal; }
void set_item_equal(Item_equal *item_eq) { item_equal= item_eq; }
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 1ab496325a8..b05bbfaead6 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -8818,7 +8818,61 @@ err:
}
-/*
+/**
+ Prepare Item_field's for fill_record_n_invoke_before_triggers()
+
+ This means redirecting from table->field to
+ table->field_to_fill(), if needed.
+*/
+void switch_to_nullable_trigger_fields(List<Item> &items, TABLE *table)
+{
+ Field** field= table->field_to_fill();
+
+ if (field != table->field)
+ {
+ List_iterator_fast<Item> it(items);
+ Item *item;
+
+ while ((item= it++))
+ item->walk(&Item::switch_to_nullable_fields_processor, 1, (uchar*)field);
+ table->triggers->reset_extra_null_bitmap();
+ }
+}
+
+
+/**
+ Test NOT NULL constraint after BEFORE triggers
+*/
+static bool not_null_fields_have_null_values(TABLE *table)
+{
+ Field **orig_field= table->field;
+ Field **filled_field= table->field_to_fill();
+
+ if (filled_field != orig_field)
+ {
+ THD *thd=table->in_use;
+ for (uint i=0; i < table->s->fields; i++)
+ {
+ Field *of= orig_field[i];
+ Field *ff= filled_field[i];
+ if (ff != of)
+ {
+ // copy after-update flags to of, copy before-update flags to ff
+ swap_variables(uint32, of->flags, ff->flags);
+ if (ff->is_real_null())
+ {
+ ff->set_notnull(); // for next row WHERE condition in UPDATE
+ if (convert_null_to_field_value_or_error(of) || thd->is_error())
+ return true;
+ }
+ }
+ }
+ }
+
+ return false;
+}
+
+/**
Fill fields in list with values from the list of items and invoke
before triggers.
@@ -8846,9 +8900,13 @@ fill_record_n_invoke_before_triggers(THD *thd, TABLE *table, List<Item> &fields,
{
bool result;
Table_triggers_list *triggers= table->triggers;
- result= (fill_record(thd, table, fields, values, ignore_errors) ||
- (triggers && triggers->process_triggers(thd, event,
- TRG_ACTION_BEFORE, TRUE)));
+
+ result= fill_record(thd, table, fields, values, ignore_errors);
+
+ if (!result && triggers)
+ result= triggers->process_triggers(thd, event, TRG_ACTION_BEFORE, TRUE) ||
+ not_null_fields_have_null_values(table);
+
/*
Re-calculate virtual fields to cater for cases when base columns are
updated by the triggers.
@@ -8994,9 +9052,12 @@ fill_record_n_invoke_before_triggers(THD *thd, TABLE *table, Field **ptr,
{
bool result;
Table_triggers_list *triggers= table->triggers;
- result= (fill_record(thd, table, ptr, values, ignore_errors, FALSE) ||
- (triggers && triggers->process_triggers(thd, event,
- TRG_ACTION_BEFORE, TRUE)));
+
+ result= fill_record(thd, table, ptr, values, ignore_errors, FALSE);
+
+ if (!result && triggers && *ptr)
+ result= triggers->process_triggers(thd, event, TRG_ACTION_BEFORE, TRUE) ||
+ not_null_fields_have_null_values(table);
/*
Re-calculate virtual fields to cater for cases when base columns are
updated by the triggers.
diff --git a/sql/sql_base.h b/sql/sql_base.h
index 3a23e2ea218..7407e230419 100644
--- a/sql/sql_base.h
+++ b/sql/sql_base.h
@@ -150,6 +150,7 @@ bool find_and_use_temporary_table(THD *thd, const TABLE_LIST *tl,
TABLE *find_temporary_table(THD *thd, const char *table_key,
uint table_key_length);
void close_thread_tables(THD *thd);
+void switch_to_nullable_trigger_fields(List<Item> &items, TABLE *);
bool fill_record_n_invoke_before_triggers(THD *thd, TABLE *table,
List<Item> &fields,
List<Item> &values,
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index 04e18403f78..692ba81510b 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -761,6 +761,7 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list,
*/
table_list->next_local= 0;
context->resolve_in_table_list_only(table_list);
+ switch_to_nullable_trigger_fields(*values, table);
while ((values= its++))
{
@@ -772,6 +773,7 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list,
}
if (setup_fields(thd, 0, *values, MARK_COLUMNS_READ, 0, 0))
goto abort;
+ switch_to_nullable_trigger_fields(*values, table);
}
its.rewind ();
@@ -870,6 +872,9 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list,
error= 1;
table->reset_default_fields();
+ switch_to_nullable_trigger_fields(fields, table);
+ switch_to_nullable_trigger_fields(update_fields, table);
+ switch_to_nullable_trigger_fields(update_values, table);
if (fields.elements || !value_count)
{
@@ -943,8 +948,8 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list,
share->default_values[share->null_bytes - 1];
}
}
- if (fill_record_n_invoke_before_triggers(thd, table, table->field, *values, 0,
- TRG_EVENT_INSERT))
+ if (fill_record_n_invoke_before_triggers(thd, table, table->field_to_fill(),
+ *values, 0, TRG_EVENT_INSERT))
{
if (values_list.elements != 1 && ! thd->is_error())
{
@@ -3704,8 +3709,8 @@ void select_insert::store_values(List<Item> &values)
fill_record_n_invoke_before_triggers(thd, table, *fields, values, 1,
TRG_EVENT_INSERT);
else
- fill_record_n_invoke_before_triggers(thd, table, table->field, values, 1,
- TRG_EVENT_INSERT);
+ fill_record_n_invoke_before_triggers(thd, table, table->field_to_fill(),
+ values, 1, TRG_EVENT_INSERT);
}
bool select_insert::prepare_eof()
diff --git a/sql/sql_load.cc b/sql/sql_load.cc
index aed26bd2fa5..e102066c0bc 100644
--- a/sql/sql_load.cc
+++ b/sql/sql_load.cc
@@ -295,6 +295,9 @@ int mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list,
if (setup_fields(thd, 0, set_values, MARK_COLUMNS_READ, 0, 0))
DBUG_RETURN(TRUE);
}
+ switch_to_nullable_trigger_fields(fields_vars, table);
+ switch_to_nullable_trigger_fields(set_fields, table);
+ switch_to_nullable_trigger_fields(set_values, table);
table->prepare_triggers_for_insert_stmt_or_event();
table->mark_columns_needed_for_insert();
diff --git a/sql/sql_trigger.cc b/sql/sql_trigger.cc
index bea19f1329c..272e1445273 100644
--- a/sql/sql_trigger.cc
+++ b/sql/sql_trigger.cc
@@ -1073,29 +1073,71 @@ Table_triggers_list::~Table_triggers_list()
@retval
True error
*/
-bool Table_triggers_list::prepare_record1_accessors(TABLE *table)
+bool Table_triggers_list::prepare_record_accessors(TABLE *table)
{
- Field **fld, **old_fld;
+ Field **fld, **trg_fld;
- if (!(record1_field= (Field **)alloc_root(&table->mem_root,
- (table->s->fields + 1) *
- sizeof(Field*))))
- return 1;
+ if ((bodies[TRG_EVENT_INSERT][TRG_ACTION_BEFORE] ||
+ bodies[TRG_EVENT_UPDATE][TRG_ACTION_BEFORE])
+ && (table->s->stored_fields != table->s->null_fields))
- for (fld= table->field, old_fld= record1_field; *fld; fld++, old_fld++)
{
- /*
- QQ: it is supposed that it is ok to use this function for field
- cloning...
- */
- if (!(*old_fld= (*fld)->make_new_field(&table->mem_root, table,
- table == (*fld)->table)))
+ int null_bytes= (table->s->stored_fields - table->s->null_fields + 7)/8;
+ if (!(extra_null_bitmap= (uchar*)alloc_root(&table->mem_root, null_bytes)))
return 1;
- (*old_fld)->move_field_offset((my_ptrdiff_t)(table->record[1] -
- table->record[0]));
+ if (!(record0_field= (Field **)alloc_root(&table->mem_root,
+ (table->s->fields + 1) *
+ sizeof(Field*))))
+ return 1;
+
+ uchar *null_ptr= extra_null_bitmap;
+ uchar null_bit= 1;
+ for (fld= table->field, trg_fld= record0_field; *fld; fld++, trg_fld++)
+ {
+ if (!(*fld)->null_ptr && !(*fld)->vcol_info)
+ {
+ Field *f;
+ if (!(f= *trg_fld= (*fld)->make_new_field(&table->mem_root, table,
+ table == (*fld)->table)))
+ return 1;
+
+ f->null_ptr= null_ptr;
+ f->null_bit= null_bit;
+ if (null_bit == 128)
+ null_ptr++, null_bit= 1;
+ else
+ null_bit*= 2;
+ }
+ else
+ *trg_fld= *fld;
+ }
+ *trg_fld= 0;
+ DBUG_ASSERT(null_ptr <= extra_null_bitmap + null_bytes);
+ bzero(extra_null_bitmap, null_bytes);
}
- *old_fld= 0;
+ else
+ record0_field= table->field;
+ if (bodies[TRG_EVENT_UPDATE][TRG_ACTION_BEFORE] ||
+ bodies[TRG_EVENT_UPDATE][TRG_ACTION_AFTER] ||
+ bodies[TRG_EVENT_DELETE][TRG_ACTION_BEFORE] ||
+ bodies[TRG_EVENT_DELETE][TRG_ACTION_AFTER])
+ {
+ if (!(record1_field= (Field **)alloc_root(&table->mem_root,
+ (table->s->fields + 1) *
+ sizeof(Field*))))
+ return 1;
+
+ for (fld= table->field, trg_fld= record1_field; *fld; fld++, trg_fld++)
+ {
+ if (!(*trg_fld= (*fld)->make_new_field(&table->mem_root, table,
+ table == (*fld)->table)))
+ return 1;
+ (*trg_fld)->move_field_offset((my_ptrdiff_t)(table->record[1] -
+ table->record[0]));
+ }
+ *trg_fld= 0;
+ }
return 0;
}
@@ -1320,13 +1362,6 @@ bool Table_triggers_list::check_n_load(THD *thd, const char *db,
table->triggers= triggers;
status_var_increment(thd->status_var.feature_trigger);
- /*
- TODO: This could be avoided if there is no triggers
- for UPDATE and DELETE.
- */
- if (!names_only && triggers->prepare_record1_accessors(table))
- DBUG_RETURN(1);
-
List_iterator_fast<ulonglong> itm(triggers->definition_modes_list);
List_iterator_fast<LEX_STRING> it_definer(triggers->definers_list);
List_iterator_fast<LEX_STRING> it_client_cs_name(triggers->client_cs_names);
@@ -1539,6 +1574,9 @@ bool Table_triggers_list::check_n_load(THD *thd, const char *db,
thd->spcont= save_spcont;
thd->variables.sql_mode= save_sql_mode;
+ if (!names_only && triggers->prepare_record_accessors(table))
+ DBUG_RETURN(1);
+
DBUG_RETURN(0);
err_with_lex_cleanup:
@@ -2107,13 +2145,13 @@ bool Table_triggers_list::process_triggers(THD *thd,
if (old_row_is_record1)
{
old_field= record1_field;
- new_field= trigger_table->field;
+ new_field= record0_field;
}
else
{
DBUG_ASSERT(event == TRG_EVENT_DELETE);
new_field= record1_field;
- old_field= trigger_table->field;
+ old_field= record0_field;
}
/*
This trigger must have been processed by the pre-locking
diff --git a/sql/sql_trigger.h b/sql/sql_trigger.h
index 1885720bf8b..fa858a0582b 100644
--- a/sql/sql_trigger.h
+++ b/sql/sql_trigger.h
@@ -68,6 +68,13 @@ class Table_triggers_list: public Sql_alloc
*/
Item_trigger_field *trigger_fields[TRG_EVENT_MAX][TRG_ACTION_MAX];
/**
+ Copy of TABLE::Field array which all fields made nullable
+ (using extra_null_bitmap, if needed). Used for NEW values in
+ BEFORE INSERT/UPDATE triggers.
+ */
+ Field **record0_field;
+ uchar *extra_null_bitmap;
+ /**
Copy of TABLE::Field array with field pointers set to TABLE::record[1]
buffer instead of TABLE::record[0] (used for OLD values in on UPDATE
trigger and DELETE trigger when it is called for REPLACE).
@@ -143,7 +150,8 @@ public:
/* End of character ser context. */
Table_triggers_list(TABLE *table_arg)
- :record1_field(0), trigger_table(table_arg),
+ :record0_field(0), extra_null_bitmap(0), record1_field(0),
+ trigger_table(table_arg),
m_has_unparseable_trigger(false)
{
bzero((char *)bodies, sizeof(bodies));
@@ -211,8 +219,16 @@ public:
trg_event_type event_type,
trg_action_time_type action_time);
+ Field **nullable_fields() { return record0_field; }
+ void reset_extra_null_bitmap()
+ {
+ int null_bytes= (trigger_table->s->stored_fields -
+ trigger_table->s->null_fields + 7)/8;
+ bzero(extra_null_bitmap, null_bytes);
+ }
+
private:
- bool prepare_record1_accessors(TABLE *table);
+ bool prepare_record_accessors(TABLE *table);
LEX_STRING* change_table_name_in_trignames(const char *old_db_name,
const char *new_db_name,
LEX_STRING *new_table_name,
@@ -234,6 +250,13 @@ private:
}
};
+inline Field **TABLE::field_to_fill()
+{
+ return triggers && triggers->nullable_fields() ? triggers->nullable_fields()
+ : field;
+}
+
+
extern const LEX_STRING trg_action_time_type_names[];
extern const LEX_STRING trg_event_type_names[];
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index 0caae7ac821..f343a17ee11 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -455,6 +455,8 @@ int mysql_update(THD *thd,
}
init_ftfuncs(thd, select_lex, 1);
+ switch_to_nullable_trigger_fields(fields, table);
+ switch_to_nullable_trigger_fields(values, table);
table->mark_columns_needed_for_update();
table->update_const_key_parts(conds);
@@ -1766,7 +1768,6 @@ int multi_update::prepare(List<Item> &not_used_values,
}
}
-
table_count= update.elements;
update_tables= update.first;
@@ -1802,7 +1803,15 @@ int multi_update::prepare(List<Item> &not_used_values,
/* Allocate copy fields */
max_fields=0;
for (i=0 ; i < table_count ; i++)
+ {
set_if_bigger(max_fields, fields_for_table[i]->elements + leaf_table_count);
+ if (fields_for_table[i]->elements)
+ {
+ TABLE *table= ((Item_field*)(fields_for_table[i]->head()))->field->table;
+ switch_to_nullable_trigger_fields(*fields_for_table[i], table);
+ switch_to_nullable_trigger_fields(*values_for_table[i], table);
+ }
+ }
copy_field= new Copy_field[max_fields];
DBUG_RETURN(thd->is_fatal_error != 0);
}
diff --git a/sql/table.h b/sql/table.h
index ab3960300e6..c45e86b695e 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -1386,6 +1386,7 @@ public:
bool prepare_triggers_for_delete_stmt_or_event();
bool prepare_triggers_for_update_stmt_or_event();
+ inline Field **field_to_fill();
bool validate_default_values_of_unset_fields(THD *thd) const;
};
View Lorry Controller Status