Sergei Golubchik added a comment - 2015-10-23 20:09 My fix is not nearly as intrusive as what 5.7 has. Still, it changes a very common code path, so I'd rather not put it into 10.0 to avoid possible regressions. I'm going to push it into 10.1 only

Mikhail Gavrilov added a comment - 2015-10-24 17:28 Sergei, I don't seen your fix, but expected sequence of checks looks for me so: For INSERT: 1. run before insert trigger (here may be generated correct values) 2. apply autoincrement for null PK and DEFAULT value for TIMESTAMP etc 3. check duplication for PK and unique index, otherwise if used ON DUPLICATE UPDATE goto update duplicate record begin with step 3. 4. check for not null and type 5. check for constraints 6. add new record 7. run after insert trigger For UPDATE: 1. run before update trigger (here may be generated correct values) 2. apply autoincrement for null PK and DEFAULT value for TIMESTAMP etc 3. check duplication for PK and unique index 4. check for not null and type checking 5. check for constraints 6. update record 7. run after update trigger

Sergei Golubchik added a comment - 2015-10-24 20:48 mikhail , right. The difficulty is in the fact that NOT NULL columns in MariaDB/MySQL cannot store NULL values. Not even temporarily, not even in memory. There is no place allocated for that. That's why NOT NULL constraint is checked very early, when the value is stored in the field in memory . To fix it we need to be able to store NULL values in the NOT NULL columns. Temporarily, only in memory, not in the table. But still, this is a big change that can potentially introduce regressions. Especially because it will affect every INSERT and UPDATE statements, not only those that store NULL and later change it in the BEFORE trigger.

Mikhail Gavrilov added a comment - 2015-10-24 21:28 I understood that this could trigger a regression. I would like you to pay attention to the moment I placed the check for null after checking DUPLICATE this in order to be able to do mass update without repeating all the columns in the query. Example from real life: INSERT INTO `appl_profiles` ( `id_profile`, `id_profile_type`, `emp_id_first`, `dynamic_cols` ) VALUES ( '99659cfc-6bf5-11e5-bcc5-0050563c3a6d', '13', '212516', COLUMN_CREATE ('bayer_part', '11') ), ( 'a270fce5-6bf5-11e5-bcc5-0050563c3a6d', '13', '212516', COLUMN_CREATE ('bayer_part', '100') ) ON DUPLICATE KEY UPDATE `dynamic_cols` = VALUES(`dynamic_cols`) I can not remove '13', '212516' because they are NOT NULLable. if checking for NULL would be after checking on duplication I could write: INSERT INTO `appl_profiles` ( `id_profile`, `dynamic_cols` ) VALUES ( '99659cfc-6bf5-11e5-bcc5-0050563c3a6d', COLUMN_CREATE ('bayer_part', '11') ), ( 'a270fce5-6bf5-11e5-bcc5-0050563c3a6d', COLUMN_CREATE ('bayer_part', '100') ) ON DUPLICATE KEY UPDATE `dynamic_cols` = VALUES(`dynamic_cols`) this is much cleaner than previous variant.

Elena Stepanova added a comment - 2015-12-26 01:07 For the record, the actual change was this: commit 0686c34d22a5cbf93015012eaf77a4a977b63afb ad5db17e882fea36dcae6f6e61996b5f9bf28962 Author: Sergei Golubchik <serg@mariadb.org> Date: Sat Nov 14 22:51:54 2015 +0100   MDEV-8605 MariaDB not use DEFAULT value even when inserted NULL for NOT NULLABLE column NOT NULL constraint must be checked *after* the BEFORE triggers. That is for INSERT and UPDATE statements even NOT NULL fields must be able to store a NULL temporarily at least while BEFORE INSERT/UPDATE triggers are running. It is important to have it here to avoid confusion, because it has very little to do with the initial bug summary.