"unknown column ... in 'on clause'" in JOIN
用HQL執行關聯查詢
1.創建表格:
CREATE TABLE a (id INT NOT NULL);
CREATE TABLE b (id INT NOT NULL);
CREATE TABLE c (a_id INT NOT NULL, b_id INT NOT NULL);
2.HQL語句
Select (c.id, c.a.id, c.b.id) from C c
3.執行查詢:
SELECT (c.id, a.id, b.id) FROM A a, B b LEFT JOIN C c ON c.a_id = a.a_id AND c.b_id = b.b_id
這句話執行應該是沒有錯誤的,但是
Mysql 5 下執行則會出錯: "ERROR 1054 (42S22): Unknown column 'a.id' in 'on clause'"
因為mysql下有這樣一個BUG,要把聯合的表用括號包含起來才行:
SELECT (c.id, a.id, b.id) FROM (A a, B b) LEFT JOIN C c ON c.a_id = a.a_id AND c.b_id = b.b_id
但是HQL生成就是這樣的語句,怎么辦呢?我們可以改變HQL的寫法來達成生成另一種SQL語句,以避免這種BUG的出錯,把select (c.id, a.id, b.id) from C c
Left join c.a a
Left join c.b b
則會生成
SELECT (c.id, a.id, b.id) FROM A a LEFT JOIN B b LEFT JOIN C c ON c.a_id = a.a_id AND c.b_id = b.b_id
這樣的話mysql下就不會出錯了