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

**URL:** <https://discourse.hibernate.org/t/invalid-alias-in-a-where-clause-with-inheritance-joined/7882>\
**Category:** Hibernate ORM\
**Created:** [June 27, 2023, 1:53pm UTC](https://discourse.hibernate.org/t/invalid-alias-in-a-where-clause-with-inheritance-joined/7882 "2023-06-27T13:53:21Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![boutss](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/boutss/32/1543_2.png) [@boutss](https://discourse.hibernate.org/u/boutss)\
**Post date:** [June 27, 2023, 1:53pm UTC](https://discourse.hibernate.org/t/invalid-alias-in-a-where-clause-with-inheritance-joined/7882/1 "2023-06-27T13:53:21Z")

</div>

Hi,

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

My model :

```auto
@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?

---

<div class="post-metadata">

**Author:** ![boutss](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/boutss/32/1543_2.png) [@boutss](https://discourse.hibernate.org/u/boutss)\
**Post date:** [June 27, 2023, 2:05pm UTC](https://discourse.hibernate.org/t/invalid-alias-in-a-where-clause-with-inheritance-joined/7882/2 "2023-06-27T14:05:28Z")

</div>

![image](https://canada1.discourse-cdn.com/flex035/uploads/hibernate/original/2X/d/d29f99ca325478269c66c07661409793f014d0b8.png)

 ![image](https://canada1.discourse-cdn.com/flex035/uploads/hibernate/original/2X/a/a520a96324d2fa60621264c60af354bc503cbdc6.png)

 ![image](https://canada1.discourse-cdn.com/flex035/uploads/hibernate/original/2X/d/d00ed49bbc872d2569f898b02f3cae4ed778da39.png)

 ![image](https://canada1.discourse-cdn.com/flex035/uploads/hibernate/original/2X/2/223baa4288ba6576b0a24470dd7ad338e900670a.png)

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [June 28, 2023, 9:01am UTC](https://discourse.hibernate.org/t/invalid-alias-in-a-where-clause-with-inheritance-joined/7882/3 "2023-06-28T09:01:44Z")

</div>

Looks like a bug, please create an issue in the issue tracker([https://hibernate.atlassian.net](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](https://github.com/hibernate/hibernate-test-case-templates/blob/master/orm/hibernate-orm-6/src/test/java/org/hibernate/bugs/JPAUnitTestCase.java)) that reproduces the issue.

---

<div class="post-metadata">

**Author:** ![boutss](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/boutss/32/1543_2.png) [@boutss](https://discourse.hibernate.org/u/boutss)\
**Post date:** [June 28, 2023, 9:06am UTC](https://discourse.hibernate.org/t/invalid-alias-in-a-where-clause-with-inheritance-joined/7882/4 "2023-06-28T09:06:02Z")

</div>

Thank you, I’ll do it quickly.

---

<div class="post-metadata">

**Author:** ![boutss](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/boutss/32/1543_2.png) [@boutss](https://discourse.hibernate.org/u/boutss)\
**Post date:** [July 3, 2023, 9:09am UTC](https://discourse.hibernate.org/t/invalid-alias-in-a-where-clause-with-inheritance-joined/7882/5 "2023-07-03T09:09:02Z")

</div>

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

Just tell me if it’s okay with you

---

<div class="post-metadata">

**Author:** ![mbladel](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/mbladel/32/2128_2.png) [@mbladel](https://discourse.hibernate.org/u/mbladel)\
**Post date:** [July 4, 2023, 3:36pm UTC](https://discourse.hibernate.org/t/invalid-alias-in-a-where-clause-with-inheritance-joined/7882/6 "2023-07-04T15:36:09Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![cigaly](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/cigaly/32/2390_2.png) [@cigaly](https://discourse.hibernate.org/u/cigaly)\
**Post date:** [July 4, 2023, 5:09pm UTC](https://discourse.hibernate.org/t/invalid-alias-in-a-where-clause-with-inheritance-joined/7882/7 "2023-07-04T17:09:25Z")

</div>

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

```auto
 		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

---

<div class="post-metadata">

**Author:** ![mbladel](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/mbladel/32/2128_2.png) [@mbladel](https://discourse.hibernate.org/u/mbladel)\
**Post date:** [July 5, 2023, 9:29am UTC](https://discourse.hibernate.org/t/invalid-alias-in-a-where-clause-with-inheritance-joined/7882/8 "2023-07-05T09:29:47Z")

</div>

@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.

---

<div class="post-metadata">

**Author:** ![cigaly](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/cigaly/32/2390_2.png) [@cigaly](https://discourse.hibernate.org/u/cigaly)\
**Post date:** [July 5, 2023, 11:18am UTC](https://discourse.hibernate.org/t/invalid-alias-in-a-where-clause-with-inheritance-joined/7882/9 "2023-07-05T11:18:24Z")

</div>

@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?

---

<div class="post-metadata">

**Author:** ![cigaly](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/cigaly/32/2390_2.png) [@cigaly](https://discourse.hibernate.org/u/cigaly)\
**Post date:** [July 5, 2023, 3:46pm UTC](https://discourse.hibernate.org/t/invalid-alias-in-a-where-clause-with-inheritance-joined/7882/10 "2023-07-05T15:46:53Z")

</div>

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

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

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

```

Then using filter in test case:

```auto
	@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:

```auto
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.

---

<div class="post-metadata">

**Author:** ![mbladel](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/mbladel/32/2128_2.png) [@mbladel](https://discourse.hibernate.org/u/mbladel)\
**Post date:** [July 6, 2023, 9:16am UTC](https://discourse.hibernate.org/t/invalid-alias-in-a-where-clause-with-inheritance-joined/7882/11 "2023-07-06T09:16:28Z")

</div>

`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 `SqlRestriction`s, 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. `@Filter`s 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.