Laravel Version

11.37.0

PHP Version

8.3.19

Database Driver & Version

MariaDB 10.6.21 for Ubuntu 24.04 LTS on x86

Description

MySQL/MariaDB permits the use to store a "zero" value of '0000-00-00' as a dummy date (https://dev.mysql.com/doc/refman/8.4/en/using-date.html), which can be treated as NULL. This is very useful for adding soft deletes to established tables that make use of unique indexes, as the deleted_at column can be included as part of the unique index. That way, only one unique non-deleted row can exist. However, the column default can't be NULL because this breaks the unique logic, so a value must be used.

By using a default value of '0000-00-00', this can be included in the unique index and everything works as expected. However, onlyTrashed() doesn't take this into consideration, therefore it believes every row is deleted.

If I modify Illuminate/Database/Eloquent/SoftDeletingScope.php, function addOnlyTrashed(), with the following logic, it works:

`
protected function addOnlyTrashed(Builder $builder)
{
$builder->macro('onlyTrashed', function (Builder $builder) {
$model = $builder->getModel();

        $qualifiedDeletedAtColumn = $model->getQualifiedDeletedAtColumn();

        $builder->withoutGlobalScope($this)->where(function ($query) use ($qualifiedDeletedAtColumn) {
            $query->whereNotNull($qualifiedDeletedAtColumn);
            $query->where($qualifiedDeletedAtColumn, '!=', '0000-00-00 00:00:00');
        });

        return $builder;
    });
}

`

Steps To Reproduce

Create a database column with the following migration code:

$table->softDeletesDatetime()->nullable(false)->default('0000-00-00 00:00:00')->after('updated_at')->index();

Ensure use SoftDeletes; is added to model.

Run tinker, and get a count of all the rows in the table with onlyTrashed()->count(). This returns the same number as if you just did 'count()', meaning onlyTrashed() is thinking every row is deleted.