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

[17 Apr 2019 16:53] Adarshdeep Cheema
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
[17 Apr 2019 19:24] MySQL Verification Team
Thank you for the bug report. Please provide the complete sq
[17 Apr 2019 19:25] MySQL Verification Team
Sorry for truncated prior comment. Please provide the complete sql script (create table, insert data, query). Thanks in advance.
[17 Apr 2019 19:38] Adarshdeep Cheema
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
[19 Apr 2019 5:54] MySQL Verification Team
Thank you for providing requested details.
Issue seen since 8.0.14, earlier versions don't show overflow error.

thanks,
Umesh
[22 Apr 2019 12:23] Adarshdeep Cheema
Can you please let us know in which MySQL version we can expect its fix
[14 May 2019 11:57] Jon Stephens
Adarshdeep, we don't know that ourselves until the issue is actually fixed.
[14 May 2019 11:58] Jon Stephens
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.