MySQL Bugs: #86803: Data truncated for column generates warning not error in TRADITIONAL mode

Bug #86803	Data truncated for column generates warning not error in TRADITIONAL mode
Submitted:	22 Jun 2017 22:09	Modified:	17 Aug 2018 14:13
Reporter:	Bear Limvere	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.7.10, 5.7.18, 5.6.36	OS:	CentOS (6.4)
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	sql-mode=TRADITIONAL

Description:
From all documentation when I have sql_mode set to TRADITIONAL it should generate an error not a warning when the data does not match the column definition. This does not seem to be the case. I have found the same behavior on MySQL Server 5.6.27.

Our application does validate data coming in from the UI, but we do occasional updates during builds and releases and this has caused some data corruption.

From the manual:
"Strict SQL mode is in effect if either STRICT_ALL_TABLES or STRICT_TRANS_TABLES is enabled, although the effects of these modes differ somewhat:

"For transactional tables, an error occurs for invalid or missing values in a data-change statement when either STRICT_ALL_TABLES or STRICT_TRANS_TABLES is enabled. The statement is aborted and rolled back."

How to repeat:
mysql > SET SESSION sql_mode = 'TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)

mysql > SET SESSION innodb_strict_mode=ON;
Query OK, 0 rows affected (0.00 sec)

mysql > CREATE TABLE foo (
    -> foo_id BIGINT(20) NOT NULL AUTO_INCREMENT,
    -> bar DECIMAL(14,2) UNSIGNED DEFAULT NULL,
    -> PRIMARY KEY (foo_id)
    -> ) ENGINE=INNODB DEFAULT CHARSET=utf8;
(3.30);Query OK, 0 rows affected (0.02 sec)

mysql > INSERT INTO foo (bar)
    -> VALUES
    -> (1.10),
    -> (2.20),
    -> (3.30);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql > UPDATE foo SET bar = 1.0025;
