2

On the Tools page for "Recent Votes", the sorting of votes is different for "Close Votes" and "Reopen Votes".

Close votes are sorted from oldest to newest and Reopen votes are sorted from newest to oldest.

This may be seen by comparing the "History" of Close and Reopen votes from the "Review queues" page with the lists on the Tools page.

The fact they are different possibly suggests a "Bug".

For the record, I prefer newest to oldest.

9
  • 2
    Most sorting issues on pages full of lists are not "bugs" they're just... that's what you get. Any list that is built by code might just be doing a foreach without an explicit order. Any query against the database without an ORDER BY is going to return results in the order SQL Server decides to return them, and it won't always be in the order you expect. Even queries with an ORDER BY that is not deterministic (not enough sorting expressions and/or no tie-breakers) could change behavior every time.
    – Aaron Bertrand Staff
    Commented Apr 18 at 22:26
  • 2
    Sometimes, it's by design, not someone saying "let's order these two lists differently," just possibly two different queries constructed (or last modified) at different times and by different people, without any dictated standard for sorting. Other times it's just an expectation based on observation, e.g. one query might have SELECT * FROM (SELECT TOP (100) FROM ... ORDER BY something) AS x; <-- many people expect the outer result to have to obey the inner ORDER BY, but SQL Server doesn't have to handle it that way. Without an ORDER BY it could very well order in the opposite direction.
    – Aaron Bertrand Staff
    Commented Apr 18 at 22:37
  • @AaronBertrand - I first noticed the behavior about two weeks ago so I have been checking regularly. I had the occasion to VTC a post so with both pages open, I VTC'd then refreshed the list. The only change I noticed was a swap of the last two items consistent with ORDER BY. Having seen that, I wanted to raise the issue here in case it was a bug and having a preference for newest to oldest.
    – Rick Smith
    Commented Apr 18 at 22:55
  • @AaronBertrand true, but in such case the bug is the inconsistent behavior, or the user experience being flawed. Minor, and might be one of those "not worth fixing" perhaps, but still something that can be improved. Commented Apr 19 at 7:38
  • 2
    @Shadow Agree, I wasn’t opposing bringing it up, just explaining why these things can happen generally, and that sometimes it becomes a bug on its own long after the code has been deployed (new index, changed data, etc). I was just picking nits because usually a bug is “behaving differently than the dev intended” but in these ordering cases it usually has nothing to do with intention. :-)
    – Aaron Bertrand Staff
    Commented Apr 19 at 10:54
  • @Aaron ah, I see now. So perhaps it's fitting as answer, to give context and details otherwise lost in the comments. Commented Apr 19 at 11:05
  • @AaronBertrand - The expanded list for Recent votes has an option to "sort by close reason" and after clicking that it shows the option "sort by date". Clicking that restores the previous order. This suggests that the original order may be "by date".
    – Rick Smith
    Commented Apr 19 at 12:12
  • 1
    @RickSmith Possibly, but date of what? Post creation? Entering the queue? Last review action? I don't know this specific piece of code has an explicit ORDER BY at all, or one deterministic enough to produce consistent behavior, until you manually choose one. So the default could not sort the output from SQL Server at all, in which case - again depending on the ORDER BY on the underlying query - be completely arbitrary.
    – Aaron Bertrand Staff
    Commented Apr 19 at 12:19
  • @AaronBertrand - As I noted previously when I VTC'd a post, the list was updated by placing the title (and updated count) of that post last, so "time of last close vote". Maybe changing the order doesn't use SQL. Could it be in the HTML? I don't know.
    – Rick Smith
    Commented Apr 19 at 12:31

1 Answer 1

5

I don't have a direct and immediate answer - never mind a fix - for your specific use case, and I don't know whether it is by design or completely unintentional. Without looking at the C# code myself (which you probably don't want me to do), I have a theory, which I'll explain below.

Generally

