Multi-table UPDATE/DELETE fails with DBAL 4 for string identifiers: temporary id table column loses its length

Bug Report

Q	A
Version	doctrine/orm 3.7.2, doctrine/dbal 4.4.4, PHP 8.5
Platforms	MariaDB, MySQL, SQL Server (PostgreSQL and SQLite are not affected)
Previous	worked with doctrine/orm 2.x + doctrine/dbal 3.x

Summary

A DQL UPDATE or DELETE on an entity that uses JOINED inheritance and has a string
identifier fails with DBAL 4.

MultiTableUpdateExecutor and MultiTableDeleteExecutor create a temporary table for the
identifiers, but build its column definition from the column type only. The length,
fixed, precision and scale of the mapped identifier are dropped. DBAL 3 silently
defaulted to VARCHAR(255); DBAL 4 removed that default and throws ColumnLengthRequired.

The mapping itself is valid: the identifier has an explicit length: 36. The exception
is thrown while the DQL is parsed, so it happens even when no row would match.

How to reproduce

Standalone script, no database connection needed (serverVersion is given):

<?php

declare(strict_types=1);

namespace Repro;

use Doctrine\DBAL\DriverManager;
use Doctrine\ORM\EntityManager;
use Doctrine\ORM\Mapping as ORM;
use Doctrine\ORM\ORMSetup;

require __DIR__ . '/vendor/autoload.php';

#[ORM\Entity]
#[ORM\Table(name: 'parent_entity')]
#[ORM\InheritanceType('JOINED')]
#[ORM\DiscriminatorColumn(name: 'discr', type: 'string')]
#[ORM\DiscriminatorMap(['parent' => ParentEntity::class, 'child' => ChildEntity::class])]
class ParentEntity
{
    #[ORM\Id]
    #[ORM\Column(type: 'string', length: 36, options: ['fixed' => true])]
    public string $id;

    #[ORM\Column(type: 'integer')]
    public int $status = 1;
}

#[ORM\Entity]
#[ORM\Table(name: 'child_entity')]
class ChildEntity extends ParentEntity
{
}

$config = ORMSetup::createAttributeMetadataConfig([__DIR__], true);
$config->enableNativeLazyObjects(true);

$connection = DriverManager::getConnection([
    'driver'        => 'pdo_mysql',
    'serverVersion' => '11.8.9-MariaDB',
], $config);

$em = new EntityManager($connection, $config);

$em->createQuery('UPDATE ' . ChildEntity::class . ' c SET c.status = 3')->getSQL();
// the same happens with: DELETE Repro\ChildEntity c

Current behavior

Fatal error: Uncaught Doctrine\DBAL\Exception\InvalidColumnType\ColumnLengthRequired: Doctrine\DBAL\Platforms\MariaDB110700Platform requires the length of a VARCHAR column to be specified in vendor/doctrine/dbal/src/Exception/InvalidColumnType/ColumnLengthRequired.php:22
Stack trace:
#0 vendor/doctrine/dbal/src/Platforms/AbstractPlatform.php(337): Doctrine\DBAL\Exception\InvalidColumnType\ColumnLengthRequired::new()
#1 vendor/doctrine/dbal/src/Platforms/AbstractPlatform.php(204): Doctrine\DBAL\Platforms\AbstractPlatform->getVarcharTypeDeclarationSQLSnippet()
#2 vendor/doctrine/dbal/src/Types/StringType.php(19): Doctrine\DBAL\Platforms\AbstractPlatform->getStringTypeDeclarationSQL()
#3 vendor/doctrine/dbal/src/Platforms/AbstractPlatform.php(1498): Doctrine\DBAL\Types\StringType->getSQLDeclaration()
#4 vendor/doctrine/dbal/src/Platforms/MariaDBPlatform.php(157): Doctrine\DBAL\Platforms\AbstractPlatform->getColumnDeclarationSQL()
#5 vendor/doctrine/dbal/src/Platforms/AbstractPlatform.php(1466): Doctrine\DBAL\Platforms\MariaDBPlatform->getColumnDeclarationSQL()
#6 vendor/doctrine/orm/src/Query/Exec/MultiTableUpdateExecutor.php(138): Doctrine\DBAL\Platforms\AbstractPlatform->getColumnDeclarationListSQL()
#7 vendor/doctrine/orm/src/Query/SqlWalker.php(256): Doctrine\ORM\Query\Exec\MultiTableUpdateExecutor->__construct()
#8 vendor/doctrine/orm/src/Query/SqlOutputWalker.php(21): Doctrine\ORM\Query\SqlWalker->createUpdateStatementExecutor()
#9 vendor/doctrine/orm/src/Query/Parser.php(368): Doctrine\ORM\Query\SqlOutputWalker->getFinalizer()
#10 vendor/doctrine/orm/src/Query.php(248): Doctrine\ORM\Query\Parser->parse()
#11 vendor/doctrine/orm/src/Query.php(717): Doctrine\ORM\Query->parse()
#12 vendor/doctrine/orm/src/Query.php(178): Doctrine\ORM\Query->getSqlExecutor()
#13 repro.php(46): Doctrine\ORM\Query->getSQL()
#14 {main}

