Sorting by "Nearest Due Date" puts all issues without a due date first.

[bobemoe]

• Gitea version (or commit ref): 1.10.0+dev-369-g1a2d7207e
• Database (use [x]):
  • PostgreSQL
  • MySQL
  • MSSQL
  • SQLite
• Can you reproduce the bug at https://try.gitea.io:
  • Yes (provide example URL)
  • No
  • Not relevant
• Log gist:

Description

When sorting by nearest due date you'd expect the overdue/imminent issues to be first in the list. If on a repo with lots of issues you can find that the issues you are looking for are actually on the last page.

https://try.gitea.io/bobemoe/test/issues?q=&type=all&sort=nearduedate&state=open&labels=0&milestone=0&assignee=0

This leaves no way to actually sort them to the top of the list.

When sorting like this, should we:

• filter out any issues without a due date
• or ensure they are always at the bottom of the list

[bobemoe]

I'm going to try and do a PR for this.. never used GO before, but looking at the code this might be quite simple? ;)

[bobemoe]

Not quite as simple as fist thought :/

Going with the first option, "filter out any issues without a due date", the above commit works, however it does not update the counts on the "open/closed" buttons.

I guess the result set is counted before it's sorted? This raises the question of should I be mixing filters into the sort function?

If its cool to do that, where should I be adapting to get the correct count?

[bobemoe]

OK, I've found what looks like a much better place to put the code; in setupSession, which is used by CountIssuesByRepo as well as Issues. The issues are still filtered as as expected but the issue count open/closed is still not correct. I can't understand why its not working!?

[bobemoe]

OK, so a bit of debugging shows CountIssuesByRepo isn't actually getting called :(

So where are these counts coming from? Wherever it is it can't be using setupSession...

[bobemoe]

I think the counts come from GetRepoIssueStats but that function hasn't got IssuesOptions available.

I think I'm beat :(

[guillep2k]

Perhaps you can try a different route. I'm not entirely sure if this is possible, given the compatibility constraints, but for example, this kind of usage is valid:

OrderBy("case when xxxx then yyy else zzz end").

I imagine that something like this can be done (note: it's wrong SQL, it's just to convey the idea):

OrderBy("case when due_date is null then '3000-12-31' else due_date end").

This at least will send all the issues without due date to the back of the query.

The problems this may pose (if you're interested in following it) are:

• Finding out the maximum date for all supported databases (example) and make sure you don't exceed the maximum common denominator.
• Finding a bulletproof way of building the date value in xorm in a way that is compatible with all engines, as Engine.Orderby() doesn't accept any parameters. Perhaps there's a helper function for that? In the database/sql package? Not sure. It must be a locale-independent method. Perhaps the ISO-8601 date format is supported by all (e.g. 3000-12-31T23:59:59Z).

[bobemoe]

Thanks for the input :) This seems to work for:

sess.OrderBy("issue.deadline_unix == 0, issue.deadline_unix")

I'm not sure if using ORDER BY CASE WHEN THEN would be more efficient? I'm sure either way will mean the deadline index cant be used for the ordering - if it even exists and was used in the first place I'm not sure.

Are there any tests I can run to ensure compatibility?

[guillep2k]

Are there any tests I can run to ensure compatibility?

Yes, there are the integration tests. Currently the example in the Hacking Gitea page points out only to sqlite (which is embedded in the code, so always available), but to test other databases you'd have to install them yourself.

But for a single statement like this I suggest try using one of the online pages like SQL Fiddle, db-fiddle, sqltest, etc. The list of engines to test is currently: MSSQL, Postgres, MySQL/MariaDB and SQLite.

[bagasme]

When doing tests, consider when end-user install distro version of Postgres or MySQL/MariaDB instead of latest upstream version and ensure that your SQL statement works correctly.

[vincentdavis]

I would really like to see this fixed in 1.11
I can cheer but not sure I can be of much more help.

[bobemoe]

I can submit my one-liner fix as a PR if you like? But I haven't had any time to run all the tests against all the DB's.

[guillep2k]

This was fixed in #8871 I believe.

[bobemoe]

Fantastic! Yep, I can confirm the fix is working :) Thanks