Issue Feature Closed

#81451Support mysql mode "ONLY_FULL_GROUP_BY"

Cihan Yesilöz 06/03/17, 09:29 updated 09/03/17, 20:54

Description

TYPO3 does not support the “ONLY_FULL_GROUP_BY” strict mode of MySQL. Therefore TYPO3 shows the exception “#1472074485: ‘company.tx_faq_domain_model_question.pid’ isn’t in GROUP BY” in case ONLY_FULL_GROUP_BY is disrespected.

Example:

class QuestionRepository extends Repository
{
    /**
     * @return array|QueryResultInterface
     */
    public function findBySettings()
    {
        $query = $this->createQuery();
        $categoryUids = [1,2];
        $or = [];
        foreach ($categoryUids as $categoryUid) {
            $or[] = $query->equals('categories.uid', (int)$categoryUid);
        }

        $query->matching($query->logicalOr($or));
        return $query->execute();
    }
}

Leads to this SQL-query:

SELECT `tx_company1_domain_model_question`.* 
FROM `tx_company1_domain_model_question` `tx_company1_domain_model_question` 
  LEFT JOIN `tx_company1_question_category_mm` `tx_company1_question_category_mm` 
    ON `tx_company1_domain_model_question`.`uid` = tx_company1_question_category_mmuid_local 
  LEFT JOIN `tx_company1_domain_model_category` `tx_company1_domain_model_category` 
    ON `tx_company1_question_category_mm`.`uid_foreign` = tx_company1_domain_model_category.uid 
WHERE ...
GROUP BY `tx_company1_domain_model_question`.`uid` 

According to the MySQL-documentation “ONLY_FULL_GROUP_BY” requires each column, used in the SELECT-clause to be also listed in the “GROUP BY” clause.

Responsible places in the TYPO3-core which are causing the issue:

To add support for “ONLY_FULL_GROUP_BY” the only possible solution which pops into my mind is:
*1)* First only fetch the uid’s instead of ““.
*2)
Build a new query, which returns all records for the uid’s found in 1)

Reviews 0

No change resolves or names this issue in its commit message, and no comment links one.