MDEV-19761 - Before Trigger not processed for Not Null Columnbb-10.1-anel-MDEV-19761

10 files changed, 97 insertions, 18 deletions

diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result
index 8852a622251..04bb9d9be68 100644
--- a/mysql-test/r/trigger.result
+++ b/mysql-test/r/trigger.result
@@ -366,6 +366,8 @@ create trigger trg1 before update on t1 for each row set @a:= @a + new.j - old.j
 create trigger trg2 after update on t1 for each row set @b:= "Fired";
 set @a:= 0, @b:= "";
 update t1, t2 set j = j + 10 where t1.i = t2.i;
+Warnings:
+Warning	1048	Column 'k' cannot be null
 select @a, @b;
 @a	@b
 10	Fired
@@ -1235,11 +1237,13 @@ insert into t1 values (1,1), (2,2), (3,3);
 create trigger t1_bu before update on t1 for each row
 set new.j = new.j + 10;
 update t1 set i= i+ 10 where j > 2;
+Warnings:
+Warning	1048	Column 'j' cannot be null
 select * from t1;
 i	j
 1	1
 2	2
-13	13
+13	0
 drop table t1;
 CREATE TABLE t1 (a INT PRIMARY KEY);
 CREATE TABLE t2 (a INT PRIMARY KEY);
diff --git a/mysql-test/r/trigger_no_defaults-11698.result b/mysql-test/r/trigger_no_defaults-11698.result
index 93672092180..b582636346a 100644
--- a/mysql-test/r/trigger_no_defaults-11698.result
+++ b/mysql-test/r/trigger_no_defaults-11698.result
@@ -1,3 +1,6 @@
+#
+# MDEV-11698 Old Bug possibly not fixed; BEFORE INSERT Trigger on NOT NULL
+#
 set sql_mode='strict_all_tables';
 create table t1 (a int not null, b int);
 insert t1 (b) values (1);
@@ -21,6 +24,9 @@ a	b
 0	30
 drop table t1;
 set sql_mode=default;
