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

Old Bug possibly not fixed; BEFORE INSERT Trigger on NOT NULL

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.1.10, 10.1.19, 5.5, 10.0, 10.1, 10.2
    • Fix Version/s: 10.1.21, 10.2.4
    • Component/s: Triggers
    • Labels:
    • 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)

      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

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                Created:
                Updated:
                Resolved: