DB with join returns empty set when running multiple tests with transactions in PGSQL

[tinyoverflow]

• Laravel Version: 9.30.1
• PHP Version: 8.1.10
• Database Driver & Version: PostgreSQL 14.5.1

Description:

I have a test class which contains multiple tests for the same method. The method that's being tested executes a query using the DB facade which contains two joins to other tables on the same database.

$data = DB::table('users')
    ->selectRaw('users.username, COUNT(tableA.*) AS count, SUM(products.price) AS sum')
    ->join('tableA', 'tableA.publisher_id', '=', 'users.id')
    ->join('products', 'tableA.product_id', '=', 'products.id')
    ->where('tableA.status', '=', 'published')
    ->when($days !== null, fn($q) => $q->whereRaw("tableA.published_at >= now()::date - interval '$days days'"))
    ->groupBy('users.username')
    ->orderByDesc($column)
    ->get();

I use the default Laravel TestCase with the addition of the RefreshDatabase trait. That trait enables transactions while running the tests.

When I now run the full test class, eg with sail test --filter=MyTestClass, only the first test which calls this method succeeds. The other tests just get an empty set. When running each one individually, it succeeds.

I suspect that this has something to do with the transactions. When I replace the RefreshDatabase trait with the DatabaseMigration trait, everything works as expected. Therefore I'm not sure if this is a problem related to Laravel or PostgreSQL.

Steps To Reproduce:

See description.