Stored programs does not validate SQLs when using certain SQL modes

10.4.29-opt>SET sql_mode = '';

Query OK, 0 rows affected (0.000 sec)

10.4.29-opt>CREATE TABLE t1 (a INT);

Query OK, 0 rows affected (0.011 sec)

10.4.29-opt>CREATE TABLE t2 (a datetime);

Query OK, 0 rows affected (0.014 sec)

10.4.29-opt>CREATE TRIGGER trg AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES('2023-02-31');

Query OK, 0 rows affected (0.006 sec)

10.4.29-opt>INSERT INTO t1 (a) VALUES (1);

Query OK, 1 row affected (0.003 sec)

10.4.29-opt>SELECT * FROM t2;

+---------------------+

| a                   |

+---------------------+

| 0000-00-00 00:00:00 |

+---------------------+

1 row in set (0.000 sec)

10.4.29-opt>SET sql_mode = 'ALLOW_INVALID_DATES';

Query OK, 0 rows affected (0.000 sec)

10.4.29-opt>INSERT INTO t1 (a) VALUES (2);

Query OK, 1 row affected (0.003 sec)

10.4.29-opt>SELECT * FROM t2;

+---------------------+

| a                   |

+---------------------+

| 0000-00-00 00:00:00 |

| 0000-00-00 00:00:00 |

+---------------------+

2 rows in set (0.005 sec)

10.4.29-opt>

10.11.3-opt>select * from t2;

+---------------------+

| a                   |

+---------------------+

| 0000-00-00 00:00:00 |

| 2023-02-31 00:00:00 |

+---------------------+

2 rows in set (0.000 sec)

10.11.3-opt>

SET sql_mode = '';

DROP TABLE t1;

CREATE TABLE t1(a datetime);

delimiter //

CREATE PROCEDURE p1()

BEGIN

  INSERT INTO t1 VALUES ('2023-02-31');

END //

delimiter ;

call p1();

SELECT * FROM t1;

SET sql_mode = 'ALLOW_INVALID_DATES';

call p1();

SELECT * FROM t1;