Bug #73458 No error or warning on truncation of TIMESTAMP/DATETIME
Submitted: 3 Aug 2014 10:23 Modified: 9 Sep 2014 15:50
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.6.19, 5.6.21, 5.0.97 OS:Any
Assigned to: Jon Stephens CPU Architecture:Any
Tags: regression
Triage: Needs Triage: D2 (Serious)

[3 Aug 2014 10:23] Peter Laursen
Description:
Testcase partially borrowed from http://bugs.mysql.com/bug.php?id=73456

No error or warning occurs if fractional seconds are truncated on INSERT.

How to repeat:
SELECT VERSION(); -- 5.6.19-log 
SHOW VARIABLES LIKE 'sql_mode';  -- returns "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" (Windows installer default)

DROP TABLE IF EXISTS ORDERS;
CREATE TABLE ORDERS (
  ORDERNUMBER INT(11) NOT NULL DEFAULT '0',
  ORDERDATE TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  REQUIREDDATE TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  SHIPPEDDATE TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  STATUS VARCHAR(15) COLLATE latin1_general_cs NOT NULL DEFAULT '',
  COMMENTS MEDIUMTEXT COLLATE latin1_general_cs,
  CUSTOMERNUMBER INT(11) NOT NULL DEFAULT '0',
  PRIMARY KEY  (ORDERNUMBER)
) ENGINE=INNODB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs;

-- nannoseconds
INSERT INTO ORDERS VALUES(1,'2004-09-05 00:00:00.000000001','2004-09-11 00:00:00.000000001','2004-09-05 00:00:00.0000000001','Shipped',NULL,166); -- success
SHOW WARNINGS; -- empty set

-- microseconds
INSERT INTO ORDERS VALUES(2,'2004-09-05 00:00:00.000001','2004-09-11 00:00:00.000001','2004-09-05 00:00:00.000001','Shipped',NULL,166); -- success
SHOW WARNINGS; -- empty set

-- milliseconds
INSERT INTO ORDERS VALUES(3,'2004-09-05 00:00:00.001','2004-09-11 00:00:00.001','2004-09-05 00:00:00.001','Shipped',NULL,166); -- success
SHOW WARNINGS; -- empty set

-- garbage
INSERT INTO ORDERS VALUES(4,'blahblihbluh','blahblihbluh','blahblihbluh','Shipped',NULL,166); -- success
-- Error Code: 1292
-- Incorrect datetime value: 'blahblihbluh' for column 'ORDERDATE' at row 1

SELECT * FROM orders;
/*
ORDERNUMBER            ORDERDATE         REQUIREDDATE          SHIPPEDDATE  STATUS   COMMENTS  CUSTOMERNUMBER  
-----------  -------------------  -------------------  -------------------  -------  --------  ----------------
          1  2004-09-05 00:00:00  2004-09-11 00:00:00  2004-09-05 00:00:00  Shipped  (NULL)                 166
          2  2004-09-05 00:00:00  2004-09-11 00:00:00  2004-09-05 00:00:00  Shipped  (NULL)                 166
          3  2004-09-05 00:00:00  2004-09-11 00:00:00  2004-09-05 00:00:00  Shipped  (NULL)                 166
*/

Suggested fix:
Note that fractions of second was truncated with no error and no warnng.

The data type is not declared to accep fractions of a second, so truncation is expected. But error or warning should appear depending on sql_mode.
[5 Aug 2014 20:05] Sveta Smirnova
Thank you for the report.

Verified as described.

I tested with couple of versions, results are surprising:

5.0.97 - bug exists
5.1.74 - first INSERT query rejected
5.5.39 - first INSERT query rejected
5.6.21 - bug exists
[6 Aug 2014 6:58] Roy Lyseng
This is in accordance with the SQL standard.

The standard says that when storing a datetime value into a datetime field, if the value can be represented in the type of the field with rounding or truncation, then the field is set to that value.

It is implementation-defined whether rounding or truncation is used, MySQL has chosen to use rounding.
[7 Aug 2014 10:07] Roy Lyseng
This is a documentation issue.  The manual lacks description on what happens when a datetime value is copied into a datetime field with a smaller FSP. This should probably be a paragraph in section 11.3.6
[7 Aug 2014 10:29] Peter Laursen
@Roy .. You have to realize that MySQL 5.5 and 5.6 are inconsistent. If 5.6 is OK, a fix in 5.5 is required - and vice versa.

Besides, I definitely thnk that at least a warning should occur.
[7 Aug 2014 11:04] Roy Lyseng
@Peter:
In 5.5, fractional seconds was not even supported, so erring out was OK.

In 5.6, MySQL implemented FSP with semantics given by SQL standard.

The SQL standard has the same requirement for DECIMAL numbers: If truncation/rounding is necessary because the target has less precision than the source, then no warning is given. I think this is reasonable, as no significant information is lost. OTOH, if an overflow occurs, an exception should be given, because significant information is lost.
[5 Sep 2014 13:26] Jon Stephens
Reassigning to myself. Status unchanged.
[9 Sep 2014 15:50] Jon Stephens
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.