TYPO3 Core

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: SQLSTATE⁴²⁸⁰³: 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.