Inconsistent YEAR value display/type for scalar subquery through VIEW/CTE and TEMPORARY TABLE

I found an inconsistency involving the YEAR data type, a scalar subquery, VIEW/CTE expansion, and CREATE TEMPORARY TABLE AS SELECT.

The same scalar subquery result is displayed differently depending on whether it is evaluated through a VIEW/CTE or materialized into a TEMPORARY TABLE.

The VIEW and CTE versions return 0, while the TEMPORARY TABLE version returns 0000.

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 a;

CREATE TABLE a (

  b YEAR

);

INSERT INTO a VALUES (0);

CREATE VIEW v0 AS

SELECT (SELECT b FROM a) AS e

FROM a;

SELECT e

FROM v0;

WITH CTE AS (

  SELECT (SELECT b FROM a) AS e

  FROM a

)

SELECT e

FROM CTE;

CREATE TEMPORARY TABLE temp_t AS

SELECT (SELECT b FROM a) AS e

FROM a;

SELECT e

FROM temp_t;

Actual result:

The VIEW query returns:

+------+

| e    |

+------+

|    0 |

+------+

The CTE query returns:

+------+

| e    |

+------+

|    0 |

+------+

The TEMPORARY TABLE query returns:

+------+

| e    |

+------+

| 0000 |

+------+

Expected result:

The VIEW, CTE, and TEMPORARY TABLE forms should be consistent in how the scalar subquery result of a YEAR column is typed or displayed.

Reason:

The table contains one YEAR value inserted as:

INSERT INTO a VALUES (0);

The scalar subquery is:

SELECT b FROM a

The same scalar subquery is used in all three forms:

1. VIEW
2. CTE
3. CREATE TEMPORARY TABLE AS SELECT

However, the VIEW and CTE versions display the value as 0, while the TEMPORARY TABLE version displays it as 0000.

This suggests a possible inconsistency in YEAR type metadata preservation, scalar subquery type inference, or CREATE TEMPORARY TABLE AS SELECT materialization.