Hibernate MySQL Query Error Unknown Column in Join On

Question

I am getting a strange error with trying to use Hibernate with MySQL. I have the following named query:

SELECT SUM(s.plotline.value), s.character FROM Story as s WHERE s.chapter.chapterID = ?1 GROUP BY s.character ORDER BY SUM(s.plotline.value)

This gets translated into the following SQL statement:

    select sum(plotline1_.Value) as col_0_0_, story0_.CharacterID as col_1_0_, character2_.CharacterID as Characte1_5_,
 character2_.AuthorID as AuthorID5_, character2_.BookID as BookID5_, character2_.CharacterName as Characte2_5_,
 character2_.LastModified as LastModi3_5_
 from fantasy.story story0_, fantasy.plotline plotline1_ 
inner join fantasy.character character2_ on story0_.CharacterID=character2_.CharacterID 
where story0_.PlotlineID=plotline1_.PlotlineID and story0_.ChapterID= 4
group by story0_.CharacterID 
order by count(plotline1_.Value)

When I execute this I get:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'story0_.CharacterID' in 'on clause'
    sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    java.lang.reflect.Constructor.newInstance(Unknown Source)
    com.mysql.jdbc.Util.handleNewInstance(Util.java:407)
    com.mysql.jdbc.Util.getInstance(Util.java:382)

If I change the query to this and run it directly in SQL it runs properly:

select sum(plotline1_.Value) as col_0_0_, story0.CharacterID as col_1_0_, character2_.CharacterID as Characte1_5_,
 character2_.AuthorID as AuthorID5_, character2_.BookID as BookID5_, character2_.CharacterName as Characte2_5_,
 character2_.LastModified as LastModi3_5_
 from fantasy.story story0, fantasy.plotline plotline1_ 
inner join fantasy.character character2_ on CharacterID = character2_.CharacterID 
where story0.PlotlineID=plotline1_.PlotlineID and story0.ChapterID= 4
group by story0.CharacterID 
order by count(plotline1_.Value)

There are two parts to this question

Why does removing the explicit qualification to CharacterID in the join make this work? My experience has mainly been with using SQL Server and it would actually fail if you tried to write the query in this manner.
Is there a way to make Hibernate give me the query in the proper form or adjust my MySQL configuration so that it accepts the query the way Hibernate is producing it?

I may have found an explanation to part 1 krisgale.com/beware-of-table-alias-in-left-join It seems that MySQL decided to require parenthesis around multiple tables if they are in the select starting in version 5. That doesn't help my fix my hibernate query, help here would still be much appreciated — Craig Suchanec
– Craig Suchanec, Commented Mar 2, 2011 at 2:22

Maxime · Accepted Answer · 2015-09-10 15:14:49Z

2

Your first request does not work because "story0_" is unknown in the "on clause"

from fantasy.story story0_, fantasy.plotline plotline1_ inner join fantasy.character character2_ on story0_.CharacterID=character2_.CharacterID

When you write "from A, B inner join C", SQL (version 5.0 and later) considers an inner jointure between B and C only and then only allows a "on clause" between these 2 tables.

2 solutions should work :

Add parenthesis

from ( fantasy.story story0_, fantasy.plotline plotline1_ ) inner join fantasy.character character2_ on story0_.CharacterID=character2_.CharacterID

Change table order

from fantasy.plotline plotline1_, fantasy.story story0_ inner join fantasy.character character2_ on story0_.CharacterID=character2_.CharacterID

More details available at : https://bugs.mysql.com/bug.php?id=13551

I imagine that the solution you have found works because CharacterID is also a field in plotline1_

I am currently facing this problem with Hibernate, and even if i found why the SQL Statement raised an exception (and how to correct it), i don't know how to configure Hibernate to force it to generate one of theses 2 solutions.

If anyone knows how to do it with Hibernate, Thanks for your help !

edited Sep 10, 2015 at 15:14

answered Sep 10, 2015 at 14:57

Maxime

213 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vinícius M Over a year ago

While I couldn't make Hibernate create parenthesis (and even manually testing that didn't help my query) simply changing the order did the trick. And in that case, all I have to do was change the order of my query building string. For e.g., move > example.append(" LEFT JOIN purchase.buyer buyer ")

Collectives™ on Stack Overflow

Hibernate MySQL Query Error Unknown Column in Join On

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related