MySQL Bugs: #86463: Data checks are performed before `BEFORE INSERT` triggers

Bug #86463	Data checks are performed before `BEFORE INSERT` triggers
Submitted:	25 May 2017 17:12	Modified:	26 Jun 2017 12:46
Reporter:	S. M.	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.7	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	triggers

Description:
Data checks (eg. Incorrect time value) are performed before `BEFORE INSERT` triggers.

Because of this, if there is a trigger which corrects data before it is inserted, the insertion will still fail.

I had a look at few trigger documentation pages:

- https://dev.mysql.com/doc/refman/5.7/en/triggers.html
- https://dev.mysql.com/doc/refman/5.7/en/faqs-triggers.html#faq-mysql-how-triggers-managed
- https://dev.mysql.com/doc/refman/5.7/en/stored-program-restrictions.html#stored-routines-t...

but I didn't find anything related.

How to repeat:
SHOW VARIABLES LIKE 'sql_mode';

# +---------------+---------------------------------------------------------------------------+
# | Variable_name | Value                                                                     |
# +---------------+---------------------------------------------------------------------------+
# | sql_mode      | STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO |
# +---------------+---------------------------------------------------------------------------+

CREATE TABLE test_table(
  mytime TIME
);

DELIMITER ;;

CREATE TRIGGER test_trigger
BEFORE INSERT ON test_table
FOR EACH ROW
BEGIN
  IF NEW.mytime = '1899-12-30' THEN
    SET NEW.mytime := '00:00:01';
  END IF;
  IF NEW.mytime = '00:00:02' THEN
    SET NEW.mytime := '00:00:01';
  END IF;
END ;;

DELIMITER ;

INSERT INTO test_table values('1899-12-30');

# ERROR 1292 (22007): Incorrect time value: '1899-12-30' for column 'mytime' at row 1

INSERT INTO test_table values('00:00:02');

SELECT * FROM test_table;

# +----------+
# | mytime   |
# +----------+
# | 00:00:01 |
# +----------+

Suggested fix:
Data checks should be performed after triggers.

Hi!

This is expected behaviour. Entered temporal values are checked before the statement is executed. This is an extension of basic syntax check. This is basic data type check and      is done   before any  statement is executed.

If you think  it should be documented, let us know.

I think it should definitely be documented; although I understand the engineering logic, it's somewhat unexpected, so users should be warned.

Hi Pippo,

I agree with you that the order of processing should be better documented when it comes to the order of parsing, interpreting constants .... firing BEFORE triggers , etc .....

Verified as a documentation bug.

Posted by developer:
 
Updated https://dev.mysql.com/doc/refman/5.7/en/create-trigger.html