SQLResultCasing ignores UnquotedIdentifierFolding and breaks hydration when using DBAL portability (Oracle)

[k0n3r]

Bug Report

Version: ORM 3.5.7, DBAL 4.3.4

Summary

The SQLResultCasing trait generates SQL aliases in UPPERCASE for Oracle, but when using DBAL's portability layer with ColumnCase::LOWER, the hydration fails because it expects lowercase keys while receiving uppercase aliases in the ResultSetMapping.

Current behavior

When using Oracle with DBAL's portability configuration:

// Custom Connection with Portability
class Connection extends BaseConnection
{
    public function __construct(array $params, Driver $driver, Configuration $config)
    {
        $portableMiddleware = new PortableMiddleware(
            PortableConnection::PORTABILITY_FIX_CASE,
            ColumnCase::LOWER  // Convert all result keys to lowercase
        );
        $driver = $portableMiddleware->wrap($driver);
        
        parent::__construct($params, $driver, $config);
    }
}

The ORM hydration fails with this setup because:

1. SQL Generation: SQLResultCasing generates aliases in UPPERCASE:

   SELECT p0_.idpais AS IDPAIS_0, p0_.nombre AS NOMBRE_1 FROM pais p0_

2. ResultSetMapping: The ORM builds the RSM with lowercase keys (before SQL generation):

   $rsm->fieldMappings = [
       'idpais_0' => 'idpais',  // lowercase
       'nombre_1' => 'nombre',  // lowercase
   ];

3. Result Fetching: DBAL portability converts the result keys to lowercase:

   // What DBAL returns after portability processing
   ['idpais_0' => 547, 'nombre_1' => 'Colombia']  // lowercase keys

   Important: The portability layer does NOT modify the platform or its UnquotedIdentifierFolding setting. It only normalizes the result set keys after the database returns them. The platform still reports UnquotedIdentifierFolding::UPPER.

4. Hydration Fails: The ORM has no way to know that portability is being applied at the connection level. It builds the RSM expecting IDPAIS_0 (uppercase, matching the SQL it generated), but receives idpais_0 (lowercase, after portability processing). When AbstractHydrator::hydrateColumnInfo('idpais_0') looks for this key in the RSM, it fails because the RSM only contains the uppercase version.

The root cause

The SQLResultCasing trait uses hardcoded platform checks:

// In Doctrine\ORM\Internal\SQLResultCasing
private function getSQLResultCasing(AbstractPlatform $platform, string $column): string
{
    if ($platform instanceof DB2Platform || $platform instanceof OraclePlatform) {
        return strtoupper($column);  // Always uppercase, ignores portability
    }
    
    if ($platform instanceof PostgreSQLPlatform) {
        return strtolower($column);
    }
    
    return $column;
}

This ignores:

• DBAL 4.x's UnquotedIdentifierFolding API (introduced in Extract UnquotedIdentifierFolding from AbstractPlatform dbal#6823)
• Custom platform implementations

Historical context: The UnquotedIdentifierFolding enum was introduced in DBAL 4.x specifically to standardize how platforms handle identifier casing. However, the ORM's SQLResultCasing trait was never updated to use this new API, continuing to rely on hardcoded instanceof checks instead.

The core issue: The ORM has no way to detect that portability middleware is being applied at the connection level. The portability layer (configured via ColumnCase::LOWER) normalizes result keys to lowercase, but the ORM still generates uppercase aliases because it only checks the platform type, not the actual portability configuration. This creates a mismatch between what the ORM expects (uppercase keys in the RSM) and what it receives (lowercase keys from the portability layer).

How to reproduce

Step 1: Configure Oracle connection with portability

// src/Custom/Connection/Oracle/Connection.php
namespace App\Custom\Connection\Oracle;

use Doctrine\DBAL\ColumnCase;
use Doctrine\DBAL\Configuration;
use Doctrine\DBAL\Connection as BaseConnection;
use Doctrine\DBAL\Driver;
use Doctrine\DBAL\Portability\Connection as PortableConnection;
use Doctrine\DBAL\Portability\Middleware as PortableMiddleware;

class Connection extends BaseConnection
{
    public function __construct(
        array $params,
        Driver $driver,
        Configuration $config,
    ) {
        $portableMiddleware = new PortableMiddleware(
            PortableConnection::PORTABILITY_FIX_CASE,
            ColumnCase::LOWER,  // Convert to lowercase for consistency
        );
        $driver = $portableMiddleware->wrap($driver);

        parent::__construct($params, $driver, $config);
    }
}

# config/packages/doctrine.yaml
doctrine:
  dbal:
    wrapper_class: App\Custom\Connection\Oracle\Connection
    driver_class: App\Custom\Connection\Oracle\CustomOracleDriver
    # ... other Oracle settings

Step 2: Create a simple entity

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity(repositoryClass: PaisRepository::class)]
#[ORM\Table(name: 'pais')]
class Pais
{
    #[ORM\Id]
    #[ORM\Column(name: 'idpais', type: 'integer')]
    private ?int $idpais = null;

    #[ORM\Column(name: 'nombre', type: 'string', length: 255)]
    private ?string $nombre = null;

    #[ORM\Column(name: 'estado', type: 'integer')]
    private ?int $estado = null;
}

Step 3: Execute a repository query

// src/Repository/PaisRepository.php
public function findByNameLike(string $nombre): array
{
    return $this->createQueryBuilder('p')
        ->where('p.nombre LIKE :nombre')
        ->andWhere('p.estado = 1')
        ->setParameter('nombre', $nombre.'%')
        ->getQuery()
        ->getResult();  // Returns empty array instead of entities
}

