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 secur...@mariadb.org
_______________________________________________
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
