Albums show incorrect date range when viewed through shared link

[micha030201]

I have searched the existing issues, both open and closed, to make sure this is not a duplicate report.

• Yes

The bug

Albums in Immich have the start and end dates shown beneath the title. This date range always shows correctly when I'm viewing the album through my account, but when I create a public share link to that album, the date range is sometimes a sub-range of the original range. For example, I have an album that shows 7 Jul 2018 - 18 Aug 2025 • 1,050 items in my profile, but 4 Apr 2024 - 7 Feb 2025 • 1,050 items when viewed though the public link.

The OS that Immich Server is running on

Debian stable (trixie)

Version of Immich Server

v1.139.2

Version of Immich Mobile App

irrelevant

Platform with the issue

• Server
• Web
• Mobile

Your docker-compose.yml content

I am using podman. Here are my quadlet units (lightly edited to remove passwords and directory names):

==> /etc/containers/systemd/immich-machine-learning.container <==
[Unit]
Description=Immich - Machine Learning container
After=immich-redis.service
Requires=immich-redis.service

[Container]
Image=ghcr.io/immich-app/immich-machine-learning:release
Network=immich.network
NetworkAlias=immich-machine-learning

SubUIDMap=immich
SubGIDMap=immich
ReadOnly=true

Volume=/srv/immich/machine-learning/cache:/cache
Volume=/srv/immich/machine-learning/root_cache:/root/.cache
Volume=/srv/immich/machine-learning/root_config:/root/.config

==> /etc/containers/systemd/immich.network <==
[Unit]
Description=Podman network for the Immich application
After=network-online.target
Wants=network-online.target

[Network]
IPv6=true

==> /etc/containers/systemd/immich-postgres.container <==
[Unit]
Description=Immich - PostgreSQL container

[Container]
Image=ghcr.io/immich-app/postgres:14-vectorchord0.4.3-pgvectors0.2.0

Network=immich.network
NetworkAlias=database

SubUIDMap=immich
SubGIDMap=immich
ReadOnly=true

Volume=/srv/immich/postgres/var_lib_postgresql_data:/var/lib/postgresql/data
Volume=/srv/immich/postgres/etc_postgresql:/etc/postgresql
Environment=POSTGRES_PASSWORD=<...>
Environment=POSTGRES_USER=postgres
Environment=POSTGRES_DB=immich
Environment="POSTGRES_INITDB_ARGS=--data-checksums"

==> /etc/containers/systemd/immich-redis.container <==
[Unit]
Description=Immich - Redis container
After=immich-postgres.service
Requires=immich-postgres.service

[Container]
Image=docker.io/valkey/valkey:8-bookworm@sha256:fec42f399876eb6faf9e008570597741c87ff7662a54185593e74b09ce83d177

Network=immich.network
NetworkAlias=redis

SubUIDMap=immich
SubGIDMap=immich
ReadOnly=true

HealthCmd=redis-cli ping || exit 1

==> /etc/containers/systemd/immich-server.container <==
[Unit]
Description=Immich Main server container
After=immich-redis.service immich-postgres.service
Requires=immich-redis.service immich-postgres.service

[Container]
Image=ghcr.io/immich-app/immich-server:release
Network=immich.network

SubUIDMap=immich
SubGIDMap=immich
ReadOnly=true

Environment=DB_PASSWORD=<...>

Volume=/etc/localtime:/etc/localtime:ro
Timezone=Asia/Tokyo

AddDevice=/dev/dri

PublishPort=2283:2283

Volume=/srv/immich/upload:/usr/src/app/upload

Volume=/mnt/<....>:/mnt/media/<...>:ro
Volume=/mnt/<....>:/mnt/media/<...>:ro

Your .env content

Not relevant, I supply all environment variables in the quadlet units.

Reproduction steps

1. create album
2. create share link for that album
3. the dates are wrong when viewed through the shared link

This is not very helpful, I know. This doesn't happen with all albums, and I haven't been able to figure out what exactly triggers this bug.

Relevant log output

Nothing notable in the log.

Additional information

As I said above, I haven't been able to figure out what is it that causes this behaviour to occur. The dates in the sub-range seem somewhat arbitrary, but creating a new album with the same pictures yields exactly the same dates (from my limited testing). It does not seem to be connected with a change in the camera model, the directory files are in, presence or absence of sidecar metadata, or the time photos were added to the album.

A potentially relevant note about my setup is that I'm not using the upload feature, all my photos are in external libraries mounted read-only. Some files have xmp sidecars.

I will be honest and say that I haven't spent a lot of time trying to find the root cause of this issue. I guess I'm hoping that it is a simple case of "there are two different DB queries that are supposed to do the same thing but one of them is actually wrong" or something like that.

Please tell me if there is any more information I can easily provide or things I can easily test, or if that's not enough and I need to have an actual compose file and set of pictures to reliably reproduce the issue.

[micha030201]

I was able to find the root cause of this! It is indeed an issue with a SQL query.

Here is the query for shared links:

immich/server/src/repositories/shared-link.repository.ts