Step 4: Debug the issue

public function findByNameLike(string $nombre): array
{
    $query = $this->createQueryBuilder('p')
        ->where('p.nombre LIKE :nombre')
        ->setParameter('nombre', $nombre.'%')
        ->getQuery();
    
    // The generated SQL has UPPERCASE aliases
    dump($query->getSQL());
    // "SELECT p0_.idpais AS IDPAIS_0, p0_.nombre AS NOMBRE_1 FROM pais p0_ WHERE ..."
    
    // Direct DBAL query returns lowercase keys (portability working)
    $stmt = $this->getEntityManager()->getConnection()->executeQuery(
        $query->getSQL(),
        ['Colombia%']
    );
    dump($stmt->fetchAssociative());
    // ['idpais_0' => 547, 'nombre_1' => 'Colombia']  ✅ lowercase
    
    // But ORM hydration returns empty
    $result = $query->getResult();
    dump($result);
    // []  ❌ Empty! Hydration failed
    
    return $result;
}

Why it fails:

1. ORM generates SQL: AS IDPAIS_0 (uppercase from SQLResultCasing based on instanceof OraclePlatform)
2. ORM builds RSM expecting: IDPAIS_0 (uppercase key)
3. DBAL portability layer converts result keys: IDPAIS_0 → idpais_0 (lowercase)
4. AbstractHydrator::hydrateColumnInfo('idpais_0') looks for idpais_0 in RSM
5. RSM only has IDPAIS_0 → lookup fails → returns null
6. No field mapping found → entity not hydrated → empty result

Key insight: The ORM cannot detect that portability is being applied because the portability layer operates at the DBAL connection level, transparently wrapping the driver. The platform still reports UnquotedIdentifierFolding::UPPER, but the actual results come back in lowercase.

Expected behavior

The ideal solution would allow users to create a custom platform that properly declares its identifier folding behavior, and have the ORM respect it:

// Custom platform with lowercase folding
class CustomOraclePlatform extends OraclePlatform
{
    public function __construct()
    {
        // Use lowercase folding to match portability configuration
        parent::__construct(UnquotedIdentifierFolding::LOWER);
    }
}

However, this approach doesn't work because SQLResultCasing ignores the UnquotedIdentifierFolding setting and uses hardcoded checks instead.

The SQLResultCasing should respect the platform's identifier folding configuration:

private function getSQLResultCasing(AbstractPlatform $platform, string $column): string
{
    return match ($platform->getUnquotedIdentifierFolding()) {
        UnquotedIdentifierFolding::UPPER => strtoupper($column),
        UnquotedIdentifierFolding::LOWER => strtolower($column),
        UnquotedIdentifierFolding::NONE => $column,
    };
}

This would allow:

1. Custom platforms to define their own casing behavior
2. Consistency between SQL generation (ORM) and result processing (DBAL)
3. Portability configurations to work correctly with ORM queries

Impact

• Breaks DBAL portability integration: The portability layer normalizes result keys, but the ORM cannot be configured to match this behavior
• No detection mechanism: The ORM has no way to know that portability is being applied at the connection level
• Inconsistent behavior: Direct DBAL queries work fine with portability, but ORM queries fail silently (return empty results)
• No extensibility: Cannot create custom platforms with different identifier folding because SQLResultCasing uses hardcoded checks
• Confusing debugging: The query executes successfully, returns data, but hydration silently fails

Proposed solution

Update SQLResultCasing::getSQLResultCasing() to use the modern API:

private function getSQLResultCasing(AbstractPlatform $platform, string $column): string
{
    return match ($platform->getUnquotedIdentifierFolding()) {
        UnquotedIdentifierFolding::UPPER => strtoupper($column),
        UnquotedIdentifierFolding::LOWER => strtolower($column),
        UnquotedIdentifierFolding::NONE => $column,
    };
}

This would:

• ✅ Maintain BC for default platforms (Oracle still uses UPPER by default)
• ✅ Allow custom platforms to override the behavior
• ✅ Make portability configurations work correctly
• ✅ Provide consistency between DBAL and ORM
• ✅ Follow the modern DBAL 4.x API design

Additional notes

The ORM has no mechanism to detect that portability middleware is being applied. While OraclePlatform declares UnquotedIdentifierFolding::UPPER by default (which is correct), users should be able to:

1. Extend the platform and use a different folding strategy to match their portability configuration
2. Have the ORM respect that custom folding configuration
3. Use portability layers without breaking ORM functionality

The current hardcoded approach in SQLResultCasing prevents all of these use cases.

Note: The UnquotedIdentifierFolding API was specifically introduced in DBAL 4.x (via doctrine/dbal#6823) to provide a unified way to handle identifier casing across different platforms. The ORM should leverage this API instead of maintaining its own hardcoded platform checks. This would allow users to create custom platforms that declare their folding strategy (e.g., LOWER to match portability configuration), and have the ORM generate SQL accordingly.

---

Related files and references

• Bug location: vendor/doctrine/orm/src/Internal/SQLResultCasing.php (line 18)
• Expected behavior defined in: vendor/doctrine/dbal/src/Platforms/AbstractPlatform.php (getUnquotedIdentifierFolding())
• Hydration failure in: vendor/doctrine/orm/src/Internal/Hydration/AbstractHydrator.php (hydrateColumnInfo())
• Related DBAL PR: Extract UnquotedIdentifierFolding from AbstractPlatform dbal#6823 (Introduction of UnquotedIdentifierFolding)