Actions
Bug #79330
closed
Extbase getObjectCountByQuery returns wrong results
Start date:
2017-01-15
Due date:
% Done:
100%
Estimated time:
TYPO3 Version:
8
PHP Version:
7.0
Tags:
Complexity:
Is Regression:
No
Sprint Focus:
Description
Before Extbase was converted to use Doctrine the method used a
COUNT(DISTINCT uid)to count on joined tables. With the migration to Doctrine this was changed to use GROUP BY uid. This works in most cases, for this use case it ends up with a different resultset.
This is visible in the functional tests in the BlogExample. The goal is to count all persons which use a Tag in either of two relations. In total there are three persons using that tag. 1 using it in both relations, and one each using it in one of them.
Thus the correct the result of the query is 3.
With the old DISTINCT based query the result would be 1 row with a count of 3. This is the intended result.
With the GROUP BY based count the resultset consists of three rows, each containing the count of usages per uid.
By coincidence MySQL implictly sorts the results in such a way the the first row of the resultset has a count of 3 uses for that uid (the other rows have 1 use each). PostgreSQL for example has the same resultset, but in a different implicit order. The first row has a counted usage of 1.
As the current getObjectCountByQuery only uses the first result row as the count the functional tests fail to detect the error on MySQL but it shows up on PostgreSQL.
Updated by Morton Jonuschat over 7 years ago
This is really easo to see when adding a fourth example record to the dataset
New query:
SELECT COUNT(*) FROM `tx_blogexample_domain_model_person` `tx_blogexample_domain_model_person` LEFT JOIN `tx_blogexample_domain_model_tag_mm` `tx_blogexample_domain_model_tag_mm` ON `tx_blogexample_domain_model_person`.`uid` = tx_blogexample_domain_model_tag_mm.uid_local LEFT JOIN `tx_blogexample_domain_model_tag_mm` `tx_blogexample_domain_model_tag_mm0` ON `tx_blogexample_domain_model_person`.`uid` = tx_blogexample_domain_model_tag_mm0.uid_local LEFT JOIN `tx_blogexample_domain_model_tag` `tx_blogexample_domain_model_tag` ON `tx_blogexample_domain_model_tag_mm`.`uid_foreign` = tx_blogexample_domain_model_tag.uid LEFT JOIN `tx_blogexample_domain_model_tag` `tx_blogexample_domain_model_tag0` ON `tx_blogexample_domain_model_tag_mm0`.`uid_foreign` = tx_blogexample_domain_model_tag0.uid WHERE ((`tx_blogexample_domain_model_tag`.`name` = 'TagForAuthor1') OR (`tx_blogexample_domain_model_tag0`.`name` = 'SpecialTagForAuthor1')) AND (`tx_blogexample_domain_model_person`.`pid` = 0) AND (tx_blogexample_domain_model_person.hidden=0 AND `tx_blogexample_domain_model_person`.`deleted` = 0) AND (((`tx_blogexample_domain_model_tag`.`pid` = 0) AND (tx_blogexample_domain_model_tag.hidden=0 AND `tx_blogexample_domain_model_tag`.`deleted` = 0)) OR (`tx_blogexample_domain_model_tag`.`uid` IS NULL)) AND (((`tx_blogexample_domain_model_tag0`.`pid` = 0) AND (tx_blogexample_domain_model_tag0.hidden=0 AND `tx_blogexample_domain_model_tag0`.`deleted` = 0)) OR (`tx_blogexample_domain_model_tag0`.`uid` IS NULL)) GROUP BY `tx_blogexample_domain_model_person`.`uid`, `tx_blogexample_domain_model_person`.`uid`
Result:
3 1 1 1
Old DISTINCT query
SELECT COUNT(DISTINCT tx_blogexample_domain_model_person.uid) FROM `tx_blogexample_domain_model_person` `tx_blogexample_domain_model_person` LEFT JOIN `tx_blogexample_domain_model_tag_mm` `tx_blogexample_domain_model_tag_mm` ON `tx_blogexample_domain_model_person`.`uid` = tx_blogexample_domain_model_tag_mm.uid_local LEFT JOIN `tx_blogexample_domain_model_tag_mm` `tx_blogexample_domain_model_tag_mm0` ON `tx_blogexample_domain_model_person`.`uid` = tx_blogexample_domain_model_tag_mm0.uid_local LEFT JOIN `tx_blogexample_domain_model_tag` `tx_blogexample_domain_model_tag` ON `tx_blogexample_domain_model_tag_mm`.`uid_foreign` = tx_blogexample_domain_model_tag.uid LEFT JOIN `tx_blogexample_domain_model_tag` `tx_blogexample_domain_model_tag0` ON `tx_blogexample_domain_model_tag_mm0`.`uid_foreign` = tx_blogexample_domain_model_tag0.uid WHERE ((`tx_blogexample_domain_model_tag`.`name` = 'TagForAuthor1') OR (`tx_blogexample_domain_model_tag0`.`name` = 'SpecialTagForAuthor1')) AND (`tx_blogexample_domain_model_person`.`pid` = 0) AND (tx_blogexample_domain_model_person.hidden=0 AND `tx_blogexample_domain_model_person`.`deleted` = 0) AND (((`tx_blogexample_domain_model_tag`.`pid` = 0) AND (tx_blogexample_domain_model_tag.hidden=0 AND `tx_blogexample_domain_model_tag`.`deleted` = 0)) OR (`tx_blogexample_domain_model_tag`.`uid` IS NULL)) AND (((`tx_blogexample_domain_model_tag0`.`pid` = 0) AND (tx_blogexample_domain_model_tag0.hidden=0 AND `tx_blogexample_domain_model_tag0`.`deleted` = 0)) OR (`tx_blogexample_domain_model_tag0`.`uid` IS NULL))
Result:
4
Updated by Gerrit Code Review over 7 years ago
- Status changed from New 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/51320
Updated by Gerrit Code Review over 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/51320
Updated by Morton Jonuschat about 7 years ago
- Status changed from Under Review to Resolved
- % Done changed from 0 to 100
Applied in changeset 728afa52280d88d55a67bd821d20db33cf525ac2.
Updated by Riccardo De Contardi over 6 years ago
- Status changed from Resolved to Closed
Updated by Michael Grundkötter about 3 years ago
- Related to Bug #93576: Typo3DbBackend::getObjectCountByQuery does not work correctly with custom doctrine queries added
Actions