15

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

Bug report image

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.

13

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.

  • 1
    So this will be going on for a while? That could breathe new life into the Stack Overflow Store. "I was displayed twice in the Stack Overflow leagues and all I got was this lousy T-Shirt" – Pëkka Oct 5 '12 at 19:05
  • Is marking this now as status-declined means your plan has been cancelled? – Shadow Oct 15 '12 at 14:11
  • 1
    @ShaWizDowArd - just some mis-communication there, tagged appropriately now – Nick Craver Oct 15 '12 at 14:15
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:

Unpossible!

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.

  • Of course, he changed from rank x to rank x-1, therefore that's two ranks in that change, so you show up twice. – Lee Louviere Oct 5 '12 at 19:19
  • @Xaade: What do you mean? – Mechanical snail Oct 6 '12 at 4:23

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .