maria-developers team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

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@xxxxxxxxxxx