Bug Report

Summary

Schema tool doesn't correctly detect the current state of the database on PostgreSQLPlatform. I can't speak for other platforms as I have not tested them. This creates issues in components that depend on it, resulting in the SchemaTool proposing changes to the database that should not occur.

Current behaviour

When using columnDefinition, the SchemaTool makes up changes that do not occur when updating the schema. I know the docs say "SchemaTool will not detect changes on the column correctly anymore if you use 'columnDefinition'.", but there is a difference between not detecting changes correctly and making up changes that are not there.

Considering the Entity below, when running orm:schema-tool:update, the first run wants to execute the following expected and well-working queries:

CREATE SEQUENCE fulltext_id_seq INCREMENT BY 1 MINVALUE 1 START 1;
CREATE TABLE fulltext (id INT NOT NULL, column1 TEXT DEFAULT NULL, column2 TEXT DEFAULT NULL, fulltext tsvector GENERATED ALWAYS AS (to_tsvector('english', coalesce(column1, '') || ' ' || coalesce(column2, ''))) STORED, PRIMARY KEY(id));

When running the update command again, the SQL is just wrong:

ALTER TABLE fulltext ALTER fulltext DROP DEFAULT;

There is already a bug report #4111 for the DROP DEFAULT itself, including a proposal that the default value checking is incorrect. I agree with that, but instead of removing the condition, I just applied parenthesis to what I think is the correct statement: from $defaultClause = $column->getDefault() === null to ($defaultClause = $column->getDefault()) === null

After doing so, the SchemaTool no longer tries to drop the default column, however, it still wants to run ALTER TABLE fulltext ALTER fulltext;, so clearly there are some false-positive changes there.

The Column Diff here shows:

^ Doctrine\DBAL\Schema\ColumnDiff^ {#8448
  +oldColumnName: "fulltext"
  +column: Doctrine\DBAL\Schema\Column^ {#4322
    #_name: "fulltext"
    #_namespace: null
    #_quoted: false
    #_type: Doctrine\DBAL\Types\TextType^ {#272}
    #_length: null
    #_precision: 10
    #_scale: 0
    #_unsigned: false
    #_fixed: false
    #_notnull: false
    #_default: null
    #_autoincrement: false
    #_platformOptions: array:1 [
      "version" => false
    ]
    #_columnDefinition: "tsvector GENERATED ALWAYS AS (to_tsvector('english', coalesce(column1, '') || ' ' || coalesce(column1, ''))) STORED"
    #_comment: null
    #_customSchemaOptions: []
  }
  +changedProperties: array:1 [
    0 => "default"
  ]
  +fromColumn: Doctrine\DBAL\Schema\Column^ {#5302
    #_name: "fulltext"
    #_namespace: null
    #_quoted: false
    #_type: Doctrine\DBAL\Types\TextType^ {#272}
    #_length: null
    #_precision: 10
    #_scale: 0
    #_unsigned: false
    #_fixed: false
    #_notnull: false
    #_default: "english"
    #_autoincrement: false
    #_platformOptions: []
    #_columnDefinition: null
    #_comment: null
    #_customSchemaOptions: []
  }
}

So far I tracked it down to the schema comparator being fed incorrect information about the current state of the column - notice how the default value is somehow set to "english", while the column definition is empty.

Dumping the two arrays here https://github.com/doctrine/dbal/blob/3.4.x/src/Schema/Comparator.php#L522 show the below:

^ array:12 [
  "name" => "fulltext"
  "type" => Doctrine\DBAL\Types\TextType^ {#272}
  **"default" => "english"**
  "notnull" => false
  "length" => null
  "precision" => 10
  "scale" => 0
  "fixed" => false
  "unsigned" => false
  "autoincrement" => false
  **"columnDefinition" => null**
  "comment" => null
]
^ array:13 [
  "name" => "fulltext"
  "type" => Doctrine\DBAL\Types\TextType^ {#272}
  **"default" => null**
  "notnull" => false
  "length" => null
  "precision" => 10
  "scale" => 0
  "fixed" => false
  "unsigned" => false
  "autoincrement" => false
  **"columnDefinition" => "tsvector GENERATED ALWAYS AS (to_tsvector('english', coalesce(column1, '') || ' ' || coalesce(column1, ''))) STORED"**
  "comment" => null
  "version" => false
]

I ran out of time for now, I wish I tracked it deeper. I'm hoping someone more familiar with Doctrine's internal can spot quicker where this is going and what needs to be done?

How to reproduce

1. Add the below entity in your (Symfony) codebase. Outside Symfony, change the command to orm:schema-tool:update. The point is to get the SchemaTool to try to detect changes.
2. Run bin/console do:sch:up --force to create the table and sequence
3. Run bin/console do:sch:up --dump-sql to see the broken update queries.

<?php

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity]
class Fulltext
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column(type: 'integer')]
    private $id;
    
    #[ORM\Column(type: 'text', nullable: true)]
    private $column1;

    #[ORM\Column(type: 'text', nullable: true)]
    private $column2;

    #[ORM\Column(
        type: 'text',
        nullable: true,
        insertable: false,
        updatable: false,
        columnDefinition: "tsvector GENERATED ALWAYS AS (to_tsvector('english', coalesce(column1, '') || ' ' || coalesce(column1, ''))) STORED",
        generated: 'ALWAYS',
    )]
    private $fulltext;
}

Expected behaviour

The schema should only change when there are changes in place, and the changes should not be incorrect. Sorry for being the Cpt Obvious