Same column returned with multiple aliases in scalar query

Description

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.

Environment

Postgres 9.x

Activity

Show:
Gail Badner
December 7, 2016, 6:45 PM

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.

Tom Ross
December 9, 2016, 8:30 AM

I have attached simple test case.

Gail Badner
December 14, 2016, 11:07 PM

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:

{{
select
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_
from
Discount discountti0_
where
discountti0_.CUSTOMER_ID=?
}}

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?

Steve Ebersole
December 17, 2016, 2:06 PM

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.

Gail Badner
April 5, 2019, 9:54 PM

, should this be resolved as out-of-date?

Assignee

Gail Badner

Reporter

Tom Ross

Fix versions

Labels

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Major
Configure