'CASE WHEN...' ok with addSelect() and addOrderBy(), but KO with addGroupBy()

[spacecodeur]

Bug Report

Q	A
BC Break	no
Version	2.12.3

Summary

On Symfony 6 and Doctrine 2.12.3, I build a new query.
In this query, I need to use 'CASE WHEN ...' in SELECT, ORDER BY and GROUP BY.

With the methods addSelect() and addOrderBy() all works fine. But I got an error if I use CASE WHEN in the parameter of the function addGroupBy(), the error is :

'Cannot group by undefined identification or result variable'

I get the same error on sqlite3 or mariadb

Current behavior

For SELECT and ORDER BY, all work fine :

$query      ->addSelect("CASE
    WHEN content.col1 IS NOT NULL THEN 'foo'
    WHEN content.col2 IS NOT NULL THEN 'faa'
    ELSE 'fuu'
END AS col4added")

            ->addOrderBy('CASE
    WHEN content.col1 IS NOT NULL THEN content.col1
    WHEN content.col2 IS NOT NULL THEN content.col2
    ELSE content.col3
END','ASC')->getQuery()->getResult();

The query works without any error.
But if I use CASE WHEN ... in a addGroupBy() function :

...
$query      ->addSelect("CASE
    WHEN content.col1 IS NOT NULL THEN 'foo'
    WHEN content.col2 IS NOT NULL THEN 'faa'
    ELSE 'fuu'
END AS col4added")

            ->addOrderBy('CASE
    WHEN content.col1 IS NOT NULL THEN content.col1
    WHEN content.col2 IS NOT NULL THEN content.col2
    ELSE content.col3
END','ASC')

            ->addGroupBy('CASE 
    WHEN content.col1 IS NOT NULL THEN content.col1 
    ELSE content.col2
END')->getQuery()->getResult();

I get the error : ''Cannot group by undefined identification or result variable''

How to reproduce

With query builder, just add a addGroupBy using CASE WHEN and you'll get the error 'Cannot group by undefined identification or result variable'' if you execute the query.

I created a demo project for illustrate the bug. The link of the repo is here.

Here the commands for clone and deploy a local server for easily see the bug :

cd /tmp && \
git clone git@gitlab.com:spacecodeur/doctrineissuecasewheningroupby.git && \
cd doctrineIssueCaseWhenInGroupBy && \
composer install && \
./bin/console d:d:c && ./bin/console d:s:c && ./bin/console d:f:l -q && \
symfony server:start -d --port=9123

Open the URL https://127.0.0.1:9123 with your favorite browser. And then, you'll see the home page where the issue is showed :

And if you click on the link, to the bottom, you'll be redirected to a page using the query using addGroupBy() with CASE WHEN ... and get the error :

Expected behavior

The query does not return an error. Use GROUP BY CASE WHEN ... return the expected result.

With DB Browser tool, I get the generated query in the bundle profiler of Symfony. And I added GROUP BY CASE WHEN .... The query is executed without any error and the expected result of the query is showed :

Here the query if you want to copy/past :

SELECT c0_.id AS id_0, c0_.col1 AS col1_1, c0_.col2 AS col2_2, c0_.col3 AS col3_3, 
CASE 
	WHEN c0_.col1 IS NOT NULL THEN 'foo' 
	WHEN c0_.col2 IS NOT NULL THEN 'faa' 
	ELSE 'fuu' 
END AS sclr_4 

FROM content c0_ 

GROUP BY CASE
	WHEN c0_.col1 IS NOT NULL THEN c0_.col1 
    ELSE c0_.col2
END
	
ORDER BY CASE 
	WHEN c0_.col1 IS NOT NULL THEN c0_.col1 
	WHEN c0_.col2 IS NOT NULL THEN c0_.col2 
	ELSE c0_.col3 
END ASC

temporary solution

After the query builder, I can extract the SQL raw query (with $query->getQuery()->getSQL()). And then, add in the string the GROUP BY CAS WHEN ....
But with this approach, I can't get results as entity objects and its very annoying...

If someone has a better solution for skirt this (apparently) bug, I take !
I have an huge building query on my website (containing several conditions, and take more than 200 lines). I really really need to use groupBy() with CASE WHEN haha

[greg0ire]

The bug still the same on sqlite3 or mariadb

I don't think it's a bug, see https://www.doctrine-project.org/projects/doctrine-orm/en/2.12/reference/dql-doctrine-query-language.html#items

GroupByItem ::= IdentificationVariable | ResultVariable | SingleValuedPathExpression

I think ResultVariable might help you:

/* ResultVariable identifier usage of mapped field aliases (the "total" of "COUNT(*) AS total") */
ResultVariable = identifier

Try aliasing the case to a variable, then group by that variable?

[spacecodeur]

Thanks for your (very) quickly reply :)