Why is Right Join not supported by Hibernate and JPA?

[Vamshs]

When I try to do a Right Join - I get exception java.lang.UnsupportedOperationException: RIGHT JOIN not supported. My Hibernate version is 5.2+ and I am using JPA Criteria API for the Join.
On searching online I found below -
This one is quite old but seems real !! - [JPA-2] - Hibernate JIRA

And this SOF post - hibernate - How do I perform an outer join with a JPA CriteriaBuilder query? - Stack Overflow also indicates that hibernate/jpa does not support Right Join and a work around using Left Join should be done.

But I see Right Join has its own significance and the other work arounds using Left Join does not have few equivalent use cases.

For example - I have below M2M relationship.
Package <-> JT <-> ProcItems <-> JT <-> SMItems

Now I have a case where I want to select all SMItems with their associated Package. If any SMItem does not have a corresponding package I still want to select it with package column as null. In SQL I can do this like below -

1. Packages - InnerJoin - JT - InnerJoin - ProcItems - InnerJoin - JT - RightJoin - SMItems
   OR in HQL/JPQL/Criteria as below
2. Packages - InnerJoin - ProcItems - RightJoin (might be not supported) - SMItems.

The alternative Left Join approach would be -

3. SMItems - LeftJoin - ProcItems - InnerJoin - Packages.
   or
4. SMItems - LeftJoin - ProcItems - LeftJoin - Packages.

While 1 & 2 will give same results if I got it right (given JT data is correct i.e no left table entry without right table mapping) . Plz correct if i am wrong with this statement → “1 & 2 will give same results”

But results of 1&2 != 3 != 4. (and 3 != 4 which is obvious).

The difference is → in 1&2 i get all SMItems whether they have package mapping or not. If they have package mappings i get that information or null for package column.
While in (4), I get unexpected and duplicate for SMItems and nulls for package column (Because of a mapping between SMItems and ProcItems, but no corresponding mapping between ProcItems and Packages). I can explain further if this is not clear. Please do let me know with out hesitation.

And in (3), the second Join which is inner join eliminates SMItems that does not have corresponding Packages.

How do I solve my problem.

[Vamshs]

thanks in advance - plz show some love and try to answer. My use case is in trouble.

[Vamshs]

apart from this Q. I want to know if Hibernate 6.x supports RIGHT Joins. Plz let me know. Tx in advance.

[beikov]

Right joins are only unsupported in the JPA Criteria implementation in 5.x. In 6.0+ this is supported, but also in HQL since 5.x.
Note though that I would suggest you to use only a single kind of join if possible (i.e. left or right), as it might be very confusing when mixing join types. I personally only use left joins as I generally try to join from a “tree root down to leaf nodes”.

[Vamshs]

firstly… thanks for confirming that 6.x supports RIGHT Join even in Criteria API. cool ++.
Next, please review my use case in the Question ( I can clarify it further by editing the Question, if its not clear). From my understanding this case can be only solved with Right Join. Doing a LEFT Join from ROOT to LEAF nodes does not solve my case, and I am forced to do LEFT Join anyway as currently RIGHT Join is not supported. And HQL is not an option for me, coz the predicates are dynamic and have permutations for predicates. Criteria is inevitable in my case from my understanding.

[beikov]

Please post the entity mappings, the (left join) Criteria query that you are using and the generated SQL. Also, please post the Hibernate version you are using. Ideally, update to the latest Hibernate 5 version, which is 5.6.10.Final, as I believe some of your issues might be fixed in newer versions already.

[Vamshs]

seems the Questions or Comments can not be edited after certain time. After how much time is this. ??

[Vamshs]

@beikov - The Entity Mapping for Packages and ProtectedItems are like as in the Question part (not yet as suggested by you in the comments) of this post. The mapping for ProtectedItems and StreamMapping is similar to above mapping.

The criteria query joins are like below and the Hibernate Version is 5.6.9

Root<StreamMappings> root = query.from(StreamMappings.class);
Join<StreamMappings, ProtectedItems> protectedItemsJoin = root.join("protectedItemsList", JoinType.LEFT);
Join<ProtectedItems, Packages> packagesJoin = protectedItemsJoin.join("packagesList", JoinType.LEFT);

