maria-developers team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

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