db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Stefan Broetz (JIRA)" <j...@apache.org>
Subject [jira] Created: (TORQUE-60) MySQL left joins may lead to "ERROR 1054: Unknown column ... in on clause"
Date Tue, 17 Oct 2006 13:44:41 GMT
MySQL left joins may lead to "ERROR 1054: Unknown column ... in on clause"
--------------------------------------------------------------------------

                 Key: TORQUE-60
                 URL: http://issues.apache.org/jira/browse/TORQUE-60
             Project: Torque
          Issue Type: Bug
          Components: Runtime
    Affects Versions: 3.2
         Environment: MySQL 5.0.x (where x >= 15)
            Reporter: Stefan Broetz


I have three tables A, B, and C, each of them having an ID and DATA column. Now I want to
inner join A and B on their ids and left outer join A and C on their ids. Using

Criteria criteria = new Criteria();
criteria.addJoin(APeer.ID, BPeer.ID);
criteria.addJoin(APeer.ID, CPeer.ID, Criteria.LEFT_JOIN);
[...]
BasePeer.doSelect(criteria);

gives me the MySQL error 1054: "Unknown column 'a.ID' in 'on clause'. The problem is the generated
SQL statement:

SELECT ... FROM a, b LEFT JOIN c ON a.id = c.id WHERE a.id = b.id ...

According to the SQL:2003 standard this means that only tables B and C are joined and a.id
is neither a column in B nor in C. If you want to join tables A and C, your SQL statement
has either to look like this

SELECT ... FROM b, a LEFT JOIN c ON a.id = c.id WHERE a.id = b.id ...

(notice that I swapped a and b in the FROM clause) or like this

SELECT ... FROM (a, b) LEFT JOIN c ON a.id = c.id WHERE a.id = b.id ...

I guess the latter is what you usually want to have. So the solution might simply be the introduction
of parantheses around the FROM clause.

See also http://bugs.mysql.com/bug.php?id=13551

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org


Mime
View raw message