Window RANGE frame with non-integral offset over integer sort key is rounded

10.6.28, 10.11.19, 11.4.13, 11.8.9, 12.1.2, 12.3.3, 13.0.2, 13.2.0 (main,
d45cf75c, 2026-09-30). Originally found on 10.6.23. Not a regression – every
release tried behaves the same.

===

A RANGE window frame is defined by values: RANGE BETWEEN d PRECEDING AND d
FOLLOWING covers the rows whose ORDER BY value lies in [v - d, v + d]. When the
ORDER BY expression is an integer type and d is not an integer, MariaDB rounds
each frame bound to an integer before comparing it with the sort key, so the
frame covers rows it should exclude. No warning is raised.

For keys 0..9 and d = 0.5 the exact frame around v = 5 is [4.5, 5.5], which holds
one row. MariaDB computes the bounds 5 - 0.5 and 5 + 0.5 as integers – 5 and 6
(half away from zero) – and returns a frame of two rows:

CREATE TABLE d1 (i INT PRIMARY KEY, x INT);

INSERT INTO d1 VALUES (0,1),(1,1),(2,1),(3,1),(4,1),(5,1),(6,1),(7,1),(8,1),(9,1);

SELECT i, COUNT(*) OVER (ORDER BY i RANGE BETWEEN 0.5 PRECEDING AND 0.5 FOLLOWING) c

FROM d1 ORDER BY i;

MariaDB 13.2.0 (main) / 13.0.2 / 10.6.28:

  i  c

  0  2

  1  2

  2  2

  3  2

  4  2

  5  2

  6  2

  7  2

  8  2

  9  1

expected (and MySQL 26.7.0): c = 1 on every row

The reference is the frame's own definition, evaluated by the same server:

SELECT COUNT(*) FROM d1 b WHERE b.i >= 5 - 0.5 AND b.i <= 5 + 0.5;   -- 1

Frame width at i = 5 for a range of offsets (window = MariaDB, exact = the
self-join above; MySQL 26.7.0 equals "exact" throughout):

  offset   window   exact

  0.25        1       1

  0.49        1       1

  0.50        2       1    <<<

  0.51        3       1    <<<

  0.75        3       1    <<<

  0.99        3       1    <<<

  1.00        3       3

  1.49        3       3

  1.50        4       3    <<<

  1.51        5       3    <<<

  2.00        5       5

  2.50        6       5    <<<

Integral offsets are correct. The fault appears exactly when the fractional part
reaches 0.5 and grows with the offset. One-sided frames show the rounded bounds
directly:

  offset   d PRECEDING AND CURRENT ROW     CURRENT ROW AND d FOLLOWING

           window  exact                   window  exact

  0.49       1       1                       1       1

  0.50       1       1                       2       1    <<<   (5 + 0.5 -> 6)

  0.51       2       1    <<< (4.49 -> 4)    2       1    <<<   (5.51 -> 6)

  1.50       2       2                       3       2    <<<   (6.5 -> 7)

  1.51       3       2    <<< (3.49 -> 3)    3       2    <<<   (6.51 -> 7)

The rounding is visible on negative keys too – for key 0 the bounds -0.5 and 0.5
become -1 and 1 and the frame holds three rows:

CREATE TABLE d3 (i INT);

INSERT INTO d3 VALUES (-2),(-1),(0),(1),(2);

SELECT i, COUNT(*) OVER (ORDER BY i RANGE BETWEEN 0.5 PRECEDING AND 0.5 FOLLOWING) c

FROM d3 ORDER BY i;

-- MariaDB: (-2,1) (-1,2) (0,3) (1,2) (2,1)      expected: every c = 1

It is not confined to COUNT. SUM over the same frame returns 1,3,5,...,17,9 for
keys 0..9 where the exact answer is 0..9, and on a 40-row fixture (i = id % 10,
x = id) SUM OVER (ORDER BY i RANGE BETWEEN 0.5 PRECEDING AND 0.5 FOLLOWING)
differs from the self-join reference on 36 of 40 rows; with 1.5 PRECEDING AND
1.5 FOLLOWING on 32 of 40.

The type of the sort key decides it. With the same offset 0.5:

  ORDER BY type     window   exact

  INT                  2       1    <<<

  BIGINT               2       1    <<<

  SMALLINT             2       1    <<<

  DECIMAL(10,2)        1       1

  DOUBLE               1       1

  FLOAT                1       1

and changing only the type of the ORDER BY expression over the same INT column
fixes it, which is also the workaround:

SELECT c FROM (SELECT i, COUNT(*) OVER (ORDER BY CAST(i AS DECIMAL(10,1))

          RANGE BETWEEN 0.5 PRECEDING AND 0.5 FOLLOWING) c FROM d1) z WHERE i = 5;  -- 1

