Bug #81490
closedMissing DISTINCT when building query with left joins in extbase repository
0%
Description
While migrating an extbase extension from 6.2 to 8.7 I noticed that a custom repository query returned duplicate result rows in T3 V8.7.
In simple terms, the model hierarchy in this example is: a competition can have one or more editions and an edition can have one or more submissionslots.
The task is to retrieve all competitions with at least one valid submission slot (i.e. deadline must not be smaller than defined start date).
The deadline check in the competition repository is done with:
$query->greaterThanOrEqual('editions.submissionSlots.deadline', $start->format('Y-m-d'));
In T3 V6.2 this resulted in the following SQL statement (simplified for readability):
SELECT DISTINCT tx_myext_competition.*
FROM tx_myext_competition
LEFT JOIN tx_myext_edition ON tx_myext_competition.uid=tx_myext_edition.competition
LEFT JOIN tx_myext_submissionslot ON tx_myext_edition.uid=tx_myext_submissionslot.edition
WHERE tx_myext_submissionslot.deadline >= '2017-06-06' [...]
In T3 V8.7 the same query results in:
SELECT tx_myext_competition.*
FROM tx_myext_competition tx_myext_competition
LEFT JOIN tx_myext_edition tx_myext_edition ON tx_myext_competition.uid = tx_myext_edition.competition
LEFT JOIN tx_myext_submissionslot tx_myext_submissionslot ON tx_myext_edition.uid = tx_myext_submissionslot.edition
WHERE tx_myext_submissionslot.deadline >= '2017-06-06' [...]
The crucial difference is the missing DISTINCT, which results in duplicate rows in the result set as a competition can have multiple valid submission slots.
(The table name aliases are also a bit weird, but do no harm...)
Updated by Morton Jonuschat about 7 years ago
- Status changed from New to Needs Feedback
Can you post the full queries, it will make debugging easier.
Updated by Michael Stopp about 7 years ago
The actual query is really huge and complicated, but I can boil it down to a very simple test case:
public function findActiveCompetitions($startDate = 'now') {
$start = new \DateTime($startDate);
$query = $this->createQuery();
$query->matching($query->greaterThanOrEqual('editions.submissionSlots.deadline', $start->format('Y-m-d')));
return $query->execute();
}
The above repository method generates the following SQL statement in 6.2:
SELECT DISTINCT tx_myext_competition.*
FROM tx_myext_competition
LEFT JOIN tx_myext_edition ON tx_myext_competition.uid=tx_myext_edition.competition
LEFT JOIN tx_myext_submissionslot ON tx_myext_edition.uid=tx_myext_submissionslot.edition
WHERE tx_myext_submissionslot.deadline >= '2017-06-07'
AND tx_myext_competition.deleted=0
AND tx_myext_competition.t3ver_state<=0
AND tx_myext_competition.pid<>-1
AND tx_myext_competition.hidden=0
AND tx_myext_competition.starttime<=1496839740
AND (tx_myext_competition.endtime=0 OR tx_myext_competition.endtime>1496839740)
AND tx_myext_edition.deleted=0
AND tx_myext_edition.t3ver_state<=0
AND tx_myext_edition.pid<>-1
AND tx_myext_edition.hidden=0
AND tx_myext_submissionslot.deleted=0
AND tx_myext_submissionslot.t3ver_state<=0
AND tx_myext_submissionslot.pid<>-1
AND tx_myext_submissionslot.hidden=0
And in 8.7 the same repository query produces:
SELECT tx_myext_competition.*
FROM tx_myext_competition tx_myext_competition
LEFT JOIN tx_myext_edition tx_myext_edition ON tx_myext_competition.uid = tx_myext_edition.competition
LEFT JOIN tx_myext_submissionslot tx_myext_submissionslot ON tx_myext_edition.uid = tx_myext_submissionslot.edition
WHERE (tx_myext_submissionslot.deadline >= '2017-06-07')
AND (
(tx_myext_competition.deleted = 0)
AND (tx_myext_competition.t3ver_state <= 0)
AND (tx_myext_competition.pid <> -1)
AND (tx_myext_competition.hidden = 0)
AND (tx_myext_competition.starttime <= 1496841180)
AND (
(tx_myext_competition.endtime = 0)
OR (tx_myext_competition.endtime > 1496841180)
)
)
AND (
(
(tx_myext_edition.deleted = 0)
AND (tx_myext_edition.t3ver_state <= 0)
AND (tx_myext_edition.pid <> -1)
AND (tx_myext_edition.hidden = 0)
)
OR (tx_myext_edition.uid IS NULL)
)
AND (
(
(tx_myext_submissionslot.deleted = 0)
AND (tx_myext_submissionslot.t3ver_state <= 0)
AND (tx_myext_submissionslot.pid <> -1)
AND (tx_myext_submissionslot.hidden = 0)
)
OR (tx_myext_submissionslot.uid IS NULL)
)
Note: tables names were shortened and backticks removed for better readability
Updated by Morton Jonuschat about 7 years ago
- Status changed from Needs Feedback to Accepted
Updated by Morton Jonuschat about 7 years ago
- Category changed from Database API (Doctrine DBAL) to Extbase
Updated by Gerrit Code Review about 7 years ago
- Status changed from Accepted to Under Review
Patch set 1 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/53503
Updated by Gerrit Code Review about 7 years ago
Patch set 2 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/53503
Updated by Gerrit Code Review about 7 years ago
Patch set 3 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/53503
Updated by Christian Kuhn about 7 years ago
- Related to Feature #80380: Missing Distinct / GroupBy option in Query added
Updated by Michael Stopp almost 7 years ago
I guess this issue could be closed. The problem (lots of duplicate rows) doesn't appear anymore with a current version of TYPO3 (8.7.6). I guess this was solved with issue #80380.
However, there still remains a weird problem: if I do something like
$sql = $queryParser->convertQueryToDoctrineQueryBuilder($query)->getSQL();
to check on the generated query, I will get the exact same SQL query in 8.7.4 and 8.7.6! But the actual result of the repository method, where this query is used, is different (duplicate rows with 8.7.4, no duplicates for 8.7.6). So the result suggests that was actually a DISTINCT query made in 8.7.6 (which the changes in #80380 seem to confirm), but the getSQL() method doesn't show that. What's going on here?
Updated by Jigal van Hemert almost 7 years ago
- Status changed from Under Review to Closed
Fixed in master.
Converting the query to doctrine and back to SQL will remove the DISTINCT as doctrine has no support for it (cross DBMS); for Extbase it's inserted at specific places to make the situations where it was used before working again.
Set to closed as the duplicate is resolved in master (future v9) and v8 branches.