Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1.10, 5.5, 10.0, 10.1, 10.1.19, 10.2
-
XAMPP v7.0.13 (MariaDB v10.1.19)
Windows 7 64-bit
Intel Core i7 2600k
16GB of RAM
240GB SSD (OCZ Vertex 460)
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:
- Create a base table in MariaDB, which has a NOT NULL column
- Create a view which points to that base table, excluding the NOT NULL column
- Create a BEFORE INSERT trigger on the base table, to populate the NOT NULL column
- 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
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
-
- Open
-