The CreateQuery function does not establish a join with the table of the column present in @SQLRestriction if the column is not included in the select, and the InheritanceType strategy is JOINED

Hibernate ORM
1

Hello,

With an HQL query on an object that has a JOINED inheritance strategy and includes an @SQLRestriction annotation on an attribute of the parent class, the constructed query does not create the join on the table of the parent class.

The join is present when the column is added to the SELECT clause of the HQL query.

@Entity
@Inheritance(strategy = JOINED)
@SQLRestriction("etatobjet=0")
public class ObjetHibernateBaseJoined {
  @Id
  int id;
  int etatObjet;
}

@Entity
public class ObjetHibernateFilsJoined extends ObjetHibernateBaseJoined {
  String filsJoined;
}

OK: session.createQuery(“SELECT id, etatObjet FROM ObjetHibernateFilsJoined WHERE id= :id”)
KO: session.createQuery(“SELECT id FROM ObjetHibernateFilsJoined WHERE id= :id”)

org.hibernate.exception.SQLGrammarException:
JDBC exception executing SQL [select spe1_0.ID from TObjetHibernateFilsJoined spe1_0 where (mod(spe1_1.etatobjet,2)=0) and spe1_0.ID=?] [ERROR: missing FROM-clause entry for table “spe1_1”

I think it’s due to this change, without pointing fingers :smiley:
image

Thank you

2

I think you reported this a while ago: [HHH-16882] - Hibernate JIRA. The Jira was marked as an improvement proposal since custom SQL restrictions on columns of a joined-inheritance structure were never supported. For the moment there are no news on that front but any contribution is more than welcome.

3

It’s not the same case; I thought so initially, but the issue arose with version 6.4.0.Final.

The problem with the other JIRA is an incorrect alias if the @SQLRestriction annotation uses an attribute from the parent class, as shown below:

@Entity
@Inheritance(strategy = JOINED)
public class ObjetHibernateBaseJoined {
  @Id
  int id;
  int etatObjet;
}

@Entity
@SQLRestriction("etatobjet=0")
public class ObjetHibernateFilsJoined extends ObjetHibernateBaseJoined {
  String filsJoined;
}

The case I’m presenting requires having a column in the select statement of the parent table to perform the join with the table of the parent class.
I imagine that this is a result of the evolution of the change I presented earlier.
And I believe what was overlooked is the fact that the @where or SQLRestriction annotation could have a column pointing to this table.

4

The nature of the issue is the same: you’re requesting a @SQLRestriction be applied to a table reference which is not resolved in the query by default because it’s not needed, thus the missing alias in the FROM clause.

Please add this case with a simple reproducer to the improvement Jira too, or create a new one and link it so we can make sure this behavior will be correctly handled too.

5

Very well, I’ll create a pull request with the test to reproduce. Thank you.

6

It’s strange, I can’t seem to reproduce it when running the test. Right now, it creates the join correctly…
Has it already been fixed? Or could it be a problem on my end that I might have overlooked?

image
image705Ă—986 66.2 KB

7

It is well fixed by this ticket :

[HHH-16967] Missing join on inheritance parent table to validate @Where condition - Hibernate JIRA (atlassian.net)

8

Can I abandon mine?

[HHH-17554] No join on the table of the parent class with JOINED inheritance strategy when the select not contains a column used in @Where or @SQLRestriction - Hibernate JIRA (atlassian.net)

9

Closed the issue as already fixed, thank you for checking.

1 Like