[MDEV-5794] Triggers can't see DEFAULT values - JIRA

Details

Type: Bug
Status: Closed
Priority: Minor
Resolution: Won't Fix
Affects Version/s: 5.1.67, 5.2.14, 5.3.12, 5.5.36, 10.0.8
Fix Version/s: None
Component/s: None
Labels:
- upstream

Description

I spoke about this in the list moths ago, but I forgot to file a bug.

Try the following code:

DROP TABLE IF EXISTS t;
CREATE TABLE t (a INT DEFAULT 999) ENGINE = InnoDB;
DELIMITER ||
CREATE TRIGGER t_bi
	BEFORE INSERT
	ON t
	FOR EACH ROW
BEGIN
	SET NEW.a = DEFAULT;
END;
||
DELIMITER ;
INSERT INTO t VALUES (0);
SELECT * FROM t;

I get no errors, but t.a is NULL (which would be ok if a.t had no default value).

Since I'm not sure about the syntax, I tried DEFAULT(a) and DEFAULT(t.a), but in both cases I got an error. I have no idea if the errors are expected or not, but as far as I understand the syntax in the example should work.

Gliffy Diagrams

Attachments

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Elena Stepanova added a comment - 2014-03-05 10:28

Hi Federico,

I think it is more about BEFORE than about TRIGGER:

DROP TABLE IF EXISTS t;
CREATE TABLE t (a INT DEFAULT 999) ENGINE = InnoDB;
DELIMITER ||
CREATE TRIGGER t_bi
	BEFORE INSERT
	ON t
	FOR EACH ROW
BEGIN
	SELECT DEFAULT(a) INTO @default_a_bi FROM t LIMIT 1;
END;
CREATE TRIGGER t_ai
	AFTER INSERT
	ON t
	FOR EACH ROW
BEGIN
	SELECT DEFAULT(a) INTO @default_a_ai FROM t LIMIT 1;
END;
||
DELIMITER ;
INSERT INTO t VALUES (0);
SELECT * FROM t;
SELECT @default_a_bi, @default_a_ai;

MariaDB [test]> SELECT @default_a_bi, @default_a_ai;
+---------------+---------------+
| @default_a_bi | @default_a_ai |
+---------------+---------------+
| NULL          |           999 |
+---------------+---------------+
1 row in set (0.00 sec)

So, it might have something to do with not having the information in a before trigger, although I haven't found anything about it in the documentation.
Since it's an upstream issue, as you know, normally we would refile it for MySQL and see what they say. However, this syntax has been removed in MySQL 5.6 and further, you cannot use there DEFAULT anymore.
It was apparently done by this change:

revno: 3858
revision-id: alexander.nozdrin@oracle.com-20120516123801-7wmajugqw48tq5jl
committer: Alexander Nozdrin <alexander.nozdrin@oracle.com>
timestamp: Wed 2012-05-16 16:38:01 +0400
message:
  WL#6230: Remove 'SET = DEFAULT'.
  
  This patch forbids assigning the DEFAULT keyword
  to stored program local variables or stored procedure
  parameters.

The worklog itself is not public, so it's hard to understand why it was done, maybe the problem you described was one of the reasons.

So, undoubtedly, MySQL won't do anything about it in 5.5.
I suppose eventually this change will make it to 10.x as well, so I'm not sure fixing it there, even if it's possible, is worth the trouble.

Do you agree, or do you want a further analysis?

Show

Elena Stepanova added a comment - 2014-03-05 10:28 Hi Federico, I think it is more about BEFORE than about TRIGGER: DROP TABLE IF EXISTS t; CREATE TABLE t (a INT DEFAULT 999) ENGINE = InnoDB; DELIMITER || CREATE TRIGGER t_bi BEFORE INSERT ON t FOR EACH ROW BEGIN SELECT DEFAULT(a) INTO @default_a_bi FROM t LIMIT 1; END; CREATE TRIGGER t_ai AFTER INSERT ON t FOR EACH ROW BEGIN SELECT DEFAULT(a) INTO @default_a_ai FROM t LIMIT 1; END; || DELIMITER ; INSERT INTO t VALUES (0); SELECT * FROM t; SELECT @default_a_bi, @default_a_ai; MariaDB [test]> SELECT @default_a_bi, @default_a_ai; +---------------+---------------+ | @default_a_bi | @default_a_ai | +---------------+---------------+ | NULL | 999 | +---------------+---------------+ 1 row in set (0.00 sec) So, it might have something to do with not having the information in a before trigger, although I haven't found anything about it in the documentation. Since it's an upstream issue, as you know, normally we would refile it for MySQL and see what they say. However, this syntax has been removed in MySQL 5.6 and further, you cannot use there DEFAULT anymore. It was apparently done by this change: revno: 3858 revision-id: alexander.nozdrin@oracle.com-20120516123801-7wmajugqw48tq5jl committer: Alexander Nozdrin <alexander.nozdrin@oracle.com> timestamp: Wed 2012-05-16 16:38:01 +0400 message: WL#6230: Remove 'SET = DEFAULT'. This patch forbids assigning the DEFAULT keyword to stored program local variables or stored procedure parameters. The worklog itself is not public, so it's hard to understand why it was done, maybe the problem you described was one of the reasons. So, undoubtedly, MySQL won't do anything about it in 5.5. I suppose eventually this change will make it to 10.x as well, so I'm not sure fixing it there, even if it's possible, is worth the trouble. Do you agree, or do you want a further analysis?

Hide

Permalink

Federico Razzoli added a comment - 2014-03-05 16:32

Hi elenst, thank you for the useful information. I feel bad if a new limitation will be added to the stored programs, which already have many limitations. But in this particular case, DEFAULT already does not work at least in BEFORE INSERT, and after all it's just a cleaner way to do something I could do without the DEFAULT keyword. So, don't mind too much about this bug.

Show

Federico Razzoli added a comment - 2014-03-05 16:32 Hi elenst, thank you for the useful information. I feel bad if a new limitation will be added to the stored programs, which already have many limitations. But in this particular case, DEFAULT already does not work at least in BEFORE INSERT, and after all it's just a cleaner way to do something I could do without the DEFAULT keyword. So, don't mind too much about this bug.

Hide

Permalink

Elena Stepanova added a comment - 2014-03-05 17:07

OK, thanks.
Then I will close it as "Won't fix" for now, if you later have more thoughts and want to re-open it, please comment.

Show

Elena Stepanova added a comment - 2014-03-05 17:07 OK, thanks. Then I will close it as "Won't fix" for now, if you later have more thoughts and want to re-open it, please comment.

Triggers can't see DEFAULT values

Details

Description

Gliffy Diagrams

Attachments

Activity

People

Dates

Agile