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語(yǔ)句

    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)該是沒(méi)有錯(cuò)誤的,但是

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

因?yàn)?/span>mysql下有這樣一個(gè)BUG,要把聯(lián)合的表用括號(hào)包含起來(lái)才行:

    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生成就是這樣的語(yǔ)句,怎么辦呢?我們可以改變HQL的寫(xiě)法來(lái)達(dá)成生成另一種SQL語(yǔ)句,以避免這種BUG的出錯(cuò),把select (c.id, a.id, b.id) from C c

    Left join c.a a

    Left join c.b b

則會(huì)生成

    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下就不會(huì)出錯(cuò)了