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

Virtual Columns are always NULL is Triggers

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0.0, 5.5.28, 5.3.8, 5.2.12
    • 10.0.1, 5.5.28a, 5.3.10, 5.2.13
    • None
    • None

    Description

      Virtual columns are always NULL in Triggers. I beilive this is a bug, because documentation says:
      "Triggers, stored procedures, informational commands (...), and partitioning based on virtual columns are all fully supported."

      Example:

      DROP VIEW   IF EXISTS  `t1`;
      		 
      DROP TABLE  IF EXISTS  `t1`;
      		 
      CREATE TABLE `t1` (
      		 
      	`a`      INTEGER UNSIGNED  NULL  DEFAULT NULL,
      		 
      	`b`      INTEGER UNSIGNED  GENERATED ALWAYS AS (`a`) VIRTUAL
      		 
      )
      		 
      	ENGINE      = MyISAM;
      		 
      DROP VIEW   IF EXISTS  `t2`;
      		 
      DROP TABLE  IF EXISTS  `t2`;
      		 
      CREATE TABLE `t2` (
      		 
      	`c1`      INTEGER UNSIGNED  NOT NULL
      		 
      )
      		 
      	ENGINE      = MyISAM;
      		 
       
      		 
       
      		 
      DELIMITER ||
      		 
       
      		 
      CREATE TRIGGER `t1_ins_aft`
      		 
      	AFTER INSERT
      		 
      	ON `t1`
      		 
      	FOR EACH ROW
      		 
      BEGIN
      		 
      	INSERT INTO `t2` (`c1`) VALUES (NEW.`b`);
      		 
      END ||
      		 
       
      		 
      CREATE TRIGGER `t1_del_bef`
      		 
      	BEFORE DELETE
      		 
      	ON `t1`
      		 
      	FOR EACH ROW
      		 
      BEGIN
      		 
      	INSERT INTO `t2` (`c1`) VALUES (OLD.`b`);
      		 
      END ||
      		 
       
      		 
      DELIMITER ;

      – Then I try those:

      INSERT INTO `t1` (`a`) VALUES (1), (2), (3);
      		 
      DELETE FROM t1;

      It looks that b is treated as NULL.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          f_razzoli Federico Razzoli created issue -
          serg Sergei Golubchik made changes -
          Field Original Value New Value
          Description Virtual columns are always NULL in Triggers. I beilive this is a bug, because documentation says:
          "Triggers, stored procedures, informational commands (...), and partitioning based on virtual columns are all fully supported."

          Example:

          DROP VIEW IF EXISTS `t1`;
          DROP TABLE IF EXISTS `t1`;
          CREATE TABLE `t1` (
          `a` INTEGER UNSIGNED NULL DEFAULT NULL,
          `b` INTEGER UNSIGNED GENERATED ALWAYS AS (`a`) VIRTUAL
          )
          ENGINE = Aria
          ROW_FORMAT = PAGE;
          DROP VIEW IF EXISTS `t2`;
          DROP TABLE IF EXISTS `t2`;
          CREATE TABLE `t2` (
          `c1` INTEGER UNSIGNED NOT NULL
          )
          ENGINE = Aria
          ROW_FORMAT = PAGE;


          DELIMITER ||

          CREATE TRIGGER `t1_ins_aft`
          AFTER INSERT
          ON `t1`
          FOR EACH ROW
          BEGIN
          INSERT INTO `t2` (`c1`) VALUES (NEW.`b`);
          END;

          CREATE TRIGGER `t1_del_bef`
          BEFORE DELETE
          ON `t1`
          FOR EACH ROW
          BEGIN
          INSERT INTO `t2` (`c1`) VALUES (OLD.`b`);
          END;

          || DELIMITER ;

          -- Then I try those:

          INSERT INTO `t1` (`a`) VALUES (1), (2), (3);
          DELETE FROM t1;

          --
          It looks that b is threated as NULL.           		Virtual columns are always NULL in Triggers. I beilive this is a bug, because documentation says:
          "Triggers, stored procedures, informational commands (...), and partitioning based on virtual columns are all fully supported."

          Example:
          {noformat}
          DROP VIEW IF EXISTS `t1`;
          DROP TABLE IF EXISTS `t1`;
          CREATE TABLE `t1` (
          `a` INTEGER UNSIGNED NULL DEFAULT NULL,
          `b` INTEGER UNSIGNED GENERATED ALWAYS AS (`a`) VIRTUAL
          )
          ENGINE = Aria
          ROW_FORMAT = PAGE;
          DROP VIEW IF EXISTS `t2`;
          DROP TABLE IF EXISTS `t2`;
          CREATE TABLE `t2` (
          `c1` INTEGER UNSIGNED NOT NULL
          )
          ENGINE = Aria
          ROW_FORMAT = PAGE;


          DELIMITER ||

          CREATE TRIGGER `t1_ins_aft`
          AFTER INSERT
          ON `t1`
          FOR EACH ROW
          BEGIN
          INSERT INTO `t2` (`c1`) VALUES (NEW.`b`);
          END;

          CREATE TRIGGER `t1_del_bef`
          BEFORE DELETE
          ON `t1`
          FOR EACH ROW
          BEGIN
          INSERT INTO `t2` (`c1`) VALUES (OLD.`b`);
          END;

          || DELIMITER ;

          -- Then I try those:

          INSERT INTO `t1` (`a`) VALUES (1), (2), (3);
          DELETE FROM t1;
          {noformat}

          It looks that {{b}} is treated as {{NULL}}.
          serg Sergei Golubchik made changes -
          Assignee Elena Stepanova [ elenst ]
          serg Sergei Golubchik made changes -
          Affects Version/s 5.2.12 [ 10702 ]
          Affects Version/s 5.3.8 [ 10900 ]
          Affects Version/s 5.5.28 [ 11200 ]
          Affects Version/s 10.0.0 [ 10000 ]
          serg Sergei Golubchik made changes -
          Fix Version/s 10.0.1 [ 11400 ]
          Fix Version/s 5.5.29 [ 11701 ]
          Fix Version/s 5.3.10 [ 11500 ]
          Fix Version/s 5.2.13 [ 10800 ]
          elenst Elena Stepanova made changes -
          Assignee Elena Stepanova [ elenst ] Igor Babaev [ igor ]
          Description Virtual columns are always NULL in Triggers. I beilive this is a bug, because documentation says:
          "Triggers, stored procedures, informational commands (...), and partitioning based on virtual columns are all fully supported."

          Example:
          {noformat}
          DROP VIEW IF EXISTS `t1`;
          DROP TABLE IF EXISTS `t1`;
          CREATE TABLE `t1` (
          `a` INTEGER UNSIGNED NULL DEFAULT NULL,
          `b` INTEGER UNSIGNED GENERATED ALWAYS AS (`a`) VIRTUAL
          )
          ENGINE = Aria
          ROW_FORMAT = PAGE;
          DROP VIEW IF EXISTS `t2`;
          DROP TABLE IF EXISTS `t2`;
          CREATE TABLE `t2` (
          `c1` INTEGER UNSIGNED NOT NULL
          )
          ENGINE = Aria
          ROW_FORMAT = PAGE;


          DELIMITER ||

          CREATE TRIGGER `t1_ins_aft`
          AFTER INSERT
          ON `t1`
          FOR EACH ROW
          BEGIN
          INSERT INTO `t2` (`c1`) VALUES (NEW.`b`);
          END;

          CREATE TRIGGER `t1_del_bef`
          BEFORE DELETE
          ON `t1`
          FOR EACH ROW
          BEGIN
          INSERT INTO `t2` (`c1`) VALUES (OLD.`b`);
          END;

          || DELIMITER ;

          -- Then I try those:

          INSERT INTO `t1` (`a`) VALUES (1), (2), (3);
          DELETE FROM t1;
          {noformat}

          It looks that {{b}} is treated as {{NULL}}.           		Virtual columns are always NULL in Triggers. I beilive this is a bug, because documentation says:
          "Triggers, stored procedures, informational commands (...), and partitioning based on virtual columns are all fully supported."

          Example:
          {noformat}
          DROP VIEW IF EXISTS `t1`;
          DROP TABLE IF EXISTS `t1`;
          CREATE TABLE `t1` (
          `a` INTEGER UNSIGNED NULL DEFAULT NULL,
          `b` INTEGER UNSIGNED GENERATED ALWAYS AS (`a`) VIRTUAL
          )
          ENGINE = MyISAM;
          DROP VIEW IF EXISTS `t2`;
          DROP TABLE IF EXISTS `t2`;
          CREATE TABLE `t2` (
          `c1` INTEGER UNSIGNED NOT NULL
          )
          ENGINE = MyISAM;


          DELIMITER ||

          CREATE TRIGGER `t1_ins_aft`
          AFTER INSERT
          ON `t1`
          FOR EACH ROW
          BEGIN
          INSERT INTO `t2` (`c1`) VALUES (NEW.`b`);
          END ||

          CREATE TRIGGER `t1_del_bef`
          BEFORE DELETE
          ON `t1`
          FOR EACH ROW
          BEGIN
          INSERT INTO `t2` (`c1`) VALUES (OLD.`b`);
          END ||

          DELIMITER ;
          {noformat}

          -- Then I try those:

          {noformat}
          INSERT INTO `t1` (`a`) VALUES (1), (2), (3);
          DELETE FROM t1;
          {noformat}

          It looks that {{b}} is treated as {{NULL}}.
          sanja Oleksandr Byelkin made changes -
          Assignee Igor Babaev [ igor ] Oleksandr Byelkin [ sanja ]
          sanja Oleksandr Byelkin made changes -
          Status Open [ 1 ] In Progress [ 3 ]
          sanja Oleksandr Byelkin made changes -
          Status In Progress [ 3 ] Open [ 1 ]
          sanja Oleksandr Byelkin made changes -
          Assignee Oleksandr Byelkin [ sanja ] Igor Babaev [ igor ]
          igor Igor Babaev (Inactive) made changes -
          Resolution Fixed [ 1 ]
          Status Open [ 1 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow defaullt [ 25102 ] MariaDB v2 [ 45828 ]
          ratzpo Rasmus Johansson (Inactive) made changes -
          Workflow MariaDB v2 [ 45828 ] MariaDB v3 [ 66526 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 66526 ] MariaDB v4 [ 146192 ]

          People

            igor Igor Babaev (Inactive)
            f_razzoli Federico Razzoli
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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