`sortBy` on collection returns different results per database

[wild glacier]

I'm using Filament and I write tests to check if sorting works correctly. After running my tests locally, with Postgresql set as my database, none of my tests failed. Then I pushed my changes to the repository and I found out that some tests are failing (which were the ones that tested the sorting functionality).

I checked which database the CI uses and it is MySQL, so I decided to change my phpunit.xml and set the database to MySQL. Yup, there it is, the same tests that failed in the pipeline also fail locally now.

After some research I though that maybe the sortBy functionality returns different results per database. Which seems to be the case. I did not verify this 100% yet but I can show you how I came to that conclussion.

The code for the test is:

it('can sort users by `name` on index page', function () {
    $user = createUserWithPermissions(['view users']);
    actingAs($user);

    $users = User::factory()->count(3)->create();

    // @phpstan-ignore-next-line
    livewire(ListUsers::class)
        ->assertCanSeeTableRecords($users)
        ->sortTable('name')
        ->assertCanSeeTableRecords($users->sortBy('name'), inOrder: true) // it fails on this line
        ->sortTable('name', 'desc')
        ->assertCanSeeTableRecords($users->sortByDesc('name'), inOrder: true);
});

When I use the orderBy on the QueryBuilder instead the test never fails:

it('can sort users by `name` on index page', function () {
    $user = createUserWithPermissions(['view users']);
    actingAs($user);

    User::factory()->count(3)->create();

    $orderedUsers = User::query()->orderBy('name')->get();
    $descOrderedUsers = User::query()->orderBy('name', 'desc')->get();

    // @phpstan-ignore-next-line
    livewire(ListUsers::class)
        ->assertCanSeeTableRecords($orderedUsers)
        ->sortTable('name')
        ->assertCanSeeTableRecords($orderedUsers, inOrder: true)
        ->sortTable('name', 'desc')
        ->assertCanSeeTableRecords($descOrderedUsers, inOrder: true);
});

This is why I think that the sortBy functionality works differently per database.

So now the question is, is this a bug?

[pseudo musk]

Firstly, yes, you should be using the same database across environments. Databases often have minor/subtle differences, which can lead to subtle bugs or unexpected behaviour.

Secondly, the test is a bit flaky, and I think the issue isn't database, but rather a test that might sometimes fail and sometimes succeed. Sorting on a collection doesn't use the database, so it's very likely they'd use different strategies, or again subtle differences. You're testing what the database is supposed to do, but you verify that against something the database didn't do

[wild glacier]

Hi @pseudo musk ,

The test does sometimes succeed, but 99% of the time it fails, but only when I set the database to MySQL. When running the test a lot of times with Postgresql set as the database, it never fails.

I also thought that it could not be a database thing, but after testing over and over I could not figure out what else it could be.

That is why I came here 😄

and I have multiple pages with sorting functionality on tables so I would expect that atleast one of the tests fails when setting the db to postgresql

But only multiple tests fail with mysql

[wild glacier]

I will give you a specific test case now

it('can sort users by `name` on index page', function () {
    $user = createUserWithPermissions(['view users']);
    actingAs($user);

    $users = User::factory()->createMany([
        ['name' => 'kand. Emma Groen B'],
        ['name' => 'Lily Zum Vörde Sive Vörding'],
        ['name' => 'Lily Güler AD'],
    ]);

    // @phpstan-ignore-next-line
    livewire(ListUsers::class)
        ->assertCanSeeTableRecords($users)
        ->sortTable('name')
        ->assertCanSeeTableRecords($users->sortBy('name'), inOrder: true)
        ->sortTable('name', 'desc')
        ->assertCanSeeTableRecords($users->sortByDesc('name'), inOrder: true);
});

This one always fails with MySQL but always passes with postgres

[wild glacier]

Aha

I think I found out why it does not work

sortBy does always return the same

orderBy sorts based on the db

Filament sorts based on the db

That is why sortBy can not work