[Bug]: SEARCH ordering by an indexed metadata property produces invalid SQL when sharding is enabled

[susnux]

⚠️ This issue respects the following points: ⚠️

• This is not a troubleshooting question, general support matter, or webserver/proxy problem, but likely a bug.
• This issue is not already reported on Github OR solved at the Community Help Forum.
• I'm using a maintained major version of Nextcloud Server and tested against the latest patch level.
• I agree to follow Nextcloud's Code of Conduct.
• I've tried my best to provide clear reproduction steps that someone unfamiliar with this bug could use to reproduce it.

Bug description

On an instance with filecache sharding/partitioning enabled, any DAV SEARCH that sorts by an indexed files-metadata property fails with a Postgres 42P01 Undefined table error. The Photos app timeline triggers this on every load, since it sorts by {http://nextcloud.org/ns}metadata-photos-original_date_time.

The generated SQL contains ORDER BY "meta_index_0"."meta_value_int" desc but no join to oc_files_metadata_index:

SELECT ... FROM "oc_filecache" "file"
  LEFT JOIN "oc_filecache_extended" "fe" ON ...
  LEFT JOIN "oc_files_metadata" "meta" ON ...
WHERE ...
ORDER BY "meta_index_0"."meta_value_int" desc, "mtime" + ? desc LIMIT 200

Root cause — two defects in server:

1. files_metadata_index is missing from the filecache shard preset. In lib/private/DB/Connection.php:90-103, companion_tables lists filecache_extended and files_metadata, but not files_metadata_index. That table has a file_id column, which is exactly the declared companion_keys value, so it belongs there; it looks like an oversight when the preset was introduced in fc05a67f192.

   Because it is not in the partition, PartitionedQueryBuilder::join() takes the "join from partition, to the main db" branch (PartitionedQueryBuilder.php:251-285) and splits the meta_index_0 join off into a separate sub-query.

2. PartitionedQueryBuilder does not override orderBy() / addOrderBy(). select, andWhere, join and setMaxResults are partition-aware; ORDER BY is not. So the order clause added by SearchBuilder::addSearchOrdersToQuery() (lib/private/Files/Cache/SearchBuilder.php:328-336) stays verbatim on the main filecache query, referencing an alias that now lives in another query — invalid SQL is emitted instead of an InvalidPartitionedQueryException.

Note that metadata filtering works fine, because andWhere() routes predicates to the correct sub-query via getPartitionForPredicate(). Only metadata sorting is broken, which is why this surfaces on the Photos timeline rather than on ordinary metadata searches.

The client side is not at fault: OCA\DAV\Files\FileSearchBackend advertises these metadata properties as sortable: true, so an app sorting by one of them should not be able to produce invalid SQL.

Steps to reproduce

1. Set up a Nextcloud instance with 'sharding' configured for the filecache partition in config.php (see Connection.php:132), on PostgreSQL.
2. Enable the Photos app and make sure at least one image has photos-original_date_time metadata indexed.
3. Open the Photos timeline — or issue the equivalent SEARCH on /remote.php/dav/ by hand:

<d:searchrequest xmlns:d="DAV:" xmlns:nc="http://nextcloud.org/ns">
  <d:basicsearch>
    <d:select><d:prop><d:getlastmodified/></d:prop></d:select>
    <d:from><d:scope>
      <d:href>/files/USERID/Photos</d:href><d:depth>infinity</d:depth>
    </d:scope></d:from>
    <d:orderby><d:order>
      <d:prop><nc:metadata-photos-original_date_time/></d:prop>
      <d:descending/>
    </d:order></d:orderby>
  </d:basicsearch>
</d:searchrequest>

4. The request returns HTTP 500 and the log shows the exception below.

Expected behavior

The SEARCH returns results ordered by the metadata value. Adding files_metadata_index to the filecache preset's companion_tables keeps the join inside the same partition, so the query stays a single valid statement.

Independently, a cross-partition ORDER BY should raise InvalidPartitionedQueryException rather than emitting SQL that references a missing FROM-clause entry, so the next app to hit this gets a diagnosable error instead of a raw driver exception.

Nextcloud Server version

36 (master)

Operating system

Other (see Additional info)

PHP engine version

(fill in)

Web server

(fill in)

Database engine version

PostgreSQL

Is this bug present after an update or on a fresh install?

(fill in)

Are you using the Nextcloud Server Encryption module?

Encryption is Disabled

What user-backends are you using?

• Default user-backend (database)

Configuration report

{
  "sharding": { "filecache": { "shards": [ "..." ] } }
}

List of activated Apps

photos, dav

Nextcloud Signing status

No errors have been found.

Nextcloud Logs

{
  "reqId": "vKyKzVn06u2Xcc0OyTIp",
  "level": 3,
  "method": "SEARCH",
  "url": "/remote.php/dav/",
  "message": "Uncaught exception",
  "version": "36.0.0.0",
  "exception": {
    "Exception": "OC\\DB\\Exceptions\\DbalException",
    "Message": "An exception occurred while executing a query: SQLSTATE[42P01]: Undefined table: 7 ERROR:  missing FROM-clause entry for table \"meta_index_0\"\nLINE 1: ... AND ((\"path\" = $8) OR (\"path\" LIKE $9)) ORDER BY \"meta_inde...",
    "Code": 7,
    "Trace": [
      "lib/private/DB/QueryBuilder/Sharded/ShardedQueryBuilder.php:404 executeQuery",
      "lib/private/DB/QueryBuilder/Partitioned/PartitionedQueryBuilder.php:445 executeQuery",
      "lib/private/Files/Cache/QuerySearchHelper.php:176 executeQuery",
      "lib/private/Files/Node/Folder.php:231 searchInCaches",
      "apps/dav/lib/Files/FileSearchBackend.php:180 search",
      "apps/dav/lib/Files/LazySearchBackend.php:51 search",
      "3rdparty/icewind/searchdav/src/DAV/SearchHandler.php:82 search"
    ]
  }
}

Additional info

Only affects instances with 'sharding' set in config.php; without it, Connection::getInnerQueryBuilder() returns a plain QueryBuilder and the meta_index_0 join is added inline, so the query is valid.

Relevant code:

• lib/private/DB/Connection.php:90-103 — SHARD_PRESETS, missing files_metadata_index
• lib/private/DB/QueryBuilder/Partitioned/PartitionedQueryBuilder.php:251-285 — cross-partition join split
• lib/private/Files/Cache/SearchBuilder.php:328-336 — addSearchOrdersToQuery()
• lib/private/FilesMetadata/MetadataQuery.php:95-124 — joinIndex()