I am having problems with "java.sql.SQLSyntaxErrorException: Unknown column '*' in 'on clause'" with hibernate.
Because jpql does not support self joins, I am trying to use other pretty ugly, but working solutions with lots of "left join on". Very often I get this exception, while the column does exist. Some random replacing of the rows (with one join per row) might help, or setting conditions of one join in other join can also help. But I do not understand why is that!
Why with one all the same query hibernate can see or unsee some colunm when I only change the order of joins or set "on" conditions to another join or into "where clause"? Thank you.
So, here is the thing. I had a complex DB, and building complex queries almost always ended up with "Unknown column '*' in 'on clause'".
The reason for that was in the structure of my DB itself and MySQL query optimizer.
MySQL and I guess any DB is trying to optimize your query and the order of joins to work as little as possible often based on the quantity of entries in each table.
One of the tables had relationship only with one another table. As it was a 1-1 relationship only with one table, the optimizer almost always was joining it in the very end, after doing all other jobs. But it could not do that, because it had to use data from that table, that will be connected in the very end. So I had to refactor my DB and make 1 table from 2, because 2 table really did not make any sense(I did that in the beginning to make DB as future-proof as possible).
I hope it helps. Feel free to ask if smth is not clear.