Generated Query -

select
        distinct cast(streammapp0_.stream_id as char) as col_0_0_,
        streammapp0_.stream_name as col_1_0_,
        cast(packages4_.package_id as char) as col_2_0_,
        packages4_.package_name as col_3_0_,
        streammapp0_.created_at as col_4_0_,
        streammapp0_.last_modified_at as col_5_0_ 
    from
        stream_mappings streammapp0_ 
    left outer join
        jt_protected_items_stream_mappings protectedi1_ 
            on streammapp0_.account_id=protectedi1_.ci_account_id 
            and streammapp0_.content_id_extension=protectedi1_.content_id_extension 
    left outer join
        protected_items protectedi2_ 
            on protectedi1_.pi_account_id=protectedi2_.account_id 
            and protectedi1_.protected_item_id=protectedi2_.protected_item_id 
    left outer join
        jt_packages_protected_items packagesli3_ 
            on protectedi2_.account_id=packagesli3_.pi_account_id 
            and protectedi2_.protected_item_id=packagesli3_.protected_item_id 
    left outer join
        packages packages4_ 
            on packagesli3_.p_account_id=packages4_.account_id 
            and packagesli3_.package_id=packages4_.package_id 
    where
        streammapp0_.account_id=? 
    order by
        cast(streammapp0_.stream_id as char) asc,
        cast(packages4_.package_id as char) asc limit ?

The problem is in below data - line 1 should be elimated and using right join this is possible as the right join on streammapping happens at the last.

Xcain3x(streamId=4009, streamName=IPL-HD, packageId=null, packageName=null, createdAt=2022-06-19T00:00:00Z, lastModifiedAt=2022-06-19T00:00:00Z)
Xcain3x(streamId=4009, streamName=IPL-HD, packageId=2, packageName=Package-2, createdAt=2022-06-19T00:00:00Z, lastModifiedAt=2022-06-19T00:00:00Z)
Xcain3x(streamId=4009, streamName=IPL-HD, packageId=4, packageName=Package-4, createdAt=2022-06-19T00:00:00Z, lastModifiedAt=2022-06-19T00:00:00Z)

[beikov]

Not sure why you think this is wrong and why right join should fix this situation, but you can filter out rows by applying a predicate e.g. query.where(builder.isNotNull(packagesJoin)) which is roughly equivalent to using an inner join for the packages join.

The thing that you seem to seek is called table group joins, but Hibernate does not support this yet. With that, you could say something like this:

from SMItems si
left join (
  si.protectedItems pi
  join pi.packages p
)

i.e. apply the inner join semantics only to the join between protected items and packages, but left join against the result of that. The right join queries you explained are equivalent to the left join queries. If a database behaves differently, I’d say that might be a bug in the database. But maybe you made use of this SQL feature in your right join tests without knowing it is special?

There is no way you can emulate this table group join in general. The best you can do, is filter rows/objects in Java after loading from the database. In your particular case there is one possible workaround, but that is totally inefficient. You’d have to add a subquery predicate into the first join, which checks that following rows exist. Something like this:

Root<StreamMappings> root = query.from(StreamMappings.class);
Join<StreamMappings, ProtectedItems> protectedItemsJoin = root.join("protectedItemsList", JoinType.LEFT);
Join<ProtectedItems, Packages> packagesJoin = protectedItemsJoin.join("packagesList", JoinType.LEFT);

Subquery subquery = query.subquery(Integer.class);
subquery.correlate(protectedItemsJoin).join("packagesList", JoinType.INNER);
subquery.select(builder.literal(1));
protectedItemsJoin.on(builder.exists(subquery));

[Vamshs]

@beikov - thanks. that’s wonder full to see how you solved it through… correlated subquery and it works.
For any one interested in the generated sql query for the earlier case - here’s it.

