MySQL Bugs: #95045: Data Truncation error occurred on a write of column 0Data was 0 bytes long and

Bug #95045	Data Truncation error occurred on a write of column 0Data was 0 bytes long and
Submitted:	17 Apr 2019 16:53	Modified:	14 May 2019 11:58
Reporter:	Adarshdeep Cheema	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	8.0.15, 8.0.14	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
Getting the error Data Truncation error occurred on a write  of column 0Data was 0 bytes long and 0 bytes were transferred.

When running the SQL :
SELECT
    ((year(`QC_DATE`) - year(`QC_TIMESTAMP`)))
    
FROM
    `DATETIME_TABLE` 
WHERE 
    `QC_DATE_KEY` = 2

However, when we use the following, It passes
SELECT
     
    ((year(`QC_TIMESTAMP`) - year(`QC_DATE`) ))
    
FROM
    `DATETIME_TABLE` 
WHERE 
    `QC_DATE_KEY` = 2

The value of year(`QC_TIMESTAMP`) is 2000 and year(`QC_DATE`) is 1999

How to repeat:
 The metadata details are :

SELECT
    
  `QC_DATE_KEY`,`QC_DATE`,`QC_TIMESTAMP`
    
FROM
    `DATETIME_TABLE` 

QC_DATE_KEY           QC_DATE     QC_TIMESTAMP         
-------------------------------------------------------
1                     1999-02-28  1999-11-28 14:45:00  
2                     1999-12-31  2000-01-01 00:00:00  
3                     2000-02-28  2000-02-28 23:59:00  
4                     2000-02-29  2000-02-29 12:00:00  
5                     2000-12-31  2000-01-01 00:00:00  
6                     1995-06-30  2001-06-30 10:12:50  
7                     2038-01-18  2038-01-18 21:14:08  
8                     2039-01-01  2039-01-01 12:00:00  
9                     2100-06-30  2100-06-30 06:30:30  
10                    3000-12-31  3000-12-31 23:59:59  
11                    1753-01-01  1753-01-01 01:01:01  
12                    1753-12-25  1753-12-25 21:45:09  
13                    1823-10-31  1823-10-31 00:00:00  
14                    <null>      <null>               
15                    <null>      <null>     

Table name :Datetime_table
Column Index: 1 columnName:QC_DATE_KEY  columnType : BIGINT Precision: 20
Column Index: 2 columnName:QC_DATE  columnType : DATE Precision: 10
Column Index: 3 columnName:QC_TIMESTAMP  columnType : DATETIME Precision: 19

Now run the query to see the error (Data Truncation error occurred on a write  of column 0Data was 0 bytes long and 0 bytes were transferred.)  :SELECT
    ((year(`QC_DATE`) - year(`QC_TIMESTAMP`)))
    
FROM
    `DATETIME_TABLE` 
WHERE 
    `QC_DATE_KEY` = 2

This only happens with MySQL  8.0.15 server. This test does not fail in MySQL 8.0.13 server

Thank you for the bug report. Please provide the complete sq

Sorry for truncated prior comment. Please provide the complete sql script (create table, insert data, query). Thanks in advance.

CREATE:

CREATE TABLE Datetime_table (QC_DATE_KEY BIGINT (20), QC_DATE DATE,QC_TIMESTAMP DATETIME);

INSERT:

INSERT INTO Datetime_table(QC_DATE_KEY,QC_DATE,QC_TIMESTAMP) VALUES(1,'1999-02-28', '1999-11-28 14:45:00');
INSERT INTO Datetime_table(QC_DATE_KEY,QC_DATE,QC_TIMESTAMP) VALUES(2,'1999-12-31', '2000-01-01 00:00:00');
INSERT INTO Datetime_table(QC_DATE_KEY,QC_DATE,QC_TIMESTAMP) VALUES(3,'2000-02-28', '2000-02-28 23:59:00');

QUERY:
SELECT
    ((year(`QC_DATE`) - year(`QC_TIMESTAMP`)))
    
FROM
    `DATETIME_TABLE` 
WHERE 
    `QC_DATE_KEY` = 2

Thank you for providing requested details.
Issue seen since 8.0.14, earlier versions don't show overflow error.

thanks,
Umesh

Can you please let us know in which MySQL version we can expect its fix

Adarshdeep, we don't know that ourselves until the issue is actually fixed.

Documented fix in the MySQL 8.0.17 changelog as follows:

    A fix for a previous issue caused YEAR values to be treated as
    unsigned, but this did not allow for possible negative values
    arising during calculations involving subtraction, which could
    lead to data truncation errors.

    Regression of BUG#92209.

Closed.