DB count() function returning data that diverges with ->get()->count()

[crazynds]

Laravel Version

v11.45.1 & v12.20.0

PHP Version

8.3

Database Driver & Version

Postgresql & SQLite

Description

I was tring the ->count() function and I was getting some strange values. Bellow I explain each result I got:

In tinker:

> $p = Problem::first();
App\Models\Problem {#8680
   id: 1,
   ...
}

> Submission::where('problem_id',$p->id)->count();
= 76

> Submission::where('problem_id',$p->id)->whereHas('user')->count();
= 76

> Submission::where('problem_id',$p->id)->whereHas('user')->get()->count();
= 76

Until here, everything looks normal, but:

# Expected
> Submission::where('problem_id',$p->id)->whereHas('user')->select('user_id')->distinct()->get()->count();
= 22
# Here, strange behavior. Won't work with distinct?
> Submission::where('problem_id',$p->id)->whereHas('user')->select('user_id')->distinct()->count();
= 76

With group_by the result is weirder:

> Submission::where('problem_id',$p->id)->whereHas('user')->groupBy('user_id')->select('user_id')->get()->count();
= 22

# Here, another strange behavior. Where is ->count() getting this number? 
> Submission::where('problem_id',$p->id)->whereHas('user')->groupBy('user_id')->select('user_id')->count();
= 10

Am I missing something? Is it something related to the SQL or Postgres?

---

When I was doing the steps to reproduce, I tried SQLite with the same issue.

Steps To Reproduce

1. Create a new Laravel project

laravel new laravel-bug-repro

2. Create the migrations

database/migrations/2025_01_01_000000_create_problems_table.php

Schema::create('problems', function (Blueprint $table) {
    $table->id();
    $table->timestamps();
});

database/migrations/2025_01_01_000001_create_users_table.php

Schema::create('users', function (Blueprint $table) {
    $table->id();
    $table->timestamps();
});

database/migrations/2025_01_01_000002_create_submissions_table.php

Schema::create('submissions', function (Blueprint $table) {
    $table->id();
    $table->unsignedBigInteger('problem_id');
    $table->unsignedBigInteger('user_id');
    $table->timestamps();

    $table->foreign('problem_id')->references('id')->on('problems');
    $table->foreign('user_id')->references('id')->on('users');
});

3. Create the models

• app/Models/User.php
• app/Models/Problem.php
• app/Models/Submission.php

4. Insert the data

Here I create a example function to load the same data of my tests.

database/seeders/DatabaseSeeder.php

public function run()
{
    $problem = Problem::create();

    $userIds = collect([
        1,1,6,5,5,8,8,4,4,4,4,4,5,5,4,4,1,4,4,4,
        1,1,10,10,24,24,10,24,10,10,10,24,24,10,
        10,24,10,1,1,24,24,24,24,101,101,108,
        108,61,33,1,1,117,158,170,165,165,1,
        180,187,186,186,176,235,261,261,261,
        2,2,2,2,2,2,2,2,2,2,
    ]);

    $userIds->unique()->each(fn ($id) => User::firstOrCreate(['id' => $id]));

    foreach ($userIds as $userId) {
        Submission::create([
            'problem_id' => $problem->id,
            'user_id' => $userId,
        ]);
    }
}

Repo here: https://github.com/crazynds/laravel-count-bug
Run: php artisan migrate --seed
And I create a command to run all queries the same time: php artisan queries:run

[ajebo365]

use php artisan db:seed

[crazynds]

I didn't understand why are you saying that. It doesnt have any to do with my problem.

[crynobone]

The best way to understand the generated queries is to review them via Debugbar, Telescope, etc.

[macropay-solutions]

Test in v12.18.0:

We have a different pivot but the logic is the same.
We also have unique on the pair of ids in the pivot unlike the issue but the seeder from the issue populates the table with unique pairs anyway BUT the results described in the query say that there are duplicates in the db (not in the seeder):

