Bug Description:

When inserting data into a table that has a BEFORE INSERT trigger, using an INSERT ... SELECT statement, the trigger is expected to populate the year column based on quarter. However, MariaDB returns an error stating that the column year cannot be null, even though the trigger sets its value.

This didn't have a problem previously.

Steps to Reproduce:

Create a test table:

CREATE TABLE `test_table` (

  `year` int(11) NOT NULL,

  `quarter` char(6) NOT NULL,

  UNIQUE KEY `test_table_quarter` (`quarter`, `year`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

Create a BEFORE INSERT trigger:

DELIMITER $$  

CREATE TRIGGER before_insert  

BEFORE INSERT ON test_table  

FOR EACH ROW  

BEGIN  

    SET NEW.year = LEFT(NEW.quarter, 4);  

END  

$$  

DELIMITER ;

Attempt an INSERT ... SELECT statement (Fails):

INSERT INTO test_table (year, quarter)

SELECT 2025 AS year, CONCAT('2025Q', seq) AS quarter FROM seq_1_to_4;

We are using a select from another table/view but here we can get the same 'bug' by just using a sequence.

Error:

Error Code: 1048. Column 'year' cannot be null

Manually inserting values (Works):

INSERT INTO test_table (`year`, `quarter`) VALUES ('2025', '2025Q1');

Expected Behavior:

The trigger should correctly populate the year column when using INSERT ... SELECT, just as it does when using INSERT ... VALUES.

Actual Behavior:

The INSERT ... SELECT statement fails because MariaDB treats year as NULL before the trigger executes, even though the trigger should set its value.

If you remove the trigger there is no problem, or if you insert a single row it works ok. The workaround in our case was to modify the insert to obtain the year rather than use the trigger.
It could be that this is intentional, however the behaviour has changed so I am reporting it.

Possibly

• MDEV-36066: "v10.11.11 introduces bug that breaks production code - INSERT SELECT and triggers not working" ​
• MDEV-36191: "Multi-row insert fails on table with update before trigger"