MySQL Bugs: #73041: Error 1366 when loading null into columns with decimal type using strict mode

Bug #73041	Error 1366 when loading null into columns with decimal type using strict mode
Submitted:	18 Jun 2014 15:16	Modified:	31 Jul 2014 10:13
Reporter:	Diane Gabrielsen	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S3 (Non-critical)
Version:	5.1.40	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	1366, strict mode

Description:
When in strict mode, while using load data infile, columns with datatype decimal receive error 1366: Incorrect decimal value: '' for column 'column_name' at row [row number] for fields with a null value.

This error does not appear when not in strict mode.

How to repeat:
CREATE TABLE bug_repeat
(
  name varchar(10),
  price decimal(12,6)
) 
ENGINE=MYISAM DEFAULT CHARSET=ascii COLLATE=ascii_bin;

Create a file called bug_test.txt and have it contain one or more null values
for the decimal column.

Example file:

name,
name,0
,
name,6
name,2
name, 
name,0
name,0
name,
name,0

load data infile 'bug_test.txt'  
into table dev.bug_repeat
fields terminated by ',' 
lines terminated by '\n';

When loading, you will get this error for every row that contains a null value:
1366: Incorrect decimal value: '' for column 'price' at row [row number]

Errors returned for example file used:

1366: Incorrect decimal value: '' for column 'price' at row 1
1366: Incorrect decimal value: '' for column 'price' at row 3
1366: Incorrect decimal value: '' for column 'price' at row 6
1366: Incorrect decimal value: '' for column 'price' at row 9

I forgot to mention that besides giving the error, MySQL replaces the null value with a 0 in the table.  This is the most serious part of the issue, since my table now cannot tell the difference between a null or a valid zero!

Thank you for the report.

Imho, this is documented behavior described in http://dev.mysql.com/doc/refman/5.1/en/load-data.html "If an input line has too many fields, the extra fields are ignored and the number of warnings is incremented.If an input line has too few fields, the table columns for which input fields are missing are set to their default values. Default value assignment is described in Section 11.6, “Data Type Default Values”."

For string types, the column is set to the empty string.
For numeric types, the column is set to 0.
For date and time types, the column is set to the appropriate “zero” value for the type. See Section 11.3, “Date and Time Types”.

These are the same values that result if you assign an empty string explicitly to a string, numeric, or date or time type explicitly in an INSERT or UPDATE statement.
Treatment of empty or incorrect field values differs from that just described if the SQL mode is set to a restrictive value. For example, if sql_mode='TRADITIONAL, conversion of an empty value or a value such as 'x' for a numeric column results in an error, not conversion to 0. (With LOCAL, warnings occur rather than errors, even with a restrictive sql_mode value, because the server has no way to stop transmission of the file in the middle of the operation.)"

See http://dev.mysql.com/doc/refman/5.1/en/load-data.html

## With strict mode
mysql-5.1.73]$ more /tmp/o.csv
name,
name,0
,
name,6
name,2
name, 
name,0
name,0
name,
name,0

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 |
+---------------+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> load data infile '/tmp/o.csv'
    -> into table test.bug_repeat
    -> fields terminated by ','
    -> lines terminated by '\n';
ERROR 1366 (HY000): Incorrect decimal value: '' for column 'price' at row 1
mysql> select * from bug_repeat;
Empty set (0.00 sec)

// Workaround - replace missing field with \N for null
mysql-5.1.73]$ more /tmp/o.csv
name,\N
name,0
\N,\N
name,6
name,2
name,\N
name,0
name,0
name,\N
name,0

mysql> truncate bug_repeat;
Query OK, 0 rows affected (0.00 sec)

mysql> load data infile '/tmp/o.csv'   into table test.bug_repeat fields terminated by ','  lines terminated by '\n';
Query OK, 10 rows affected (0.00 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from bug_repeat;
+------+----------+
| name | price    |
+------+----------+
| name |     NULL |
| name | 0.000000 |
| NULL |     NULL |
| name | 6.000000 |
| name | 2.000000 |
| name |     NULL |
| name | 0.000000 |
| name | 0.000000 |
| name |     NULL |
| name | 0.000000 |
+------+----------+
10 rows in set (0.00 sec)