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