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

[kesselb]

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 CalDAV fix search with limit and time range #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

• Calendar widget shows events in the order of creation _not_ in the order of schedule calendar#5902
• dashboard widget not showing all calendar events calendar#6433
• Dashboard Widget show some Appointments, other not calendar#6765