MySQL Bugs: #65202: Error code 1292 not well documented

Bug #65202	Error code 1292 not well documented
Submitted:	4 May 2012 13:53	Modified:	8 May 2012 9:21
Reporter:	Jorge Pinho	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.5.15	OS:	Windows (win7 x64)
Assigned to:		CPU Architecture:	Any
Tags:	error code

Description:
I got the error "1292 Incorrect date value .... " however, in the docs:

http://dev.mysql.com/doc/refman/5.6/en/error-messages-server.html

error 1292 is documented has "Truncated incorrect %s value: '%s'"

How to repeat:
Create a field with a date datatype and insert something like:

INSERT INTO table1 SET myDate = ''

Returns:
Error Code: 1292. Incorrect date value: '' for column 'col1' at row 1

Thank you for the report.

Please send us exact CREATE TABLE and INSERT statements: I get different error message.

mysql> \W
Show warnings enabled.
mysql> create temporary table t1(f1 datetime);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 set f1='';
Query OK, 1 row affected, 1 warning (0.03 sec)

Warning (Code 1264): Out of range value for column 'f1' at row 1
mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> create temporary table t1(f1 date);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 set f1='';
Query OK, 1 row affected, 1 warning (0.00 sec)

Warning (Code 1265): Data truncated for column 'f1' at row 1

CREATE TABLE table1 (
    field1 date
);

INSERT INTO table1 SET field1 = '';

Error Code: 1292. Incorrect date value: '' for column 'field1' at row 1

On Linux according the Manual and perror tool:

[miguel@izalco ~]$ perror 1292
MySQL error code 1292 (ER_TRUNCATED_WRONG_VALUE): Truncated incorrect %-.32s value: '%-.128s'
[miguel@izalco ~]$ mysql -uroot -p                                                           
Enter password:                                                                              
Welcome to the MySQL monitor.  Commands end with ; or \g.                                    
Your MySQL connection id is 5                                                                
Server version: 5.5.23-log MySQL Community Server (GPL) by Remi                              

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE TABLE table1 (
    ->     field1 date
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql>
mysql> INSERT INTO table1 SET field1 = '';
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1265 | Data truncated for column 'field1' at row 1 |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)

mysql>

I couldn't repeat with latest source:

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

h:\dbs>55

h:\dbs>h:\dbs\5.5\bin\mysql -uroot --port=3540 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.24 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 >use test
Database changed
mysql 5.5 >CREATE TABLE table1 (
    ->     field1 date
    -> );
Query OK, 0 rows affected (0.13 sec)

mysql 5.5 >
mysql 5.5 >INSERT INTO table1 SET field1 = '';
Query OK, 1 row affected, 1 warning (0.05 sec)

mysql 5.5 >show warnings;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1265 | Data truncated for column 'field1' at row 1 |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)

mysql 5.5 >

But, why is this value truncated ?

field1 = '' is smaller than the expected value for 'date' 0000-00-00

Server version: 5.5.27 MySQL Community Server (GPL), running on Windows Server 2008 R2

I believe too the documentation is not well documented or incomplete because I get the same, from the docs different, error message.

http://dev.mysql.com/doc/refman/5.6/en/error-messages-server.html states:
"Error: 1292 SQLSTATE: 22007 (ER_TRUNCATED_WRONG_VALUE)
Message: Truncated incorrect %s value: '%s'"

Test1:
mysql> insert into kunden_import2 set geburtstag='11';
ERROR 1292 (22007): Incorrect date value: '11' for column 'geburtstag' at row 1

Test2:
mysql> insert into kunden_import2 set geburtstag='';
ERROR 1292 (22007): Incorrect date value: '' for column 'geburtstag' at row 1

mysql> show create table kunden_import2;
(big table/truncated ouput)
...
  `geburtstag` date DEFAULT NULL,
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8