MySQL Bugs: #119998: ERROR 1292 Incorrect value on INSERT…SELECT when CAST result is NULL

Bug #119998	ERROR 1292 Incorrect value on INSERT…SELECT when CAST result is NULL
Submitted:	6 Mar 14:56	Modified:	9 Mar 10:57
Reporter:	mu mu	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.4.8	OS:	Ubuntu (22.04)
Assigned to:		CPU Architecture:	Any

Description:
On the write path INSERT INTO … SELECT, when the SELECT list contains CAST(column AS DATE) and that conversion fails (e.g. a CHAR value that cannot be parsed as a valid date; it appears as NULL in a read-only SELECT), the INSERT fails with:

ERROR 1292 (22007): Incorrect value

The following cases work as expected:

- INSERT SELECT with literal NULL, subquery returning NULL, or expressions like 1/0 that evaluate to NULL — NULL is inserted successfully.
- SET @d = (SELECT CAST(c1 AS DATE) FROM t1); INSERT INTO t2 VALUES (@d); — the same CAST result assigned to a variable and then inserted succeeds and stores NULL.

How to repeat:
CREATE DATABASE IF NOT EXISTS test_bug;
USE test_bug;

DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
CREATE TABLE t1 (c1 CHAR(20));
INSERT INTO t1 VALUES (7621214);   /* CHAR 7621214 cannot be parsed as valid DATE; CAST yields NULL */
CREATE TABLE t2 (d DATE);

INSERT INTO t2 SELECT CAST(c1 AS DATE) FROM t1;

Expected result

- `INSERT INTO t2 SELECT CAST(c1 AS DATE) FROM t1` should succeed and insert one row with NULL into `t2.d` (DATE columns allow NULL).
- `SELECT * FROM t2` should then return one row with `d` = NULL.

Example:

| d    |
|------|
| NULL |

Actual result

- Executing `INSERT INTO t2 SELECT CAST(c1 AS DATE) FROM t1` returns:
  - ERROR 1292 (22007): Incorrect value
- No row is inserted; `SELECT * FROM t2` shows no new data (or table remains empty).

Same data, only the way the value is written differs. Behavior is inconsistent.

USE test_bug;
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
CREATE TABLE t1 (c1 CHAR(20));
INSERT INTO t1 VALUES (7621214);
CREATE TABLE t2 (d DATE);

SET @d = (SELECT CAST(c1 AS DATE) FROM t1);
INSERT INTO t2 VALUES (@d);
SELECT * FROM t2;

Result: Succeeds; `SELECT * FROM t2` returns one row with `d = NULL`.

Thank you for the bug report.
This may seem inconsistent, but this is actually how the server works.
It operates in strict mode for data change operations like INSERT, UPDATE and
DELETE, but it operates in non-strict mode for SELECT operations.
Thus, the SELECT into the variable completes with a warning, whereas the
INSERT completes with an error.
Setting the server in non-strict mode will make the results seem consistent,
but this may of course lead to more inconsistent data inserted in the database,
thus we do not recommend it.

Thank you for the clarification, Roy. I have performed further testing based on your feedback, and I believe the current behavior points to a deeper architectural inconsistency rather than just a Strict Mode side effect.

1. Violation of Semantic Equivalence
Logically, the following two paths should be equivalent:

Path A (Single Statement): INSERT INTO t2 SELECT CAST(c1 AS DATE) FROM t1; -> Fails (Error 1292)

Path B (User Variable): SET @d = (SELECT CAST(c1 AS DATE) FROM t1); INSERT INTO t2 VALUES (@d); -> Succeeds (Inserts NULL)

In both cases, the source expression is identical. If the server's goal is to prevent "incorrect values" from being inserted, Path B represents a significant bypass of that logic. If the server accepts the result of the CAST via a variable, it should logically accept it via a direct SELECT list.

2. Inconsistent Data Results in Non-Strict Mode
Even more concerning is the behavior when Strict Mode is disabled. While both paths "succeed," they produce different data:

Path A: Inserts NULL.

Path B: Inserts '0000-00-00' (Zero date).

This proves that the inconsistency is not just about "Error vs. Warning," but about how the expression's result is physically materialized. The same CAST expression yields different values depending on whether it is piped directly into an INSERT or passed through a variable.

3. Impact on Data Integrity and Migration
This inconsistency makes data migration and ETL processes unpredictable. A developer testing a transformation via SELECT or user variables will see one result, but the production INSERT...SELECT will either fail or store different data.

I don't see the behavior that you describe.

When I disable strict mode with e.g.

 set sql_mode='';

I get NULL inserted by both statements.