Centos 7

I don't think it's a bug. Looks like the expected behavior. The metadata of the result (and number of decimals is part of metadata) is normally derived from the metadata of the arguments. Like, if you add DECIMAL(10,2) and DECIMAL(9,1), you'll get DECIMAL(11,2), while if you multiply DECIMAL(10,2) and DECIMAL(9,1), you'll get DECIMAL(19,3). Note that it does not depend on actual data, only on the metadata.

Similarly, UNIX_TIMESTAMP() result has as many decimals as its argument does.

But in the first case your argument is a string. It doesn't have a fixed number of decimals, so UNIX_TIMESTAMP() must assume the worst and return the highest possible number of decimals to avoid information loss. Consider this case:

CREATE TABLE t1 (a VARCHAR(100));

INSERT t1 VALUES ("2010-10-10 11:12:13"), ("2011-11-11 10:13:14.543319");

SELECT UNIX_TIMESTAMP(a) FROM t1;

In the second case you explicitly cast the argument to DATETIME(0), this clearly has no microseconds, so UNIX_TIMESTAMP() result doesn't either.

In the last two cases you use a literal constant. It cannot possibly change during the query execution, so MariaDB can analyze it and derive proper metadata from the constant value. It's an exception from the rule above, metadata depend on data, but it's only possible for constants.

Thanks for the explanation Sergei. Still seems "wrong" from an end user naturally expected behaviour standpoint but certainly it's minor enough to not be worth the effort to improve.