[MDEV-11698] Old Bug possibly not fixed; BEFORE INSERT Trigger on NOT NULL Created: 2016-12-31  Updated: 2023-05-01  Resolved: 2017-01-17

Status: Closed
Project: MariaDB Server
Component/s: Triggers
Affects Version/s: 10.1.10, 5.5, 10.0, 10.1, 10.1.19, 10.2
Fix Version/s: 10.1.21, 10.2.4

Type: Bug Priority: Major
Reporter: Yurelle Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: upstream-fixed
Environment:

XAMPP v7.0.13 (MariaDB v10.1.19)
Windows 7 64-bit
Intel Core i7 2600k
16GB of RAM
240GB SSD (OCZ Vertex 460)

Issue Links:
Relates
relates to MDEV-10002 Before Insert trigger does not work w... Closed
relates to MDEV-19761 Before Trigger not processed for Not ... Open

 Description   

I believe this old ticket is either still broken, or was perhaps broken again, recently by something else:

I asked a question on StackExchange for some help to make sure that it wasn’t a configuration error on my part before bothering you guys, but I didn’t get any response. I apologize if this turns out to be a PEBKAC.

The short of it is:

  1. Create a base table in MariaDB, which has a NOT NULL column
  2. Create a view which points to that base table, excluding the NOT NULL column
  3. Create a BEFORE INSERT trigger on the base table, to populate the NOT NULL column
  4. Insert a row into the view (thus implying NULL for the NOT NULL column; expecting the trigger to populate)

Result:
Error: “Field '__' doesn't have a default value”

I’ve tried doing several insert tests on the base table, and it looks like the trigger is just not firing at all. I searched around online for other people with this problem, but they all seemed to just be syntax errors. I don’t have much experience with this kind of “deep” SQL, but I think mine’s correct.

I have tested on:

[FAILS] -> MariaDB v10.1.19 (via XAMPP v7.0.13)
 
[FAILS] -> MySQL v5.6
 
[WORKS] -> MySQL v5.7

I have created a simple demonstration schema, and a SQL Fiddle:

DELIMITER //
 
 
 
CREATE TABLE privateNotes (
 
  id int(11) NOT NULL,
 
  userId int(11) NOT NULL,
 
  text varchar(500) NOT NULL,
 
  groupId int(11) NOT NULL
 
);
 
 
 
INSERT INTO privateNotes (id, userId, text, groupId) VALUES
 
(30, 1, '[John] A Secret Message',         101),
 
(32, 2, '[George] A Secret Message',       101),
 
(34, 3, '[Sarah] A Secret Message',        202),
 
(36, 4, '[Mary] A Secret Message',         202);
 
 
 
-- --------------------------------------------------------
 
 
 
-- Get
 
CREATE FUNCTION getCurrentGroupId()
 
    RETURNS int(11)
 
    NO SQL
 
    BEGIN
 
      RETURN @currentGroupId;
 
    END //
 
 
 
-- Set
 
CREATE FUNCTION setCurrentGroupId(groupId_in int(11))
 
    RETURNS int(11)
 
    NO SQL
 
    BEGIN
 
      SET @currentGroupId = groupId_in;
 
      RETURN @currentGroupId;
 
    END //
 
 
 
-- --------------------------------------------------------
 
 
 
CREATE VIEW myPrivateNotes AS
 
SELECT
 
  privateNotes.id AS id,
 
  privateNotes.userId AS userId,
 
  privateNotes.text AS text
 
FROM
 
  privateNotes
 
WHERE
 
  privateNotes.groupId = getCurrentGroupId(); /* 101, 202, etc. */
 
 
 
-- --------------------------------------------------------
 
-- Trigger
 
 
 
CREATE TRIGGER before_insert_privateNotes
 
	BEFORE INSERT ON privateNotes
 
	FOR EACH ROW BEGIN
 
		IF NEW.groupId IS NULL THEN
 
			SET NEW.groupId = getCurrentGroupId();
 
		END IF;
 
	END //
 
 
 
-- --------------------------------------------------------
 
-- ------- --
 
--  TESTS
 
-- ------- --
 
 
 
-- View Insert Test
 
SELECT setCurrentGroupId(999);
 
//
 
-- (Uncomment) Error: Field 'groupId' doesn't have a default value
 
-- INSERT INTO privateNotes (id, userId, text) VALUES (50, 51, 'a cool message');
 
//
 
 
 
-- View Select Tests 
SELECT setCurrentGroupId(NULL);
 
//
 
SELECT * from myPrivateNotes; /* Returns Zero Results [Correct] */
 
//
 
 
 
SELECT setCurrentGroupId(101);
 
//
 
SELECT * from myPrivateNotes; /* Returns Valid Results [Correct] */
 
//
 
 
 
DELIMITER ;

Thanks,
-Yurelle

 Comments   
Comment by Elena Stepanova [ 2017-01-03 ]

Thanks for the report and test case.
It looks like MDEV-10002 has never been fixed actually, it was closed wrongly. MDEV-8605 fixes different scenarios, but not this one.

MariaDB [test]> set sql_mode='strict_all_tables';
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> create table t1 (a int not null, b int);
 
Query OK, 0 rows affected (0.40 sec)
 
 
 
MariaDB [test]> create trigger trgi before insert on t1 for each row set new.a=ifnull(new.a,new.b);
 
Query OK, 0 rows affected (0.09 sec)
 
 
 
MariaDB [test]> insert t1 (b) values (2);
 
ERROR 1364 (HY000): Field 'a' doesn't have a default value

Comment by Sergei Golubchik [ 2017-01-16 ]

This has nothing to do with MDEV-8605, there is no Field cannot be NULL error here. It's a different bug.

Comment by andreas eschbacher [ 2023-03-15 ]

sorry it is not fixed,
we have the same issue in MariaDB 10.5.9

Comment by Sergei Golubchik [ 2023-05-01 ]

do you have a test case?

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