show warnings result:
warning 1292 Incorrect datetime value: '2012-07-31 24:00:00'
if i execute this:
set sql_mode=PIPES_AS_CONCAT
and after the query, no error is displayed (query run nice, but no result...) and at show warnings i have the same result
warning 1292 Incorrect datetime value: '2012-07-31 24:00:00'
if i correct it to 2012-07-31 23:59:59 i can see some rows in table without problem (and no warning)
so, what´s the expected result of sql_mode=allow_invalid_dates? it only works for update, replace and insert?
Roberto,
If you consider it a bug, please provide a test case and an explanation (preferably confirmed by the manual, unless it's obvious) why it should work differently. Please also try to collect all essential information and put it in the description, rather than keep adding pieces as separate comments, because this way it's very unreadable and difficult to work with. Which, again, shows that for questions like that IRC (the chat) is a much better place.
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');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2011-02-17 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2011-02-09 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2011-02-08 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2011-02-24 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-05-02 00:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-04-09 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-04-09 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-04-09 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-03-21 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-03-15 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-03-15 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-03-14 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-03-14 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-02-29 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-01-11 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-01-11 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2011-12-23 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-01-05 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-01-06 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2011-08-02 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2011-08-31 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2011-09-02 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2011-10-13 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2011-10-13 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2011-10-18 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2011-10-18 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2011-10-24 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2011-11-11 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2011-11-11 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2011-12-13 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2011-12-13 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2011-12-14 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2011-11-18 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2011-11-18 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-06-25 16:57:39');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-05-08 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-05-08 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-05-08 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-05-08 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2010-11-15 06:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2010-10-22 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2011-11-21 06:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-05-22 00:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-05-22 00:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-05-22 00:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2011-10-14 05:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-05-29 05:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-06-06 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-06-06 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-06-06 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-06-06 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-06-06 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-06-08 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-06-11 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-06-14 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2011-12-14 06:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-04-19 15:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-05-14 15:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2011-11-16 16:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2011-12-16 16:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-01-16 16:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-03-15 15:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-04-16 15:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-06-18 15:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2011-10-17 16:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-07-02 08:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-07-02 08:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-07-02 08:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-07-02 08:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-07-02 08:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-07-02 08:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-07-06 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-07-06 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-07-06 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-07-06 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-07-06 18:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-07-04 08:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-07-10 08:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-07-06 08:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-07-06 08:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-07-05 08:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-07-05 08:00:00');
INSERT INTO `t` (`data_compensacao_origem`) VALUES ('2012-07-10 08:00:00');
set sql_mode=allow_invalid_dates;
// THIS DON´T WORK, BUT DON´T GIVE ERROR, JUST WARNINGS:
SELECT * FROM t WHERE
data_compensacao_origem>="2001-07-06 00:00:00" AND
data_compensacao_origem<"2012-07-31 24:00:00"
// THIS WORK WITHOUT WARNINGS
SELECT * FROM t WHERE
data_compensacao_origem>="2001-07-06 00:00:00" AND
data_compensacao_origem<"2012-07-31 23:59:59"
SELECT * FROM t WHERE
data_compensacao_origem>="2001-07-06 00:00:00" AND
data_compensacao_origem<"2012-08-01 00:00:00"
//AT MANUAL:
http://dev.mysql.com/doc/refman/5.6/en/server-sql-mode.html#sqlmode_allow_invalid_dates
Do not perform full checking of dates. Check only that the month is in the range from 1 to 12 and the day is in the range from 1 to 31. This is very convenient for Web applications where you obtain year, month, and day in three different fields and you want to store exactly what the user inserted (without date validation). This mode applies to DATE and DATETIME columns. It does not apply TIMESTAMP columns, which always require a valid date.
The server requires that month and day values be legal, 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. With strict mode enabled, invalid dates generate an error. To permit such dates, enable ALLOW_INVALID_DATES.
///
the point is, should it consider hour,minute and second? or, is my date being changed to 0000-00-00 00:00:00 ?
just more information...
explain extended
SELECT * FROM t
WHERE
data_compensacao_origem>="2001-07-06 00:00:00" AND
data_compensacao_origem<"2012-07-31 24:00:00"
show warnings result:
select `spd_cashflow`.`t`.`data_compensacao_origem` AS `data_compensacao_origem` from `spd_cashflow`.`t` where ((`spd_cashflow`.`t`.`data_compensacao_origem` >= '2001-07-06 00:00:00') and (`spd_cashflow`.`t`.`data_compensacao_origem` < '2012-07-31 24:00:00'))
no conversion to 0000-00-00 00:00:00
Roberto,
Again, please specify what is the bug here. What, to your opinion, is supposed to be the result of the set of commands you provided, as opposed to what you observe, and why.
E.g. what do you mean by "THIS DON´T WORK, BUT DON´T GIVE ERROR, JUST WARNINGS:"? What do you expect it to do, what it does, and why do you think it's wrong?
And please put it all together, rather than through scattered comments.
Please also use the version of the manual which corresponds the version of your server.
—
expected values:
for allow_invalid_date turned off, a error
for allow_invalid_date turned on, a warning, maybe a better explanation of what happened (does the datetime 'converted' to 0000-00-00 00:00:00 or other result? any other explain)
—
well, let me show the query results...
SELECT * FROM t WHERE data_compensacao_origem>="2001-07-06 00:00:00" AND data_compensacao_origem<"2012-07-31 23:59:59"
it return about 84 rows (ok, no warnings and no error)
for this:
SELECT * FROM t WHERE data_compensacao_origem>="2001-07-06 00:00:00" AND data_compensacao_origem<"2012-07-31 24:00:00"
it return 0 rows, with warning, AND NO ERROR, with or WITHOUT allow_invalid_date
when I turn off allow_invalid_date, it should (at least) give an error instead of a warning (like explained in manual "With strict mode enabled, invalid dates generate an error.", check that this part of manual didn´t changed since version 5.1, maybe it should check invalid hours too? that´s why it don´t return a error?)
—
why i´m reporting this as bug:
my problem started when a client told me that a query wasn´t running, since it was a datetime field, i tried to turn off allow_invalid_dates to check if it´s a datetime error (in application errors are showed to users, warnings not)
since no erros was returned by the query, i didn´t found the error at user interface
i got inside code and output the sql to user, with the sql query I executed at database and checked that the problem was the 24:00:00
rewrite it to 2012-08-01 00:00:00, and it worked (ok i put this workaround in my program and it´s working now)
but why not an error with allow_invalid_dates turned off?
—
just a opnion about what should be done:
when using a invalid DATE and a valid HOUR it runs nice:
set sql_mode=allow_invalid_dates; (with sql_mode='' it give the same result!, should show a error of invalid date, day 31 don´t exist in november)
SELECT * FROM t
WHERE
data_compensacao_origem>="2001-07-06 00:00:00" AND
data_compensacao_origem<"2012-11-31 00:00:00"
with 84 rows, no warnings!!!
but when used a invalid HOUR it always return 0 rows
maybe it should run invalid hour like it run invalid date, and return 84 rows to user...
Hi Roberto,
Thank you, it's much better, now I see what you meant. Your logic has a couple of flaws.
1. As you yourself quoted earlier, the manual says "With strict mode enabled, invalid dates generate an error. To permit such dates, enable ALLOW_INVALID_DATES". Note the "strict mode" part! It means that ALLOW_INVALID_DATES is supplementary to the strict mode (that is, to STRICT_ALL_TABLES, in your case). In other words, with the default sql_mode='' invalid values are permitted; with sql_mode='STRICT_ALL_TABLES' invalid values are forbidden; with sql_mode='STRICT_ALL_TABLES,ALLOW_INVALID_DATES' invalid values are generally forbidden, but an exception is made for certain invalid dates.
2. Now, taking this into consideration, you'll see that you also need to read a section about STRICT_ALL_TABLES mode. You'll find there that the mode is applied, specifically, to invalid values when rows are inserted or updated (not to the values used for comparison). So, even if you used STRICT_ALL_TABLES, it would not be applicable to your case (SELECT with a wrong constant in comparison would not start producing an error).
But what I do agree with, regardless the whole thing with sql modes, is that the very last result you observed looks weird. I mean this (simplified):
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)
Logically, it should produce a warning and most likely shouldn't return the row (logic based on http://dev.mysql.com/doc/refman/5.5/en/datetime.html saying how invalid values are interpreted). Fast check confirms that it works the same way on MySQL 5.1.63, but I'm not sure if it's a new bug or it has always been this way. You might want to file it in MySQL bug base, at least to find out whether it was filed before or not, and track the progress; or, if it turns out to be intentional behavior, to request mentioning it in the manual.
nice i will put there
i was reading 5.2.5 code, at my_time.c
line 417
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)
{
/* Only give warning for a zero date if there is some garbage after */
if (!not_zero_date) /* If zero date */
{
for (; str != end ; str++)
{
if (!my_isspace(&my_charset_latin1, *str))
}
}
*was_cut= test(not_zero_date);
goto err;
}
maybe here:
l_time->day > 31 || 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))
i will talk with mysql bug, i think it´s running since mysql 3.xxx in this way
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')
bug at mysql created
http://bugs.mysql.com/bug.php?id=65875
well tested with mysql 5.5.25a
and it works
just in mariadb don´t work
maybe because the checks at my_time.c
Roberto,
In MySQL it "works" due to sheer luck, I would not advise to rely on this, at least until it is documented as intentional behavior – so far it directly contradicts the manual, and hence can change any time, just as many other over-relaxed conversions already changed between MySQL 5.1 and 5.5.
MariaDB works as currently documented. "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". The constant is invalid, it's converted to 0000-00-00 00:00:00 (as the documented conversion rules require), and comparison is done with the result of this conversion. If you insert zero values into your table and try to do "less or equal" comparison, they will be returned.
In MySQL they are compared as strings. If you want to replicate this behavior on MariaDB, you can convert your datetime values to strings and proceed.
nice, in others words i would rewrite it to:
SELECT * FROM t WHERE (data_compensacao_origem || "")<"2012-11-31 24:00:00"
does i will have problems with index optimizations?
could check if leap second will work in mariadb? i didn´t tryed and nobody talked it as an error, maybe never will talk since nobody is using my system at 23:59:60 they normally use between 08:00:00 and 20:00:00, at test 23:59:60 returned a warning (and no result) and it´s a 'legal' datetime value at least to UTC timezone
http://dev.mysql.com/doc/refman//5.5/en/time-zone-leap-seconds.html
"Leap second values are returned with a time part that ends with :59:59. This means that a function such as NOW() can return the same value for two or three consecutive seconds during the leap second. It remains true that literal temporal values having a time part that ends with :59:60 or :59:61 are considered invalid."
nice, but they are for timestamp fields, what about datetime fields? i´m asking because i read some parts of manual and didn´t found the information (i agree with you that mysql isn´t like manual but for a user changing from mysql to maria maybe this could be a problem to port code, for me no problem i can rewrite the query but for someone that have many queries this can be a problem)
in others words...
to allow a easy user port from mysql to mariadb, could we add a new sql_mode option? (feature request)
maybe COMPARE_DATETIME_AS_STRING to make things easier?
i´m considering this as a feature request instead of a bug
please close this bug, i reported a feature request since i understood that mysql isn´t doing a datetime comparision, it´s doing string comparision... \o/ mariadb rocks =)
new feature request id is MDEV-391