[MDEV-9428] NO_AUTO_VALUE_ON_ZERO is ignored when a trigger before insert is defined Created: 2016-01-18  Updated: 2016-06-28  Resolved: 2016-06-28

Status: Closed
Project: MariaDB Server
Component/s: Triggers
Affects Version/s: 10.1.10
Fix Version/s: 10.1.11, 10.1.15

Type: Bug Priority: Major
Reporter: Rich Theobald Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: regression
Environment:

Ubuntu 14.04

Issue Links:
Relates
relates to MDEV-8605 MariaDB not use DEFAULT value even wh... Closed
Sprint: 10.1.11

 Description    

set session sql_mode ='NO_AUTO_VALUE_ON_ZERO';
 
 
 
drop table if exists test;
 
            
create table test (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,  PRIMARY KEY (`id`));
 
 
 
insert into test (id) values (0);
 
 
 
select id from test;
 
 
 
#+----+
 
# | id |
 
# +----+
 
# |  0 |
 
# +----+
 
 
 
drop table if exists test;
 
            
create table test (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,  PRIMARY KEY (`id`));
 
 
 
drop trigger if exists test_before_insert;
 
              
delimiter ;;
 
    
CREATE TRIGGER `test_before_insert` 
BEFORE INSERT ON `test` 
FOR EACH ROW 
BEGIN
 
END ;;
 
 
 
delimiter ;
 
 
 
insert into test (id) values (0);
 
 
 
select id from test;
 
 
 
# +----+
 
# | id |
 
# +----+
 
# |  1 |
 
# +----+
 
 



 Comments   
Comment by Elena Stepanova [ 2016-01-18 ]

Thanks for the report and the test case.

The problem was introduced by the following revision:

commit 0686c34d22a5cbf93015012eaf77a4a977b63afb
 
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.

Comment by Andrew January [ 2016-05-11 ]

The fix does not work correctly.

It only works if you don't specify the column names (e.g. "insert t1 values (0)")

If you specify the column names (e.g. "insert t1(ID) values (0)") as in the original ticket, the commit 5da7c34 does not fix it.

Comment by Sergei Golubchik [ 2016-05-11 ]

You're right. Sorry for this. Will be fixed in the next release.

Generated at Thu Feb 08 07:34:36 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.