echo OperationProductPivot::query()->where('product_id',$i)->count() . "\n";
echo OperationProductPivot::query()->where('product_id', $i)->whereHas('operation')->count() . "\n";
echo OperationProductPivot::query()->where('product_id', $i)->whereHas('operation')->get()->count() . "\n";
echo '-------------------- Until here, everything looks normal, but:' . "\n";
logger('-------------------- Until here, everything looks normal, but:');
echo OperationProductPivot::query()->where('product_id', $i)->whereHas('operation')->select('operation_id')->distinct()->get()->count() . "\n";
echo OperationProductPivot::query()->where('product_id', $i)->whereHas('operation')->select('user_id')->distinct()->count() . "\n";
echo '-------------------- With group_by the result is weirder:' . "\n";
logger('-------------------- With group_by the result is weirder:');
echo OperationProductPivot::query()->where('product_id', $i)->whereHas('operation')->groupBy('operation_id')->select('operation_id')->get()->count() . "\n";
echo OperationProductPivot::query()->where('product_id', $i)->whereHas('operation')->groupBy('operation_id')->select('operation_id')->count() . "\n";
die;

3
3
3
-------------------- Until here, everything looks normal:
3
3
-------------------- With group_by the result is weirder:
3
1

select count(*) as aggregate from `operations_products_pivot` where `product_id` = 1  
select count(*) as aggregate from `operations_products_pivot` where `product_id` = 1 and exists (select * from `operations` where `operations_products_pivot`.`operation_id` = `operations`.`id`)  
select * from `operations_products_pivot` where `product_id` = 1 and exists (select * from `operations` where `operations_products_pivot`.`operation_id` = `operations`.`id`)  
-------------------- Until here, everything looks normal, but:  
select distinct `operation_id` from `operations_products_pivot` where `product_id` = 1 and exists (select * from `operations` where `operations_products_pivot`.`operation_id` = `operations`.`id`)  

-- !!!!!!!!!!!!!!!! notice that count does not have the distinct in it:

select count(*) as aggregate from `operations_products_pivot` where `product_id` = 1 and exists (select * from `operations` where `operations_products_pivot`.`operation_id` = `operations`.`id`)  

-- !!!!!!the query should had been : 
-- select count(*) as aggregate from (select distinct `operations_products_pivot`.`operation_id` from `operations_products_pivot`) as `aggregate_table`"

-------------------- With group_by the result is weirder:  
select `operation_id` from `operations_products_pivot` where `product_id` = 1 and exists (select * from `operations` where `operations_products_pivot`.`operation_id` = `operations`.`id`) group by `operation_id`  
select count(*) as aggregate from `operations_products_pivot` where `product_id` = 1 and exists (select * from `operations` where `operations_products_pivot`.`operation_id` = `operations`.`id`) group by `operation_id`  

If you run manually the last query, it will return 3 rows each with 1 as result

This explains the weird behavior

This is our table

Test in v12.20.0 is the same.

Conclusion

The old V8 pagination distinct bug is surfacing one more time. We fixed it in our projects via macros at that time. There is even an unit text preventing a BAD fix for it. We can't find it at the moment.

Demo with the macro fixes

limit=0&page=1&hasRelations[operations][from]=1&distincts[0]=operation_id

select count(*) as aggregate from (select distinct `operations_products_pivot`.`operation_id` from `operations_products_pivot`) as `aggregate_table`

limit=0&page=1&hasRelations[operations][from]=1&aggregates[groupBys][0]=operation_id

select count(*) as aggregate from (select COUNT(DISTINCT `operations_products_pivot`.`operation_id`, `operations_products_pivot`.`product_id`) as group_count, `operations_products_pivot`.`operation_id` as operation_id from `operations_products_pivot` group by `operation_id`) as `aggregate_table`

https://maravelith.laravel-crud-wizard.com/maravelith-10/laravel-lumen-crud-wizard#operations-products-pivot
both return

"total": 1392776

[macropay-solutions]

@crynobone this is an issue with the distincts but as it was not fixed in the past marking this as discussion follows the same path.