Sorting "issues" come up often enough with lists that I should explain why they can happen. They're typically not bugs in the most formal sense (e.g. the code behaves differently than the author had originally intended), particularly if the C# is just rendering HTML in the order it receives the data from the database and not making explicit sorting decisions itself.

  • Sometimes, there is no intention at all. A foreach against a list that came from {somewhere else} might not have any declaration or expectation of order, it is just looping through what it has. A query against the database with no ORDER BY tells SQL Server it is free to return the rows in the most efficient way it can, which isn't always based on some predictable thing like Id or CreationDate. Especially when parallelism is involved.
  • Sometimes, there isn't enough intention. A query with a single sorting expression, like ORDER BY CreationDate, can come up with ties. In the event of a tie, for the rows that are tied, the previous bullet applies: If you don't tell SQL Server how to break the tie, it's going to decide for you. And it may not be the tie-breaker you expect.
  • In both of these cases, the behavior can change from execution to execution, even with zero code changes, if a new execution plan is generated because:
    • An index used by the query was added, removed, or changed.
    • An execution plan got bumped out of the plan cache.
    • The entire plan cache was wiped due to a failover, restart, or configuration change.
    • Data changed enough to hit a threshold that updated statistics (or statistics were manually updated via maintenance operations).
    • In all of these cases, SQL Server could generate a new execution plan and - armed with different information - it might choose a different index, a different join algorithm, parallelism or not, and so on. In which case, the "left up to the database" sorting may change.
  • Sometimes, it's by design - not someone saying "let's order these two lists differently," just possibly two different queries constructed (or last modified) at different times and/or by different people, without any dictated standard for sorting.
  • Sometimes it's just an inferred expectation based on observation, e.g. one query might have SELECT * FROM (SELECT TOP (100) FROM ... ORDER BY something) AS x; <-- many people expect the outer result to have to obey the inner ORDER BY, but SQL Server doesn't have to handle it that way. Without an ORDER BY it could very well order in the opposite direction. Or they might see SELECT ... {no ORDER BY} order in a particular way when they test the query, and the data is small, but that changes due to any of the factors mentioned above.

In cases like this

In some cases, we do things like this (and I can really only grok the SQL calls that happen here, not the code that calls them or that does anything with the result):

SELECT TOP (<n>) Id FROM Posts WHERE ... ORDER BY ...;

This would be great, if the code then just streamed that result to the page with a SqlDataReader or whatever. But it doesn't always do that! Sometimes, Dapper puts those into a comma-separated list (maybe in order, but maybe not), and the code then returns the list based on STRING_SPLIT:

DECLARE @Ids varchar(max) = '345, 987, 654, ...';

SELECT <cols> FROM Posts 
  WHERE Id IN 
  (
    SELECT CONVERT(int, value)
      FROM STRING_SPLIT(@Ids, ',')
  ); 

Notice no ORDER BY, so you get whatever order SQL Server decides. Anecdotally, you could trust STRING_SPLIT() to output in the same order as the delimited string, though it's not guaranteed. From the docs:

The output rows might be in any order. The order isn't guaranteed to match the order of the substrings in the input string. You can override the final sort order by using an ORDER BY clause on the SELECT statement, for example, ORDER BY value or ORDER BY ordinal.

But there is no guarantee the initial list was constructed in the desired order anyway, even if you do trust STRING_SPLIT more than I do. On first glance, it looks like one list is constructed in Posts.Id order descending, while the other is not constructed in any discernible way. Though this, too, may not be intentional, just a culmination of other factors.

🎵 One of these lists is not like the other... 🎵

Note that - hypothetically - the code has no idea about any other context that will be pulled separately in some other method as part of drawing the page, like when the review was initiated or when any action on a review was taken. It basically only knows "here's a list of IDs to pass around."

Code like this could be updated to:

  • Place an explicit ORDER BY (Id ASC or Id DESC), to at least get the posts in post creation order.
  • Use the new enable_ordinal argument to STRING_SPLIT(), to ensure the results are returned in the order they were entered (assuming the list was generated in the desired order in the first place).
  • Pull in more information to be able to sort the data initially in an expected and deterministic way.

I believe it's up to the app-side code (C#? JavaScript?) to respond to clicks that re-sort so, potentially, another option would be to automatically perform one of those sort actions on page load. If this is the problem in the first place.

Whether this theory is right, whether it has anything to do with the queries at all, and whether these are practical ways to fix it if I am right, are anybody's guesses.

And most importantly, whether this meets the bar for fixing, is a different question, and one for people that aren't me. :-)

You must log in to answer this question.

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