Skip to content

CEIL/FLOOR of a windowed DECIMAL returns unexpected results #11542

Description

@wanteatfruit

CEIL(d) and FLOOR(d) on a DECIMAL call sql.DecimalCtx.Ceil(num, num) / Floor(num, num), which mutate num in place. Window aggregates (MAX(d) OVER (PARTITION BY d), also MAX() OVER () / ORDER BY) hand every peer in the partition the same *apd.Decimal pointer. Projecting the window column next to CEIL/FLOOR therefore turns d into the rounded value for every row after the first peer.

ROUND does not; BIGINT/DOUBLE windows do not; a one-row partition does not; SELECT * without CEIL does not. index_builder.go in the same module already copies into a fresh *apd.Decimal before Ceil — the scalar function does not.

CREATE TABLE t (id BIGINT, d DECIMAL(10,2));
INSERT INTO t VALUES (1, 12.34), (2, 12.34);

SELECT id, d, CEIL(d)
FROM (SELECT id, MAX(d) OVER (PARTITION BY d) AS d FROM t) s
ORDER BY id;

However, removing the equivalent identity table would make the result correct:

CREATE TABLE t (id BIGINT, d DECIMAL(10,2));
INSERT INTO t VALUES (1, 12.34), (2, 12.34);
SELECT id, d, CEIL(d) FROM t ORDER BY id;
-- Expected/actual: (1, 12.34, 13), (2, 12.34, 13)

Reproducible on Dolt 8.0.31 / v2.2.3-49-ga995f245c @a995f245c, go-mysql-server v0.20.1-0.20260805191915-e5eafe0da809.

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't workingcorrectnessWe don't return the same result as MySQLcustomer issue

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions