bar please be aware.

midenok, thanks for the link.
I agree that in case of user variables it's better to go with TIMESTAMP by default,
as user variables do not have a strict data type.
The question is though what to do in case of an indirect variable use, e.g.:
select * from t1 for system_time as of LEAST(@p1,@p2);

Note, I recently sent a patch fixing MDEV-16094 and MDEV-16100 to Sergei's review:
https://lists.launchpad.net/maria-developers/msg11272.html

Let's return to this MDEV after those two.

midenok, I suggest you wait for my patch for MDEV-16094 and MDEV-16100 to get pushed first.

Alexey, the patch for MDEV-16094 and MDEV-16100 is now in 10.3.
It added a new virtual method Item::type_handler_for_system_time().
The easiest way to make user variables always resolve as TIMESTAMP is to override type_handler_for_system_time() in Item_func_user_var.
Something like this should work:

  const Type_handler *type_handler_for_system_time() const

  {

    return &type_handler_timestamp2;

  }

bar I don't think changes from MDEV-16100 are needed. TIMESTAMP should be always default. That's too much logic about unit resolution for almost no win in usability.

Changes from MDEV-16100 that added new methods in Type_handler are needed anyway, to disallow various non-relevant data types in FOR SYSTEM_TIME, such as GEOMETRY, ROW, ENUM/SET. And we'll be adding new data types soon (e.g. INET4, INET6, UUID), which are also non-relevant in timestamp context.

I think it's fine to make undefined units to resolve to timestamp by default.
But in this case the following expressions in FOR SYSTEM_TIME should probably return an error when used without explicit unit:

• Expressions that have the BIT data type (i.e. table fields, SP variables, hybrid functions like COALESCE)
• BIT-alike constants B'01011101'
• HEX hybrid constants 0x616263
  because the user is obviously doing something wrong. These expressions can very rarely represent a valid TIMESTAMP.

So they should require either an explicit unit, or even an explicit CAST.

bar Okay, I see your point. It seems, that it is not possible to simplify anything if type checking is required in SYSTEM_TIME. Unit resolution comes as a "free bonus". Therefore, closing.

This is the requirement for MDEV-16226.

Discussed with Aleksey on slack to make the patch more Type_handler friendly.

The latest version in https://github.com/MariaDB/server/pull/860 is now Type_handler friendly. Ok to push from my side.