SELECT c FROM (SELECT i, COUNT(*) OVER (ORDER BY i + 0.0

          RANGE BETWEEN 0.5 PRECEDING AND 0.5 FOLLOWING) c FROM d1) z WHERE i = 5;  -- 1

SELECT c FROM (SELECT i, COUNT(*) OVER (ORDER BY i + 0

          RANGE BETWEEN 0.5 PRECEDING AND 0.5 FOLLOWING) c FROM d1) z WHERE i = 5;  -- 2 (still integer)

The rounding mode follows the offset's type: a DECIMAL offset rounds half away
from zero (frame [5,6], width 2); a DOUBLE offset 0.5e0 rounds half to even
(bounds 4 and 6, width 3). Both are wrong; MySQL gives 1 for both.

Where it happens (main, d45cf75c): sql/sql_window.cc, Frame_range_n_top and
Frame_range_n_bottom. Their constructors create the comparison cache from the
ORDER BY expression,

    range_expr= (Cached_item_item*) new_Cached_item(thd, src_expr, FALSE);

    ...

    item_add= new (thd->mem_root) Item_func_plus(thd, src_expr, n_val);   // or Item_func_minus

so for an integer sort key range_expr is a Cached_item_int. The bound is then
loaded with range_expr->fetch_value_from(item_add): Cached_item_item::
fetch_value_from() swaps item_add in and calls cmp(), and Cached_item_int::cmp()
stores item->val_int() – the DECIMAL value v +/- d converted to an integer – and
cmp_read_only() later compares the integer sort key against that integer. The
bound is therefore rounded to the sort key's type before it is ever compared.
The cache should be built with the type of item_add (the DECIMAL/DOUBLE sum), and
the sort key compared against it in that type; alternatively the server could
reject a non-integral offset over an integer key, as PostgreSQL does
("RANGE with offset PRECEDING/FOLLOWING is not supported for column type integer
and offset type numeric"), rather than silently rounding. MySQL and SQLite both
accept the query and compute the exact value-based frame.

================================================================================
STEPS TO REPRODUCE
================================================================================

CREATE TABLE d1 (i INT PRIMARY KEY, x INT);

INSERT INTO d1 VALUES (0,1),(1,1),(2,1),(3,1),(4,1),(5,1),(6,1),(7,1),(8,1),(9,1);

-- 1. frame width at every row

SELECT i, COUNT(*) OVER (ORDER BY i RANGE BETWEEN 0.5 PRECEDING AND 0.5 FOLLOWING) c

FROM d1 ORDER BY i;

-- 2. the definition of that frame for i = 5

SELECT COUNT(*) FROM d1 b WHERE b.i >= 5 - 0.5 AND b.i <= 5 + 0.5;

-- 3. one-sided

SELECT c FROM (SELECT i, COUNT(*) OVER (ORDER BY i RANGE BETWEEN CURRENT ROW AND 0.5 FOLLOWING) c

               FROM d1) z WHERE i = 5;

-- 4. same column, DECIMAL sort expression (control / workaround)

SELECT c FROM (SELECT i, COUNT(*) OVER (ORDER BY i + 0.0 RANGE BETWEEN 0.5 PRECEDING AND 0.5 FOLLOWING) c

               FROM d1) z WHERE i = 5;

-- 5. negative keys

CREATE TABLE d3 (i INT);

INSERT INTO d3 VALUES (-2),(-1),(0),(1),(2);

SELECT i, COUNT(*) OVER (ORDER BY i RANGE BETWEEN 0.5 PRECEDING AND 0.5 FOLLOWING) c

FROM d3 ORDER BY i;

================================================================================
EXPECTED
================================================================================

1.  c = 1 for i = 0..9

2.  1

3.  1

4.  1

5.  c = 1 for i = -2..2

(MySQL 26.7.0 returns exactly this.)

================================================================================
ACTUAL (13.2.0 main d45cf75c; identical on 13.0.2 and 10.6.28 ... 12.3.3)
================================================================================

1.  c = 2 for i = 0..8, c = 1 for i = 9

2.  1

3.  2

4.  1

5.  (-2,1) (-1,2) (0,3) (1,2) (2,1)

SHOW WARNINGS is empty after each statement.

================================================================================
ATTACHMENTS
================================================================================

repro.py (pymysql; exits non-zero when a RANGE frame disagrees with the self-join
that defines it; checks the offset sweep, one-sided frames, six sort-key types and
the 40-row SUM fixture). VERIFY-main-d45cf75c-2026-09-30.txt: full transcript on
main, 13.0.2, MySQL 26.7.0 and the six older images.