Mixing implicit and explicit JOINs

I am having a problem with Hibernate generating invalid SQL. Specifically, mixing and matching implicit and explicit joins. This seems to be an open bug.

However, I'm not sure why this is invalid SQL. I have come up with a small toy example that generates the same syntax exception.

Schema
CREATE TABLE Employee (
    employeeID INT,
    name VARCHAR(255),
    managerEmployeeID INT   
)
Data
INSERT INTO Employee (employeeID, name) VALUES (1, 'Gary')
INSERT INTO Employee (employeeID, name, managerEmployeeID) VALUES (2, 'Bob', 1)
Working SQL

Both of these queries work. I realize there is a Cartesian product; that's intentional.

Explicit JOIN:

SELECT e1.name,
       e2.name,
       e1Manager.name
  FROM Employee e1
 CROSS JOIN Employee e2
 INNER JOIN Employee e1Manager
    ON e1.managerEmployeeID = e1Manager.employeeID

Implicit JOIN:

SELECT e1.name,
       e2.name,
       e1Manager.name
  FROM Employee e1,
       Employee e2,
       Employee e1Manager
 WHERE e1.managerEmployeeID = e1Manager.employeeID
Invalid SQL

This query does NOT work on MSSQL 2000/2008 or MySQL:

SELECT e1.name, 
       e2.name, 
       e1Manager.name
  FROM Employee e1,
       Employee e2
 INNER JOIN Employee e1Manager 
    ON e1.managerEmployeeID = e1Manager.employeeID

In MS2000, I get the error:

The column prefix 'e1' does not match with a table name or alias name used in the query.

In MySQL, the error is:

Unknown column 'e1.managerEmployeeID' in 'on clause'.

Question(s)
  1. Why is this syntax invalid?
  2. Bonus: Is there a way to force Hibernate to use only explicit JOINs?

It results in an error because according to the SQL standard, the JOIN keyword has higher precedence than the comma. The sticky point is that table aliases are not usable until after the corresponding table has been evaluated in the FROM clause.

So when you reference e1 in your JOIN...ON expression, e1 doesn't exist yet.

Please stand by while I research Hibernate and find out if you can persuade it to use JOIN in all cases.


Hmm. Everything at Hibernate.org seems to be redirecting to jboss.org. So no way to read HQL documentation online right now. I'm sure they'll figure out their name serving eventually.

Mixing and matching implicit and explicit JOINs, Mixing and matching implicit and explicit JOINs. Back in the caveman days, there was only one way to JOIN tables in a SQL query. Now, we  In my experience, an implicit join is the most commo n way to connect two tab les. However, my observation might be heavily biased, because an implicit join is the way I tend to write things in my daily work. Using explicit joins. The following example sho ws an exp licit join. Some people prefer the explicit join syntax over implicit joins

This might be a bit off topic, because it doesn't concern hibernate at all, but the comment from Bill Karwin really opened my eyes. Instead of writing the implicit joining first, you need to do the explicit joining first. This syntax is especially interesting if you have multiple implicit joins.

Check the following example in MS SQL. Not all contacts have a country code defined, but all contacts have an attribute val which will be looked up in the table Tbl. So the intuitive solution will not work:

SELECT * FROM 
contacts, Tbl
LEFT OUTER JOIN country ON CtryCod = country.CtryCod 
WHERE val = Tbl.val

Instead you might rather want to use the following syntax:

SELECT * FROM 
contacts LEFT OUTER JOIN country ON CtryCod = country.CtryCod, 
Tbl
WHERE val = Tbl.val

JOIN, This implicit join syntax can be a useful substitute for explicit join syntax, some important restrictions on combining arrow syntax with explicit join syntax. If I remember correctly, explicit joins are being processed before implicit joins, therefore - t2. is not available yet. Solution: Avoid the use of implicit join syntax, and use the proper syntax of joins , just like the second part of your query.

PostgreSQL gives an error too:

ERROR:  invalid reference to FROM-clause entry for table "e1"
LINE 7:     ON e1.managerEmployeeID = e1Manager.employeeID;
               ^
HINT:  There is an entry for table "e1", but it cannot be referenced from this part of the query.

I think the problem is that when you join two tables a and b (e2 and e1Manager in this case) you can only reference those two tables in the "ON" clause. So you can reference e2 and e1Manager in this ON clause, but not e1.

I think this extends so that if you have a chain of "JOIN" statements, you can reference other tables in the same chain in "ON" clauses, but you can't cross a ",". So something like `a JOIN b ON a.a_id = b.a_id JOIN c ON c.b_id = b.b_id AND c.a_id = a.a_id" is allowed.

What's the HQL you are using to produce this SQL? Something like "select e1.name, e2.name, e1.manager.name from Employee e1, Employee e2" ?

Are implicit joins as efficient as explicit joins in Postgres?, You may want to use subqueries, CTEs, mix explicit and implicit joins, use parentheses among groups of FROM clause items or play with  Mixing and matching implicit and explicit JOINs Back in the caveman days, there was only one way to JOIN tables in a SQL query. Now, we refer to this as "table list", "theta" or simply "implicit" JOIN syntax:

[HHH-3388] Problem mixing implicit and explicit joins, With Hibernate 2, mixing explicit and implicit joins produced an SQL query with all "theta style" joins at the end of the query. In Hibernate 3, ANSI and "theta style"​  With Hibernate 2, mixing explicit and implicit joins produced an SQL query with all "theta style" joins at the end of the query. In Hibernate 3, ANSI and "theta style" are mixed, which Oracle doesn't like. HBM: select a.description, a.seller, b.item.description " + "from org.hibernate.auction.AuctionItem a inner join a.bids b

Mixing Explicit and Implicit Flat Joins in Different Queries Yields ON , Mixing Explicit and Implicit Flat Joins in Different Queries Yields ON-Clause Error #1538. Open. deusaquilus opened this issue on Jul 24, 2019 · 0 comments. In SQL Server, explicit and implicit join’s performances are almost the same. Still, if you want to understand the differences between them, differences based on their notation are explained below. The implicit syntax is difficult to understand whereas, the explicit join is easier to read.

The Firebird Book: A Reference for Database Developers, COLY FROM Table1 JOIN Table2 ON Table1. FK JOIN Table3 ON TABLE2. Mixing Implicit and Explicit Syntaxes Writing statements that include a mixture of  When explicit wait starts and looks for element, because of implicit wait it needs to wait for 10 seconds because element is not found. So both waits completes 10 seconds wait time. But sometime you get wait time as 20 seconds because of synchronization issue. Implicit wait throws exception if element is not found.

Comments
  • SELECT a.id, a.category.id, a.category.name, a.owner FROM Candidate a, where category and owner are associations. The real kicker is that if you put owner first, it works, due to a lucky re-ordering of the JOINs.
  • Based on a quick reproduction of the problem, you might like to try not mixing selecting entities and properties: either select a.id, a.category, a.owner or a.id, a.category.id, a.category.name, a.owner.id -- both these alternatives produced valid SQL for me (but your HQL produced invalid SQL as you said) For myself, I'd typically just get the Candidate object and let Hibernate automatically load the category and owner on demand. Or use "left join fetch" to fetch them eagerly.