Bug #8294 Traditional: Misleading error message for invalid CAST to DATE
Submitted: 3 Feb 2005 16:18 Modified: 2 Dec 2005 19:58
Reporter: Trudy Pelzer Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.3-alpha-debug OS:Linux (SuSE 9.1)
Assigned to: Sergey Glukhov CPU Architecture:Any

[3 Feb 2005 16:18] Trudy Pelzer
Description:
Under sql_mode='traditional', invalid values for a
particular data type are rejected. But sometimes,
the text message returned by MySQL is misleading
and could cause confusion. This is the case when
an invalid date value is CAST to DATE.

How to repeat:
mysql> set sql_mode=traditional;

mysql> create table t1(col1 date);

mysql> insert into t1 values (cast('2004-9-31' as date));
ERROR 1292 (22007): Truncated incorrect datetime value: '2004-9-31'
-- The SQLSTATE returned is correct but the error message
is confusing. The word "truncation" implies that the string
'2004-9-31' is too long for a DATE column, not that the value
itself is an invalid date. A better message would be:
"Incorrect datetime value: '2004-9-31' for column 'col1' at row 1"
which is the same message returned for the following,
equivalent statement:

mysql> insert into t1 values ('2004-9-31');
ERROR 1292 (22007): Incorrect date value: '2004-9-31' for column 'col1' at row 1
[11 Feb 2005 21:39] Matthias Leich
I have a similar problem in strict mode during insertion of too big numbers.
CREATE TABLE NUM1 (
NUM1C1 NUMERIC (3, 2) NOT NULL,
NUM1C2 NUMERIC (2) NOT NULL,
NUM1C3 NUMERIC NOT NULL);
INSERT INTO NUM1 VALUES (0, 100, 0);
ERROR 22003: Out of range value adjusted for column 'NUM1C2' at row 1
Why!! and how(we get error 22003) was the value "adjusted" ?
It looks like the error message describes the implicit server actions I will get
in non strict mode, that means it equals to the non strict mode warning .
[20 Oct 2005 12:55] Sergey Glukhov
Looks like there is a not exactly correct bug description.

Existing behaviour:

Example1:
mysql> set sql_mode=traditional;
mysql> create table t1(col1 date);
mysql> insert into t1 values (cast('2004-9-31' as date));
ERROR 1292 (22007): Truncated incorrect datetime value: '2004-9-31'

ok, let's check what is result for cast() in traditional mode:

Example2:
set sql_mode=traditional;
select cast('2004-9-31' as date);
cast('2004-9-31' as date)
NULL
Warnings:
Warning       1292    Truncated incorrect datetime value: '2004-9-31'

well, result is 'NULL' value.
And NULL can be inserted into table t1.
"insert into t1 values (NULL);" works without any errors
in traditional mode. So I suppose that record should
be inserted into table with NULL value in Example1.
And only warning should be produced, not error message.

Suggested fix:
Allow insert the record in case of Example1 and produce a warning.
Add new warning for wrong values: "Incorrect %-.32s value: '%-.128s'"

Trudy, do you agree to suggested fix?
[20 Oct 2005 19:43] Trudy Pelzer
No, I do not agree with the suggested fix. Under strict mode,
any function or operation must return an error if the result is 
an invalid value for the result data type. Currently, mysql is
unable to "catch" such errors in the select list, so your example:
select cast('2004-9-31' as date);
returns NULL, which is the same result when sql_mode=''.
But that is not correct behaviour for sql_mode='traditional'.

The server is doing the right thing by rejecting the result of
cast('2004-9-31' as date), so please do not change this
behaviour. The request is merely that the message returned
should be the same message returned for
[20 Oct 2005 19:44] Trudy Pelzer
INSERT INTO t VALUES ('2004-9-31').

(Sorry, inadvertently sent previous comment before 
I was finished.)
[25 Oct 2005 12:04] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/31447
[9 Nov 2005 9:13] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32101
[2 Dec 2005 10:57] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32953
[2 Dec 2005 11:00] Sergey Glukhov
Fixed in 5.1.4-alpha
[2 Dec 2005 19:58] Paul Dubois
Noted in 5.1.4 changelog.