Illegal SQL generated for count distinct queries of embeddables
Description
the following query generates illegal SQL if the entity uses an embeddable as primary key:
This is just the simplest example for such a query. In reality the query is much more complicated and involves left joins (the reason for the distinct). It does not matter whether this is done in JPQL or as a criteria query. The generated SQL is not legal in Oracle:
Trying to reformulate as e.g. distinct concat(field1,field2) fails in HQL. For Oracle in particular the correct SQL should probably look more like:
Just a note so this can be found more easily: JPQL 'select count( distinct e ) from entity e' will lead to ORA-00909: invalid number of arguments if e carries an embedded ID (@EmbeddedId).
Concerning the fix: wouldn't it be appropriate to generally count table.rowid when counting rows on Oracle DBMS? That is, if the argument to jpql count() is an identification variable, distinct or not.
Christian BeikovSeptember 16, 2016 at 8:07 AM
I proposed a solution in which is a duplicate. You can use that as workaround until it is merge into core.
the following query generates illegal SQL if the entity uses an embeddable as primary key:
This is just the simplest example for such a query. In reality the query is much more complicated and involves left joins (the reason for the distinct). It does not matter whether this is done in JPQL or as a criteria query.
The generated SQL is not legal in Oracle:
Trying to reformulate as e.g.
distinct concat(field1,field2)fails in HQL. For Oracle in particular the correct SQL should probably look more like: