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
• Status: Closed
• Category: MySQL Server: General
• Severity: S3 (Non-critical)
• Version: 5.0
• OS: Any
• Assigned to: Ramil Kalimullin
• CPU Architecture: Any

[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).