Search code examples
mysqlsqlrow-numberranking-functions

ROUND not working when row_number() function is present

This is a simplified version of what I'm trying to do. I am trying to rank users based on how many miles they walked overall.

This data is stored in the table called walks. Every time a user makes a walk, an entry is added.

create temporary table walks
(
    id       int unsigned auto_increment primary key,
    user_id  int unsigned             not null,
    miles_walked float unsigned default '0' not null,
    date date not null
);

To fill in the table:

insert into walks (user_id, miles_walked, date)
values
    (1, 10.1, '2022-12-20'),
    (2, 60.2, '2022-12-21'),
    (3, 30.3, '2022-12-22'),
    (1, 0.4, '2022-12-23'),
    (2, 10.5, '2022-12-24'),
    (3, 10.6, '2022-12-25'),
    (1, 40.7, '2022-12-26'),
    (2, 80.8, '2022-12-27'),
    (3, 30.9, '2022-12-28');

select * from walks;

The result of the above query

select user_id,
       SUM(miles_walked) as miles_walked_total,
       ROUND(SUM(miles_walked), 1) as miles_walked_total_rounded,
       row_number() over (order by SUM(miles_walked) desc)  as miles_rank
from walks
group by user_id
order by user_id

The result of the above query

As you can see, rounding is WRONG for users with id 2 and 3. What happened? Like I said, this is a simplified example. In my real case, not just rounding, but the ranking is wrong for the whole set when I use functions like ROUND and LENGTH:

ROW_NUMBER() OVER (ORDER BY (SUM(LENGTH(reports.comments)) + SUM(report_items.report_items_characters_number)) DESC) AS ranking
Solution

  • I can't duplicate it in 8.0.30: https://dbfiddle.uk/y04TcMlp

    I suspect it's a bug that's been fixed. I recommend you upgrade.