'Available Updates' page recommends beta release over stable release

[quicksketch]

Mirror of issue reported in core queue at https://github.com/backdrop/backdrop-issues/issues/3128.

Reported by @BWPanda:

I have the Auto Menu Settings module installed on a few sites and, on the 'Available Updates' page (/admin/reports/updates), they all recommend 'updating' from 1.x-1.0.0 to 1.x-1.0.0-beta1.

Clearly the beta version came out first, then the stable release later. The stable release (that I have installed) should be the recommended version and so I shouldn't get any update notifications. What have I done wrong with releasing this module?

[I thought this issue already existed somewhere, but I couldn't find it... Please mark as duplicate if you find the original, or let me know if I need to post this elsewhere instead.]

[quicksketch]

The problem here is that backdropcms.org is returning the wrong order for the release XML file. The source of the problem is in project_release_query_releases():

  $query = db_select('node', 'n');
  $query->innerJoin('project_release', 'pr', 'n.nid = pr.nid');
  $query->fields('n', array('nid'));
  $query->condition('n.status', 1);
  $query->condition('pr.project_nid', $project_nid);
  if (isset($version_api)) {
    $query->condition('pr.version_api', $version_api);
  }
  if (isset($version_major)) {
    $query->condition('pr.version_major', $version_major);
  }
  if ($access) {
    $query->addTag('node_access');
  }
  $query->orderBy('pr.version_api', 'DESC');
  $query->orderBy('pr.version_major', 'DESC');
  $query->orderBy('pr.version_minor', 'DESC');
  $query->orderBy('pr.version_patch', 'DESC');
  $query->orderBy('pr.version_extra', 'DESC');

Running this on the backdropcms.org database we get this:

mysql> select n.nid, title from node n inner join project_release pr ON n.nid = pr.nid WHERE n.status = 1 AND pr.project_nid = 1667 AND pr.version_api = '1.x' ORDER BY pr.version_api DESC, pr.version_major DESC, pr.version_minor DESC, pr.version_patch DESC, pr.version_extra DESC;
+------+------------------------------------+
| nid  | title                              |
+------+------------------------------------+
| 1669 | auto_menu_settings 1.x-1.0.0-beta1 |
| 1736 | auto_menu_settings 1.x-1.0.0       |
+------+------------------------------------+

So the query orders any beta/alpha/etc over the final release, if it matches in every other regard. Looks like we can fix this by adding a sort on the version_extra that matches an empty string, as suggested at https://stackoverflow.com/questions/9307613/mysql-order-by-null-first-and-desc-after