DELETE statement with CAST() executes differently within and outside a transaction

[sayJason]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

/* init */ CREATE TABLE t0(c0 BOOL);
/* init */ REPLACE INTO t0 VALUES (true);

/* tx */ BEGIN;
/* tx */ UPDATE t0 SET c0=false;
/* tx */ DELETE FROM t0 WHERE CAST('-' AS DATE); -- report an error
/* tx */ COMMIT;

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

tidb> UPDATE t0 SET c0=false;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

tidb> DELETE FROM t0 WHERE CAST('-' AS DATE);
Query OK, 0 rows affected, 1 warning (0.00 sec)

tidb> SHOW WARNINGS;
+---------+------+--------------------------------------------------+
| Level   | Code | Message                                          |
+---------+------+--------------------------------------------------+
| Warning | 1292 | evaluation failed: Incorrect datetime value: '-' |
+---------+------+--------------------------------------------------+
1 row in set (0.00 sec)

3. What did you see instead (Required)

tidb> BEGIN;
Query OK, 0 row affected (0.00 sec)

tidb> UPDATE t0 SET c0=false;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

tidb> DELETE FROM t0 WHERE CAST('-' AS DATE);
ERROR 1292 (22007): Incorrect datetime value: '-'

4. What is your TiDB version? (Required)

Release Version: v8.1.0
Edition: Community
Git Commit Hash: 945d07c5d5c7a1ae212f6013adfb187f2de24b23
Git Branch: HEAD
UTC Build Time: 2024-05-21 03:51:57
GoVersion: go1.21.10
Race Enabled: false
Check Table Before Drop: false
Store: tikv