itVincent Blog - Java Working Fun!

"unknown column ... in 'on clause'" in JOIN

用HQL執(zhí)行關(guān)聯(lián)查詢

1.創(chuàng)建表格:

     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.執(zhí)行查詢:

     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

這句話執(zhí)行應(yīng)該是沒有錯誤的,但是

Mysql 5 下執(zhí)行則會出錯: "ERROR 1054 (42S22): Unknown column 'a.id' in 'on clause'"

因為mysql下有這樣一個BUG,要把聯(lián)合的表用括號包含起來才行:

    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下就不會出錯了