Description:
sql_mode is default and includes NO_ZERO_IN_DATE
When casting a string to date which contains a 00 month or day:
mysql> select cast('2012-00-00' as date);
+----------------------------+
| cast('2012-00-00' as date) |
+----------------------------+
| 2012-00-00 |
+----------------------------+
1 row in set (0.00 sec)
the cast is successful which is a bit surprising, because according to the doc:
The NO_ZERO_IN_DATE mode affects whether the server permits dates in which the year part is nonzero but the month or day part is 0. (This mode affects dates such as '2010-00-01' or '2010-01-00', but not '0000-00-00'.)
It is even more surprising since:
mysql> select cast('0000-00-00' as date);
+----------------------------+
| cast('0000-00-00' as date) |
+----------------------------+
| NULL |
+----------------------------+
1 row in set, 1 warning (0.00 sec)
So it looks like NO_ZERO_DATE check is enforced, but not the NO_ZERO_IN_DATE one.
Another interesting discrepancy is:
mysql> select date '2012-00-00';
ERROR 1525 (HY000): Incorrect DATE value: '2012-00-00'
It seems it should behave the same as: select cast('2012-00-00' as date);
and yet one allows for such a date and the other does not.
How to repeat:
select cast('2012-00-00' as date);
select cast('0000-00-00' as date);
select date '2012-00-00';
Suggested fix:
Checking against NO_ZERO_IN_DATE in sql_time.cc
bool str_to_datetime_with_warn(String *str, MYSQL_TIME *l_time,
my_time_flags_t flags) {
MYSQL_TIME_STATUS status;
THD *thd = current_thd;
if (thd->variables.sql_mode & MODE_NO_ZERO_DATE) flags |= TIME_NO_ZERO_DATE;
already adds the NO_ZERO_DATE flag. It would make sense if it also added the NO_ZERO_IN_DATE flag:
if (thd->variables.sql_mode & MODE_NO_ZERO_IN_DATE)
flags |= TIME_NO_ZERO_IN_DATE;
Description: sql_mode is default and includes NO_ZERO_IN_DATE When casting a string to date which contains a 00 month or day: mysql> select cast('2012-00-00' as date); +----------------------------+ | cast('2012-00-00' as date) | +----------------------------+ | 2012-00-00 | +----------------------------+ 1 row in set (0.00 sec) the cast is successful which is a bit surprising, because according to the doc: The NO_ZERO_IN_DATE mode affects whether the server permits dates in which the year part is nonzero but the month or day part is 0. (This mode affects dates such as '2010-00-01' or '2010-01-00', but not '0000-00-00'.) It is even more surprising since: mysql> select cast('0000-00-00' as date); +----------------------------+ | cast('0000-00-00' as date) | +----------------------------+ | NULL | +----------------------------+ 1 row in set, 1 warning (0.00 sec) So it looks like NO_ZERO_DATE check is enforced, but not the NO_ZERO_IN_DATE one. Another interesting discrepancy is: mysql> select date '2012-00-00'; ERROR 1525 (HY000): Incorrect DATE value: '2012-00-00' It seems it should behave the same as: select cast('2012-00-00' as date); and yet one allows for such a date and the other does not. How to repeat: select cast('2012-00-00' as date); select cast('0000-00-00' as date); select date '2012-00-00'; Suggested fix: Checking against NO_ZERO_IN_DATE in sql_time.cc bool str_to_datetime_with_warn(String *str, MYSQL_TIME *l_time, my_time_flags_t flags) { MYSQL_TIME_STATUS status; THD *thd = current_thd; if (thd->variables.sql_mode & MODE_NO_ZERO_DATE) flags |= TIME_NO_ZERO_DATE; already adds the NO_ZERO_DATE flag. It would make sense if it also added the NO_ZERO_IN_DATE flag: if (thd->variables.sql_mode & MODE_NO_ZERO_IN_DATE) flags |= TIME_NO_ZERO_IN_DATE;