Next Doctrine\DBAL\Exception\InvalidColumnDeclaration: Column "id" has invalid type in vendor/doctrine/dbal/src/Exception/InvalidColumnDeclaration.php:16
Stack trace:
#0 vendor/doctrine/dbal/src/Platforms/AbstractPlatform.php(206): Doctrine\DBAL\Exception\InvalidColumnDeclaration::fromInvalidColumnType()
#1 vendor/doctrine/dbal/src/Types/StringType.php(19): Doctrine\DBAL\Platforms\AbstractPlatform->getStringTypeDeclarationSQL()
#2 vendor/doctrine/dbal/src/Platforms/AbstractPlatform.php(1498): Doctrine\DBAL\Types\StringType->getSQLDeclaration()
#3 vendor/doctrine/dbal/src/Platforms/MariaDBPlatform.php(157): Doctrine\DBAL\Platforms\AbstractPlatform->getColumnDeclarationSQL()
#4 vendor/doctrine/dbal/src/Platforms/AbstractPlatform.php(1466): Doctrine\DBAL\Platforms\MariaDBPlatform->getColumnDeclarationSQL()
#5 vendor/doctrine/orm/src/Query/Exec/MultiTableUpdateExecutor.php(138): Doctrine\DBAL\Platforms\AbstractPlatform->getColumnDeclarationListSQL()
#6 vendor/doctrine/orm/src/Query/SqlWalker.php(256): Doctrine\ORM\Query\Exec\MultiTableUpdateExecutor->__construct()
#7 vendor/doctrine/orm/src/Query/SqlOutputWalker.php(21): Doctrine\ORM\Query\SqlWalker->createUpdateStatementExecutor()
#8 vendor/doctrine/orm/src/Query/Parser.php(368): Doctrine\ORM\Query\SqlOutputWalker->getFinalizer()
#9 vendor/doctrine/orm/src/Query.php(248): Doctrine\ORM\Query\Parser->parse()
#10 vendor/doctrine/orm/src/Query.php(717): Doctrine\ORM\Query->parse()
#11 vendor/doctrine/orm/src/Query.php(178): Doctrine\ORM\Query->getSqlExecutor()
#12 repro.php(46): Doctrine\ORM\Query->getSQL()
#13 {main}

The DELETE query fails the same way, at
vendor/doctrine/orm/src/Query/Exec/MultiTableDeleteExecutor.php(98)
(called from SqlWalker.php(265)).

Results of the same script with other platforms:

Driver / platform	Result
`pdo_mysql` / MariaDB 11.8	`ColumnLengthRequired` (VARCHAR)
`pdo_mysql` / MySQL 8.0	`ColumnLengthRequired` (VARCHAR)
`pdo_sqlsrv` / SQL Server	`ColumnLengthRequired` (NVARCHAR)
`pdo_pgsql` / PostgreSQL 16	no error, but the column is created without its length
`pdo_sqlite`	no error

Expected behavior

The temporary table uses the identifier's mapped column definition:

CREATE TEMPORARY TABLE parent_entity_id_tmp (id CHAR(36) NOT NULL, PRIMARY KEY(id))

Cause

src/Query/Exec/MultiTableUpdateExecutor.php (lines 130-134) and the same block in
src/Query/Exec/MultiTableDeleteExecutor.php (lines 90-94):

foreach ($idColumnNames as $idColumnName) {
    $columnDefinitions[$idColumnName] = [
        'name'    => $idColumnName,
        'notnull' => true,
        'type'    => Type::getType(PersisterHelper::getTypeOfColumn($idColumnName, $rootClass, $em)),
        // length / fixed / precision / scale of the mapped column are lost here
    ];
}

The temporary identifier table created by MultiTableUpdateExecutor and
MultiTableDeleteExecutor only takes the DBAL type from the mapping of each
identifier column. Everything else in the column definition is dropped, which
causes two problems:

DDL errors. DBAL 4 requires a length for (N)VARCHAR columns on
MySQL/MariaDB and SQL Server, and a precision for DECIMAL columns on all
platforms. As a result, UPDATE/DELETE on a JOINED child entity fails
with ColumnLengthRequired or "Column precision is not specified".
PostgreSQL does not fail, but it silently creates the column without its
length.
Collation mismatches. If the temporary column gets the connection's
default collation instead of the collation of the real column, the
comparison in WHERE (id) IN (SELECT id FROM <tmp>) can fail on MySQL
("Illegal mix of collations"), or it can stop using the index.

$tableOptions      = ($rootClass->table['options'] ?? []) + ($conn->getParams()['defaultTableOptions'] ?? []);
$columnDefinitions = [];

foreach ($idColumnNames as $idColumnName) {
    $mapping  = PersisterHelper::getFieldMappingOfColumn($idColumnName, $rootClass, $em);
    $type     = Type::getType($mapping->type);
    $isString = $type instanceof StringType && ! $type instanceof GuidType; // GUID may map to non-collatable UUID

    $columnDefinitions[$idColumnName] = [
        'name'      => $idColumnName,
        'notnull'   => true,
        'type'      => $type,
        'length'    => $mapping->length ?? ($isString ? $em->getConfiguration()->getDefaultStringTypeSchemaLength() : null),
        'fixed'     => $mapping->options['fixed'] ?? false,
        'precision' => $mapping->precision,
        'scale'     => $mapping->scale,
        'unsigned'  => $mapping->options['unsigned'] ?? false,
        'collation' => $isString ? ($mapping->options['collation'] ?? $tableOptions['collation'] ?? null) : null,
    ];
}

I'm happy to open a PR with this change and a regression test covering if this fix oky..

Fix temporary id table columns for multi-table DQL UPDATE and DELETE

Navigation Menu

Repository navigation

Multi-table UPDATE/DELETE fails with DBAL 4 for string identifiers: temporary id table column loses its length #12634

Bug Report

Summary

How to reproduce

Current behavior

Expected behavior

Cause

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Multi-table UPDATE/DELETE fails with DBAL 4 for string identifiers: temporary id table column loses its length #12634

Description

Bug Report

Summary

How to reproduce

Current behavior

Expected behavior

Cause

Activity

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions