BEFORE triggers and NOT NULL columns in MySQL

Introduction

  For a long time there was a Bug#6295 in implementation of BEFORE triggers related to handling of NOT NULL column. The problem was that if a column is declared as NOT NULL, it wasn't possible to do INSERT NULL (or UPDATE to NULL) even though there was associated trigger, setting NOT-NULL value.

For example:

• There is the table 't1' with a NOT NULL column 'c1'
• The table has BEFORE INSERT trigger which sets the 'c1' column
  to NOT NULL value (SET NEW.c1 = 1)
• User executes the SQL statement INSERT INTO t1 VALUES(NULL) that fails with the following error:
      ERROR 1048 (23000): Column 'c1' cannot be null
• The user will get the same error if there is a BEFORE UPDATE trigger that sets the 'c1' column to NOT NULL and  the statement UPDATE t1 SET c1=NULL is executed

The reason for such wrong behavior was that the column constraints were checked before trigger were executed. Such behavior violated the SQL Standard that requires that column constraints are checked at the end of an SQL statement.

To fix this issue we redesigned the mysql server in the way that:

• the server allows NOT NULL columns to be NULL temporarily. That means, every NOT NULL column must be able to store NULL value  temporarily during statement execution;
• at the end of INSERT / UPDATE statement (after executions of all associated triggers) the server checks every NOT NULL in the record to ensure that NOT NULL constraint is valid;
• if there is NULL among NOT-NULL fields, the server reports an error, otherwise passes the record to a Storage Engine.

Note that:

• a NOT-NULL field can be a part of complex SELECT statement, in this case it still should be treated as NOT NULL, otherwise the optimizer  might choose a wrong plan;
• a NOT-NULL field can be a part of the SET and WHERE parts of an UPDATE statement, and it should be treated as nullable in the SET part, and as NOT NULL in the WHERE part;
• it should be possible to check nullability of a NOT-NULL field inside trigger body and it should work as if the field is nullable:
• For example, it should be possible to have expressions like (NEW.col IS NULL) when the column 'col' is declared as NOT NULL.

This wrong behavior was fixed in version 5.7.1.

Changes in handling of warnings

    For the strict sql_mode we added grouping of warning messages by column for the following error codes:

• ER_BAD_NULL_ERROR
• ER_NO_DEFAULT_FOR_FIELD
• ER_NO_DEFAULT_FOR_VIEW_FIELD.

  It means that any such warning message will be shown at most once for every column during handling of statement INSERT/REPLACE/INSERT SELECT/CREATE SELECT. In the strict sql_mode server reports an error for first occurrence of correspond violation and terminates statement execution. Before this the server wrote warning message corresponded to error codes mentioned above for every row that provoked this error. So, for example, if an user executed the statement INSERT SELECT for a table with a NOT NULL column and for this column the user sets the value NULL then the server generated as many warnings as there were rows that violated NOT NULL constraint. For LOAD STATEMENT the behavior remains the same as before since it makes sense to know what lines of imported file generates each line of warning.

Code test coverage

Around 40 new MTR tests were added during implementation of new functionality. These test are public and reside in mysql-test/t/trigger_wl6030.test. Implemented changes were 100% covered by these tests.

Technical details

To solve the task described in the introduction we decided to implement the following approach:

• Introduce two new attributes into Field:
• Field::m_is_tmp_nullable -- a flag, indicating if this particular field can accept NULL even though it is declared as NOT NULL;
• Field::m_is_tmp_null -- a place to store NULL value in case of NOT NULL field.
• Keep the physical table's record [table row buffer] intact;
• Introduce a new method Field::is_tmp_nullable() and use it in cases when we have to check whether NULL value can be assigned to this Field object;
• Change Field::is_null() so that it takes into account is_tmp_nullable and is_tmp_null;
• Extend interface of class Field by method check_constraints() in order to check NOT NULL constraint for the field.

When mysql server handles INSERT statement it iterates along the rows involved by the statement, sets defaults values for appropriate fields (if any) and calls the function fill_record_and_invoke_before_triggers(). This function does all things that are necessary to  complete the statement execution. For case when the table referenced by INSERT statement has BEFORE INSERT trigger this function does the following steps:

• For every field affected by the statement sets temporary nullability flag by calling the method Field::set_tmp_nullable();
• For every field affected by the statement sets the value specified by the statement for this field;
• In case when the value being set is NULL the function set_field_to_null_with_conversions() is called to handle this case;
• This function sets temporary NULL value for those fields that are declared as NOT NULL;
• Calls the BEFORE INSERT trigger;
• For every field affected by the statement clears temporary nullability flag by calling the method Field::reset_tmp_nullable();
• Calls the function check_record() to check NOT NULL constraint for every field involved by the statement being executed;
• The function check_record()  iterates along the Field objects and calls the method Field::check_constraints() for every one;
• The method Field::check_constraints() checks whether corresponding field can be NULL. If it is true the the method returns ok. If corresponding field in the table can't be NULL and Field::m_is_tmp_null is set then the error returned and corresponding reason is set in Diagnostics area.

Based on performance measurements the introduction of two additional attributes to the class Field didn't lead to performance degradation.