MySQL Bugs: #64603: misleading warning for documented LOAD DATA INFILE behavior

Bug #64603	misleading warning for documented LOAD DATA INFILE behavior
Submitted:	9 Mar 2012 17:46	Modified:	9 Mar 2012 18:05
Reporter:	Stephen Dewey	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S3 (Non-critical)
Version:	5.3.5-MariaDB-ga-log	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	1265, data truncated, LOAD DATA INFILE, warning 1265

Description:
Documented behavior of LOAD DATA INFILE is:

>> An empty field value is interpreted differently than if the field value is missing:
>> 
>> 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 10.3, “Date and Time Types”.

So far so good, but if you do LOAD DATA INFILE with empty data into certain column types, for example DATE, you get Warning 1265, "Data truncated for column." Doesn't happen for all data types, for example doesn't happen for VARCHAR.

Considering that the data hasn't been directly truncated, but instead interpreted to a zero value for the data type, I think this warning may be misleading. I would suggest a new warning type for this, or perhaps even eliminating the warning since it is a documented behavior. Or, the documentation could clarify that a warning will be issued.

How to repeat:
**create table statement**

CREATE TABLE `myt` (
  `my1` varchar(20) NOT NULL,
  `my2` date DEFAULT NULL,
  `my3` varchar(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

**file contents**

my1|my2|my3
"abcd"||"a123"
"zyx"|"abc"|"456"

**load data infile command**

LOAD DATA LOCAL INFILE "C:\\Users\\myuser\\Desktop\\my2.txt"
INTO TABLE `myt` 
FIELDS TERMINATED BY '|' 
OPTIONALLY ENCLOSED BY '"' 
ESCAPED BY '\\' 
LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES;

Suggested fix:
Update documentation or create a new warning type.

As a follow-up to my suggested fix, I think that it would be best to keep the warning, since changing empty fields to "zero" may often be an undesirable behavior. I do think the warning could be more helpful or should be documented in the LOAD DATA INFILE section.

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

This is actually not LOAD DATA INFILE issue, but standard warning for cases when data is not proper for certain column type:

mysql> CREATE TABLE `myt` (   `my1` varchar(20) NOT NULL,   `my2` date DEFAULT NULL,   `my3` varchar(20) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.15 sec)

mysql> insert into myt values('abcd','','a123');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'my2' at row 1 |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from myt;
+------+------------+------+
| my1  | my2        | my3  |
+------+------------+------+
| abcd | 0000-00-00 | a123 |
+------+------------+------+
1 row in set (0.00 sec)

I don't think we need to fix standard behavior.

Thanks for the follow-up. The reason I think it is confusing/wrong is the definition of truncate is "to shorten by cutting off a part." In this case it isn't actually being truncated because it is already a zero-length string, so I felt it was confusing.

However, I do see your point about it being an extension of a standard processing rule.