Shared sequence default on primary key columns is dropped in subsequent migrations

[xkrdudrlf]

Bug Report

Q	A
Version	4.2.3
Previous Version if the bug is a regression	N/A

Summary

When sharing a sequence between two Doctrine entities (Z1 and Z2) using a custom sequence (z_shared_seq), the initial migration is generated correctly. However, after running the migration and generating a new migration, Doctrine attempts to drop the default value for the id columns in both tables, effectively removing the sequence usage, even though no changes were made to the entity definitions. This appears to be an incorrect behavior, as the sequence should remain in use for both tables.

Current behavior

After running the initial migration and then generating a new migration with php bin/console make:migration, Doctrine generates a migration script that alters both z1.id and z2.id columns to drop their default values (i.e., the sequence), even though the entity mapping has not changed. The generated migration looks like this:

public function up(Schema $schema): void
{
// this up() migration is auto-generated, please modify it to your needs
$this->addSql(<<<'SQL'
ALTER TABLE z1 ALTER id TYPE INT
SQL);
$this->addSql(<<<'SQL'
ALTER TABLE z1 ALTER id DROP DEFAULT
SQL);
$this->addSql(<<<'SQL'
ALTER TABLE z2 ALTER id TYPE INT
SQL);
$this->addSql(<<<'SQL'
ALTER TABLE z2 ALTER id DROP DEFAULT
SQL);
}

This results in the removal of the sequence-based auto-increment behavior for both tables, which is not intended.

Expected behavior

After running the initial migration and generating a new migration without any changes to the entity mapping, Doctrine should not generate a migration that alters or drops the default value for the id columns. The sequence (z_shared_seq) should remain as the default for both z1.id and z2.id, and no migration should be generated unless there is an actual change in the entity mapping.

How to reproduce

1. Create two Doctrine entities (Z1 and Z2) that both use the same sequence for their primary key, as shown below:

   #[ORM\Entity(repositoryClass: Z1Repository::class)]
   class Z1
   {
       #[ORM\Id]
       #[ORM\GeneratedValue(strategy: 'SEQUENCE')]
       #[SequenceGenerator(sequenceName: 'z_shared_seq')]
       #[ORM\Column(
           columnDefinition: "INT NOT NULL DEFAULT nextval('z_shared_seq')"
       )]
       private ?int $id = null;
       // ...
   }
   
   #[ORM\Entity(repositoryClass: Z2Repository::class)]
   class Z2
   {
       #[ORM\Id]
       #[ORM\GeneratedValue(strategy: 'SEQUENCE')]
       #[SequenceGenerator(sequenceName: 'z_shared_seq')]
       #[ORM\Column(
           columnDefinition: "INT NOT NULL DEFAULT nextval('z_shared_seq')"
       )]
       private ?int $id = null;
       // ...
   }

2. Run php bin/console make:migration to generate the initial migration. The generated migration should correctly create the shared sequence and both tables using the sequence as the default for the id columns.

3. Run php bin/console doctrine:migrations:migrate to apply the migration.

4. Without making any changes to the entities, run php bin/console make:migration again.

5. Observe that a new migration is generated, which attempts to drop the default value for the id columns in both tables, even though no changes were made.

Expected: No migration should be generated, as the database schema matches the entity mapping.

Actual: A migration is generated that removes the sequence default from the id columns, which is incorrect.

---

Note:
This issue may be related to how Doctrine DBAL compares the column definitions and handles shared sequences. It seems that the diff logic does not recognize that the default value using the shared sequence is already present and attempts to "fix" it by dropping it.

[morozov]

@xkrdudrlf, you reported this bug in the DBAL repository, while the reproduction steps involve the ORM and Migrations. To make sure that this is indeed a DBAL issue, could you reproduce it using only the DBAL?