Doctrine Migrations Repeatedly Generates Same Migration for Unique Constraint with Where Condition

[wietsedecnijf]

Bug Report

Q	A
BC Break	yes/no
Version	3.8.2
Database	postgresql 16

Summary

The issue involves Doctrine migrations generating the same migration repeatedly, even when the schema hasn't changed. This occurs when adding a unique constraint with a where condition to an entity.

Current behavior

The doctrine:migrations:diff command generates the same migration repeatedly, even when the schema hasn't changed.

final class Version20250324153617 extends AbstractMigration
{
    public function getDescription(): string
    {
        return '';
    }

    public function up(Schema $schema): void
    {
        // this up() migration is auto-generated, please modify it to your needs
        $this->addSql('DROP INDEX UNIQ_523E1DC0F1FAD9D3296CD8AE3952D0CB');
        $this->addSql('CREATE UNIQUE INDEX UNIQ_523E1DC0F1FAD9D3296CD8AE3952D0CB ON app_bundle_ids (bundle_id, team_id, platform) WHERE deleted IS NULL');
    }

    public function down(Schema $schema): void
    {
        // this down() migration is auto-generated, please modify it to your needs
        $this->addSql('DROP INDEX uniq_523e1dc0f1fad9d3296cd8ae3952d0cb');
        $this->addSql('CREATE UNIQUE INDEX uniq_523e1dc0f1fad9d3296cd8ae3952d0cb ON app_bundle_ids (bundle_id, team_id, platform) WHERE (deleted IS NULL)');
    }
}

How to reproduce

1. Add the following annotation to an entity:

   #[ORM\UniqueConstraint(columns: ['bundle_id', 'team_id', 'platform'], options: ['where' => 'deleted IS NULL'])]
   

2. Run the bin/console doctrine:migrations:diff command.
3. Run the bin/console doctrine:migrations:migrate command.
4. Run the bin/console doctrine:migrations:diff command.
5. Observe that the same migration is generated repeatedly.

Expected Behavior

The doctrine:migrations:diff command should not generate a migration if the schema is already in sync with the mapping information.

[stof]

The schema introspection and comparison is implemented in DBAL, so that's probably where it needs to be fixed.

[wietsedecnijf]

I've added a generated migration as example.

[stof]

@morozov could it be that the platform-aware schema comparison detects a change due to the difference in parenthesis wrapping the WHERE clause ?

[wietsedecnijf]

When I run bin/console doctrine:schema:validate it outputs:

Mapping

[OK] The mapping files are correct.

Database

[ERROR] The database schema is not in sync with the current mapping file.

[morozov]

@morozov could it be that the platform-aware schema comparison detects a change due to the difference in parenthesis wrapping the WHERE clause ?

It is quite possible. The current DBAL design doesn't allow for proper handling of expressions in partial indexes, check constraints, default expressions, view definitions, etc.