The order of posts in my offered bounties look like this:
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.
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. :-)
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:
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:
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:
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.
AwardedDate for the last 3 items. Is it NAN?
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.
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
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:
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.