Query OK, 3 rows affected, 3 warnings (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 3

mysql > SHOW WARNINGS;
+-------+------+-------------------------------------------------+
| Level | Code | Message                                         |
+-------+------+-------------------------------------------------+
| Note  | 1265 | Data truncated for column 'bar' at row 1 |
| Note  | 1265 | Data truncated for column 'bar' at row 2 |
| Note  | 1265 | Data truncated for column 'bar' at row 3 |
+-------+------+-------------------------------------------------+
3 rows in set (0.00 sec)

mysql > SELECT * FROM foo;
+--------+------------+
| foo_id | bar |
+--------+------------+
|      1 |       1.00 |
|      2 |       1.00 |
|      3 |       1.00 |
+--------+------------+
3 rows in set (0.00 sec)

mysql > SHOW VARIABLES LIKE 'sql_mode';
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                                |
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql > SHOW VARIABLES LIKE 'innodb_strict_mode';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_strict_mode | ON    |
+--------------------+-------+
1 row in set (0.00 sec)

Suggested fix:
Generate an error rather than a warning on data truncation (Code=1265)

Hello Bear Limvere,

Thank you for the report.
Imho, discussed internally with developer and concluded that this is an expected behavior because generally notes are not upgraded to error in strict mode.

mysql > UPDATE foo SET bar = 1.0025;
Query OK, 3 rows affected, 3 warnings (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 3

mysql > SHOW WARNINGS;
+-------+------+-------------------------------------------------+
| Level | Code | Message                                         |
+-------+------+-------------------------------------------------+
| Note  | 1265 | Data truncated for column 'bar' at row 1 |
| Note  | 1265 | Data truncated for column 'bar' at row 2 |
| Note  | 1265 | Data truncated for column 'bar' at row 3 |
+-------+------+-------------------------------------------------+

These are flagged as "Note" and not warnings. 

root@localhost [test]> SET SESSION sql_mode = 'TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)

root@localhost [test]> SET SESSION innodb_strict_mode=ON;
Query OK, 0 rows affected (0.00 sec)

root@localhost [test]> SET SESSION SQL_NOTES=0;
Query OK, 0 rows affected (0.00 sec)

root@localhost [test]> show variables like 'sql_mode';
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                                |
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost [test]> show variables like 'innodb_strict_mode';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_strict_mode | ON    |
+--------------------+-------+
1 row in set (0.00 sec)

root@localhost [test]>
root@localhost [test]> DROP TABLE IF EXISTS foo;
Query OK, 0 rows affected (0.00 sec)

root@localhost [test]>  CREATE TABLE foo (
    ->      foo_id BIGINT(20) NOT NULL AUTO_INCREMENT,
    ->      bar DECIMAL(14,2) UNSIGNED DEFAULT NULL,
    ->      PRIMARY KEY (foo_id)
    ->      ) ENGINE=INNODB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

root@localhost [test]>  INSERT INTO foo (bar)
    ->      VALUES
    ->      (1.10),
    ->      (2.20),
    ->   (3.30);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

root@localhost [test]>  UPDATE foo SET bar = 1.0025;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

^^ If you see, "0" warnings. The sql_notes system variable controls whether note messages increment  warning_count and whether the  server stores them. By default, sql_notes is 1, but if set to  0, notes do not increment warning_count and the server  does not store them.

Thanks,
Umesh

Umesh -- my request is not to make the Notes disappear. The way it is working allows data corruption. It also is inconsistent; please consider the following:

user@localhost/bear> DROP TABLE IF EXISTS foo;

user@localhost/bear> CREATE TABLE foo (
    -> foo_id BIGINT(20) NOT NULL AUTO_INCREMENT,
    -> bar DECIMAL(14,2) UNSIGNED DEFAULT NULL,
    -> PRIMARY KEY (foo_id)
    -> ) ENGINE=INNODB DEFAULT CHARSET=utf8;

user@localhost/bear> INSERT INTO foo (bar)
    -> VALUES
    -> (1.10),
    -> (2.20),
    -> (3.30);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

user@localhost/bear> UPDATE foo SET bar = 123123123123123.23;
ERROR 1264 (22003): Out of range value for column 'bar' at row 1

user@localhost/bear> UPDATE foo SET bar = 1.0025;
Query OK, 3 rows affected, 3 warnings (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 3

Note (Code 1265): Data truncated for column 'bar' at row 1
Note (Code 1265): Data truncated for column 'bar' at row 2
Note (Code 1265): Data truncated for column 'bar' at row 3

Why does "UPDATE foo SET bar = 123123123123123.23;" generate an error while "UPDATE foo SET bar = 1.0025" not? Both are out of range for the datatype of the column, yet the second one is simply truncated with a note.

INO the bug is, that a note and not - depending on sql_mode - a warning/error is returned.

-- Peter
-- not a MySQL/Oracle person

>>Why does "UPDATE foo SET bar = 123123123123123.23;" generate an error while "UPDATE foo SET bar = 1.0025" not? Both are out of range for the datatype of the column, yet the second one is simply truncated with a note.

Thank you for the feedback, per our manual this is handled this way:
If a number is inserted into an exact type column (DECIMAL or integer), it is inserted with its exact value if it is within the column range.

If the value has too many digits in the fractional part, rounding occurs and a warning is generated. Rounding is done as described in Section 12.21.4, “Rounding Behavior” - https://dev.mysql.com/doc/refman/5.7/en/precision-math-rounding.html.

If the value has too many digits in the integer part, it is too large and is handled as follows:

- If strict mode is not enabled, the value is truncated to the nearest legal value and a warning is generated.

- If strict mode is enabled, an overflow error occurs.

Underflow is not detected, so underflow handling is undefined. 

See https://dev.mysql.com/doc/refman/5.7/en/precision-math-expressions.html

Per manual , UPDATE should have generated a warning instead of NOTE.

This is not a bug.

A range error and a data truncation are two different problems: The first is always an error, while the second is a simple adjustment to make the data value fit to a specific data type. Imagine when a floating point value is assigned to a decimal or integer column: There is no way we can handle that without a rounding operation.

This is also compliant with the SQL standard. In fact, reporting a warning in this case is beyond the standard.

I also tried a similar operation with Postgres and it succeeds with no notes or warnings.

I am changing this to a documentation bug so that we can decide whether a documentation update is needed or not.

Posted by developer:
 
As Roy points out, truncation of the fractional part is not a bug. I'll make a note of that in the docs. Also, the diagnostic is a Note and not a Warning, so I'll change that as well:

"
If a number is inserted into an exact type column (DECIMAL or integer), it is inserted with its exact value if it is within the column range.

If the value has too many digits in the fractional part, rounding occurs and a note is generated. Rounding is done as described in Section 12.23.4, “Rounding Behavior”. Truncation due to rounding of the fractional part is not an error, even in strict mode.
"

However, I have also found that the Note is generated for inserts of numbers into DECIMAL columns but not integer columns. This is an inconsistency, and the Note is not required by the SQL standard, anyway, so I'll file a request that it not be generated at all.