MapBinder.createFormulatedValue() does not honor DB schema name when creating query

Description

I detected a problem with a ManyToMany mapping using a Map in the following setup:

@Entity
@Table(name = "entitya", schema = "myschema")
class EntityA {

...

@ManyToMany(cascade = { CascadeType.PERSIST, CascadeType.MERGE })
@MapKey(name = "identifier")
@JoinTable(name = "entitya_entityb", schema="myschema", joinColumns = @JoinColumn(name = "entitya_pk"), inverseJoinColumns = @JoinColumn(name = "entityb_pk"))
@Cascade( { org.hibernate.annotations.CascadeType.SAVE_UPDATE, org.hibernate.annotations.CascadeType.DELETE_ORPHAN })
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
private Map<String, EntityB> entityBMap = new HashMap<String, EntityB>();
}

@Entity
@Table(name = "entityb", schema = "myschema")
class EntityB {
...

}

The SQL generated to load the entityBMap collection contains a subquery in the select clause, which looks like this:
select a8.identifier from entityb a8 where a8.pk=entityb0_.entityb_pk (entityb0_.entityb_pk comes from the outer select clause)

The problem here is, that entityb in the from clause is not prepended by the schema name "myschema" (the outer query is correctly doing this in the join clause, however). I tracked this down to the createFormulatedValue() method in the MapBinder class; the responsible code snippet is contained in lines 292 - 296:

StringBuilder fromAndWhereSb = new StringBuilder( " from " )
.append( associatedClass.getTable().getName() )
//.append(" as ") //Oracle doesn't support it in subqueries
.append( " " )
.append( alias ).append( " where " );

associatedClass.getTable().getName() does not return the fully qualified table name, so I think this should look something like this:

StringBuilder fromAndWhereSb = new StringBuilder( " from " );
if (associatedClass.getTable().getSchema() != null) {
fromAndWhereSb.append(associatedClass.getTable().getSchema()).append('.');
}
fromAndWhereSb.append( associatedClass.getTable().getName() )
//.append(" as ") //Oracle doesn't support it in subqueries
.append( " " )
.append( alias ).append( " where " );

Environment

Hibernate 3.3.0.SP1, PostgresQL 8.3

Activity

Show:
Julien RENAUT
November 12, 2010, 2:59 PM

I just ran into this bug at work.

A poor solution would be to change the entity to use a Set instead of a Map (since the Key comes from the Value anyways) and build a transient Map when needed.

But i'd like to see this solved as it doesn't seem complicated.

One last comment: the snippet provided takes into account the possibility of a schema being provided in the @Table annotation but a schema can also be provided through the use of the "default_schema" configuration property. This must be checked too.

JoãoE
November 19, 2010, 4:09 PM

Just found this bug as well here at work and would like to see it solved. Does anyone know when it'll be solved?

Jason Novotny
February 20, 2013, 8:50 PM

I confirmed this major bug is still an issue in Hibernate 4.2.0 final... 4 years later, any idea when it might be fixed? I agree with Julien, doesn't seem too hard.

Francisco Alejandro Lozano López
October 14, 2019, 8:26 AM
Edited

I confirmed this still happens in 5.4.6. I proposed a quick PR to fix it:

It seems pretty serious …

 

Assignee

Unassigned

Reporter

Sven Panko

Fix versions

Labels

None

backPortable

Backport?

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Priority

Major
Configure