Bug #27176 Assigning a string to an year column has unexpected results
Submitted: 15 Mar 2007 14:49 Modified: 11 Apr 2007 1:52
Reporter: Ramil Kalimullin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.0 OS:Any
Assigned to: Ramil Kalimullin

[15 Mar 2007 14:49] Ramil Kalimullin
Description:
The problem described in the
bug #6147: Traditional: Assigning a string to a numeric column has unexpected results
affects year columns as well.

How to repeat:
create table t2(a year); 
insert into t2 values (2000.5),('2000.5'); 
select * from t2;

+------+
| a    |
+------+
| 2001 | 
| 2000 | 
+------+
2 rows in set (0.00 sec)
[16 Mar 2007 7:06] Ramil Kalimullin
Another problem is that we get wrong warnings/errors assigning "wrong" strings to an year column:

create table t2(a year);
insert into t2 values ('-'), ('a');
show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'a' at row 1 |
| Warning | 1264 | Out of range value for column 'a' at row 2 |
+---------+------+--------------------------------------------+

Should be changed to
#1366:  Incorrect integer value: '-' for column 'a' at row 1
[10 Apr 2007 16:07] Timothy Smith
Pushed, for 5.0.40 and 5.1.18-beta
[11 Apr 2007 1:52] Paul Dubois
Noted in 5.0.40, 5.1.18 changelogs.

String truncation upon insertion into an integer or year column did
not generate a warning (or an error in strict mode).