Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.3.15, 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
-
Official MariaDB Docker Image (MARIADB_VERSION=1:10.3.15+maria~bionic)
Description
Bug description:
When inserting a new row into a table without specifying a column that is set to not null will give the error "Field 'xxx' doesn't have a default value" - even if a trigger exists, that should replace the NULL value with a not-null value before inserting it.
Expected behavior:
Triggers declared as "before insert on 'table_xxx' " should be fired if they might change any of the new values to insert BEFORE column constrains are verified.
See similar SQL bug that has been fixed:
- https://mysqlserverteam.com/before-triggers-and-not-null-columns-in-mysql/
- https://bugs.mysql.com/bug.php?id=6295
Actual behavior:
If a new row contains a null value for a column that is defined as not null but is changed in a trigger, that trigger won't be fired at all as immediately a error is thrown.
Example SQL code to reproduce the error:
drop database if exists test_database; |
create database test_database; |
use test_database; |
|
|
create table test_table( |
id int, |
rate DECIMAL(23 , 10 ) NOT NULL, |
|
|
primary key (id) |
);
|
|
|
create table currency( |
name varchar(4), |
rate DECIMAL(23 , 10 ) NOT NULL, |
primary key (name) |
);
|
|
|
|
|
delimiter ///
|
create trigger test_trigger |
before insert on test_table |
for each row |
begin
|
if new.rate is null then |
set new.rate = ( |
select c.rate |
from currency c |
where c.name = 'EUR' |
);
|
end if; |
end; |
///
|
delimiter ;
|
|
|
insert into currency (name, rate) values ('EUR', 1.234); |
insert into test_table (id) values (1); |
|
|
-- Error in query (1364): Field 'rate' doesn't have a default value -- |
Workaround:
- define column "NULLABLE" without "NOT NULL" constraint.
- Insert keyword instead of NULL value. For example -1 or 'NULL'
- Inside Trigger: "if new.column = -1" or " if new.column = 'NULL' " then do something
Example SQL code to demonstrate the workaround:
(Note: Only the insert statement as well as the comparison in the trigger (if new.rate is null) changed.)
drop database if exists test_database; |
create database test_database; |
use test_database; |
|
|
create table test_table( |
id int, |
rate DECIMAL(23 , 10 ) NOT NULL, |
|
|
primary key (id) |
);
|
|
|
create table currency( |
name varchar(4), |
rate DECIMAL(23 , 10 ) NOT NULL, |
primary key (name) |
);
|
|
|
|
|
delimiter ///
|
create trigger test_trigger |
before insert on test_table |
for each row |
begin
|
if new.rate = -1 then |
set new.rate = ( |
select c.rate |
from currency c |
where c.name = 'EUR' |
);
|
end if; |
end; |
///
|
delimiter ;
|
|
|
insert into currency (name, rate) values ('EUR', 1.234); |
insert into test_table (id, rate) values (1, -1); |
|
|
select rate from test_table; |
Attachments
Issue Links
- causes
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
Activity
OK tu push, but as a nit-peacking I'd add tests:
1) case with field not NULL in the trigger (i.e inserted both fields)
2) explicit case when trigger try to put NULL in not NULL field even if it was not NULL and was NULL originally (should return error obviously)
Thanks to everyone involved for your long work on this and resolving the issue 
Five years since this ugly (and MAJOR!) bug has been reported and is not addressed. Any hope on being fixed?
MariaDB 11.6.2 here