Bug #92514

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

Added by Stephan GroƟberndt 15 days ago.

Status:
New
Priority:
Should have
Assignee:
-
Category:
Extbase
Target version:
-
Start date:
2020-10-08
Due date:
% Done:

0%

TYPO3 Version:
9
PHP Version:
Tags:
postgresql
Complexity:
Is Regression:
Sprint Focus:

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 Dolor@s 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.

Also available in: Atom PDF