Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-9428

NO_AUTO_VALUE_ON_ZERO is ignored when a trigger before insert is defined

Details

    • 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 |
      		 
      # +----+
      		 
       

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-8605 MariaDB not use DEFAULT value even when inserted NULL for NOT NULLABLE column.

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova added a comment -

            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.

            elenst Elena Stepanova added a comment - 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.
            ajanuary Andrew January added a comment -

            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.

            ajanuary Andrew January added a comment - 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.
            serg Sergei Golubchik added a comment -

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

            serg Sergei Golubchik added a comment - You're right. Sorry for this. Will be fixed in the next release.

            People

              serg Sergei Golubchik
              Rich Rich Theobald
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.