TYPO3 Core

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.