MySQL Bugs: #21674: STRICT_TRANS_TABLES is not always being honoured

Bug #21674	STRICT_TRANS_TABLES is not always being honoured
Submitted:	16 Aug 2006 14:05	Modified:	25 Aug 2006 1:06
Reporter:	Matthijs van der Vleuten	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.24	OS:	Windows (Windows)
Assigned to:		CPU Architecture:	Any

Description:
When the STRICT_TRANS_TABLES SQL mode is enabled, LOAD DATA INFILE doesn't fail when it should fail.

Situation:
- A table, containing at least one INTEGER column.
- A data file, containing valid data.

How to repeat:
Considering the situation described above.

Procedure A:
- Add a new line at the beginning of the data file, with an empty value for the INTEGER column.
- Execute LOAD DATA INFILE on the data file.

This fails with the error:
"Out of range value adjusted for column 'testField' at row 1",
and the table is unmodified.

Procedure B:
- Add a new line anywhere in the data file (but not at the beginning of the file) with an empty value for the INTEGER column.
- Execute LOAD DATA INFILE on the data file.

This warning is generated:
"Out of range value adjusted for column 'testField' at row 2",
and rows are added to the table. In place of the empty values, the value '0' is used.

Suggested fix:
Procedure B should fail like Procedure A instead of modifying the table.
Alternatively, Procedure A should handle empty values just like Proceduce B does.

The described behaviour is not a bug. It is described in the manual: http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

Thank you for the bug report. How it was already commented by Mr. Ad Hendrikx
it isn't a bug, please read the pointed Manual chapter regarding the sql_mode.