Host software pagination returns short/overlapping pages when a title has multiple installers

[getvictor]

Fleet version: 4.82 (unreleased)

---

Actual behavior

When a software title has multiple installer versions (possible since the FMA migration changed the unique constraint from (global_or_team_id, title_id) to (global_or_team_id, title_id, version)), the host software pagination returns fewer items than per_page on some pages and can show duplicate items across adjacent pages.

This happens because LIMIT/OFFSET is applied to the raw UNION query rows before Go-side deduplication collapses multiple installer rows into one item per title. The raw row offset doesn't align with deduplicated page boundaries.

Example observed in QA with 314 software items, per_page=20, and 5 titles having 2 installers each:

• Page 14 returned 18 items instead of 20

To fix

Restructure ListHostSoftware to paginate the distinct software_titles.id set first (using the count query's grouping), then hydrate installer/app details for just that page's title IDs. This ensures page boundaries align with unique titles regardless of how many installer rows each title produces.

The relevant code is in server/datastore/mysql/software.go, function ListHostSoftware (where appendListOptionsToSQL is called on the combined UNION query).

Steps to reproduce

These steps:

• Have been confirmed to consistently lead to reproduction in multiple Fleet instances.

1. Have a host with many software titles (100+)
2. Create multiple installer versions for some titles in the host's team (insert into software_installers with same title_id and global_or_team_id but different version)
3. Navigate to the host's software inventory page
4. Page through the results; pages containing titles with multiple installers will have fewer than per_page items, and the same title may appear on adjacent pages

More info

• This is an unreleased bug introduced in 4.82 by the FMA migration (20260218175704) which allows multiple installers per title
• The HasNextResults pagination metadata bug was fixed separately in Next is greyed out even though there are more software records #41233
• Pages returning fewer items than per_page can mislead users into thinking software is missing from their host's inventory