Issue Bug Closed

#103961Postgres: Extbase LIKE query is case sensitive

Andreas Kießling 05/31/24, 15:23 updated 07/09/24, 15:35

Description

While updating a pretty old site to 12.4 i stumbled up on a like query that is now executed case sensitive.
Similar to #98570 —> \TYPO3\CMS\Extbase\Persistence\Generic\Storage\Typo3DbQueryParser::parseDynamicOperand

Kind of ugly to patch that in there, but seems to do the job.

case QueryInterface::OPERATOR_LIKE:
    $placeHolder = $this->createTypedNamedParameter($value, Connection::PARAM_STR);
    $platform = $this->queryBuilder->getConnection()->getDatabasePlatform();
    if ($platform instanceof \Doctrine\DBAL\Platforms\PostgreSQLPlatform) {
        $expr = $exprBuilder->comparison($fieldName, 'ILIKE', $placeHolder);
    } else {
        $expr = $exprBuilder->comparison($fieldName, 'LIKE', $placeHolder);
    }
    break;

It seems that the old adodb/dbal setup automatically used ILIKE for Postgres

if ($isLikeOperator && $functionMapping) {
    if ($this->databaseConnection->runningADOdbDriver('postgres') || $this->databaseConnection->runningADOdbDriver('postgres64') || $this->databaseConnection->runningADOdbDriver('postgres7') || $this->databaseConnection->runningADOdbDriver('postgres8')) {
        // Remap (NOT)? LIKE to (NOT)? ILIKE
        // and (NOT)? LIKE BINARY to (NOT)? LIKE
        switch ($v['comparator']) {
            case 'LIKE':
                $v['comparator'] = 'ILIKE';
                break;
            case 'NOT LIKE':
                $v['comparator'] = 'NOT ILIKE';
                break;
            default:

Maybe the old behavior should be restored? I’m fine with having my instance patched though if you’d like to stick to the defaults

Reviews 2