Inconsistent behavior of INSERT…SELECT vs variable path when CAST fails

On the write path INSERT INTO … SELECT, when the SELECT list contains CAST(column AS DATE) and the conversion fails (e.g. a CHAR value that cannot be parsed as a valid date), the INSERT fails with ERROR 1292 (22007). The equivalent flow using a variable succeeds and inserts 0000-00-00. Both approaches represent the same logical operation and should behave consistently.

Summary

When using INSERT INTO … SELECT, if the SELECT list includes CAST(column AS DATE) and the conversion fails (e.g. a CHAR value that cannot be parsed as a valid date), the INSERT fails with ERROR 1292 (22007). The same value passed via a variable and then inserted succeeds. Both approaches are logically equivalent; the behavior should be consistent.

Environment

• Version: MariaDB 11.8.5 (11.8.5-MariaDB-ubu2204)

Steps to Reproduce

Path A – Direct INSERT SELECT (fails)

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 DATE */

CREATE TABLE t2 (d DATE);

-- Path A: Direct INSERT SELECT — FAILS

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

Result: ERROR 1292 (22007): Incorrect datetime value: '7621214'

Path B – Same value via variable (succeeds)

USE test_bug;

DROP TABLE IF EXISTS t2;

CREATE TABLE t2 (d DATE);

-- Path B: Same value via variable — SUCCEEDS

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 = 0000-00-00.

Observed Behavior

Expected Behavior

Both Path A and Path B should yield the same outcome:

• Either INSERT ... SELECT succeeds and inserts the same value as Path B (0000-00-00 or NULL, depending on intended semantics), or
• Both paths fail with the same error.

The current inconsistency makes the behavior unpredictable and violates the principle of least surprise.

Additional Notes

• SELECT CAST(c1 AS DATE) FROM t1 returns NULL with a warning in strict mode; the result is well-defined.
• The variable path inserts 0000-00-00, not NULL — this may be another behavioral nuance worth clarifying.