maria-developers team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

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