Wrong result with CAST(DECIMAL AS DOUBLE) through VIEW/CTE and ROUND/GROUP BY

I found a wrong-result issue involving CAST(DECIMAL AS DOUBLE), VIEW/CTE expansion, ROUND(), GROUP BY, and HAVING.

The same query returns different numeric results depending on whether CAST(a.d AS DOUBLE) is evaluated through a VIEW, a CTE, or a TEMPORARY TABLE.

The VIEW and CTE versions return:

100000000000000000

while the equivalent TEMPORARY TABLE version returns:

629702948047463700000000000000000000000000000000000000

This looks incorrect because the input DECIMAL value is around 6.297029480474637e53. After CAST(a.d AS DOUBLE) and ROUND(g), the result should still have that magnitude. The VIEW and CTE result 100000000000000000 is many orders of magnitude smaller.

Environment:

MariaDB version:
12.2.2-MariaDB-ubu2404

Related issue:

This may be related to the issue involving POWER(BIGINT, 2) through VIEW/CTE and ROUND/GROUP BY, but this test case is not identical. It uses CAST(DECIMAL AS DOUBLE), GROUP BY, and HAVING COUNT(DISTINCT h).

How to repeat:

DROP VIEW IF EXISTS v0;

DROP TEMPORARY TABLE IF EXISTS temp_t;

DROP TABLE IF EXISTS b;

CREATE TABLE b (

  d DECIMAL(62),

  f FIXED(25)

);

INSERT INTO b VALUES (

  '629702948047463716119214871241075636959691004970309482.54633769',

  '5221133321218405129522370'

);

CREATE VIEW v0 AS

SELECT

  CAST(a.d AS DOUBLE) AS g,

  a.f AS h

FROM b AS a

JOIN b;

SELECT ROUND(g) AS i

FROM v0

GROUP BY i

HAVING COUNT(DISTINCT h);

WITH CTE AS (

  SELECT

    CAST(a.d AS DOUBLE) AS g,

    a.f AS h

  FROM b AS a

  JOIN b

)

SELECT ROUND(g) AS i

FROM CTE

GROUP BY i

HAVING COUNT(DISTINCT h);

CREATE TEMPORARY TABLE temp_t AS

SELECT

  CAST(a.d AS DOUBLE) AS g,

  a.f AS h

FROM b AS a

JOIN b;

SELECT ROUND(g) AS i

FROM temp_t

GROUP BY i

HAVING COUNT(DISTINCT h);

Actual result:

The VIEW query returns:

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

| i                  |

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

| 100000000000000000 |

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

The CTE query returns:

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

| i                  |

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

| 100000000000000000 |

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

The TEMPORARY TABLE query returns:

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

| i                                                      |

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

| 629702948047463700000000000000000000000000000000000000 |

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

Expected result:

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

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

| i                                                      |

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

| 629702948047463700000000000000000000000000000000000000 |

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

Reason:

The table contains one DECIMAL value:

629702948047463716119214871241075636959691004970309482.54633769

The expression is:

CAST(a.d AS DOUBLE)

The result is then rounded with:

ROUND(g)

Since the input value is around 6.297029480474637e53, the rounded result should also have that magnitude.

The TEMPORARY TABLE version confirms this expected magnitude and returns:

629702948047463700000000000000000000000000000000000000

However, the VIEW and CTE versions return:

100000000000000000

This is many orders of magnitude smaller than expected.

Observed inconsistency:

The following forms should be semantically consistent:

1. VIEW:
SELECT ROUND(g) FROM v0 GROUP BY i HAVING COUNT(DISTINCT h)

2. CTE:
WITH CTE AS (...) SELECT ROUND(g) FROM CTE GROUP BY i HAVING COUNT(DISTINCT h)

3. TEMPORARY TABLE:
CREATE TEMPORARY TABLE temp_t AS ...;
SELECT ROUND(g) FROM temp_t GROUP BY i HAVING COUNT(DISTINCT h)

However, the VIEW and CTE versions return a much smaller value than the TEMPORARY TABLE version.

This suggests a wrong-result bug involving CAST(DECIMAL AS DOUBLE), numeric precision/type handling, VIEW/CTE expansion, ROUND(), GROUP BY, or HAVING.