We won't fix this issue.

MySQL's behavior has changed in a revid:raveenkumar.hulakund@oracle.com-20120319175914-0wm47wvsglkl24ui as a fix for http://bugs.mysql.com/34280

In fact, the bug report is incorrect - the issue is not at all specific to invalid dates, all invalid values (numbers and strings too) in defaults are rejected. One can easily see from the test cases and bugdb that this was done to fix bug http://bugs.mysql.com/5903

Now Oracle has re-introduced this bug, that allows to break the data consistency and insert invalid data in the TRADITIONAL mode.

We won't do that.

Should we report it to MySQL?

sure, why not?

i meant, maybe you've seen some indication that they re-introduced it intentionally...
Okay, I'll file it if I don't find it among already submitted ones.

No, I'm pretty sure, that whoever fixed it, simply updated the test cases that started to fail with that "fix", without thinking for a moment why these test cases are there, and what were they testing.

I'll re-open it for now and will assign to myself so that I don't forget to submit it to MySQL bugbase – if it's a non-intentional change, we need to have it fixed there, since it breaks the cross-replication.

Filed in MySQL bug base as http://bugs.mysql.com/bug.php?id=68041.

Looking at the history of this issue, I can't help wondering if there is a good technical reason for it to be such a long-term problem. From a user's point of view, the expected behavior seems to be pretty obvious, and it was actually described back in bug #5903. There was no requirement to reject creation of a table, even less so to do it regardless the current mode, and from the bug comments it's not clear whether it was a judgement call or a technical necessity.

Instead, the reasonable behavior seems to be to handle creation and insertion separately, each time depending on the current mode. That is, no matter how the table was created and how it appeared in the server – maybe it was just placed in the data directory? – if the current mode prohibits inserting a certain value, it should be rejected, whether it's inserted explicitly or via default.

Anyway, I agree that their bugfix for bug #34280 is incorrect, closing this bugreport.

I was going to report a new bug, but it is probably better to comment here.
I was able to insert a zero-date in MariaDB 5.5.25. And I didn't use a zero default value, I used another default that was ignored.
As far as I can understand, this behaviour really seems to be wrong:

MariaDB [test]> SET sql_mode = 'NO_ZERO_DATE';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> CREATE TABLE t (c DATE DEFAULT '1994-01-01');
Query OK, 0 rows affected (0.12 sec)

MariaDB [test]> INSERT INTO t (c) VALUES ('0000-00-00');
Query OK, 1 row affected, 1 warning (0.09 sec)

MariaDB [test]> SHOW WARNINGS;
-------------------------------------------------------

-------------------------------------------------------

-------------------------------------------------------
1 row in set (0.00 sec)

MariaDB [test]> SELECT * FROM t;
------------

------------

------------
1 row in set (0.01 sec)

MariaDB [test]>

Hi Federico,

This is actually the documented behavior. NO_ZERO_DATE alone makes server to produce a warning on inserting a zero date, but still allows to do so, while NO_ZERO_DATE + strict mode, e.g. NO_ZERO_DATE,STRICT_ALL_TABLES should prohibit inserting a zero date at all.

Actually I didn't set 'STRICT_ALL_TABLES' - sorry for wasting your time.