#87190Can not use groupBy in ContentObjectRenderer with MySQL strict mode
Description
Currently it is not possible to use GROUP BY with the ContentObjectRenderer when using MySQL in strict mode (as it is by default with MySQL 5.7). The problem is that the method sanitizeSelectPart adds uid,@pid@ and t3ver_state fields that MySQL does not know how to aggregate.
How to reproduce
Create a new page with a new template record and the following TypoScript setup:
page = PAGE
page.10 = CONTENT
page.10 {
table = tt_content
select {
selectFields = YEAR(FROM_UNIXTIME(crdate)) AS years
groupBy = years
}
renderObj = COA
renderObj {
10 = TEXT
10.field = years
10.wrap = year: |<br>
}
}
Error message
An exception occurred while executing 'SELECT YEAR(FROM_UNIXTIME(crdate)) AS years, `tt_content`.`uid` AS `uid`, `tt_content`.`pid` AS `pid`, `tt_content`.`t3ver_state` AS `t3ver_state` FROM `tt_content` WHERE [..] GROUP BY `years` ORDER BY `years` DESC': Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'typo3_9.tt_content.uid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Workaround
A workaround is to add COUNT(*) to the selectFields list as this prevents TYPO3 from adding the additional fields.
page.10.select.selectFields = YEAR(FROM_UNIXTIME(crdate)) AS years, COUNT(*)
Possible solution
Maybe it would be possible to not use sanitizeSelectPart if there is a groupBy option set.
Reviews 2
History 59
-
Markus Mächler commented <span data-processor="relativetime" data-value="2018-12-17T16:49:54+00:00" > 12/17/18, 16:49 </span>
-
Markus Mächler changed subject
Can not use orderBy in ContentObjectRenderer with MySQL strict modeCan not use groupBy in ContentObjectRenderer with MySQL strict mode <span data-processor="relativetime" data-value="2018-12-17T16:54:19+00:00" > 12/17/18, 16:54 </span> -
Susanne Moog changed category <span data-processor="relativetime" data-value="2020-03-26T09:51:17+00:00" > 03/26/20, 09:51 </span>
-
Gerrit Code Review pushed patch set 1 for main to change 84725 status
NewUnder Review <span data-processor="relativetime" data-value="2024-06-15T19:26:56+00:00" > 06/15/24, 19:26 </span> -
Gerrit Code Review pushed patch set 2 for main to change 84725 <span data-processor="relativetime" data-value="2024-07-08T17:24:56+00:00" > 07/08/24, 17:24 </span>
-
Gerrit Code Review pushed patch set 3 for main to change 84725 <span data-processor="relativetime" data-value="2026-02-25T11:21:07+00:00" > 02/25/26, 11:21 </span>
-
Gerrit Code Review pushed patch set 1 for 13.4 to change 93007 <span data-processor="relativetime" data-value="2026-02-26T11:10:18+00:00" > 02/26/26, 11:10 </span>
-
Georg Ringer commented and changed status
Under ReviewResolved done0 %100 % <span data-processor="relativetime" data-value="2026-02-26T11:20:08+00:00" > 02/26/26, 11:20 </span>Applied in changeset commit:7e012577716c683e8e1dab67f7a93912fbfc9a54.
-
Benni Mack changed status
ResolvedClosed <span data-processor="relativetime" data-value="2026-03-31T09:00:28+00:00" > 03/31/26, 09:00 </span>
This also affects
TYPO3 8and probably older versions as well.