Postgres subselect: ERROR: subquery in FROM must have an alias

Description

When filling a collection with a subselect using Postgres (any version?) Postgres complains missing a compulsory alias of the subselect in the FROM.

NOW:
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'm trying to make a test version, so you can reproduce the issue.

Environment

Hibernate
Postgres 9 (any version)

Activity

Show:
Guillaume Smet
May 17, 2018, 9:48 AM

Hi Miguel,

Could you try to build a self contained test case using https://github.com/hibernate/hibernate-test-case-templates/tree/master/orm/hibernate-orm-5 ?

That would help.

Thanks!

Miguel Ruiz Velasco
August 7, 2018, 3:06 PM

The error only happens with lazy=extra (extraLazy) activated and when making count() or isEmpty() in collections.
I fixed the bug and can provide the patch. Only one file changed.

Guillaume Smet
August 7, 2018, 3:15 PM

interested in the patch and the test case reproducing it. You can either try to put together a pull request or attach the test case and the fix to this issue.

Thanks!

Assignee

Unassigned

Reporter

Miguel Ruiz Velasco

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Trivial
Configure