Issue Bug Closed

#87190Can not use groupBy in ContentObjectRenderer with MySQL strict mode

Markus Mächler 12/17/18, 16:47 updated 03/31/26, 09:00

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:&nbsp;|<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

  1. Markus Mächler commented <span data-processor="relativetime" data-value="2018-12-17T16:49:54+00:00" > 12/17/18, 16:49 </span>

    This also affects TYPO3 8 and probably older versions as well.

  2. Markus Mächler changed subject Can not use orderBy in ContentObjectRenderer with MySQL strict mode Can 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>

  3. Georg Ringer commented and changed status Under Review Resolved done 0 % 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.