Room search filters seating capacity as a string, dropping rooms that fit

[Rikdekker]

Summary

searchPrincipalsByCapacity() compares the requested seating capacity as a string, because the column it filters on is a varchar. Rooms are therefore filtered by lexicographic order rather than by number, and rooms that easily fit the meeting are dropped from the result.

The most visible consequence is in Calendar's room suggestions, which pass the attendee count as the capacity filter: a room seating 100 disappears from the suggestions for a 2-person meeting, while a room seating 4 is kept.

Steps to reproduce

1. Create several bookable rooms with seating capacities that differ in digit count, e.g. 4, 6, 8, 10, 12, 16, 20, 100.
2. In Calendar, create an event and add 2 attendees.
3. Open the room suggestions.

Expected behaviour

Every room seating 2 or more is a candidate — all eight rooms.

Actual behaviour

Only 4, 6, 8, 20, 25, 30 survive the filter. The rooms seating 10, 12, 16 and 100 are dropped, because "10" < "2" as a string.

Worked out over a realistic set:

Attendees	Rooms passing the filter
2	4, 6, 8, 20, 25, 30
3	4, 6, 8, 30
4	4, 6, 8
5	6, 8

The pattern is counter-intuitive in both directions: adding attendees can make more rooms appear, and the largest rooms vanish first.

Cause

https://github.com/nextcloud/server/blob/master/apps/dav/lib/CalDAV/ResourceBooking/AbstractPrincipalBackend.php#L354-L358

private function searchPrincipalsByCapacity(string $key, string $value, array $usersGroups = []): array {
    $query = $this->getMetadataQuery($key);
    $query->andWhere($query->expr()->gte('value', $query->createNamedParameter($value)));
    return $this->getRows($query, $usersGroups);
}

getMetadataQuery() selects from the metadata table (calendar_rooms_md / calendar_resources_md), whose value column is a string type shared by every metadata key — building address, room type, features and seating capacity all live in it. gte on that column is a string comparison in every supported database.

This is not a caller problem: {http://nextcloud.com/ns}room-seating-capacity is a DAV property and arrives as a string by definition, so a backend has nothing else to hand over.

Suggested fix

Cast on the comparison for this one key, e.g. CAST(value AS INTEGER) (or the DBAL equivalent that works across sqlite/mysql/postgres), guarded so it only applies to the capacity key — the same column holds non-numeric values for other keys.

A cast on a varchar is not index-friendly. If that matters, a dedicated numeric column for capacity avoids both the cast and the type confusion.

Note on test coverage

apps/dav/tests/unit/CalDAV/ResourceBooking/ has no test exercising the capacity filter, which is likely why this has gone unnoticed. Any fix is worth pairing with a case that mixes single- and multi-digit capacities — a same-digit-length fixture passes either way.

Server configuration

Nextcloud version: reproduced against 34.0.4; the same code is present on master
Database: PostgreSQL (the comparison is string-based on all supported databases)

Found while investigating a downstream report against a third-party room backend (RoomVox#43), but the behaviour is independent of which backend supplies the rooms.

[Rikdekker]

One clarification on scope, after testing against Calendar 6.6.0-rc.2.

Calendar's new room picker (6.6.0) fetches the room principals and filters on capacity client-side, numerically — so that view is not affected by this bug. The still-affected caller in Calendar is the older room suggestion list, which does pass the attendee count into this search (tracked at nextcloud/calendar#8949).

That narrows how visible this is in the Calendar UI, but it does not make the query correct: searchPrincipalsByCapacity() is a public search path on AbstractPrincipalBackend, used for both rooms and resources, and any CalDAV client issuing a principal-property-search on {http://nextcloud.com/ns}room-seating-capacity gets the same string comparison. A client asking for "at least 2 seats" is silently told that a 100-seat room does not qualify.

Worth noting the workaround it pushes onto integrators: capacities have to be zero-padded to equal width (004, 010, 100) for the comparison to happen to be right, which then shows up as leading zeros in every client that displays the value.

[susnux]

cc @SebastianKrupinski