Wrong result with UNION through VIEW/CTE and outer YEAR() predicate

I found a wrong-result issue involving UNION, VIEW/CTE expansion, a DATE column, and an outer YEAR() predicate.

The same query returns different results depending on whether the UNION result is evaluated through a VIEW, a CTE, or a TEMPORARY TABLE.

The VIEW and CTE versions return an empty set, while the equivalent TEMPORARY TABLE version returns the expected date row.

This looks incorrect because the table contains the date '1721-02-08'. YEAR('1721-02-08') is 1721, which is less than 2000, so the outer predicate YEAR(vc_1) < 2000 should preserve this row.

Environment:

MariaDB version:
12.2.2-MariaDB-ubu2404

How to repeat:

DROP VIEW IF EXISTS v0;

DROP TEMPORARY TABLE IF EXISTS temp_t;

DROP TABLE IF EXISTS t0;

CREATE TABLE t0 (

  c0 DATE

);

INSERT INTO t0 VALUES ('1721-02-08');

CREATE VIEW v0 AS

SELECT c0 AS vc_1 FROM t0

UNION

SELECT c0 AS vc_1 FROM t0;

SELECT vc_1 AS d_0

FROM v0

WHERE YEAR(vc_1) < 2000;

WITH CTE AS (

  SELECT c0 AS vc_1 FROM t0

  UNION

  SELECT c0 AS vc_1 FROM t0

)

SELECT vc_1 AS d_0

FROM CTE

WHERE YEAR(vc_1) < 2000;

CREATE TEMPORARY TABLE temp_t AS

SELECT c0 AS vc_1 FROM t0

UNION

SELECT c0 AS vc_1 FROM t0;

SELECT vc_1 AS d_0

FROM temp_t

WHERE YEAR(vc_1) < 2000;

Actual result:

The VIEW query returns:

Empty set

The CTE query returns:

Empty set

The TEMPORARY TABLE query returns:

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

| d_0        |

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

| 1721-02-08 |

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

Expected result:

The VIEW and CTE queries should return the same result as the TEMPORARY TABLE query:

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

| d_0        |

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

| 1721-02-08 |

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

Reason:

The table contains one row:

1721-02-08

The VIEW/CTE/TEMPORARY TABLE definition is:

SELECT c0 AS vc_1 FROM t0

UNION

SELECT c0 AS vc_1 FROM t0

Since both SELECT branches return the same date value, UNION should produce one distinct row:

1721-02-08

The outer predicate is:

WHERE YEAR(vc_1) < 2000

For the date value '1721-02-08', YEAR(vc_1) should evaluate to 1721, so the predicate should be true.

The TEMPORARY TABLE version confirms the expected result and returns the date row. However, the VIEW and CTE versions return an empty set.

Observed inconsistency:

The following forms should be semantically consistent:

1. VIEW:
SELECT vc_1 FROM v0 WHERE YEAR(vc_1) < 2000

2. CTE:
WITH CTE AS (...) SELECT vc_1 FROM CTE WHERE YEAR(vc_1) < 2000

3. TEMPORARY TABLE:
CREATE TEMPORARY TABLE temp_t AS ...;
SELECT vc_1 FROM temp_t WHERE YEAR(vc_1) < 2000

However, the VIEW and CTE versions return an empty set, while the TEMPORARY TABLE version returns the expected date row.

This suggests a wrong-result bug involving UNION, VIEW/CTE expansion, predicate pushdown, or evaluation of YEAR() on DATE values.