Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.1, 10.3.15, 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
-
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
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-