+#
+# MDEV-11842 Fail to insert on a table where a field has no default
+#
 create table t1 (
 id int(11) not null auto_increment primary key,
 data1 varchar(10) not null,
@@ -38,3 +44,26 @@ id	data1	data2
 1		x
 2		y
 drop table t1;
+#
+# MDEV-19761 - Before Trigger not processed for Not Null Columns
+#
+create database my_test;
+use my_test;
+create table t1( id1 int, id2 int, rate int not null);
+create or replace trigger test_trigger before insert on t1 for each row 
+begin if new.rate is null then set new.rate = 5; end if; end;
+$$
+insert into t1 (id1,id2) values (20,30);
+select * from t1;
+id1	id2	rate
+20	30	5
+drop trigger test_trigger;
+create trigger test_trigger before insert on t1 for each row 
+set new.rate=if(new.rate is null,5,new.rate);
+$$
+insert into t1 (id1,id2) values (20,30);
+select * from t1;
+id1	id2	rate
+20	30	5
+20	30	5
+drop database my_test;
diff --git a/mysql-test/r/trigger_null-8605.result b/mysql-test/r/trigger_null-8605.result
index 10315988708..cb02c38ccce 100644
--- a/mysql-test/r/trigger_null-8605.result
+++ b/mysql-test/r/trigger_null-8605.result
@@ -352,7 +352,7 @@ 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	
+a	int(11)	YES	PRI	NULL	
 drop table t1;
 create table t1 (
 pk int primary key,
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 e6a10592424..465ed58d177 100644
--- a/mysql-test/suite/funcs_1/r/innodb_trig_09.result
+++ b/mysql-test/suite/funcs_1/r/innodb_trig_09.result
@@ -187,8 +187,6 @@ a	NULL	Test 3.5.9.4	7	999	995.240000000000000000000000000000
 0	0	0	0	0	0
 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
 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
@@ -196,7 +194,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	NULL	999	NULL
+a	NULL	Test 3.5.9.4-trig	0	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 5394aa01bf6..e833f641ad7 100644
--- a/mysql-test/suite/funcs_1/r/memory_trig_09.result
+++ b/mysql-test/suite/funcs_1/r/memory_trig_09.result
@@ -188,8 +188,6 @@ a	NULL	Test 3.5.9.4	7	999	995.240000000000000000000000000000
 0	0	0	0	0	0
 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
 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
@@ -197,7 +195,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	NULL	999	NULL
+a	NULL	Test 3.5.9.4-trig	0	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 5394aa01bf6..e833f641ad7 100644
--- a/mysql-test/suite/funcs_1/r/myisam_trig_09.result
+++ b/mysql-test/suite/funcs_1/r/myisam_trig_09.result
@@ -188,8 +188,6 @@ a	NULL	Test 3.5.9.4	7	999	995.240000000000000000000000000000
 0	0	0	0	0	0
 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
 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
@@ -197,7 +195,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	NULL	999	NULL
+a	NULL	Test 3.5.9.4-trig	0	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/t/trigger_no_defaults-11698.test b/mysql-test/t/trigger_no_defaults-11698.test
index d7e391a8bb2..4eafdb5adb2 100644
--- a/mysql-test/t/trigger_no_defaults-11698.test
+++ b/mysql-test/t/trigger_no_defaults-11698.test
@@ -1,6 +1,6 @@
-#
-# MDEV-11698 Old Bug possibly not fixed; BEFORE INSERT Trigger on NOT NULL
-#
+--echo #
+--echo # MDEV-11698 Old Bug possibly not fixed; BEFORE INSERT Trigger on NOT NULL
+--echo #
 set sql_mode='strict_all_tables';
 create table t1 (a int not null, b int);
 --error ER_NO_DEFAULT_FOR_FIELD
@@ -25,9 +25,9 @@ select * from t1;
 drop table t1;
 set sql_mode=default;
 
-#
-# MDEV-11842 Fail to insert on a table where a field has no default
-#
+--echo #
+--echo # MDEV-11842 Fail to insert on a table where a field has no default
+--echo #
 create table t1 (
   id int(11) not null auto_increment primary key,
   data1 varchar(10) not null,
@@ -38,3 +38,28 @@ create trigger test_trigger before insert on t1 for each row begin end;
 insert into t1 (data2) values ('y');
 select * from t1;
 drop table t1;
+
+--echo #
+--echo # MDEV-19761 - Before Trigger not processed for Not Null Columns
+--echo #
+create database my_test;
+use my_test;
+create table t1( id1 int, id2 int, rate int not null);
+delimiter $$;
+create or replace trigger test_trigger before insert on t1 for each row 
+  begin if new.rate is null then set new.rate = 5; end if; end;
+$$
+delimiter ;$$
+ 
+insert into t1 (id1,id2) values (20,30);
+select * from t1;
+drop trigger test_trigger;
+
+delimiter $$;
+create trigger test_trigger before insert on t1 for each row 
+ set new.rate=if(new.rate is null,5,new.rate);
+ $$
+delimiter ;$$
+insert into t1 (id1,id2) values (20,30);
+select * from t1;
+drop database my_test;
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index e8bdff8b48f..d9fe37c83da 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -8959,6 +8959,29 @@ void switch_to_nullable_trigger_fields(List<Item> &items, TABLE *table)
 
     while ((item= it++))
       item->walk(&Item::switch_to_nullable_fields_processor, 1, (uchar*)field);
+    uint16 cnt=0;
+    uchar *nptr;
+    nptr= (uchar*)alloc_root(&table->mem_root, (table->s->fields - table->s->null_fields + 7)/8);
+    // First find null_ptr for NULL field in case of mixed NULL and NOT NULL fields
+    for (Field **f= field; *f; f++)
+    {
+      if (table->field[cnt]->null_ptr)
+      {
+        nptr= table->field[cnt]->null_ptr;
+        break;
+      }
+    }
+    for (Field **f= field; *f; f++)
+    {
+        if (!table->field[cnt]->null_ptr)
+        {
+          (*f)->null_bit= 1<<(cnt+1);
+          (*f)->flags&= ~(NOT_NULL_FLAG);
+          (*f)->null_ptr= nptr;
+        }
+        cnt++;
+    } 
+    bzero(nptr, (table->s->fields - table->s->null_fields + 7)/8);
     table->triggers->reset_extra_null_bitmap();
   }
 }
diff --git a/sql/sql_trigger.cc b/sql/sql_trigger.cc
index 4ecd8139921..7de2fde0e5c 100644
--- a/sql/sql_trigger.cc
+++ b/sql/sql_trigger.cc
@@ -1118,7 +1118,7 @@ bool Table_triggers_list::prepare_record_accessors(TABLE *table)
 
         f->flags= (*fld)->flags;
         f->null_ptr= null_ptr;
-        f->null_bit= null_bit;
+        f->null_bit= (*fld)->null_bit;
         if (null_bit == 128)
           null_ptr++, null_bit= 1;
         else
diff --git a/sql/sql_trigger.h b/sql/sql_trigger.h
index f451dfda1ee..c1174bfd36e 100644
--- a/sql/sql_trigger.h
+++ b/sql/sql_trigger.h
@@ -226,6 +226,10 @@ public:
                      trigger_table->s->null_fields + 7)/8;
     bzero(extra_null_bitmap, null_bytes);
   }
+  uchar *get_extra_null_bitmap() const
+  {
+    return extra_null_bitmap;
+  }
 
 private:
   bool prepare_record_accessors(TABLE *table);