Wrong result with POWER(BIGINT, 2) through VIEW/CTE and ROUND/GROUP BY

I found a wrong-result issue involving POWER() on a BIGINT value, VIEW/CTE expansion, ROUND(), and GROUP BY.

The same query returns different numeric results depending on whether POWER(b.c0, 2) 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:

33013796717057550000000000000000000000

This looks incorrect because POWER(-5745763371133338700, 2) should be a very large positive value around 3.301379671705755e37. The VIEW and CTE result 100000000000000000 is many orders of magnitude smaller.

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 BIGINT

);

INSERT INTO t0 VALUES (-5745763371133338700);

CREATE VIEW v0 AS

SELECT

  1 AS vc_0,

  POWER(b.c0, 2) AS vc_1

FROM t0 AS d

JOIN t0 AS b;

SELECT ROUND(vc_1, 0) AS gk1, COUNT(DISTINCT vc_0)

FROM v0

GROUP BY 1;

WITH CTE AS (

  SELECT

    1 AS vc_0,

    POWER(b.c0, 2) AS vc_1

  FROM t0 AS d

  JOIN t0 AS b

)

SELECT ROUND(vc_1, 0) AS gk1, COUNT(DISTINCT vc_0)

FROM CTE

GROUP BY 1;

CREATE TEMPORARY TABLE temp_t AS

SELECT

  1 AS vc_0,

  POWER(b.c0, 2) AS vc_1

FROM t0 AS d

JOIN t0 AS b;

SELECT ROUND(vc_1, 0) AS gk1, COUNT(DISTINCT vc_0)

FROM temp_t

GROUP BY 1;

Actual result:

The VIEW query returns:

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

| gk1                | COUNT(DISTINCT vc_0) |

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

| 100000000000000000 |                    1 |

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

The CTE query returns:

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

| gk1                | COUNT(DISTINCT vc_0) |

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

| 100000000000000000 |                    1 |

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

The TEMPORARY TABLE query returns:

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

| gk1                                    | COUNT(DISTINCT vc_0) |

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

| 33013796717057550000000000000000000000 |                    1 |

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

Expected result:

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

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

| gk1                                    | COUNT(DISTINCT vc_0) |

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

| 33013796717057550000000000000000000000 |                    1 |

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

Reason:

The table contains one BIGINT value:

-5745763371133338700

The expression is:

POWER(b.c0, 2)

Squaring this value should produce a very large positive value, approximately:

3.301379671705755e37

Therefore, applying:

ROUND(vc_1, 0)

should still produce a value around 3.301379671705755e37, not 100000000000000000.

The TEMPORARY TABLE version confirms this expected magnitude. However, the VIEW and CTE versions return 100000000000000000, which is many orders of magnitude smaller.

Observed inconsistency:

The following forms should be semantically consistent:

1. VIEW:
SELECT ROUND(vc_1, 0), COUNT(DISTINCT vc_0) FROM v0 GROUP BY 1

2. CTE:
WITH CTE AS (...) SELECT ROUND(vc_1, 0), COUNT(DISTINCT vc_0) FROM CTE GROUP BY 1

3. TEMPORARY TABLE:
CREATE TEMPORARY TABLE temp_t AS ...;
SELECT ROUND(vc_1, 0), COUNT(DISTINCT vc_0) FROM temp_t GROUP BY 1

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

This suggests a wrong-result bug involving POWER(), BIGINT values, numeric precision/type handling, VIEW/CTE expansion, ROUND(), or GROUP BY expression evaluation.