When filling a collection with a subselect using Postgres (any version?) Postgres complains missing a compulsory alias of the subselect in the FROM. I know some DBMS don’t need the alias, but postgres expects it.
I need very much this fixed, does anyone has any pointer to search in the hibernate source?
select count(tipoRelacion) from ( < subselect > ) where recepcion_id =?
Needed:
select count(tipoRelacion) from ( < subselect > ) as t where recepcion_id =?
This is exclusive to Postgres. Hibernate only has to add an as alias
The Hibernate error:
Hibernate: select count(tipoRelacion) from ( select
r1.recepcion_id,r1.tfd_uuid, rel.tipoRelacion, r2.tfd_uuid as rel_uuid,
rel.rel_uuid as uuid, r2.recepcion_id as r2
from recepcion_cfdi as r1
join recepcion_cfdi_rel as rel on (r1.tfd_uuid=rel.rel_uuid)
join recepcion_cfdi as r2 on (rel.recepcion_id = r2.recepcion_id)
) where recepcion_id =?
2018-05-16 12:22:45.001 [ajp-nio-8009-exec-1] WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 0, SQLState: 42601
2018-05-16 12:22:45.002 [ajp-nio-8009-exec-1] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - ERROR: subquery in FROM must have an alias
Hint: For example, FROM (SELECT ...) [AS] foo.
Position: 33
The mapping follows:
<hibernate-mapping package="com.fcm.cfdi.recepcion" schema="public">
<class name="Recepcion" entity-name="Recepcion" table="recepcion_cfdi" >
<id name="id" column="recepcion_ID">
<generator class="native"/>
</id>
...
<set name="relacionadosIn" lazy="extra" cascade="none" >
<subselect>select r1.recepcion_id,r1.tfd_uuid,
rel.tipoRelacion, r2.tfd_uuid as rel_uuid, rel.rel_uuid as uuid,
r2.recepcion_id as r2
from recepcion_cfdi as r1
join recepcion_cfdi_rel as rel on (r1.tfd_uuid=rel.rel_uuid)
join recepcion_cfdi as r2 on (rel.recepcion_id = r2.recepcion_id)
</subselect>
<synchronize table="recepcion_cfdi_rel"/>
<key column="recepcion_id" not-null="true" update="false"/>
<composite-element class="RecepcionRelacion">
<property name="tipoRelacion" not-null="true" length="2"/>
</composite-element>
</set>
</class>
</hibernate-mapping>
I haven’t used the old mappings for more than 10 years, so I wonder what would the subselect do inside a set since its annotation equivalent applies to entities only.
Also, you didn’t write the actual Hibernate Query you executed which resulted in that SQL you added.
On the long run, you are better off switching to annotations since the old hbm mappings are deprecated and will be replaced by a new JPA XML extension.
The system is rather large and was started a decade ago using the hbm mappings; we are already migrated to JPA EntityManager and using JPA annotations in some of the newer parts. The downside is that JPA annotations are rather constraining (poor) and some things that hibernate does cannot be expressed with them. Even the @SubSelect is a Hibernate propietary, not JPA.
Regarding your question, I didn’t write any query, I’m only loading the object from DB, then the < subselect > in the hbm mappings loads the set, using the subselect as a table.
The code that loads the entity is:
Session session = em.unwrap(Session.class);
Recepcion r = (Recepcion)session.get(entityName, id);
then we load the collection size: r.getRelacionadosIn().size();
So the subselect kicks in, as a table to load the values.
Which outputs the query: select count(tipoRelacion) from ( < subselect > ) where recepcion_id =?
lacking the alias
While PostgreSQL is expecting this: select count(tipoRelacion) from ( < subselect > ) as t where recepcion_id =?
This is the relevant code of the entity:
public class Recepcion implements Serializable, Cloneable {
private static final long serialVersionUID = 3114747307063177058L;
private long id;
private int version=0;
...
protected Set<RecepcionRelacion> relacionadosOut;
protected Set<RecepcionRelacion> relacionadosIn;
}
public class RecepcionRelacion implements Serializable {
private static final long serialVersionUID = -5731048065395330779L;
protected String tipoRelacion;
protected UUID uuid;
...
}
I have already downloaded version 5.2.17 source and found this class:
/**
* Implements subselect fetching for a collection
* @author Gavin King
*/
public class SubselectCollectionLoader extends BasicCollectionLoader {...
So I’m trying to debug it and find out if I’m in the right place. Any pointers will be appreciated.
The system is rather large and was started a decade ago using the hbm mappings; we are already migrated to JPA EntityManager and using JPA annotations in some of the newer parts.
That’s a good idea for the long-run.
The downside is that JPA annotations are rather constraining (poor) and some things that hibernate does cannot be expressed with them. Even the @SubSelect is a Hibernate propietary, not JPA.
Nope. That’s not true. In fact, there are many features that are now only available in annotations, not in HBM. Not to mention that there are more issues in the HBM-related code than in annotation handling.
The @Subselect for collections sounds like you are better of using an JPQL query and just remove the collection.
I think the SubselectCollectionLoader is for the FetchMode.SUBSELECT, not for your mapping.
Just debug the code prior to throwing that exception and see where the query is generated.
In fact that was Plan B; but I do prefer a richer data model.
I have found the problem. It only happens when you configure lazy=“extra”. Extra means you can call size(), isEmpty() and contains() without loading the full collection (a big plus on reduced memory usage). I’m curious to see how to do this with annotations, I did not find a way, only EAGER or LAZY, but no extra.
The culprit is: org.hibernate.persister.collection.AbstractCollectionPersister . With a very simple change on method determineTableName() , the alias is added only when subqueries are used.
I would like to contribute back the change. How can I make a push to GitHub so a pull request can be filed over the 5.2 branch?
That’s another design code smell. If you have to use extra lazy, it means that your collections are not suitable for being mapped since they are too large and can cause performance issues. As explained in this article, a @ManyToOne plus queries tailored for every use case is a much better alternative.
Now, back to your problem. Since we released 5.3.0, all changes will go to 5.3. And, only if @gbadner decides that the change should be backported to older branches, this change will be available in 5.2.
So, just fork Hibernate, apply the change and send a Pull Request. I’ll review it once you send it.