select
        distinct cast(streammapp0_.stream_id as char) as col_0_0_,
        streammapp0_.stream_name as col_1_0_,
        cast(packages6_.package_id as char) as col_2_0_,
        packages6_.package_name as col_3_0_,
        streammapp0_.created_at as col_4_0_,
        streammapp0_.last_modified_at as col_5_0_ 
    from
        stream_mappings streammapp0_ 
    left outer join
        (
            jt_protected_items_stream_mappings protectedi1_ 
        left outer join
            protected_items protectedi2_ 
                on protectedi1_.pi_account_id=protectedi2_.account_id 
                and protectedi1_.protected_item_id=protectedi2_.protected_item_id
            ) 
                on streammapp0_.account_id=protectedi1_.ci_account_id 
                and streammapp0_.content_id_extension=protectedi1_.content_id_extension 
                and (
                    exists (
                        select
                            1 
                    from
                        jt_packages_protected_items packagesli3_,
                        packages packages4_ 
                    where
                        protectedi2_.account_id=packagesli3_.pi_account_id 
                        and protectedi2_.protected_item_id=packagesli3_.protected_item_id 
                        and packagesli3_.p_account_id=packages4_.account_id 
                        and packagesli3_.package_id=packages4_.package_id
                )
            ) 
        left outer join
            jt_packages_protected_items packagesli5_ 
                on protectedi2_.account_id=packagesli5_.pi_account_id 
                and protectedi2_.protected_item_id=packagesli5_.protected_item_id 
        left outer join
            packages packages6_ 
                on packagesli5_.p_account_id=packages6_.account_id 
                and packagesli5_.package_id=packages6_.package_id 
        where
            streammapp0_.account_id='some account id'
        order by
            cast(streammapp0_.stream_id as char) asc,
            cast(packages6_.package_id as char) asc limit 100

My next join involves four tables. I am not able to piece together how to write the correlated subquery in this case. Would you mind solving this one too.

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Xcain4> query = cb.createQuery(Xcain4.class);
        Root<Devices> root = query.from(Devices.class);
        Join<Devices, Packages> packagesJoin = root.join("packagesList", JoinType.LEFT);
        Join<Packages, ProtectedItems> protectedItemsJoin = packagesJoin.join("protectedItemsList", JoinType.LEFT);
        Join<ProtectedItems, StreamMappings> streamMappingsJoin = protectedItemsJoin.join("streamMappingsList", JoinType.LEFT);

And thank for bringing up the table group joins… that seems much simpler than the subquery approach and am hoping Hibernate will support that too one day… but as RIGHT Join is available in 6.x all this will be quite simple. I am working with Spring Boot and seems only from spring boot 3.x onwards Hibernate 6.x will be supported. Till then I have to look at work arounds.
If you don’t mind could you plz give the subquery for the above case…
–PS - kudos to the H team, this discourse is unbelievably active and helpful.

[beikov]

Should be easy, but depends what kind of behavior you’d like. Maybe this works:

Subquery subquery = query.subquery(Integer.class);
subquery.correlate(packagesJoin).join("protectedItemsList", JoinType.INNER)
  .join("streamMappingsList", JoinType.INNER);
subquery.select(builder.literal(1));
packagesJoin.on(builder.exists(subquery));

[Vamshs]

@beikov - i see row with null even after applying above correlate subquery -
The output is same in both cases(with and without subquery) and it’s like below -

Report4(dev0800000190, streamId=null, streamName=null)
Report4(dev0800000190, streamId=4000, streamName=HD-audio)
Report4(dev0800000190, streamId=4001, streamName=SD-audio)
...
Report4(dev0800000192, streamId=null, streamName=null)
Report4(dev0800000192, streamId=4000, streamName=HD-audio)
Report4(dev0800000192, streamId=4001, streamName=SD-audio)
...

And the query generated is as below -

