MySQL Bugs: #73458: No error or warning on truncation of TIMESTAMP/DATETIME

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)

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.

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

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.

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

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

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

Reassigning to myself. Status unchanged.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.