[Maria-developers] MDEV-21287 dev community opinion wanted
Hi all, I created the Jira ticket MDEV-21287 10 months ago and didn't get much feedback. I tried to take a shot at it myself, but found out that the resolution MDEV-20403 introduced this behavior by purposefully evaluating 'update functions' before the BEFORE UPDATE trigger. I couldn't really understand why this was necessary, and I'd like to get feedback from the dev community to try and understand what is really supposed to happen on update. I kinda hate getting my trigger called with a new value that's not going to be persisted anyway, what do you think about that? Regards, Pierre-Jean
Hi, Pierre-Jean! On Oct 09, Pierre-Jean Clement wrote:
Hi all,
I created the Jira ticket MDEV-21287 10 months ago and didn't get much feedback.
I tried to take a shot at it myself, but found out that the resolution MDEV-20403 introduced this behavior by purposefully evaluating 'update functions' before the BEFORE UPDATE trigger.
"update functions" include generated columns and default functions, it's not only about ON UPDATE. In that particular bug a generated column wasn't properly evaluated using the new value of the ON UPDATE timestamp column. As far as I remember.
I couldn't really understand why this was necessary, and I'd like to get feedback from the dev community to try and understand what is really supposed to happen on update.
I kinda hate getting my trigger called with a new value that's not going to be persisted anyway, what do you think about that?
I think you're right. I also think that if this is changed, someone else will write that "I kinda hate getting my trigger with a NEW value being actually the OLD value, not what will be written into the table" And I don't really know how to resolve it without a time machine, because we can only know whether the row is changed after the BEFORE trigger. The thing is that as far as I understand SQL Standard doesn't have a concept "row is only updated if some new values differ from old values". If there's an UPDATE - the row is updated, triggers must be invoked, a history row must be generated (for system versoned tables) and so on. But the historical ON UPDATE feature - it looks whether values were actually changed. There's probably no way to combine this seamlessly with SQL standard features like triggers. But I'd love to be proven wrong here. Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
Hi Sergei, Never expected such a fast and comprehensive answer! Thanks a lot. Added thoughts below. Le 09/10/2020 à 16:57, Sergei Golubchik a écrit :
Hi, Pierre-Jean!
On Oct 09, Pierre-Jean Clement wrote:
Hi all,
I created the Jira ticket MDEV-21287 10 months ago and didn't get much feedback.
I tried to take a shot at it myself, but found out that the resolution MDEV-20403 introduced this behavior by purposefully evaluating 'update functions' before the BEFORE UPDATE trigger. "update functions" include generated columns and default functions, it's not only about ON UPDATE.
In that particular bug a generated column wasn't properly evaluated using the new value of the ON UPDATE timestamp column. As far as I remember.
TABLE::evaluate_update_default_function() seems to only handle the ON UPDATE time(), am I missing something here?
I couldn't really understand why this was necessary, and I'd like to get feedback from the dev community to try and understand what is really supposed to happen on update.
I kinda hate getting my trigger called with a new value that's not going to be persisted anyway, what do you think about that? I think you're right. Glad that my point of view has other subscribers! I also think that if this is changed, someone else will write that "I kinda hate getting my trigger with a NEW value being actually the OLD value, not what will be written into the table" I'm not sure anyone ever complained about that before, and it has been working that way for years. And I don't really know how to resolve it without a time machine, because we can only know whether the row is changed after the BEFORE trigger.
I tend to think of it that way: - For an UPDATE that have no changes in values one would see: * in BEFORE UPDATE trigger: OLD = NEW and NEW = persisted value * in AFTER UPDATE trigger: OLD = NEW and NEW = persisted value (I think nobody would complain about this) - For an UPDATE that have changes in values one would see: * in BEFORE UPDATE trigger: OLD = NEW and NEW <> persisted value * in AFTER UPDATE trigger: OLD <> NEW but NEW = persisted value Here indeed, as you said, someone might complain that it is undesirable that BEFORE UPDATE trigger see a NEW value that's not the persisted value (even though that's how it has ever been for as long as I've been using MySQL and MariaDB). To that I'd answer that what is not reasonable is to expect to see the future - what will happen ON UPDATE.On the contrary seeing ON UPDATE results in the AFTER UPDATE trigger makes a lot of sense.
The thing is that as far as I understand SQL Standard doesn't have a concept "row is only updated if some new values differ from old values". If there's an UPDATE - the row is updated, triggers must be invoked, a history row must be generated (for system versoned tables) and so on. But the historical ON UPDATE feature - it looks whether values were actually changed. There's probably no way to combine this seamlessly with SQL standard features like triggers.
But I'd love to be proven wrong here.
Do standards specify NEW and OLD value have to be the same in both BEFORE and AFTER triggers, as suggested in my example above? Would you have any good documentation available on SQL standards? Regards, Pierre-Jean
Hi, Pierre-Jean! On Oct 09, Pierre-Jean Clement wrote:
Never expected such a fast and comprehensive answer! Thanks a lot.
and I wasn't able to keep up, sorry :(
Le 09/10/2020 à 16:57, Sergei Golubchik a écrit :
On Oct 09, Pierre-Jean Clement wrote:
I created the Jira ticket MDEV-21287 10 months ago and didn't get much feedback.
I tried to take a shot at it myself, but found out that the resolution MDEV-20403 introduced this behavior by purposefully evaluating 'update functions' before the BEFORE UPDATE trigger.
"update functions" include generated columns and default functions, it's not only about ON UPDATE.
In that particular bug a generated column wasn't properly evaluated using the new value of the ON UPDATE timestamp column. As far as I remember.
TABLE::evaluate_update_default_function() seems to only handle the ON UPDATE time(), am I missing something here?
Consider a table with a TIMESTAMP ON UPDATE NOW() column, and another generated virtual indexed column that used an expression with the timestamp column value, something like CREATE TABLE t1 ( a TIMESTAMP ON UPDATE NOW(), b TIMESTAMP GENERATED ALWAYS AS (a + INTERVAL 1 DAY), INDEX (b) ) here `b` must be recalculated after `a` is updated. That is TABLE::evaluate_update_default_function should be called before TABLE::update_virtual_columns
I tend to think of it that way:
- For an UPDATE that have no changes in values one would see: * in BEFORE UPDATE trigger: OLD = NEW and NEW = persisted value * in AFTER UPDATE trigger: OLD = NEW and NEW = persisted value
(I think nobody would complain about this)
- For an UPDATE that have changes in values one would see: * in BEFORE UPDATE trigger: OLD = NEW and NEW <> persisted value * in AFTER UPDATE trigger: OLD <> NEW but NEW = persisted value
as you see from my example, this "NEW <> persisted value" would also apply to all virtual columns that directly or indirectly use timestamp column value.
Here indeed, as you said, someone might complain that it is undesirable that BEFORE UPDATE trigger see a NEW value that's not the persisted value (even though that's how it has ever been for as long as I've been using MySQL and MariaDB). To that I'd answer that what is not reasonable is to expect to see the future - what will happen ON UPDATE.On the contrary seeing ON UPDATE results in the AFTER UPDATE trigger makes a lot of sense.
The only reasonable solution that I can think of is to disable non-standard detection of whether anything has changed whenever triggers are present. That is, if there're triggers - ON UPDATE always happens. I suspect you (and many others) won't like it, though.
The thing is that as far as I understand SQL Standard doesn't have a concept "row is only updated if some new values differ from old values". If there's an UPDATE - the row is updated, triggers must be invoked, a history row must be generated (for system versoned tables) and so on. But the historical ON UPDATE feature - it looks whether values were actually changed. There's probably no way to combine this seamlessly with SQL standard features like triggers.
But I'd love to be proven wrong here.
Do standards specify NEW and OLD value have to be the same in both BEFORE and AFTER triggers, as suggested in my example above?
NEW values should be those that end up written into the table. Otherwise - if ON UPDATE happens after the trigger - we'd need special handling of various corner cases. For example, what if you do (assuming table definition as above) CREATE TRIGGER tr1 BEFORE UPDATE ON t1 FOR EACH ROW SET NEW.a='2020-10-10 10:10:10'; should `a` be overwritten by ON UPDATE NOW value or should it stay as set by the trigger? There is no logical answer, we can define it either way, by a special exception rule.
Would you have any good documentation available on SQL standards?
I don't, sorry. I've seen that it's possible to google up something. Wikipedia has some old links too. Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
participants (2)
-
Pierre-Jean Clement -
Sergei Golubchik