Bug #14799 invalid datetime converting when sql_mode is not set
Submitted: 9 Nov 2005 17:51 Modified: 15 Nov 2005 19:00
Reporter: Alexander Y. Fomichev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.15 OS:Linux (Linux)
Assigned to: MySQL Verification Team CPU Architecture:Any

[9 Nov 2005 17:51] Alexander Y. Fomichev
Description:
When sql_mode is not set mysql converts invalid datetimes in some strange manner.  
According to documentation:
* In non-strict mode, the MySQL server performs only basic checking
     on the validity of a date: The ranges for year, month, and day are
     1000 to 9999, 00 to 12, and 00 to 31, respectively. Any date
     containing parts not within these ranges is subject to conversion
     to `'0000-00-00''. Please note that this still allows you to store
     invalid dates such as `'2002-04-31''. To ensure that a date is
     valid when not using strict mode, you should perform a check in
     your application.
and,
 * `ALLOW_INVALID_DATES'
[skip]
 For example, `'2004-04-31'' is legal with strict mode
     disabled, but illegal with strict mode enabled. To allow such
     dates in strict mode, enable `ALLOW_INVALID_DATES' as well.

How to repeat:
localhost tmp # cat test.sql 
/* SET sql_mode=ALLOW_INVALID_DATES; */
SELECT @@sql_mode;
DROP TABLE IF EXISTS a;
CREATE TABLE `a` (   
`i` int(11) unsigned NOT NULL default '0', 
`time1` datetime NOT NULL default '0000-00-00 00:00:00', 
`time2` datetime NOT NULL default '0000-00-00 00:00:00' 
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;
INSERT INTO  a VALUES (1,'2005-00-00 00:00:00','2005-00-00 00:00:00');
INSERT INTO  a VALUES (1,'2005-04-31 11:11:11','2005-04-31 11:11:11');
SELECT * FROM a;
DROP TABLE IF EXISTS a;

localhost tmp # mysql test < /var/tmp/test.sql 
@@sql_mode

i       time1   time2
1       2005-00-00 00:00:00     2005-00-00 00:00:00
1       0000-00-00 00:00:00     0000-00-00 00:00:00

AFAICS datetime 2005-04-31 11:11:11 is valide under the terms of basic checking.

( When sql_mode is set to ALLOW_INVALID_DATES or STRICT* all thingth looks good )
localhost tmp # mysql test < /var/tmp/test.sql 
@@sql_mode
ALLOW_INVALID_DATES
i       time1   time2
1       2005-00-00 00:00:00     2005-00-00 00:00:00
1       2005-04-31 11:11:11     2005-04-31 11:11:11

localhost tmp # mysql test < /var/tmp/test.sql 
@@sql_mode
STRICT_ALL_TABLES
ERROR 1292 (22007) at line 10: Incorrect datetime value: '2005-04-31 11:11:11' for column 'time1' at row 1

I could reproduse it with latest bk snapshop 5.0.16 20051109

Suggested fix:
I don't know really whether documentation have to be fixed or STRICT_* flags should be checked
before  check_date() will be called, but something should i mean. 
And yes, in last case mysqldump should be fixed IMO beacuse it overrides default SQL_MODE and as far as i can see i can't append  ALLOW_INVALID_DATES here so can't load dump from 4.1 to 5.0 ( i.g. if i have unique index on the datetime fild with invalid values )
[10 Nov 2005 0:06] MySQL Verification Team
Could you please provide which is the expected result you think is
correct?

miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.17-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SET @@sql_mode=ALLOW_INVALID_DATES;
Query OK, 0 rows affected (0.01 sec)

mysql> select @@sql_mode;
+---------------------+
| @@sql_mode          |
+---------------------+
| ALLOW_INVALID_DATES |
+---------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE `a` (   
    -> `i` int(11) unsigned NOT NULL default '0', 
    -> `time1` datetime NOT NULL default '0000-00-00 00:00:00', 
    -> `time2` datetime NOT NULL default '0000-00-00 00:00:00' 
    -> ) ENGINE=MyISAM DEFAULT CHARSET=cp1251;
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO  a VALUES (1,'2005-00-00 00:00:00','2005-00-00 00:00:00');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO  a VALUES (1,'2005-04-31 11:11:11','2005-04-31 11:11:11');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM a;
+---+---------------------+---------------------+
| i | time1               | time2               |
+---+---------------------+---------------------+
| 1 | 2005-00-00 00:00:00 | 2005-00-00 00:00:00 |
| 1 | 2005-04-31 11:11:11 | 2005-04-31 11:11:11 |
+---+---------------------+---------------------+
2 rows in set (0.00 sec)

mysql> 

Thanks in advance.
[10 Nov 2005 10:11] Alexander Y. Fomichev
Sorry, probably i'm couldn't show exactly what i mean, works well,  but what about:

localhost tmp # mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 36 to server version: 5.0.16-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SET @@sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE `a` (
    -> `i` int(11) unsigned NOT NULL default '0',
    -> `time1` datetime NOT NULL default '0000-00-00 00:00:00',
    -> `time2` datetime NOT NULL default '0000-00-00 00:00:00'
    -> ) ENGINE=MyISAM DEFAULT CHARSET=cp1251;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO  a VALUES (1,'2005-04-31 11:11:11','2005-04-31 11:11:11');
Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql> SELECT * FROM a;
+---+---------------------+---------------------+
| i | time1               | time2               |
+---+---------------------+---------------------+
| 1 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+---+---------------------+---------------------+
1 row in set (0.00 sec)

If i understand documentation correctly '2005-04-31 11:11:11' _should not_ be converted to '0000-00-00 00:00:00'. In a strict mode it should be ERROR 1292 (22007) : Incorrect datetime value:
and when sql_mode=ALLOW_INVALID_DATES it should be '2005-04-31 11:11:11',
or i missing something?
[10 Nov 2005 10:13] Alexander Y. Fomichev
- Sorry, probably i'm couldn't show exactly what i mean, works well,  but what
about:
+ Sorry, probably i'm couldn't show exactly what i mean, ALLOW_INVALID_DATES works well,  but what about:
[15 Nov 2005 19:00] Paul DuBois
Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

The documentation needed to be updated.  ALLOW_INVALID_DATES
doesn't just apply in strict mode.

This is actually how MySQL works now, as of 5.0.2:

MySQL requires month and day to be correct (not just month = 1..12,
day = 1..31)
If a date is invalid, it's converted to 0000-00-00 and a warning occurs
in non-strict mode.  In strict mode, an error occurs.

Setting ALLOW_INVALID_DATES suppresses full checking. The only check
is that month = 1..12, day = 1..31.  This works in non-strict or strict
mode.