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)
- Why is this syntax invalid?
- Bonus: Is there a way to force Hibernate to use only explicit JOINs?