Hi, Yitzchak! On Jan 26, Yitzchak Scott-Thoennes wrote:

Using the ubuntu 14.04 mariadb (5.5.46), convert_tz unexpectedly returns a time with fractional seconds if its first argument is an expression using a column. This doesn't happen with mysqldb 5.5.46.

Example:

select convert_tz(concat('2006-05-25 17:45:00',''),'+00:00','+00:00') no_columns_used, convert_tz(concat('2006-05-25 17:45:00',blank),'+00:00','+00:00') column_used from (select '' blank) foo \G

*************************** 1. row *************************** no_columns_used: 2006-05-25 17:45:00 column_used: 2006-05-25 17:45:00.000000

In the second query MariaDB sees that you have a string non-constant expression used as a temporal value. MariaDB does not know whether this string will contain "2006-05-25 17:45:00" or "2006-05-25 17:45:00.123456" or something else. That is, it does not know whether this temporal-value-in-a-string will have microseconds or not. So, to avoid precision loss, it assumes the value might have microseconds. You'd have the same for, say CREATE TABLE t1 (blank VARCHAR(100)); -- a string can contain anything In the first query all values are constants and MariaDB can examine them and see no microseconds. Other ways to avoid microseconds: CREATE TABLE t1 (blank INT); -- integer columns have no fractonal part CREATE TABLE t1 (blank DECIMAL(20,0)); - no fractonal part CREATE TABLE t1 (blank DATETIME(0)); -- datetime with no microseconds or with a cast: select convert_tz(cast(concat(...) as datetime(0))) MySQL 5.5 does not exhibit this behavior, because MySQL 5.5 does not support microseconds in DATETIME - this was added only in MySQL 5.6. Regards, Sergei Chief Architect MariaDB and security@mariadb.org