Bug #88599 Data truncation in WHERE condition of UPDATE for excluded value
Submitted: 22 Nov 2017 6:10 Modified: 22 Dec 2017 8:11
Reporter: Courtney Miles Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.7.17 OS:Any
Assigned to: CPU Architecture:Any

[22 Nov 2017 6:10] Courtney Miles
Description:
SET SQL_MODE='STRICT_TRANS_TABLES';

CREATE TABLE entity_tbl (
  entity_id INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  flag ENUM('active','inactive') NOT NULL
);

CREATE TABLE attribute_tbl (
  entity_id INT(10) UNSIGNED NOT NULL,
  attr_name VARCHAR(50) NOT NULL,
  attr_value VARCHAR(255) NULL
);

INSERT INTO entity_tbl (entity_id, flag) VALUES (1, 'inactive'), (2, 'inactive');
INSERT INTO attribute_tbl (entity_id, attr_name, attr_value) VALUES (1, 'foo', 'not_date');

How to repeat:
The following UPDATE will trigger an error.

UPDATE entity_tbl e
  JOIN attribute_tbl a1 ON (e.entity_id = a1.entity_id AND a1.attr_name = 'start_date')
  SET e.flag = 'active'
  WHERE e.flag = 'inactive'
    AND a1.attr_value <= CURDATE();

The error reads, "Data truncation: Truncated incorrect date value: 'not_date'" This is unexpected because the join condition excludes this value.

Performing the equivalent SELECT query does not trigger the same data truncation error.

SET SQL_MODE='STRICT_TRANS_TABLES';
SELECT *
  FROM entity_tbl e
    JOIN attribute_tbl a1 ON (e.entity_id = a1.entity_id AND a1.attr_name = 'start_date')
    JOIN attribute_tbl a2 ON (e.entity_id = a2.entity_id AND a2.attr_name = 'end_date')
  WHERE e.flag = 'inactive'
    AND a1.attr_value <= CURDATE();
[22 Nov 2017 8:11] MySQL Verification Team
Thank you for the bug report. Please try latest released version the result with most recent source server is: 

C:\dbs>c:\dbs\5.7\bin\mysql -uroot --port=3570 -p --local-infile  --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.21-log Source distribution 2017-NOV-10

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > create database a;
Query OK, 1 row affected (0.01 sec)

mysql 5.7 > use a
Database changed
mysql 5.7 > SET SQL_MODE='STRICT_TRANS_TABLES';
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql 5.7 >
mysql 5.7 > CREATE TABLE entity_tbl (
    ->   entity_id INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    ->   flag ENUM('active','inactive') NOT NULL
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql 5.7 >
mysql 5.7 > CREATE TABLE attribute_tbl (
    ->   entity_id INT(10) UNSIGNED NOT NULL,
    ->   attr_name VARCHAR(50) NOT NULL,
    ->   attr_value VARCHAR(255) NULL
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql 5.7 >
mysql 5.7 > INSERT INTO entity_tbl (entity_id, flag) VALUES (1, 'inactive'), (2, 'inactive');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 5.7 > INSERT INTO attribute_tbl (entity_id, attr_name, attr_value) VALUES (1, 'foo', 'not_date');
Query OK, 1 row affected (0.01 sec)

mysql 5.7 > UPDATE entity_tbl e
    ->   JOIN attribute_tbl a1 ON (e.entity_id = a1.entity_id AND a1.attr_name = 'start_date')
    ->   SET e.flag = 'active'
    ->   WHERE e.flag = 'inactive'
    ->     AND a1.attr_value <= CURDATE();
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0
[23 Dec 2017 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".