MySQL Bugs: #65875: datetime field don´t working with time = 24:00:00

Bug #65875	datetime field don´t working with time = 24:00:00
Submitted:	11 Jul 2012 17:49	Modified:	11 Jul 2012 19:26
Reporter:	Roberto Spadim (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	any	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	datetime field

Description:
hi guys, i was talking with mariadb guys and i found a diferent problem with date time fields... (more info at https://mariadb.atlassian.net/browse/MDEV-390)

let me explain again...

How to repeat:
CREATE TABLE `t` (
  `data_compensacao_origem` datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2011-01-13 18:00:00');
select * from t where data_compensacao_origem<'2012-01-01 00:00:00'

this return one row without errors and without warnings (nice!)

select * from t where data_compensacao_origem<'2012-01-01 24:00:00'
this don´t return rows without errors (even without ALLOW_INVALID_DATES) and a warning (Incorrect datetime value: '2012-01-01 24:00:00')

nice...
but... could it allow 24:00:00 ? since it´s a 1 second datetime problem?

Suggested fix:
i was reading my_time.c at source code of mariadb 5.2.5 at line 417
check the part of code:
 if (number_of_fields < 3 ||
      l_time->year > 9999 || l_time->month > 12 ||
      l_time->day > 31 || l_time->hour > 23 ||
      l_time->minute > 59 || l_time->second > 59)
  {

maybe there´s orthers parts that use this condition (i don´t know about source code)
but in this case maybe here:

      l_time->hour > 23 ||
      l_time->minute > 59 || l_time->second > 59)

should be changed to 
(l_time->hour > 24 && l_time->minute > 0 && l_time->second > 0) ||
(l_time->hour < 24 && (l_time->minute > 59 || l_time->second > 59))

thanks :)

check that this can be a bug, since http://en.wikipedia.org/wiki/Leap_second consider that this time : "2012-06-30 23:59:60" exists (maybe my 24:00:00 don´t exists... =] )
check that : 
select * from t where data_compensacao_origem<'2012-06-30 23:59:60'
return a warning (Incorrect datetime value: '2012-06-30 23:59:60')

A couple of comments to simplify the hard part for MySQL people.

1. You can safely skip MDEV-390, it's long and doesn't have any relevant information.

2. Roberto wrote: 
" select * from t where data_compensacao_origem<'2012-01-01 24:00:00'
this don´t return rows without errors (even without ALLOW_INVALID_DATES) and a warning (Incorrect datetime value: '2012-01-01 24:00:00') "

Most likely it's not true for MySQL, that's where MariaDB and MySQL behavior differs. MySQL would produce a warning, but would still return rows (which I suppose is incorrect, because if the wrong constant is truncated to 0000-00-00 00:00:00, as it's supposed to, table values should not be less than that.)

3. The tricky part is this:

mysql> select * from t where d < '2012-11-31 00:00:00'; 
+---------------------+ 
| d | 
+---------------------+ 
| 2012-07-11 21:04:44 | 
+---------------------+ 
1 row in set (0.00 sec) 

No warnings are produced. Values are returned. 

http://dev.mysql.com/doc/refman/5.5/en/datetime.html states that "The server requires that month and day values be valid, and not merely in the range 1 to 12 and 1 to 31, respectively. With strict mode disabled, invalid dates such as '2004-04-31' are converted to '0000-00-00' and a warning is generated." 
The "strict mode" part is irrelevant here, but the rest should still be applicable. 
Also, it's inconsistent with behavior in (2).

This is what I see:

macbook-pro:5.5 openxs$ 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 7
Server version: 5.5.26-debug 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> CREATE TABLE `t` (
    ->   `data_compensacao_origem` datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.14 sec)

mysql> INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2011-01-13 18:00:00');
Query OK, 1 row affected (0.03 sec)

mysql> select * from t where data_compensacao_origem<'2012-01-01 00:00:00';
+-------------------------+
| data_compensacao_origem |
+-------------------------+
| 2011-01-13 18:00:00     |
+-------------------------+
1 row in set (0.02 sec)

mysql> select * from t where data_compensacao_origem<'2012-01-01 24:00:00';
+-------------------------+
| data_compensacao_origem |
+-------------------------+
| 2011-01-13 18:00:00     |
+-------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1292
Message: Incorrect datetime value: '2012-01-01 24:00:00' for column 'data_compensacao_origem' at row 1
1 row in set (0.04 sec)

mysql> show session variables like 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
1 row in set (0.05 sec)

mysql> set session sql_mode='ALLOW_INVALID_DATES';
Query OK, 0 rows affected (0.04 sec)

mysql> select * from t where data_compensacao_origem<'2012-01-01 24:00:00';
+-------------------------+
| data_compensacao_origem |
+-------------------------+
| 2011-01-13 18:00:00     |
+-------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1292
Message: Incorrect datetime value: '2012-01-01 24:00:00' for column 'data_compensacao_origem' at row 1
1 row in set (0.00 sec)

So, independently of SQL mode setting we get same, correct(?) results, 1 row, and we get warning.

Do you see the same results? If you don't what exact server version do you use? If you do, what is the bug here? 

Why do you assume that ALLOW_INVALID_DATES SQL mode should control value of "hour" in time of the day part (that always should be in 0-23 range)?

Note that this also works as expected:

mysql> select * from t where data_compensacao_origem<'2011-01-13 24:00:00';
+-------------------------+
| data_compensacao_origem |
+-------------------------+
| 2011-01-13 18:00:00     |
+-------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from t where data_compensacao_origem<'2011-01-13 00:00:00';
Empty set (0.00 sec)

So, looks like incorrect value is just replaced with closest correct value (typical for MySQL).

I do not see any bug here. Maybe a request for more clarifications for the SQL modes manual page, based on this your example. 

P.S. I'd also say that it's a bit incorrect to report bugs at bugs.mysql.com and refer to line numbers in source code files and versions of MariaDB...

maybe just explain better in manual for mysql, and mariadb maybe runs different, must check, i will do it soon i don´t have last version of mysql and mariadb but i will check and reply here, i will change to non critical...

it runs nice in mysql 5.5.25a and don´t run in mariadb
i will close bug here

Hi Valeriy,

>> So, looks like incorrect value is just replaced with closest correct value

Actually, it's not quite so. Please consider the following example:

mysql> select * from t;
+---------------------+
| d                   |
+---------------------+
| 2012-12-12 23:59:59 |
| 2012-12-13 00:00:00 |
+---------------------+
2 rows in set (0.00 sec)

mysql> select * from t where d < '2012-12-12 24:00:00';
+---------------------+
| d                   |
+---------------------+
| 2012-12-12 23:59:59 |
+---------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from t where d <= '2012-12-12 24:00:00';
+---------------------+
| d                   |
+---------------------+
| 2012-12-12 23:59:59 |
+---------------------+
1 row in set, 1 warning (0.00 sec)

That is, '2012-12-12 24:00:00' is strictly greater than '2012-12-12 23:59:59', but is still "less" than '2012-12-13 00:00:00'.

My best guess is that they are compared as strings, but it somewhat contradicts to the manual:

http://dev.mysql.com/doc/refman/5.5/en/type-conversion.html
" If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed."

And the conversion rules for invalid dates are explained here http://dev.mysql.com/doc/refman/5.5/en/datetime.html.

Also, it still does not explain why this does not produce even a warning (note the wrong date, Nov 31):

mysql> select * from t where d > '2012-11-31 00:00:00';
+---------------------+
| d                   |
+---------------------+
| 2012-12-12 23:59:59 |
| 2012-12-13 00:00:00 |
+---------------------+
2 rows in set (0.00 sec)

+-----------+------------------------------+
| @@version | @@version_comment            |
+-----------+------------------------------+
| 5.5.25    | MySQL Community Server (GPL) |
+-----------+------------------------------+
1 row in set (0.00 sec)

IMHO
2000-01-01 24:00:00
is less than
2000-01-02 00:00:00

since there´s some cases (i don´t know if it´s right or not) that we can have times like: 2000-01-01 23:59:60 or maybe rewrite as 24:00:00 ... but they still in date 2000-01-01 and not at 2000-01-02
so string comparison is right

maybe a new option could be added for example AUTOMATIC_CONVERT_INVALID DATES
and dates with values like:
2012-07-32 00:00:00 should be rewrite as 
2012-08-01 00:00:00 and a warning showed "automatic convertion of invalid date 2012-07-32 00:00:00 to 2012-08-01 00:00:00"

there´s others cases where this could be used, for example:
2000-00-00 00:00:00 should be rewrite as 2000-01-01 00:00:00
2012-01-01 48:00:00 should be rewrite as 2012-01-02 24:00:00 or maybe 2012-01-03 00:00:00 (must check if 24:00:00 is valid...)

just some ideas...