Another subselect instead of join issue

Hibernate ORM
1

I’ve found serveral topics about issues where Hibernate 6 does a subselect instead of a join. Those issues were bugs and have been fixed meanwhile.
However, I’ve found another one which I believe is a bug. It’s quite easy to reproduce. A reproducer is attached.

This one seems to happen when dealing with inheritance.

Setup:

Base classes: A and B
Sub classes of A: AA, AB
Sub classes of B: BA, BB

AA is owner of a one-to-one relation to BA

SELECT AA FROM AA JOIN FETCH BA ba ON ba.baProp = 'test'

This results in following SQL:

    select
        'AA' 
    from
        A a1_0 
    join
        (select
            * 
        from
            B t 
        where
            t.DTYPE='BA') b1_0 
            on b1_0.baProp='test' 
    where
        a1_0.DTYPE='AA'

This doesn’t happen with Hibernate 5 or Hibernate 6.2.6. It’s a join as expected in these versions.

I hope a fix can be released for Hibernate 6.6 as we have a lot of queries like this.

Reproducer: File-Upload.net - bug.zip

2

This is intended behavior: the subselect is needed to ensure correct join semantics when using discriminator-based inheritance, which wouldn’t be preserved with a simple join + where clause restrictions.

Why is using a subselect a problem in your case?

3

I believe the performance is worse but I have no hard evidence on that. The performance is the same with this simplified query but we have bigger queries with multiple joins that are now subselects.

This is how the query looked in previous versions:

select
        'AA' 
    from
        A a1_0 
    join
        B b1_0 
            on b1_0.DTYPE='BA' 
            and b1_0.baProp='test' 
    where
        a1_0.DTYPE='AA'
4

We can certainly change the rendering of the SQL, but since this requires a lot of effort, we need actual evidence that this makes a difference. From a pure relational perspective it does not make any sense that the performance is different, because filters can be pushed down into table scans across inner joins. Every database leverages this optimization, otherwise the performance of many queries, including ones that rely on SQL views would suffer dramatically.

5

Alright, if this is intended behavior I’ll accept it as a solution. Maybe other people can contribute to this topic in the future and provide real evidence.