Wrong result when comparing STDDEV_SAMP(INT) with DECIMAL value

Description

MariaDB returns an unexpected result when the result of STDDEV_SAMP() over an INT column is compared directly with a DECIMAL value.

The underlying STDDEV_SAMP() value is:

1.581138830084190

Casting the same result to DECIMAL(18,6) produces:

1.581139

Therefore:

1.581138830084190 < 1.581139

should evaluate to TRUE.

However, MariaDB evaluates the direct comparison to FALSE.

Explicitly casting both operands to DOUBLE makes the same comparison evaluate to TRUE.

The equivalent testcase on MySQL 8.0.43 also evaluates the direct comparison to TRUE.

How to reproduce

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (i INT);

INSERT INTO t1 VALUES

(1),(2),(3),(4),(5);

SELECT

    STDDEV_SAMP(i) AS raw_value,

    CAST(

        STDDEV_SAMP(i)

        AS DECIMAL(30,15)

    ) AS precise_value,

    CAST(

        STDDEV_SAMP(i)

        AS DECIMAL(18,6)

    ) AS rounded_value,

    STDDEV_SAMP(i)

        <

    CAST(

        STDDEV_SAMP(i)

        AS DECIMAL(18,6)

    ) AS direct_cmp,

    CAST(

        STDDEV_SAMP(i)

        AS DOUBLE

    )

        <

    CAST(

        CAST(

            STDDEV_SAMP(i)

            AS DECIMAL(18,6)

        )

        AS DOUBLE

    ) AS double_cmp

FROM t1;

SELECT VERSION();

Actual result on MariaDB 12.2.2
-------------------------------------------------------------

-------------------------------------------------------------

-------------------------------------------------------------

Version:

12.2.2-MariaDB-ubu2404

The important inconsistency is:

direct_cmp = 0

double_cmp = 1

Although:

precise_value = 1.581138830084190

rounded_value = 1.581139

and therefore the first value is strictly smaller than the second.

Expected result

direct_cmp = 1

double_cmp = 1

MySQL comparison

The same testcase on MySQL 8.0.43 produces:

----------------------------------------------------------------------

----------------------------------------------------------------------

----------------------------------------------------------------------

Version:

8.0.43

Thus, both systems calculate essentially the same underlying STDDEV_SAMP() value and the same DECIMAL(18,6) value, but MariaDB returns a different result for the direct comparison.
The equivalent testcase was also cross-checked on PostgreSQL, where both the direct comparison and the explicit DOUBLE PRECISION comparison evaluate to TRUE.

Additional observation

The displayed MariaDB value:

1.5811

does not appear to indicate loss of internal precision.

For example:

SELECT CAST(STDDEV_SAMP(i) AS DECIMAL(30,15))

FROM t1;

returns:

1.581138830084190

Therefore, the issue appears to be related to numeric comparison or implicit type conversion rather than incorrect computation of STDDEV_SAMP() itself.

Environment

MariaDB:

12.2.2-MariaDB-ubu2404

OS:

Ubuntu 24.04