Unread filter in threaded view only returns threads whose latest message is unread

[FelipeVeiga]

Steps to reproduce

1. Use a mailbox with long threads where older messages are unread but the most
   recent message of the thread has already been read.
2. Open the mailbox with the message list in threaded view.
3. Enable the "unread" filter and scroll down.

Expected behaviour

Every unread message is reachable, and infinite scroll keeps loading pages until
the folder's unread count is accounted for.

Actual behaviour

Only threads whose latest message is unread are returned. The second page comes
back empty, infinite scroll treats it as end-of-list and stops. The folder badge
says 53 unread while the list shows 10 rows.

Numbers from a real INBOX (178 cached messages, 52 unread, 114 threads):

request	items returned
filter=flags:unread&limit=5&sort=newest&view=threaded	5
filter=flags:unread&limit=20&sort=newest&view=threaded	10
filter=flags:unread&limit=200&sort=newest&view=threaded	10
filter=flags:unread&limit=200&cursor=<ts of 10th item>&sort=newest&view=threaded
filter=flags:unread&limit=200&sort=oldest&view=threaded	10 (same list sort has no effect)
filter=flags:unread&limit=200&sort=newest&view=singleton	52

limit has no influence past 10, so this is not a page-size problem the query
simply does not find more IDs.

Confirmed directly in the database:

SELECT COUNT(*) total_threads, SUM(topo) threads_with_unread_top FROM (
  SELECT thread_root_id,
         CAST(SUBSTRING_INDEX(GROUP_CONCAT(flag_seen ORDER BY sent_at DESC, id DESC), 'S topo
  FROM oc_mail_messages WHERE mailbox_id = ? GROUP BY thread_root_id) t;
-- 114 total threads, 10 with an unread newest message

Those 10 are exactly the 10 the API returns. 34 threads contain unread messages,
so 24 threads / 39 unread messages are unreachable from the UI. view=singleton
returns all 52, which confirms the threading path is where they are dropped.

Client side, the empty second page produces:

[ERROR] mail: could not fetch next envelope page
[WARN]  mail: envelope list is not defined, can't fetch next envelopes

Server configuration

- Mail app: 5.10.10
- Nextcloud: 33.0.6.2
- Database: MariaDB 11.8.8
- Container image: nextcloud:33.0.6-apache
- IMAP: external provider, ~180 messages in the folder

### Expected behavior

All unread messages should be reachable through the unread filter, and infinite
  scroll should keep loading pages until the folder's unread count is accounted for.

  A thread should appear in the unread filter when *any* message inside it is
  unread, not only when its most recent message is unread.

  Steps to reproduce:
  1. Use a mailbox with long threads where older messages are unread but the most
     recent message of each thread has already been read.
  2. Open the mailbox with the message list in threaded view (default).
  3. Enable the "unread" filter.
  4. Scroll down to load more.

  Actual behavior:
  Only threads whose latest message is unread are returned. The second page comes
  back empty, the infinite scroll treats it as end-of-list and stops loading. The Only threads whose latest message is unread are returned. The second page comes
back empty, the infinite scroll treats it as end-of-list and stops loading. The
folder badge shows 53 unread while the list shows 10 rows.

Real INBOX: 178 cached messages, 52 unread, 114 threads. 34 threads contain
unread messages, but only 10 have an unread newest message, and those 10 are
exactly what the API returns. 39 unread messages are unreachable from the UI.

  filter=flags:unread&limit=5&sort=newest&view=threaded     -> 5
  filter=flags:unread&limit=20&sort=newest&view=threaded    -> 10
  filter=flags:unread&limit=200&sort=newest&view=threaded   -> 10
  filter=flags:unread&limit=200&cursor=<ts>&view=threaded   -> 0
  filter=flags:unread&limit=200&sort=oldest&view=threaded   -> 10 (same list, sort ignored)
limit has no influence past 10, so this is not a page-size issue: the query
simply does not find more IDs. view=singleton returns all 52, which points at
the threading path as where they are dropped.

Verified in the database:

  SELECT COUNT(*) total_threads, SUM(topo) threads_with_unread_top FROM (
    SELECT thread_root_id,
           CAST(SUBSTRING_INDEX(GROUP_CONCAT(flag_seen ORDER BY sent_at DESC, id DESC), ',', 1) AS UNSIGNED) = 0 AS topo
    FROM oc_mail_messages WHERE mailbox_id = ? GROUP BY thread_root_id) t;
  -- 114 total threads, 10 with an unread newest message

Mail app version: 5.10.10
Nextcloud version: 33.0.6.2
Database: MariaDB 11.8.8
Web server / PHP: Apache + PHP from the official nextcloud:33.0.6-apache image
Operating system: Debian (Docker, nextcloud:33.0.6-apache)

Logs / browser console:
[ERROR] mail: could not fetch next envelope page
[WARN]  mail: envelope list is not defined, can't fetch next envelopes
  folder badge shows 53 unread while the list shows 10 rows.

  Real INBOX: 178 cached messages, 52 unread, 114 threads. 34 threads contain
  unread messages, but only 10 have an unread newest message, and those 10 are
  exactly what the API returns. 39 unread messages are unreachable from the UI.

    filter=flags:unread&limit=5&sort=newest&view=threaded     -> 5
    filter=flags:unread&limit=20&sort=newest&view=threaded    -> 10
    filter=flags:unread&limit=200&sort=newest&view=threaded   -> 10
    filter=flags:unread&limit=200&cursor=<ts>&view=threaded   -> 0
    filter=flags:unread&limit=200&sort=oldest&view=threaded   -> 10 (same list, sort ignored)
    filter=flags:unread&limit=200&sort=newest&view=singleton  -> 52

  limit has no influence past 10, so this is not a page-size issue: the query
  simply does not find more IDs. view=singleton returns all 52, which points at
  the threading path as where they are dropped.


### Actual behavior

All unread messages should be reachable through the unread filter, and infinite
  scroll should keep loading pages until the folder's unread count is accounted for.

  A thread should appear in the unread filter when *any* message inside it is
  unread — not only when its most recent message is unread.

  Já deixo os outros campos prontos, na ordem do formulário:

  Steps to reproduce:
  1. Use a mailbox with long threads where older messages are unread but the most
     recent message of each thread has already been read.
  2. Open the mailbox with the message list in threaded view (default).
  3. Enable the "unread" filter.
  4. Scroll down to load more.

  Actual behavior:
  Only threads whose latest message is unread are returned. The second page comes
  back empty, the infinite scroll treats it as end-of-list and stops loading. The


### Mail app version

_No response_

### Nextcloud version

_No response_

### Mailserver or service

_No response_

### Operating system

_No response_

### PHP engine version

None

### Nextcloud memory caching

_No response_

### Web server

None

### Database

None

### Additional info

_No response_