Bug #120079 Incorrect result when casting VARIANCE() inside IF() expression to SIGNED

• Submitted: 17 Mar 5:04
• Modified: 17 Mar 11:12
• 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, type-conversion, VARIANCE, wrong-result

[17 Mar 5:04] Wang Ojiken

Description:
Casting the result of VARIANCE() to SIGNED produces inconsistent results depending on whether the expression is wrapped inside an IF() function.

The following two expressions are semantically equivalent:

CAST(VARIANCE(t2.c0) AS SIGNED)

and

CAST(IF(FALSE, '-1', VARIANCE(t2.c0)) AS SIGNED)

Since the condition is FALSE, the IF expression should always evaluate to VARIANCE(t2.c0).

However, the results differ significantly, indicating incorrect type handling or evaluation behavior in the presence of mixed-type branches (STRING vs DOUBLE).

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

CREATE INDEX i0 ON t2 (c0);

INSERT INTO t2 (c0) VALUES (1);
INSERT INTO t2 (c0) VALUES (127790617);

-- Query 1
SELECT CAST(VARIANCE(t2.c0) AS SIGNED) FROM t2;

-- Query 2
SELECT CAST(IF(FALSE, '-1', VARIANCE(t2.c0)) AS SIGNED) FROM t2;

Actual result:
Query 1:
4082610384414864

Query 2:
4

Expected result:
Both queries should return the same value:
4082610384414864

Suggested fix:
1.Ensure IF() expression does not promote result type incorrectly when one branch is unreachable
2.Delay type coercion until after branch selection when condition is constant
3.Avoid coercing numeric expressions into string context due to unused branches.

[17 Mar 11:12] Roy Lyseng

Thank you for the bug report.
However, this is not a bug.
With an IF statement, the coerced data type becomes string, and the calculation is correct.
Workaround to obtain the desired output: Replace '-1' with the equivalent numeric value -1.