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

Before Trigger not processed for Not Null Columns

    Details

    • Type: Bug
    • Status: In Review (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.1, 10.2, 10.3, 10.4, 10.3.15
    • Fix Version/s: 10.1
    • Labels:
    • Environment:
      Official MariaDB Docker Image (MARIADB_VERSION=1:10.3.15+maria~bionic)

      Description

      Bug description:

      When inserting a new row into a table without specifying a column that is set to not null will give the error "Field 'xxx' doesn't have a default value" - even if a trigger exists, that should replace the NULL value with a not-null value before inserting it.

      Expected behavior:

      Triggers declared as "before insert on 'table_xxx' " should be fired if they might change any of the new values to insert BEFORE column constrains are verified.

      See similar SQL bug that has been fixed:

      Actual behavior:

      If a new row contains a null value for a column that is defined as not null but is changed in a trigger, that trigger won't be fired at all as immediately a error is thrown.

      Example SQL code to reproduce the error:

      drop database if exists test_database;
      create database test_database;
      use test_database;
       
      create table test_table(
          id int,
          rate DECIMAL(23 , 10 ) NOT NULL,
       
          primary key (id)
      );
       
      create table currency(
          name varchar(4),
          rate DECIMAL(23 , 10 ) NOT NULL,
          primary key (name)
      );
       
       
      delimiter ///
      create trigger test_trigger
      	before insert on test_table
          for each row
      begin
          if new.rate is null then
      		set new.rate = (
      			select c.rate
      			from currency c
      			where c.name = 'EUR'
      		);
          end if;
      end;
      ///
      delimiter ;
       
      insert into currency (name, rate) values ('EUR', 1.234);
      insert into test_table (id) values (1);
       
      -- Error in query (1364): Field 'rate' doesn't have a default value --
      

      Workaround:

      • define column "NULLABLE" without "NOT NULL" constraint.
      • Insert keyword instead of NULL value. For example -1 or 'NULL'
      • Inside Trigger: "if new.column = -1" or " if new.column = 'NULL' " then do something

      Example SQL code to demonstrate the workaround:

      (Note: Only the insert statement as well as the comparison in the trigger (if new.rate is null) changed.)

      drop database if exists test_database;
      create database test_database;
      use test_database;
       
      create table test_table(
          id int,
          rate DECIMAL(23 , 10 ) NOT NULL,
       
          primary key (id)
      );
       
      create table currency(
          name varchar(4),
          rate DECIMAL(23 , 10 ) NOT NULL,
          primary key (name)
      );
       
       
      delimiter ///
      create trigger test_trigger
      	before insert on test_table
          for each row
      begin
          if new.rate = -1 then
      		set new.rate = (
      			select c.rate
      			from currency c
      			where c.name = 'EUR'
      		);
          end if;
      end;
      ///
      delimiter ;
       
      insert into currency (name, rate) values ('EUR', 1.234);
      insert into test_table (id, rate) values (1, -1);
       
      select rate from test_table;
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                serg Sergei Golubchik
                Reporter:
                langfingaz langfingaz
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated: