UnknownTableReferenceException: Unable to determine TableReference

Hibernate ORM
1

I’ve seen similar posts related to this when using Criteria,but this occurs with TypedQuery.
I’m using Hibernate 6.1.6, and have one class, Thing, with a unidirectional association to another, ThingStats. (Getters and setters omitted):

@Entity
public class Thing {    
    @Id @Column(name="THING_PK")
    Long pk;

    @OneToOne(targetEntity = ThingStats.class, fetch = FetchType.LAZY, optional=true)
    @JoinColumn(name = "THING_PK")
    private ThingStats thingStats;
}

@Entity
public class ThingStats {
    @Id @Column(name = "THING_FK", nullable = false)
    private Long thingPk; // PK is also fk to Thing

    @Column(name = "COUNT", nullable = false)
    private long count;

    @Column(name = "REJECTED_COUNT", nullable = false)
    private long rejectedCount;
}

The following fails with: org.hibernate.sql.ast.tree.from.UnknownTableReferenceException: Unable to determine TableReference (Thing) for org.hibernate.bugs.entities.Thing(thing).thingStats(thingStats)`:

	@Test
	public void testFindSeriesSopCount() {
		em = entityManagerFactory.createEntityManager();

		// This is all good:
		Thing s = em.find(Thing.class, THING1_ID);
		assertNotNull(s);  // << this works
		assertNotNull(s.getThingStats()); // <<-- This works
		assertEquals((Long) THING1_ID, s.getThingStats().getThingPk());

		// But this fails:
		String ql = "select thing from Thing thing"
				+  " left join thing.thingStats thingStats "
				+ " where thingStats is null or thingStats.rejectedCount = 0";

		TypedQuery<Thing> q = em.createQuery(ql, Thing.class);
		List<Thing> results = q.getResultList();  // <<---  Throws PersistenceException
	}

Is this a bug, and if so, is there some workaround?

2

Hi @mconner ,

It looks like a bug, can you please open a Jira ticket with a reprodurer?

Thanks a lot

3

Ticket is: HHH-16080

4

Just a side note, it might be better to formulate the query like this instead:

select thing from Thing thing
left join thing.thingStats thingStats on thingStats.rejectedCount = 0
5

@beikov:, I don’t think putting the criteria on the left join works in this case.

Fyi, my testcase morphed some from the original case, and I lost something distilling it down for the example. I’m actually looking for Things excluding those completely rejected (rejectedCount = count), but including those without an associated ThingStats (we don’t have stats yet).

That said, sticking with the example I presented, it is looking for Things that either don’t have ThingStats, or have them with rejectedCounts = 0. Adding that criteria to the join effectively results in a [Thing, null] tuple when rejectedCount <> 0 which would look the same as a Thing without a ThingStats. I tested this to confirm, and Thing with an associated ThingStats.rejectedCount = 10 was included in the results, which is not what we want.

However, I found a workaround: If I do the following:

select thing from Thing thing
   left join thing.thingStats thingStats
   where (thingStats.thingPk is null or thingStats.rejectedCount = 0)

That is, simply reference something on the joined entity that cannot be null – here, thingStats.thingPk (which is also an FK to Thing) – avoids the UnknownTableReferenceException, and gives the expected results. (Not sure how I missed that till now.)

6

You can easily try out the two resulting SQL statements which are more or less semantically equivalent:

select t.id
from (values (1), (2)) t(id)
left join (values (1, 0)) ts(fk,rejected_count)
  on t.id = ts.fk and ts.rejected_count = 0

select t.id
from (values (1), (2)) t(id)
left join (values (1, 0)) ts(fk,rejected_count)
  on t.id = ts.fk
where ts.fk is null or ts.rejected_count = 0

If a left join does not find matching rows on the joined table, it will fill the cells of the row for that table with nulls.

So the first statement will only select rows from ts that match the FK and have a rejected_count = 0. If there are no rows matching that, it will fill up the cells with null for a row of t.
The second statement will first join all rows from ts that match the FK. Next, it will filter out rows that have a null FK (this can only be the case if no rows exist in ts with a matching FK), or rows that have a rejected_count different from 0.

7

If a left join does not find matching rows on the joined table, it will fill the cells of the row for that table with nulls.

That’s the problem.

Again, we want Things with either:

  1. No stats, or
  2. stats with rejectedCount = 0.

We don’t want a Thing with thingStats.rejectedCount = 1 (or any non-zero value) included in the results.

Putting the criteria in the join, as you suggest, will not match rejectedCount = 1, and so, as you said, the stats table cells will be null. But we still get a row for that Thing, when we want it to be excluded.
(We can’t use a full join, since we do want to include the Things that don’t have stats.)

I’ve confirmed this in a test case. I can attach it to HHH-16080, if you like, but it isn’t relevant to that issue.

8

when we want it to be excluded

Alright, now I get what you are trying to achieve. Might be better for reader to formulate this with an exists predicate though.

9

If you mean with a subquery, I don’t know that that would be more readable.