sql - Mixing implicit and explicit JOINs

Question

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?

---

Answer 1

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.