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