Lines 22 to 109 in 0729887

	@GenerateSql({ params: [DummyValue.UUID, DummyValue.UUID] })
	get(userId: string, id: string) {
	return this.db
	.selectFrom('shared_link')
	.selectAll('shared_link')
	.leftJoinLateral(
	(eb) =>
	eb
	.selectFrom('shared_link_asset')
	.whereRef('shared_link.id', '=', 'shared_link_asset.sharedLinksId')
	.innerJoin('asset', 'asset.id', 'shared_link_asset.assetsId')
	.where('asset.deletedAt', 'is', null)
	.selectAll('asset')
	.innerJoinLateral(
	(eb) =>
	eb
	.selectFrom('asset_exif')
	.selectAll('asset_exif')
	.whereRef('asset_exif.assetId', '=', 'asset.id')
	.as('exifInfo'),
	(join) => join.onTrue(),
	)
	.select((eb) => eb.fn.toJson('exifInfo').as('exifInfo'))
	.orderBy('asset.fileCreatedAt', 'asc')
	.as('a'),
	(join) => join.onTrue(),
	)
	.leftJoinLateral(
	(eb) =>
	eb
	.selectFrom('album')
	.selectAll('album')
	.whereRef('album.id', '=', 'shared_link.albumId')
	.where('album.deletedAt', 'is', null)
	.leftJoin('album_asset', 'album_asset.albumsId', 'album.id')
	.leftJoinLateral(
	(eb) =>
	eb
	.selectFrom('asset')
	.selectAll('asset')
	.whereRef('album_asset.assetsId', '=', 'asset.id')
	.where('asset.deletedAt', 'is', null)
	.innerJoinLateral(
	(eb) =>
	eb
	.selectFrom('asset_exif')
	.selectAll('asset_exif')
	.whereRef('asset_exif.assetId', '=', 'asset.id')
	.as('exifInfo'),
	(join) => join.onTrue(),
	)
	.select((eb) => eb.fn.toJson(eb.table('exifInfo')).as('exifInfo'))
	.orderBy('asset.fileCreatedAt', 'asc')
	.as('assets'),
	(join) => join.onTrue(),
	)
	.innerJoinLateral(
	(eb) =>
	eb
	.selectFrom('user')
	.selectAll('user')
	.whereRef('user.id', '=', 'album.ownerId')
	.where('user.deletedAt', 'is', null)
	.as('owner'),
	(join) => join.onTrue(),
	)
	.select((eb) =>
	eb.fn.coalesce(eb.fn.jsonAgg('assets').filterWhere('assets.id', 'is not', null), sql`'[]'`).as('assets'),
	)
	.select((eb) => eb.fn.toJson('owner').as('owner'))
	.groupBy(['album.id', sql`"owner".*`])
	.as('album'),
	(join) => join.onTrue(),
	)
	.select((eb) =>
	eb.fn
	.coalesce(eb.fn.jsonAgg('a').filterWhere('a.id', 'is not', null), sql`'[]'`)
	.$castTo<MapAsset[]>()
	.as('assets'),
	)
	.groupBy(['shared_link.id', sql`"album".*`])
	.select((eb) => eb.fn.toJson('album').$castTo<Album | null>().as('album'))
	.where('shared_link.id', '=', id)
	.where('shared_link.userId', '=', userId)
	.where((eb) => eb.or([eb('shared_link.type', '=', SharedLinkType.Individual), eb('album.id', 'is not', null)]))
	.orderBy('shared_link.createdAt', 'desc')
	.executeTakeFirst();
	}

The start and end dates are then extracted like this:

immich/server/src/dtos/album.dto.ts

Lines 194 to 195 in 0729887

	let startDate = assets.at(0)?.localDateTime;
	let endDate = assets.at(-1)?.localDateTime;

This relies on the assets in the album to be ordered by their creation date, which I presume is why the .orderBy('asset.fileCreatedAt', 'asc') are in the query.

However, this doesn't actually ensure the assets are ordered -- the assets are fed into json_agg, an aggregate function, which does not preserver order:

Ordinarily, the input rows are fed to the aggregate function in an unspecified order. In many cases this does not matter; for example, min produces the same result no matter what order it receives the inputs in. However, some aggregate functions (such as array_agg and string_agg) produce results that depend on the ordering of the input rows. When using such an aggregate, the optional order_by_clause can be used to specify the desired ordering.

Instead, the json_agg needs its own order by clause. In Kysely, the query builder used, this is achieved with an .orderBy on .jsonAgg.

I have queried my production database to verify my assumptions

The original query that produces the album where the assets are not sorted. The `fileCreatedAt` on the first and last assets in the list correspond to the dates I'm seeing in the web interface:

