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?