I'm ranked twice in the leagues!

Question

I'm not as famous as Jon Skeet but I thought I'd file a bug report anyway for appearing twice. Here

FYI - I changed my user name within the period being viewed.

---

I wonder, is StackExchange hiring SQL programmers? I know how to get Unique values from a table! As @Yannis has pointed out and I myself found, this bug keeps coming back like a bad dream. The ones I looked at before I reported this looked like they affected all users, and I thought mine was unique. In fact scrolling up the page I can see #6 Mihal Iorga is showing twice as well.

Answer 1

I wonder, is StackExchange hiring SQL programmers? I know how to get Unique values from a table!

Yes, we are!...but that's beside the point.

The issue here isn't solved by a Distinct() or any other simple means, because it's a high speed transactional problem. The load on a single SQL server is kept down via dirty reads using a READ UNCOMMITTED isolation level. The issue comes into play when thinking about how the leagues are generated.

Once a day we hit an API on the requested site that returns a massive list of users for that site along with their rep, ranks, and deltas for that period. Think about what this means on Stack Overflow: Summing up every reputation event of 96,566 users (200+ rep) and doing these ranks. But that aggregate query is done on our RepHistory table which contains records for all 723,181 users with reputation changes totaling 34,941,129 rows as of about 5 seconds ago. None of those values are what I consider large, but they are significant and rapidly changing.

We can't do summations like this with 100% accuracy without locking the table, that's why READ UNCOMMITTED is used in our high transaction environment.

That's the why. What do we plan to do about it? I'll probably add a RepHistoryDaily table or some such that already sums up the day since that has multiple uses besides making these queries simpler. That's the off-the-top-of-my-head plan. But, we're coming up on some deadlines for moving our primary datacenter, so this will have to wait a while since that takes top priority and there's a lot of work left to get it done.

Answer 2

Confirming.

Now Mihai Iorga is showing up twice as #6, as well as Richard the Kiwi.

Also, #2 and #4 seem to be missing, which (net of the duplicates) seems to give the correct total number of entries on the first page.

**It's also counting the number of entries wrong: on the default 30 per page, it gives ranks #1, ..., #34 (inclusive) on the first page; 50 per page, up to #53 inclusive; 15 per page, up to #16 inclusive.

Screenshot:

Related

A similar issue occurs with different sorts (above is the default, by "month rank"). For example, if you sort by "Rank Change", Mohamed KALLEL shows up twice.