混合隐式和显式连接 [英] Mixing implicit and explicit JOINs

问题描述

我遇到了Hibernate生成无效SQL的问题。具体而言，混合和匹配隐式和显式连接。这似乎是打开错误。

但是，我不确定为什么这是无效的SQL。

模式

我的小玩具示例产生了相同的语法异常。  CREATE TABLE Employee（
 employeeID INT，
 name VARCHAR（255），
 managerEmployeeID INT 
）
  

$ b

数据

  INSERT INTO Employee（employeeID，name） VALUES（1，'Gary'）
 INSERT INTO Employee（employeeID，name，managerEmployeeID）VALUES（2，'Bob'，1）
  

工作SQL

这两个查询都有效。我意识到有一个笛卡儿的产品;这是故意的。

显式连接：

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

隐式JOIN：

  SELECT e1.name，
 e2.name，
 e1Manager.name 
 FROM Employee e1，
雇员e2，
雇员e1Manager 
 WHERE e1.managerEmployeeID = e1Manager.employeeID 
  

< h2>无效SQL

此查询不适用于MSSQL 2000/2008或MySQL：

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

在MS2000中，我收到了错误r：

列前缀'e1'与
不匹配，表名或别名使用
查询。

在MySQL中，错误是：

lockquote

未知列'e1.managerEmployeeID'
in'on clause'。

问题

1. 为什么此语法无效？ 强制Hibernate只使用显式连接的方法？

---

解决方案

它导致错误，因为根据SQL标准， JOIN 关键字的优先级高于逗号。问题的关键在于，在 FROM 子句中评估相应的表之前，表别名不可用。

因此，当您在 JOIN ... ON e1 $ c>表达式， e1 还不存在。

当我研究Hibernate并查明如果你能说服它在所有情况下都使用 JOIN 。

---

嗯。 Hibernate.org上的所有内容似乎都重定向到了jboss.org。所以现在没有办法在线阅读HQL文档。我相信他们最终会找出他们的名字。

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.

这篇关于混合隐式和显式连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！