Bug #87190

Can not use groupBy in ContentObjectRenderer with MySQL strict mode

Added by Markus Mächler 12 months ago. Updated 12 months ago.

Status:
New
Priority:
Should have
Assignee:
-
Category:
-
Target version:
-
Start date:
2018-12-17
Due date:
% Done:

0%

TYPO3 Version:
9
PHP Version:
Tags:
Complexity:
Is Regression:
Sprint Focus:

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.

History

#1 Updated by Markus Mächler 12 months ago

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

#2 Updated by Markus Mächler 12 months ago

  • Subject changed from Can not use orderBy in ContentObjectRenderer with MySQL strict mode to Can not use groupBy in ContentObjectRenderer with MySQL strict mode

Also available in: Atom PDF