Project

General

Profile

Actions

Bug #79988

closed

Fix ensureCorrectPostOrderingByCategoryTitle extbase test on postgres

Added by Christian Kuhn over 7 years ago. Updated over 7 years ago.

Status:
Closed
Priority:
Should have
Assignee:
-
Category:
-
Target version:
Start date:
2017-02-24
Due date:
% Done:

0%

Estimated time:
TYPO3 Version:
8
PHP Version:
Tags:
Complexity:
Is Regression:
No
Sprint Focus:
Stabilization Sprint

Related issues 5 (0 open5 closed)

Related to TYPO3 Core - Task #79987: Disable ensureCorrectPostOrderingByCategoryTitle extbase test on postgresClosed2017-02-24

Actions
Related to TYPO3 Core - Bug #79932: extbase: wrong doctrine migration yields wrong DB resultClosed2017-02-21

Actions
Related to TYPO3 Core - Bug #79931: Extbase ordering of results via child record (through MM) wrongClosed2017-02-20

Actions
Related to TYPO3 Core - Bug #79330: Extbase getObjectCountByQuery returns wrong resultsClosedMorton Jonuschat2017-01-15

Actions
Related to TYPO3 Core - Task #80014: Remove extbase ensureCorrectPostOrderingByCategoryTitle testClosedMorton Jonuschat2017-02-25

Actions
Actions #1

Updated by Christian Kuhn over 7 years ago

The functional test in extbase with the 3 data sets added with patch https://review.typo3.org/#/c/51320/ reveals a nasty issue in extbase query building:

TYPO3\CMS\Extbase\Persistence\Generic\Storage\Exception\SqlErrorException: SQLSTATE42803: Grouping error: 7 ERROR: column "sys_category.title" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ..."tx_blogexample_domain_model_post"."uid" ORDER BY "sys_categ...

The offending query is:

SELECT "tx_blogexample_domain_model_post".*
FROM "tx_blogexample_domain_model_post" "tx_blogexample_domain_model_post" 
LEFT JOIN "sys_category_record_mm" "sys_category_record_mm" ON ("tx_blogexample_domain_model_post"."uid" = sys_category_record_mm.uid_foreign) AND (("sys_category_record_mm"."tablenames" = :dcValue1) AND ("sys_category_record_mm"."fieldname" = :dcValue2))
LEFT JOIN "sys_category" "sys_category" ON "sys_category_record_mm"."uid_local" = sys_category.uid
WHERE ("tx_blogexample_domain_model_post"."uid" IN (5, 2)) AND ("tx_blogexample_domain_model_post"."sys_language_uid" IN (0, -1)) AND ("tx_blogexample_domain_model_post"."pid" = 0) AND (tx_blogexample_domain_model_post.hidden=0 AND "tx_blogexample_domain_model_post"."deleted" = 0) AND ((("sys_category"."sys_language_uid" IN (0, -1)) AND ("sys_category"."pid" IN (0, 0)) AND (sys_category.hidden=0 AND (sys_category.starttime<=1487870820) AND (sys_category.endtime=0 OR sys_category.endtime>1487870820) AND "sys_category"."deleted" = 0)) OR ("sys_category"."uid" IS NULL))
GROUP BY "tx_blogexample_domain_model_post"."uid" 
ORDER BY "sys_category"."title" ASC, "tx_blogexample_domain_model_post"."uid" ASC

The problem is basically that the order by 'sys_category.title' requires that this field is within the group by, too - but that changes the result set.

The group by is added by typo3/sysext/extbase/Classes/Persistence/Generic/Storage/Typo3DbQueryParser.php ~980 where the join handling is done in method addUnionStatement().

Playing around with the query, a limited solution could be:


SELECT DISTINCT ON (category.title) post.*
FROM post 
    LEFT JOIN cat_mm ON (post.uid = cat_mm.uid_foreign) 
    LEFT JOIN category ON cat_mm.uid_local = category.uid 

    WHERE (post.uid IN (1, 2))
    ORDER BY category.title ASC, post.uid ASC

Deal is here that the "category.title" order by is the first in the order list, and this is then used as "distinct on" ... limit: this works only if there is no further sorting added.

Actions #2

Updated by Christian Kuhn over 7 years ago

  • Status changed from New to Closed

issue handled with #80014

Actions

Also available in: Atom PDF