select
        distinct devices0_.device_id as col_0_0_,
        cast(streammapp10_.stream_id as char) as col_1_0_,
        streammapp10_.stream_name as col_2_0_ 
    from
        devices devices0_ 
    left outer join
        (
            jt_devices_packages packagesli1_ 
        left outer join
            packages packages2_ 
                on packagesli1_.p_account_id=packages2_.account_id 
                and packagesli1_.package_id=packages2_.package_id
            ) 
                on devices0_.account_id=packagesli1_.d_account_id 
                and devices0_.device_id=packagesli1_.device_id 
                and (
                    exists (
                        select
                            1 
                    from
                        jt_packages_protected_items protectedi3_,
                        protected_items protectedi4_ 
                    inner join
                        jt_protected_items_stream_mappings streammapp5_ 
                            on protectedi4_.account_id=streammapp5_.pi_account_id 
                            and protectedi4_.protected_item_id=streammapp5_.protected_item_id 
                    inner join
                        stream_mappings streammapp6_ 
                            on streammapp5_.ci_account_id=streammapp6_.account_id 
                            and streammapp5_.content_id_extension=streammapp6_.content_id_extension 
                    where
                        packages2_.account_id=protectedi3_.p_account_id 
                        and packages2_.package_id=protectedi3_.package_id 
                        and protectedi3_.pi_account_id=protectedi4_.account_id 
                        and protectedi3_.protected_item_id=protectedi4_.protected_item_id
                )
            ) 
        left outer join
            jt_packages_protected_items protectedi7_ 
                on packages2_.account_id=protectedi7_.p_account_id 
                and packages2_.package_id=protectedi7_.package_id 
        left outer join
            protected_items protectedi8_ 
                on protectedi7_.pi_account_id=protectedi8_.account_id 
                and protectedi7_.protected_item_id=protectedi8_.protected_item_id 
        left outer join
            jt_protected_items_stream_mappings streammapp9_ 
                on protectedi8_.account_id=streammapp9_.pi_account_id 
                and protectedi8_.protected_item_id=streammapp9_.protected_item_id 
        left outer join
            stream_mappings streammapp10_ 
                on streammapp9_.ci_account_id=streammapp10_.account_id 
                and streammapp9_.content_id_extension=streammapp10_.content_id_extension 
        where
            devices0_.account_id=?

May be another second subquery is required… not sure where and how… lol…
and thanks for your time and suggestions.

[beikov]

You’ll have to tell me more details then about what you want to achieve.

[Vamshs]

@beikov - sure… let me try to keep it simple.

Given I have below tables with many-to-many relationship between them mapped by join table.

Devices <–> Packages <–> ProtectedItems <–> StreamsMapping.

I want the below -

Query 1 -

StreamMapping 
    Inner Join
ProtectedItems
    Inner Join
Packages
    Right Join
Devices.

Since Right Join is not supported by Hibernate 5.6.x with Criteria API, i want a query that gives same result as above using Left Join.

The below query is not equivalent - Because I am loosing Device Table rows that does not a corresponding mapping with ProtectedItems.

Query 2 -

Devices
    Left Join
Packages
    Inner Join
ProtectedItems
    Inner Join
StreamMappings

Effectively the join order matters - Query 2 is equivalent to
Query 3 -

(((Devices
    Left Join
Packages)
    Inner Join
ProtectedItems)
    Inner Join
StreamMappings)

Instead what I want is
Query 4 -

(Devices
    Left Join
(Packages
    Inner Join
(ProtectedItems
    Inner Join
StreamMappings)))

Effectively -
→ Query 2 = Query 3 - this will not serve my purpose.
→ Query 1 = Query 4 - this will serve my purpose.
So I want equivallent of Q1 or Q4 using LEFT Join or any other equivalent approach that works with Criteria API Hibernate 5.6.x

[beikov]

As far as I can tell, the query I provided you should produce exactly the result that you are looking for. If you think it doesn’t, please provide me table DDL and insert SQL, as well as the expected outcome so I can try out the SQL and see what needs to be done for this to work.

[Vamshs]

@beikov - can i email you the mapping, table schema and generated sql, the current outcome and the expected outcome. I don’t think these details will help the community any way.

[beikov]

You can send me a PM here on discourse.

[Vamshs]

cool and thanks -

i found below info on Messages Sent page

Need to have a direct personal conversation with someone, outside the normal conversational flow? Message them by selecting their avatar and using the message button.

Not sure where i can select the avatar - but I tried this profile page - Profile - beikov - Hibernate. I don’t see any Message Icon here.

@beikov - how can i find/select any ones Avatar2: The Hibernate Way.

[beikov]

On my comment, click on my profile picture and the popup will show you a message button.