[Bug]: Calendar::search misses events when using limit and time range

Bug description

The Talk team reached out because they were missing events in our search responses. The issue is also reproducible with the calendar widget.

The query to find events is ordered by the id column. Without an ORDER BY clause, the same query may return different results depending on how the SQL server works. To ensure more reproducible results, an ORDER BY clause was added some time ago.

Steps to reproduce

1. Create event "1" tomorrow at 12:00
2. Create event "2" tomorrow at 12:30
3. Create event "3" tomorrow at 13:00
4. Create event "4" tomorrow at 13:30
5. Create event "5" tomorrow at 14:00
6. Create event "6" tomorrow at 14:30
7. Create event "7" tomorrow at 15:00
8. Create event "8" tomorrow at 11:00

Expected Order:
8-1-2-3-4-5-6-7

Actual Order:
1-2-3-4-5-6-7-8

Additional info

• Affected versions: 27 upwards.
• We encountered issues with the sorting of events in https://github.com/nextcloud/server/pull/45222.
• Removing the ORDER BY "id" clause does not resolve the problem because the order is then still up to the SQL server and only by chance in the correct order.
• Using firstoccurrence to order works, but we should add an index on it.

Possible bug reports

• https://github.com/nextcloud/calendar/issues/5902
• https://github.com/nextcloud/calendar/issues/6433
• https://github.com/nextcloud/calendar/issues/6765

Start by reproducing the calendar search with a limit and time range using the eight events described, then inspect the query ordering by id. Compare the results with firstoccurrence ordering and determine whether the relevant search query needs an index on firstoccurrence. Done means events are returned in chronological order rather than id order.