MySQL Bugs: #70740: Don't lose microseconds without warning

Bug #70740	Don't lose microseconds without warning
Submitted:	27 Oct 2013 10:00	Modified:	28 Oct 2013 11:54
Reporter:	Daniël van Eeden (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.6.14	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The microsecond part of a value inserted in a TIMESTAMP(0) column gets lost without warning.

How to repeat:
mysql> CREATE TABLE t1 (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES(CURRENT_TIMESTAMP(6));
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+---------------------+
| ts                  |
+---------------------+
| 2013-10-27 10:56:12 |
+---------------------+
1 row in set (0.00 sec)

Suggested fix:
Add a sql_mode to generate warnings on lost microseconds (e.g. WARN_ON_TIMESTAMP_CASTING) and one to generate errors (e.g. ERROR_ON_TIMESTAMP_CASTING)

A warning is already generated if the time part is lost:
mysql> CREATE TABLE t1 (d DATE);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES(CURRENT_TIMESTAMP);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+-------+------+---------------------------------------------------------------------+
| Level | Code | Message                                                             |
+-------+------+---------------------------------------------------------------------+
| Note  | 1292 | Incorrect date value: '2013-10-27 10:59:14' for column 'd' at row 1 |
+-------+------+---------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t1;
+------------+
| d          |
+------------+
| 2013-10-27 |
+------------+
1 row in set (0.00 sec)

Thank you for the bug report.

We consider this to be a feature request.

The SQL standard has this to say about assignment to timestamp and time values when fractional seconds are truncated, in chapter 9.2 Store assignment:

Let T be the TARGET and let V be the VALUE 
...
If the declared type DT of T is datetime, then
...
A) If V is a member of the declared type of T, then T is set to V.
B) If a member of the declared type of T can be obtained from V by rounding or truncation, then T is set to that value. It is implementation-defined whether the approximation is obtained by rounding or truncation.
C) Otherwise, an exception condition is raised: data exception — datetime field overflow.

Thus, there is no mention of a warning in the case of rounding or truncation.

However, there is precedent in the MySQL behavior for e.g. assignment between DECIMAL values:

create table td(a decimal(20,10), b decimal(10,0));
Query OK, 0 rows affected (0.04 sec)

insert into td values(987654321.0123456789, 0);
Query OK, 1 row affected (0.00 sec)

update td set b=a;
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 1

show warnings;
+-------+------+----------------------------------------+
| Level | Code | Message                                |
+-------+------+----------------------------------------+
| Note  | 1265 | Data truncated for column 'b' at row 1 |
+-------+------+----------------------------------------+
1 row in set (0.00 sec)

select * from td;
+----------------------+-----------+
| a                    | b         |
+----------------------+-----------+
| 987654321.0123456789 | 987654321 |
+----------------------+-----------+
1 row in set (1.67 sec)