Bug #120080 Inconsistent result when using LEAST() with string literal and VAR_POP(), especially when combined with CAST to SIGNED

• Submitted: 17 Mar 5:35
• Modified: 17 Mar 11:23
• Reporter: Wang Ojiken
• Status: Not a Bug
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 9.6.0
• OS: Any
• CPU Architecture: Any
• Tags: aggregate-function, cast, expression, LEAST, type-conversion, VARIANCE, wrong-result

[17 Mar 5:35] Wang Ojiken

Description:
When using LEAST() with a string literal and an aggregate function (VAR_POP), casting the result to SIGNED produces an incorrect result.

The following query:

SELECT CAST(LEAST("135", VAR_POP((t0.c0))) AS SIGNED) FROM t0;

returns:

1

However, evaluating the inner expression:

SELECT LEAST("135", VAR_POP((t0.c0))) FROM t0;

returns:

1.9432843334915213e17

This indicates that:

CAST(LEAST("135", VAR_POP(...)) AS SIGNED) ≠ CAST(1.9432843334915213e17 AS SIGNED)

which is inconsistent and suggests incorrect evaluation or type coercion.

How to repeat:
DROP TABLE IF EXISTS t0;
CREATE TABLE t0 (
    c0 BIGINT NULL
);

CREATE INDEX i0 ON t0 (c0);

INSERT INTO t0 (c0) VALUES (1);
INSERT INTO t0 (c0) VALUES (881653977);

-- Query 1
SELECT CAST(LEAST("135", VAR_POP((t0.c0))) AS SIGNED) FROM t0;

-- Query 2
SELECT LEAST("135", VAR_POP((t0.c0))) FROM t0;

Actual result:

Query 1:
1

Query 2:
1.9432843334915213e17

Expected result:
The result of CAST(... AS SIGNED) should be consistent with the evaluated value of the inner expression.
That is:
CAST(LEAST("135", VAR_POP(...)) AS SIGNED)

should be equivalent to:

CAST(1.9432843334915213e17 AS SIGNED)

which should yield a large integer (subject to DOUBLE precision), not 1.

Suggested fix:
1. Ensure that LEAST() is evaluated before CAST(), without altering its result due to type coercion side effects.

2. Fix inconsistent type inference when mixing string literals and numeric aggregate functions inside LEAST().

3. Prevent incorrect intermediate conversions that lead to loss of magnitude (e.g., conversion to string and partial parsing).

4. Ensure CAST(... AS SIGNED) operates on the actual evaluated numeric result, not a coerced or truncated intermediate value.

[17 Mar 11:23] Roy Lyseng

Thank you for the bug report.
However, this is not a bug.
LEAST when used with at least one character string arguments returns a character string result, and CAST(<char string> AS SIGNED) behaves properly.
The type derivation rules must be kept simple and deterministic.
If you want correct numeric results, it is important to ensure that all arguments to respective functions are numeric and not character strings.
A wrokaround may be to add explicit CAST to numeric values where appropriate.