Wrong result: DAYOFWEEK() divided through VIEW/CTE returns 0.9999 instead of 1.5000

I found a wrong-result issue involving DAYOFWEEK(), VIEW/CTE, a derived table, LIMIT, and arithmetic division.

The same query returns different numeric results depending on whether DAYOFWEEK(b) is evaluated through a VIEW, a CTE, or a TEMPORARY TABLE.

The VIEW and CTE versions return 0.9999, while the equivalent TEMPORARY TABLE version returns 1.5000.

This looks incorrect because DAYOFWEEK('3670-04-22') should evaluate to 3. Therefore, d / 2 should evaluate to 1.5000, not 0.9999.

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 DATE

);

INSERT INTO a VALUES ('3670-04-22');

CREATE VIEW v0 AS

SELECT DAYOFWEEK(b) AS d

FROM a;

SELECT e

FROM (

  SELECT d / 2 AS e

  FROM v0

  LIMIT 2

) AS x;

WITH CTE AS (

  SELECT DAYOFWEEK(b) AS d

  FROM a

)

SELECT e

FROM (

  SELECT d / 2 AS e

  FROM CTE

  LIMIT 2

) AS x;

CREATE TEMPORARY TABLE temp_t AS

SELECT DAYOFWEEK(b) AS d

FROM a;

SELECT e

FROM (

  SELECT d / 2 AS e

  FROM temp_t

  LIMIT 2

) AS x;

Actual result:

The VIEW query returns:

+--------+

| e      |

+--------+

| 0.9999 |

+--------+

The CTE query returns:

+--------+

| e      |

+--------+

| 0.9999 |

+--------+

The TEMPORARY TABLE query returns:

+--------+

| e      |

+--------+

| 1.5000 |

+--------+

Expected result:

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

+--------+

| e      |

+--------+

| 1.5000 |

+--------+

Reason:

The inserted DATE value is:

'3670-04-22'

DAYOFWEEK(b) should evaluate to 3 for this row. Therefore:

d / 2

should evaluate to:

1.5000

The TEMPORARY TABLE version confirms this result. However, when the same expression is evaluated through a VIEW or CTE and then divided in an outer derived table, the result becomes 0.9999.

Observed inconsistency:

The following forms should be semantically consistent:

1. VIEW:
SELECT e FROM (SELECT d / 2 AS e FROM v0 LIMIT 2) AS x

2. CTE:
WITH CTE AS (...) SELECT e FROM (SELECT d / 2 AS e FROM CTE LIMIT 2) AS x

3. TEMPORARY TABLE:
CREATE TEMPORARY TABLE temp_t AS ...;
SELECT e FROM (SELECT d / 2 AS e FROM temp_t LIMIT 2) AS x

However, the VIEW and CTE versions return 0.9999, while the TEMPORARY TABLE version returns 1.5000.

This suggests a wrong-result bug in the optimizer/execution path for VIEW/CTE expansion, possibly involving DAYOFWEEK(), expression substitution, type/precision handling, derived tables, or LIMIT.