Asset paths are treated case-insensitive for content, but case-sensitive for metadata

[faltjo]

Bug description

After creating and saving an asset using eloquent-driver, the used AssetContainer returns true when checking for existence of a file on the same path and same name, except the filename uses different upper- or lower-case letters than the asset from before.

This might be expected behavior, especially when config statamic.assets.lowercase = true, however when trying to read meta-data of the file with differing case filename nothing is returned.

Expected behavior
If filenames (or paths?) are treated case-insensitive for handling the file's content, the same should be true for the file's metadata.

How to reproduce

A minimal example is given in a test case here
https://github.com/faltjo/eloquent-driver-case-sensitive-filenames

1. git clone https://github.com/faltjo/eloquent-driver-case-sensitive-filenames
2. composer install
3. create .env file (from .env.example)
4. php artisan key:generate
5. ./vendor/bin/pest
6. Test fails, because no meta-data is retrieved for the file with uppercase filename.

Logs

No response

Environment

Environment
Application Name: Statamic
Laravel Version: 11.23.5
PHP Version: 8.3.8
Composer Version: 2.7.7
Environment: local
Debug Mode: ENABLED
URL: localhost
Maintenance Mode: OFF
Timezone: UTC
Locale: en

Cache
Config: NOT CACHED
Events: NOT CACHED
Routes: NOT CACHED
Views: NOT CACHED

Drivers
Broadcasting: log
Cache: file
Database: sqlite
Logs: stack / single
Mail: log
Queue: sync
Session: file

Statamic
Addons: 1
Sites: 1
Stache Watcher: Enabled (auto)
Static Caching: Disabled
Version: 5.25.0 Solo

Statamic Addons
statamic/eloquent-driver: 4.14.3

Statamic Eloquent Driver
Asset Containers: eloquent
Assets: eloquent
Blueprints: file
Collection Trees: file
Collections: file
Entries: file
Forms: file
Global Sets: file
Global Variables: file
Navigation Trees: file
Navigations: file
Revisions: file
Sites: file
Taxonomies: file
Terms: file
Tokens: file

Additional details

No response

[duncanmcclean]

Might be slightly related to statamic/cms#5595.

[ryanmitchell]

What collation is your database using? I'm guessing its case sensitive?

[faltjo]

In the example I am using a sqlite database. I am not 100 % sure, but what I have found, the default for sqlite databases is NOT to use NOCASE collation, which means: yes, it is case-sensitive.

In production, where I originally stumbled over this problem, a postgres DB with UTF-8 collation is used, which is also case-sensitive.

[ryanmitchell]

I'm torn as to whether we should fix this or not. It's entirely feasible your database is case sensitive and your filesystem is too, in which case the current behaviour is expected, not a bug.

My feeling would be if you are wanting case insensitivity you should also make a migration to set the assets columns to be case insensitive.