sudo podman exec systemd-immich-postgres psql --dbname=immich --username=postgres -c "select \"shared_link\".*, coalesce(json_agg(\"a\") filter(where \"a\".\"id\" is not null), '[]') as \"assets\", to_json(\"album\") as \"album\" from \"shared_link\" left join lateral (select \"asset\".*, to_json(\"exifInfo\") as \"exifInfo\" from \"shared_link_asset\" inner join \"asset\" on \"asset\".\"id\" = \"shared_link_asset\".\"assetsId\" inner join lateral (select \"asset_exif\".* from \"asset_exif\" where \"asset_exif\".\"assetId\" = \"asset\".\"id\") as \"exifInfo\" on true where \"shared_link\".\"id\" = \"shared_link_asset\".\"sharedLinksId\" and \"asset\".\"deletedAt\" is null order by \"asset\".\"fileCreatedAt\" asc) as \"a\" on true left join lateral (select \"album\".*, coalesce(json_agg(\"assets\") filter(where \"assets\".\"id\" is not null), '[]') as \"assets\", to_json(\"owner\") as \"owner\" from \"album\" left join \"album_asset\" on \"album_asset\".\"albumsId\" = \"album\".\"id\" left join lateral (select \"asset\".*, to_json(\"exifInfo\") as \"exifInfo\" from \"asset\" inner join lateral (select \"asset_exif\".* from \"asset_exif\" where \"asset_exif\".\"assetId\" = \"asset\".\"id\") as \"exifInfo\" on true where \"album_asset\".\"assetsId\" = \"asset\".\"id\" and \"asset\".\"deletedAt\" is null order by \"asset\".\"fileCreatedAt\" asc) as \"assets\" on true inner join lateral (select \"user\".* from \"user\" where \"user\".\"id\" = \"album\".\"ownerId\" and \"user\".\"deletedAt\" is null) as \"owner\" on true where \"album\".\"id\" = \"shared_link\".\"albumId\" and \"album\".\"deletedAt\" is null group by \"album\".\"id\", \"owner\".*) as \"album\" on true where \"shared_link\".\"id\" = '0d47f7e0-306a-403d-9578-d8aa82361d9f' and \"shared_link\".\"userId\" = 'e8b32154-ea5b-480b-88e9-782076e6062b' and (\"shared_link\".\"type\" = 'INDIVIDUAL' or \"album\".\"id\" is not null) group by \"shared_link\".\"id\", \"album\".* order by \"shared_link\".\"createdAt\" desc;" | vim - -c "set nowrap"

The modified query where the assets in the album are in the correct order:
sudo podman exec systemd-immich-postgres psql --dbname=immich --username=postgres -c "select \"shared_link\".*, coalesce(json_agg(\"a\") filter (where \"a\".\"id\" is not null), '[]') as \"assets\", to_json(\"album\") as \"album\" from \"shared_link\" left join lateral (select \"asset\".*, to_json(\"exifInfo\") as \"exifInfo\" from \"shared_link_asset\" inner join \"asset\" on \"asset\".\"id\" = \"shared_link_asset\".\"assetsId\" inner join lateral (select \"asset_exif\".* from \"asset_exif\" where \"asset_exif\".\"assetId\" = \"asset\".\"id\") as \"exifInfo\" on true where \"shared_link\".\"id\" = \"shared_link_asset\".\"sharedLinksId\" and \"asset\".\"deletedAt\" is null order by \"asset\".\"fileCreatedAt\" asc) as \"a\" on true left join lateral (select \"album\".*, coalesce(json_agg(\"assets\" ORDER BY \"assets\".\"fileCreatedAt\" ASC) filter (where \"assets\".\"id\" is not null), '[]') as \"assets\", to_json(\"owner\") as \"owner\" from \"album\" left join \"album_asset\" on \"album_asset\".\"albumsId\" = \"album\".\"id\" left join lateral (select \"asset\".*, to_json(\"exifInfo\") as \"exifInfo\" from \"asset\" inner join lateral (select \"asset_exif\".* from \"asset_exif\" where \"asset_exif\".\"assetId\" = \"asset\".\"id\") as \"exifInfo\" on true where \"album_asset\".\"assetsId\" = \"asset\".\"id\" and \"asset\".\"deletedAt\" is null) as \"assets\" on true inner join lateral (select \"user\".* from \"user\" where \"user\".\"id\" = \"album\".\"ownerId\" and \"user\".\"deletedAt\" is null) as \"owner\" on true where \"album\".\"id\" = \"shared_link\".\"albumId\" and \"album\".\"deletedAt\" is null group by \"album\".\"id\", \"owner\".*) as \"album\" on true where \"shared_link\".\"id\" = '0d47f7e0-306a-403d-9578-d8aa82361d9f' and \"shared_link\".\"userId\" = 'e8b32154-ea5b-480b-88e9-782076e6062b' and (\"shared_link\".\"type\" = 'INDIVIDUAL' or \"album\".\"id\" is not null) group by \"shared_link\".\"id\", \"album\".* order by \"shared_link\".\"createdAt\" desc;" | vim - -c "set nowrap"

Sorry for the messiness, this is why it's under a spoiler :)

Presumably, the fix should be as simple as putting that .orderBy in there and removing it from where it is right now. I haven't set up a build environment to properly test this and make a pull request, and I'd be more than happy if anyone gets to it before I have the time to :)

[jrasm91]

Thanks for essentially solving this one @micha030201. I've opened a PR with the suggested changes.