Invalid alias in a @Where clause with @Inheritance(JOINED)

Hi,

I have a case where assigning an alias to a column defined in a @Where clause is incorrect.

My model :

@Inheritance(JOINED)
@Entity
class A {
 private int etatObjet = 0;
}

@Entity
@Where(clause="etatobjet=0")
class B extends A {
}

@Entity
class C {
Set<B> setOf;
}

In this case, it will create a query with the alias “B.etatobjet”
while it should be necessary to “A.etatobjet”

See : AbstractCollectionPersister#applyWhereFragments

Did I miss something?

Looks like a bug, please create an issue in the issue tracker(https://hibernate.atlassian.net) with a test case(hibernate-test-case-templates/orm/hibernate-orm-6/src/test/java/org/hibernate/bugs/JPAUnitTestCase.java at main · hibernate/hibernate-test-case-templates · GitHub) that reproduces the issue.

Thank you, I’ll do it quickly.

[HHH-16882] Invalid alias in a @Where clause with @Inheritance(JOINED) - Hibernate JIRA (atlassian.net)

Just tell me if it’s okay with you

Thanks @boutssm, the issue and the reproducer look fine. We will look into it as soon as we get the chance to.

1 Like

Problem is in org.hibernate.persister.collection.AbstractCollectionPersister#applyBaseManyToManyRestrictions

 		if ( manyToManyWhereString != null ) {
			final TableReference tableReference = tableGroup.resolveTableReference( ( (Joinable) elementPersister ).getTableName() );

(Joinable) elementPersister ).getTableName() will return dog as table name instead of animal. To solve it, manyToManyWhereString should be parsed to find which property is used, then to locate table

1 Like

@cigaly thanks for the insight on the problem. I believe, rather than parsing a string and determine the property name which might be tricky, we should be able to retrieve the correct persister, i.e. the one on which @Where annotation was applied to, and use that to get the table name.

@mbladel I am afraid that this how it is working at this moment. @Where annotation is applied on class Dog, but actif property (and/or column) is in class Animal. How to determine that Animal persister should be used without knowing name of property? And how to know name of property without some kind of clause parsing?

And to make thing more interesting, I’ve created similar test with slight changes.

First used @Filter and @FilterDef annotations on dog entity:

	@Entity(name = "dog")
	@Filter(name = "dog", condition = "actif = 0")
	@FilterDef(name = "dog")
	public static class Dog extends Animal {
	}

Then using filter in test case:

	@Test
	public void testFilter(EntityManagerFactoryScope scope) {
		scope.inTransaction(
				entityManager -> {
					entityManager.persist( new Dog() );
					entityManager.persist( new Animal() );
				}
		);

		scope.inTransaction(
				entityManager -> {
					entityManager.unwrap( Session.class ).enableFilter( "dog" );
					entityManager.createQuery( "from dog", Dog.class ).getResultList();
				}
		);
	}

This is causing error very similar to one by original test case:

org.hibernate.exception.SQLGrammarException: could not prepare statement [Column "D1_0.ACTIF" not found; SQL statement:
select d1_0.id,d1_1.actif from dog d1_0 join animal d1_1 on d1_0.id=d1_1.id where d1_0.actif = 0 [42122-214]] [select d1_0.id,d1_1.actif from dog d1_0 join animal d1_1 on d1_0.id=d1_1.id where d1_0.actif = 0]

Since @SqlRestriction is handled (almost?) identically as @Where , it will cause identical error.

1 Like

SqlRestriction is what took the place of the deprecated @Where annotation. I think both that and @Filter have as a requirement that the expression uses columns of the entity/table where the annotation is placed.

Using the example of SqlRestrictions, we already parse the SQL fragment in org.hibernate.sql.Template#renderWhereStringTemplate, and then we insert the table alias in org.hibernate.persister.entity.AbstractEntityPersister#applyWhereRestrictions. This methods might be modified to handle multi-table restrictions. @Filters are handled a bit differently, but the same might be applied there.

@cigaly feel free to give it a try if you want, let me know if I can give you any more info.