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
_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
