[hibernate-issues] [JIRA] (HHH-15470) Prevent N+1 problem on successful join results

Jin Kwon ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) *created* an issue Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiZjYzNjI0YzM1... ) / Improvement ( https://hibernate.atlassian.net/browse/HHH-15470?atlOrigin=eyJpIjoiZjYzNj... ) HHH-15470 ( https://hibernate.atlassian.net/browse/HHH-15470?atlOrigin=eyJpIjoiZjYzNj... ) Prevent N+1 problem on successful join results ( https://hibernate.atlassian.net/browse/HHH-15470?atlOrigin=eyJpIjoiZjYzNj... ) Issue Type: Improvement Affects Versions: 5.6.10 Assignee: Unassigned Components: hibernate-entitymanager Created: 30/Aug/2022 00:14 AM Environment: Apache Maven 3.8.6 (84538c9988a25aec085021c365c560670ad80f63) Maven home: /opt/homebrew/Cellar/maven/3.8.6/libexec Java version: 18.0.2.1, vendor: Eclipse Adoptium, runtime: /Library/Java/JavaVirtualMachines/temurin-18.jdk/Contents/Home Default locale: en_KR, platform encoding: UTF-8 OS name: "mac os x", version: "12.5", arch: "aarch64", family: "mac" Labels: n+1 Priority: Minor Reporter: Jin Kwon ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) When I tried to join two tables with a VARCHAR column with different case(upper/lower), Hibernate still does N+1 select even joined entries found. I have two tables with no FK. Table A’s column contains lowercased values while table B’s column contains uppercased values. Note that there is no FK, and the join process succeeds. Hibernate does this, first. SELECT b. *, a.* FROM b outer join a ON b.col = a.col; which selects all tuples like charm. b.col a.col ... {{ AAA aaa ...}} {{ BBB bbb. ...}} And it also executes queries for all (already) joined tuples. (N+1) SELECT * FROM a WHERE a.col = 'AAA'; SELECT * FROM a WHERE a.col = 'BBB'; SELECT * FROM a WHERE a.col = 'CCC'; I have a workaround. @Valid @NotFound(action = NotFoundAction.IGNORE) // -> EAGER! // partner_id 컬럼의 값들이 uppercase 다. // join 은 되는데... hibernate 가 (잘 찾고도) N+1 문제를 일으킨다. @JoinFormula( "LOWER(" + COLUMN_NAME_PARTNER_ID + ")" ) // @@? @MapsId(MappedPartnerSearchRestriction_.PARTNER_ID) @ManyToOne(optional = /* false */ true , fetch = FetchType.LAZY) //@JoinColumn(name = COLUMN_NAME_PARTNER_ID, nullable = false , insertable = false , updatable = false , // foreignKey = @ForeignKey(ConstraintMode.NO_CONSTRAINT)) @EqualsAndHashCode.Exclude @ToString.Exclude private Some some; But the solution relies on speculations (b.col is always uppercased and a.col is always lowercased) Do we have any chance for enhancement? ( https://hibernate.atlassian.net/browse/HHH-15470#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-15470#add-comment?atlOrigin=ey... ) Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=Em... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100206- sha1:1e36365 )