In the case of a relation "OneToMany" between two JPA entities, when we make a search on the parent entity, the generated SQL presents duplicated fields. Here is an example:
In more complicated cases, there can be more than two duplicated fields. Although the duplication of fields doesn't distort performance at a DB level.
This issue does not duplicate HHH-3045. is about aliases that are literally the same. This issue involves the same column value being returned with 2 different aliases.
I have attached simple test case.
The following reproduces this issue the same way as the attached test case:
org.hibernate.test.annotations.onetomany.OneToManyTest#testSimpleOneToManyCollection using a bidirectional many-to-one/one-to-many association with the resulting SQL:
discountti0_.CUSTOMER_ID as CUSTOMER3_3_0_,
discountti0_.id as id1_3_0_,
discountti0_.id as id1_3_1_,
discountti0_.discount as discount2_3_1_,
discountti0_.CUSTOMER_ID as CUSTOMER3_3_1_
Aliases ending with "_1" are used when reading entity data (e..g, Discount).
Aliases ending with "_0" are used when reading the collection "key" and "element". The collection "key" is the JoinColumn, which is used to resolve the collection owner (e.g, the particular Customer that owns the collection being read). For OneToMany, the "element" is the entity ID (e.g., Discount ID), which is used to add the entity element to the collection being read.
If a particular entity is included as an element in a collection being read, then that entity's ID will be read twice (once with an alias ending with "_0", and once with alias ending with "_1").
If the association is bidirectional ManyToOne/OneToMany, then the JoinColumn will also be read twice as well.
It may be possible to remove the duplication, but I'm not sure how complicated it would be to do this in the current code base. I am also not sure how much of a savings it would be unless the entity ID or join column is particularly expensive to query. In that case, it would be desirable to choose a more suitable ID/join column.
, are there any plans to remove this duplication in SQM?
This is in fact already in place in SQM. From the proof-of-concept's design doc:
The background, as you allude to , is really the name-based reads from the ResultSet which expects certain patterns to the alias names it should read. As 6.0 (with SQM) moves to position-based reads this is not necessary anymore.
, should this be resolved as out-of-date?