Queries in FormDataRepository failing when sql_mode=only_full_group_by is enabled

[samsauter]

Hello there,

the queries build in FormDataRepository.php which are using a GROUP BY statement are causing exceptions to be thrown if sql_mode=only_full_group_by is configured (which seems to be the case in many server setups).

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db-1.p0_.persistence_object_identifier' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I added addGroupBy() statements for every column in this table to get it to work. There are two affected methods:

    public function findAllUniqueForms(): iterable
    {
        $queryBuilder = $this->createQueryBuilder('form');
        return $queryBuilder
            ->groupBy('form.formIdentifier')
	    ->addGroupBy('form.Persistence_Object_Identifier')
            ->addGroupBy('form.hash')
            ->addGroupBy('form.formData')
	    ->addGroupBy('form.date')
	    ->addGroupBy('form.siteName')
	    ->addGroupBy('form.contentDimensions')
            ->addSelect('count(form) AS entryCount')
            ->addSelect('MAX(form.date) as latestDate')
            ->orderBy('latestDate', 'DESC')
            ->getQuery()->execute();
    }

protected function findAllUniqueSiteNames(): array
    {
        $queryBuilder = parent::createQueryBuilder('form');
        $formDataGroupedBySite = $queryBuilder
            ->groupBy('form.siteName')
	    ->addGroupBy('form.Persistence_Object_Identifier')
	    ->addGroupBy('form.formIdentifier')
	    ->addGroupBy('form.hash')
	    ->addGroupBy('form.formData')
	    ->addGroupBy('form.date')
	    ->addGroupBy('form.contentDimensions')
            ->getQuery()->execute();
        …
    }

Do you think this is fine? It seems like it, though.

[daniellienert]

@samsauter, thanks for the issue. Sadly your approach won't work. When grouping by anything, the grouping has no effect anymore. I currently try to get my mariaDB into that mode.

[samsauter]

@daniellienert Oh, of course … I did not check with multiple sites or just different forms and look and the actual results. I just wanted the query to not throw an error anymore. Thank you for looking into it!

[paavo]

Any plans if and when this will be fixed @daniellienert?
I'd love to use this package.

[paavo]

Is there a known workaround @daniellienert or @samsauter?
disabling sql_mode=only_full_group_by maybe?

[paavo]

Looks like we need to reopen this bug @daniellienert @samsauter 🙈
When using privilegeTargets this throws an Error SELECT list is not in GROUP BY

[paavo]

May someone reopen this Bug @daniellienert @samsauter?

[samsauter]

May someone reopen this Bug @daniellienert @samsauter?

Sry, I don't have permissions to reopen.

[daniellienert]

Hey Guys, I reopened the issue. I already tried to rewrite the queries but it seems that it is pretty hard to do using the doctrine persistence layer and we would need to completely rewrite it. Sadly I have to say, that we do not have this restrictions in any setup of our projects. But of course we appreciate any PR fixing the issue.