Issue Bug New Orphaned

#92514SELECT DISTINCT and ORDER BY in Extbase query lead to database error when using PostgreSQL

Stephan Großberndt 10/08/20, 10:08 updated 10/07/25, 12:18

Description

Model Lorem has a property $ipsum for a 1:1 relation to Model Ipsum, which has a property $name.
Model Lorem has a property $dolors for a n:1 relation to Model Dolor, which has a property $lorem and a property $code

The query

class LoremRepository extends \TYPO3\CMS\Extbase\Persistence\Repository {
public function findLoremFilteredByDolorsOrderedByIpsum()
  $query = $this->createQuery();
  return $query
    ->matching($query->in('dolors.code', ['A', 'B']))
    ->setOrderings(['ipsum.name' => 'ASC'])
    ->execute();
  }
}

is translated by Extbase to the following SQL query

SELECT DISTINCT tx_my_lorem.*
FROM tx_my_lorem
LEFT JOIN tx_my_dolor ON tx_my_lorem.uid = tx_my_dolor.lorem
LEFT JOIN tx_my_ipsum ON tx_my_lorem.ipsum = tx_my_ipsum.uid
WHERE tx_my_dolor.code IN ('A','B')
ORDER BY tx_my_ipsum.name;

This query works on MySQL but fails on PostgreSQL (and probably SQLite, MSSQL as well) with a Doctrine\DBAL\Exception\DriverException: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

This happens because the DISTINCT result is sorted by tx_my_ipsum.name, a column which is not retrieved in SELECT.

In
@extbase/Classes/Persistence/Generic/Storage/Typo3DbQueryParser.php@
for $columnMap->getTypeOfRelation() = ColumnMap::RELATION_HAS_MANY)@ and @$columnMap->getTypeOfRelation() = ColumnMap::RELATION_HAS_AND_BELONGS_TO_MANY)
the property $this->suggestDistinctQuery = true; is set.
This is used in the method getObjectDataByQuery() of extbase/Classes/Persistence/Generic/Storage/Typo3DbBackend.php
by checking isDistinctQuerySuggested()
to add DISTINCT to the query: $selectParts[0] = 'DISTINCT ' . $selectParts[0];

Extbase adds the DISTINCT because requesting a Lorem filtered by multiple Dolors could lead to the same Lorem being returned multiple times which is not wanted in Extbase context when constituting objects.

The sorting by tx_my_ipsum.name is the wanted and correct behaviour.

In case Extbase adds the DISTINCT to the query it must also SELECT the ORDER BY columns, even if it does not use them (as a random column name?).
When selecting the ORDER BY columns care must be taken this random column name does not interfere with the columns of lorem

SELECT DISTINCT tx_my_lorem.*, tx_my_ipsum.name AS some_random_string_which_does_not_interfere_with_lorem_columns
FROM tx_my_lorem
LEFT JOIN tx_my_dolor ON tx_my_lorem.uid = tx_my_dolor.lorem
LEFT JOIN tx_my_ipsum ON tx_my_lorem.ipsum = tx_my_ipsum.uid
WHERE tx_my_dolor.code IN ('A','B')
ORDER BY tx_my_ipsum.name;

This query works on PostgreSQL as well.

Reviews 0

No change resolves or names this issue in its commit message, and no comment links one.

Fix this issue

Start the commit message of your patch with this. Add what the patch does, and the versions it goes to in "Releases:". How to create a patch

Commit message
[BUGFIX] SELECT DISTINCT and ORDER BY in Extbase query lead to database error when using PostgreSQL Resolves: #92514 Releases: main