MySQL Bugs: #8294: Traditional: Misleading error message for invalid CAST to DATE

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:	Sergei Glukhov	CPU Architecture:	Any

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

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 .

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?

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

INSERT INTO t VALUES ('2004-9-31').

(Sorry, inadvertently sent previous comment before 
I was finished.)

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

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

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

Fixed in 5.1.4-alpha

Noted in 5.1.4 changelog.