From 96569b793a369bcbff1dc3aa0260b198a0107eff Mon Sep 17 00:00:00 2001 From: Anel Husakovic Date: Tue, 25 Jun 2019 00:05:41 -0700 Subject: MDEV-19761 - Before Trigger not processed for Not Null Column --- mysql-test/r/trigger.result | 6 +++- mysql-test/r/trigger_no_defaults-11698.result | 29 +++++++++++++++++++ mysql-test/r/trigger_null-8605.result | 2 +- mysql-test/suite/funcs_1/r/innodb_trig_09.result | 4 +-- mysql-test/suite/funcs_1/r/memory_trig_09.result | 4 +-- mysql-test/suite/funcs_1/r/myisam_trig_09.result | 4 +-- mysql-test/t/trigger_no_defaults-11698.test | 37 ++++++++++++++++++++---- sql/sql_base.cc | 23 +++++++++++++++ sql/sql_trigger.cc | 2 +- sql/sql_trigger.h | 4 +++ 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 &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); -- cgit v1.2.1