4

The order of posts in my offered bounties look like this:

Offered bounties

I don't get it, what's the sorting criteria used here?

Post position Offered time
first 2023-01-20 10:04:25Z
middle 2021-03-15 13:55:51Z
last 2023-02-03 13:28:55Z

The last one is currently active.

0

2 Answers 2

10

It's ordered by the "AwardedDate" of the bounty, newest first. This property isn't exposed in SEDE and is actually a layered relationship between the two bounty "votes." You won't be able to reproduce the sort in SEDE as a result. You'll just have to trust me, I guess. :-)

How offered bounties are sorted

The active tab is quite similar, there's just more criteria added to the WHERE clause, which makes the sorting completely up to SQL Server (as there is no creation date for the award since, by definition, it hasn't been awarded). Basically:

Why active bounties are NOT sorted

Now, these queries are simplified slightly from how they exist in the application code; basically, the order is determined by a row_number() calculated inside a CTE, and the outer WHERE also has BETWEEN to facilitate paging. The row number inside the CTE is always based on the outer vote creation date, and only on the outer creation date.

As @rene correctly identified, this could lead to unpredictable results as you page through many bounties, since any two or more rows with a tie on awarded date/time (which I thought was going to be pretty rare, but here we are) could fall on the boundary of 50 per page, for example. Or row numbers are applied arbitrarily if the only column used to define them is NULL in all rows. So, in theory, it is possible you could get the rows ordered A,B on the first page, then B,A on the second page, and see A twice, and B never.

On the other hand, the plans here are pretty stable, and the types of engine changes that lead to differences here are rarely introduced (and we don't exactly upgrade to every CU the day it is released), so the likelihood of that seems pretty small.

Arguably:

  1. Sorting could be amended by a tie-breaker as a secondary ordering column, to make sorting predictable.
  2. The Active case could be adjusted so that the row numbers were determined by a non-null column (though this wouldn't be necessary in the case of (1)).

I wasn't here, but I would bet that at the time of implementation, the arguments against these were:

  1. "How could there possibly be a tie in awarded date?" Honestly I don't know how the OP managed that except that if they were all picked up and awarded as some system sweep that maybe didn't exist, or was slower at the time, or just nobody thought to try to simulate that scenario?
  2. This one is simpler: you can only have 3 bounties at a time, so probably, "We don't need to introduce additional performance penalties of sorting on the active tab, because paging isn't needed and how many people have more than one at a time anyway?"

Knowing what we know today, we would potentially make different decisions, though due to plan stability I'm generally against adding secondary sort columns just for predictability of ordering. It's essentially, to me: "I want this query to be slightly worse all the time for no possible gain even when there's a tie." And in the case of pagination, there is so much else that can make things inconsistent and bump the order or the exact rows that belong on a specific page as you navigate (new bounty gets added, a bounty gets awarded, etc.), the value of perfection is non-existent IMHO.

6
  • If I look at those 3 dates at 2022-11-13, those are all the same and similar to PostNotices.deletiondate. If there is no other field in the order by, wouldn't the sorting be unstable? Or at least left to SQL Server to decide what order to return rows?
    – rene
    Feb 3, 2023 at 16:31
  • What about last 3 items (actually active bounty)? Should they be visible on "offered" tab at all? Feb 3, 2023 at 16:31
  • @rene yes, that is correct, in the event of a tie, it's all up to SQL Server.
    – Aaron Bertrand Staff
    Feb 3, 2023 at 16:32
  • @αλεχολυτ That's a question for someone else, I'm afraid. I'm answering the question asked: "How are the items on the offered tab sorted?" I can't answer about the behavior of the page or who decided which bounties should show up on which tab and in which order.
    – Aaron Bertrand Staff
    Feb 3, 2023 at 16:35
  • Ok. So what the AwardedDate for the last 3 items. Is it NAN? Feb 3, 2023 at 16:45
  • @αλεχολυτ SQL Server doesn't have NaN. The bounties haven't been awarded, so the AwardedDate is NULL. ORDER BY NULL tells SQL Server to just return whatever is most convenient, so that's what you get : an arbitrary order. This is an implementation of the underlying engine, and only our fault in a very tiny way.
    – Aaron Bertrand Staff
    Feb 3, 2023 at 16:47
6

The list is sorted by the date when the bounty was "awarded". Technically we don't have that date but the PostNotice gets deleted at the same time, so

  • PostNotice.DeletionDate descending

should give you a close enough approximation of the order.

as proven by this SEDE query

select pn.postid [Post Link]
     , pn.creationdate
     , format(pn.deletiondate, 'yyyy-MM-dd HH:mm:ss:fffff') ["Awarded"]
from postnotices pn
where owneruserid = ##userid##
order by deletiondate  desc

Compare the oddness:

lines to indicate the match between UI order and query row order, highlight fields in records used in order by

For now this seems to be a stable order for your case. It might well be that the actual order by in the code turns out to be different and/or non-existing. The latter case is the prevalent implementation choice by SE developers. Not having an order by is more performant in which case the order of records is whatever SQL Server dreams up.

Yes, this order might differ with each run of the query. Note that in case of a tie (see the bounties on 2022-11-13) it is left to SQL Server in what order to return rows. In fact, SQL Server can return a different order for that specific case with each execution of the query. In the end that is an internal implementation detail that might/will change with each version of SQL Server.

6
  • SEDE query didn't return active bounties (3 items). The last items in bounty list in profile page. Feb 3, 2023 at 15:28
  • 1
    because SEDE updates once a week, on Sunday 03:00 UTC
    – rene
    Feb 3, 2023 at 15:42
  • So we're expecting the last 3 item from profile page will be added (on the next Sunday) to the top of SEDE query result? Or to the bottom? Feb 3, 2023 at 15:58
  • at the top would be my expectation.
    – rene
    Feb 3, 2023 at 16:09
  • Then the order on the page will differ from SEDE order. Feb 3, 2023 at 16:12
  • 1
    I haven't looked into how post notices get updated, and it's a good observation for something that is exposed in SEDE, but there's an additional layer of assumed dependency there - in theory it's possible that the job that updates post notices could update awarded bounties in a different order or even in different runs than the job that updates the bounty itself, or it could be structured to process n rows at a time, and for similar reasons as ties cause a problem with pagination, could cause a problem with perfect sync between two tables.
    – Aaron Bertrand Staff
    Feb 3, 2023 at 17:55

You must log in to answer this question.

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