← Back to team overview

maria-developers team mailing list archive

Re: Thoughts on a variable to control high resolution temporal type format and rounding behavior?


Hi, Jeremy!

On Nov 11, Jeremy Cole wrote:

> I would propose adding a new sysvar to control the temporal time format
> e.g. temporal_microsecond_format={mysql|mariadb} which controls whether
> newly created columns are created as Field_*_hires or Field_*f. This would
> especially allow:
>    1. Tests to be run in both Oracle MySQL and MariaDB modes.
>    2. A single test to create a column in one format and request conversion
>    to the other.
>    3. A user to convert the format of their existing tables for
>    compatibility.


> Additionally I would suggest a new sysvar to control the rounding
> behavior of the same temporal types, e.g.
> temporal_microsecond_rounding={round|truncate} which controls whether
> to round (as Oracle MySQL 5.6 does) or to truncate (as MariaDB does)
> when faced with fitting a larger number of decimals in a value into a
> smaller number decimals in a field. (This of course assumes that the
> current MariaDB behavior and divergence from Oracle MySQL behavior is
> intentional, and not a bug. Even if it is considered a bug it could
> probably not be changed outright at this point and the sysvar would
> still be needed.)

Perhaps, yes. When this feature was discussed in MySQL, while I was
still there, we all were in agreement about truncating, not rounding.
With the reasoning - to avoid drastic date changes, like, when a year
changes when you store 1999-12-31 23:59:59.9999 in DATETIME(1) column.

I don't know why Oracle has changed that and their arguments, we in
MariaDB still thought that these drastic date changes would be an
undesirable gotcha, and we've decided to truncate. So, not a bug.

> Any thoughts on either of these? We would of course be willing to do
> the work and provide the patches for them. I just wanted to get your
> general feelings on the ideas.

Bar made a point about sysvars being difficult for replication.

I'd say, it depends on your use case. If it's important to run CREATE
TABLE on the master and have a temporal column to be created on the
slave using exactly the same binary on-disk format (especially, if one
expects to change this sysvar often) - then sysvar changes must be
replicated, and it would need special support from the replication code.

Otherwise - if the above is not the goal - then sysvar is a good
and clean approach, agree.


Follow ups