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

Before Trigger not processed for Not Null Columns

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.5, 10.6, 10.3.15, 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
    • 10.5, 10.6
    • 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

              anel Anel Husakovic
              langfingaz langfingaz
              Votes:
              1 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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