Group by with alias not working with Oracle db #5727

New Issue

admin · 2026-01-22T15:15:46+01:00

admin commented

2026-01-22 15:15:46 +01:00

Originally created by @manfredjb on GitHub (Oct 4, 2017).

Originally assigned to: @lcobucci on GitHub.

In MySQL, this query run correctly:

select substr(name, 1, 1) capital_letter from users group by capital_letter;

MySQL accepts alias in order and group by clauses. However, the same query is not valid in Oracle 10g, 11g (tested cases):

select substr(name, 1, 1) capital_letter from users group by capital_letter;

ORA-00904: "capital_letter": invalid identifier

In order to get it working, you must call substr in the clause too:

select substr(name, 1, 1) capital_letter from users group by substr(name, 1, 1);

Said that, in doctrine this is only valid for MySQL:

return $em->createQueryBuilder()
    ->select(['substring(a.name, 0, 1) HIDDEN capitalLetter'])
    ->from(User::class, 'a')
    ->groupBy('capitalLetter')
    ->getQuery()
    ->getResult();

And this (expected syntax for Oracle) display an error that all of us know:

return $em->createQueryBuilder()
    ->select(['substring(a.name, 0, 1) capitalLetter'])
    ->from(User::class, 'a')
    ->groupBy('substring(a.name, 0, 1)')
    ->getQuery()
    ->getResult();

[Semantical Error] line 0, col 291 near 'substring(a.name,': Error: Cannot group by undefined identification or result variable.

I know that using native query is a workaround, but could be good to make it works in query builder for Oracle vendor too. I would like to know your comments about. Thank you in advance.

Originally created by @manfredjb on GitHub (Oct 4, 2017). Originally assigned to: @lcobucci on GitHub. In MySQL, this query run correctly: ```sql select substr(name, 1, 1) capital_letter from users group by capital_letter; ``` MySQL accepts alias in `order` and `group by` clauses. However, the same query is not valid in Oracle 10g, 11g (tested cases): ```sql select substr(name, 1, 1) capital_letter from users group by capital_letter; ``` > ORA-00904: "capital_letter": invalid identifier In order to get it working, you must call `substr` in the clause too: ```sql select substr(name, 1, 1) capital_letter from users group by substr(name, 1, 1); ``` Said that, in doctrine this is only valid for MySQL: ```php return $em->createQueryBuilder() ->select(['substring(a.name, 0, 1) HIDDEN capitalLetter']) ->from(User::class, 'a') ->groupBy('capitalLetter') ->getQuery() ->getResult(); ```` And this (expected syntax for Oracle) display an error that all of us know: ```php return $em->createQueryBuilder() ->select(['substring(a.name, 0, 1) capitalLetter']) ->from(User::class, 'a') ->groupBy('substring(a.name, 0, 1)') ->getQuery() ->getResult(); ```` > [Semantical Error] line 0, col 291 near 'substring(a.name,': Error: Cannot group by undefined identification or result variable. I know that using native query is a workaround, but could be good to make it works in query builder for Oracle vendor too. I would like to know your comments about. Thank you in advance.

admin added the Improvement Won't Fix labels 2026-01-22 15:15:46 +01:00

admin closed this issue

2026-01-22 15:15:47 +01:00

admin commented

2026-01-22 15:15:48 +01:00

@lcobucci commented on GitHub (Nov 26, 2017):

@manfredjb I'm sorry but I think that the ORM shouldn't handle platform-specific things. To be honest I'd say that you could use the DBAL connection directly because you don't even need the ORM stuff to build this query (your desired result set contains only one column and not the entity).

@lcobucci commented on GitHub (Nov 26, 2017): @manfredjb I'm sorry but I think that the ORM shouldn't handle platform-specific things. To be honest I'd say that you could use the DBAL connection directly because you don't even need the ORM stuff to build this query (your desired result set contains only one column and not the entity).

admin commented

2026-01-22 15:15:49 +01:00

@lcobucci commented on GitHub (Nov 26, 2017):

I'll close this as Won't fix due to given explanation, please re-open if you feel the need of discussing it further.

@lcobucci commented on GitHub (Nov 26, 2017): I'll close this as `Won't fix` due to given explanation, please re-open if you feel the need of discussing it further.

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#5727