Using NO_ZERO_IN_DATE without strict mode has some incompatible behaviors with MySQL

[YangKeao]

Bug Report

Using NO_ZERO_IN_DATE without strict mode is deprecated, so this issue is only here to record and it's marked as minor.

1. Minimal reproduce step (Required)

SET SQL_MODE='NO_ZERO_DATE'; CREATE TABLE t1 (id INT PRIMARY KEY, d DATE); CREATE VIEW v1 AS SELECT * FROM t1 WHERE d = '0000-00-00'; INSERT INTO t1 VALUES (1, '0000-00-00'); SELECT * FROM v1 ORDER BY id; DROP VIEW v1; DROP TABLE t1; SET SQL_MODE=default;
SET SQL_MODE='NO_ZERO_IN_DATE'; CREATE TABLE t1 (d DATE DEFAULT '2023-00-01'); INSERT INTO t1 VALUES (); SELECT * FROM t1 ORDER BY d; DROP TABLE t1; SET SQL_MODE=default;

2. What did you expect to see? (Required)

mysql> SET SQL_MODE='NO_ZERO_DATE'; CREATE TABLE t1 (id INT PRIMARY KEY, d DATE); CREATE VIEW v1 AS SELECT * FROM t1 WHERE d = '0000-00-00'; INSERT INTO t1 VALUES (1, '0000-00-00'); SELECT * FROM v1 ORDER BY id; DROP VIEW v1; DROP TABLE t1; SET SQL_MODE=default;
Query OK, 0 rows affected, 1 warning (0.01 sec)

Query OK, 0 rows affected (0.06 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 1 row affected, 1 warning (0.01 sec)

ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SET SQL_MODE='NO_ZERO_IN_DATE'; CREATE TABLE t1 (d DATE DEFAULT '2023-00-01'); INSERT INTO t1 VALUES (); SELECT * FROM t1 ORDER BY d; DROP TABLE t1; SET SQL_MODE=default;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.08 sec)

Query OK, 1 row affected (0.00 sec)

+------------+
| d          |
+------------+
| 0000-00-00 |
+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

3. What did you see instead (Required)

mysql> SET SQL_MODE='NO_ZERO_DATE'; CREATE TABLE t1 (id INT PRIMARY KEY, d DATE); CREATE VIEW v1 AS SELECT * FROM t1 WHERE d = '0000-00-00'; INSERT INTO t1 VALUES (1, '0000-00-00'); SELECT * FROM v1 ORDER BY id; DROP VIEW v1; DROP TABLE t1; SET SQL_MODE=default;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.05 sec)

Query OK, 0 rows affected (0.04 sec)

Query OK, 1 row affected, 1 warning (0.00 sec)

Empty set, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.10 sec)

Query OK, 0 rows affected (0.08 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SET SQL_MODE='NO_ZERO_IN_DATE'; CREATE TABLE t1 (d DATE DEFAULT '2023-00-01'); INSERT INTO t1 VALUES (); SELECT * FROM t1 ORDER BY d; DROP TABLE t1; SET SQL_MODE=default;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.06 sec)

Query OK, 1 row affected (0.02 sec)

+------------+
| d          |
+------------+
| 2023-00-01 |
+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.08 sec)

Query OK, 0 rows affected (0.00 sec)

4. What is your TiDB version? (Required)