MySQL Bugs: #6295: Triggers are not processed for NOT NULL columns

Bug #6295	Triggers are not processed for NOT NULL columns
Submitted:	28 Oct 2004 1:30	Modified:	26 Sep 2008 10:52
Reporter:	Peter Gulutzan
Status:	Verified
Category:	Server: SP	Severity:	S2 (Serious)
Version:	5.0,5.1	OS:	Linux (any OS)
Assigned to:		Target Version:
Triage:	Triaged: D2 (Serious) / R3 (Medium) / E4 (High)

Description:
I have a column which is defined as NOT NULL. I insert a NULL value. But I have a 
BEFORE trigger which sets the column value to something which is not NULL. This change 
should happen before the check for NOT NULL violation. But I get an error message 
"Column ... cannot be null". 

How to repeat:
mysql> create table th (s1 int not null); 
Query OK, 0 rows affected (0.28 sec) 
 
mysql> create trigger th_bi before insert on th for each row set new.s1 = 1; 
Query OK, 0 rows affected (0.01 sec) 
 
mysql> insert into th values (null); 
ERROR 1048 (23000): Column 's1' cannot be null

An additional case to consider: an invalid field value with traditional sql_mode

set sql_mode='traditional';
create table t1 (dt datetime);
create trigger t1_bi before insert on t1 for each row set new.dt = '2005-05-01';
insert into t1 values ('0000-00-00');
select * from t1;
drop trigger t1_bi;
drop table t1;

In general, MySQL apparently runs "before insert" triggers after it evaluates the data to
be inserted against table/column constraints.

This is making it difficult for me to port my application to MySQL.  Oracle and
PostgtreSQL (at least that I know of) run "before insert" triggers first, and then check
constraints.

I think it's an important bug to be fixed, because it can make life very inconvenient in
the following circumstances:
*) porting applications from oracle or PostgreSQL (maybe others?)
*) if you want to change your data model without affecting code accessing the database -
by checking constraints before "before insert" triggers are fired means that you cannot
abstract some changes from application code - meaning that some changes can be more
intrusive/time consuming/expensive, etc

For me, this weakens the power of triggers considerably (also along with the limitation
that triggers are not fired on secondary tables) and makes my porting job that much more
difficult.

I hope that in my case I can fix this by adding default values to the affected columns
(in my case the problem is caused by the fact that not-null columns are not included in
the insert queries because they are set by triggers - these columns are also foreign keys
into other tables), but I can imagine other scenarios where the fix would not be so easy.

I just wanted to express my opinion that it is a compatibility issue; it can be a
significant problem, and, in my opinion, because it weakens the power of MySQL triggers
and can make porting from Oracle and PostgreSQL more difficult, it should be addressed in
a future release of MySQL.

thanks,
David

This bug still exists in 5.1.23 Does anybody have somme news?

There is some solutions to avoid this problem, but none for triggers having to 
set NOT NULL field.

- set SQL_MODE='' does not work with triggers before b.e SET NEW.FIELD=Value; and it is
dangerous to configure server with this option (no more integrity).
- default value does not work when you want to use a GEOMETRY FIELD.

In my case, i need a trigger to set a GEOMETRY field whith x,y values given in the
insert.
The field must have a spatial index so must be not null.

The code i have to write is:

DELIMITER//
DROP TRIGGER IF EXISTS MYTRIG//
CREATE TRIGGER MYTRIG BEFORE INSERT ON MYTABLE
FOR EACH ROW
BEGIN
SET @@sql_mode='';
SET NEW.geom=GeomFromText(CONCAT('POINT(',NEW.x,' ',NEW.y,')'));
END//

Any solution ???
Or good news : a new fix for this ugly problem (since 2004)???

Just came up against this bug using a POINT type column with SPATIAL INDEX applied (as
above). The column can not be NULL due to the index limitation.

This seems like a big problem to me, perhaps the 'Non-critical' severity should be
elevated.

Any idea if this is going to be fixed in the near future? Been open for around 4 years
now!

Simple test case:

CREATE TABLE `test` (
`ID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`Title` VARCHAR( 50 ) NOT NULL,
`Val1` INT NOT NULL DEFAULT '5',
`Val2` INT NOT NULL ,
PRIMARY KEY ( `ID` )
) ENGINE = MYISAM ;

CREATE TRIGGER Val2_Insert BEFORE INSERT ON test
  FOR EACH ROW SET NEW.Val2 = (NEW.Val1 + 10);

INSERT INTO `test`
(`Title`)
VALUES
('This is an inserted row');

Bug #39660 is a duplicate of this bug here.

Please look into bug #39660 for getting a test case.

Copy the test case here:

CREATE TABLE t(i INTEGER NOT NULL);

DELIMITER §

CREATE TRIGGER tr_null BEFORE INSERT ON t
FOR EACH ROW BEGIN
IF NEW.i IS NULL THEN
SET NEW.i = 0;
END IF;
END§

DELIMITER ;

INSERT INTO t(i) VALUES(NULL);

Bug #43279 is set as duplicate of this bug here