Bug #15823 | Fake "Unknown column" error with some JOIN syntaxes | ||
---|---|---|---|
Submitted: | 17 Dec 2005 12:22 | Modified: | 23 Jan 2006 10:41 |
Reporter: | Chris Wilson | Email Updates: | |
Status: | No Feedback | Impact on me: | |
Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
Version: | 5.0.16-standard | OS: | Linux (Linux (FC2)) |
Assigned to: | CPU Architecture: | Any |
[17 Dec 2005 12:22]
Chris Wilson
[17 Dec 2005 13:49]
MySQL Verification Team
Please read the release changes done in release 5.0.12 where the join syntax was changed. Also the Manual about. miguel@hegel:~/dbs/5.0> bin/mysql -uroot db5 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 5.0.18-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create temporary table events (id int4); Query OK, 0 rows affected (0.00 sec) mysql> create temporary table memberships (id int4); Query OK, 0 rows affected (0.01 sec) mysql> create temporary table groups (id int4); Query OK, 0 rows affected (0.01 sec) mysql> create temporary table eventtypes (id int4); Query OK, 0 rows affected (0.00 sec) mysql> SELECT 1 FROM events,memberships LEFT JOIN eventtypes ON eventtypes.ID = -> events.id,groups limit 10; ERROR 1054 (42S22): Unknown column 'events.id' in 'on clause' mysql> SELECT 1 FROM (events,memberships) LEFT JOIN eventtypes ON eventtypes.ID = events.id,groups limit 10; Empty set (0.00 sec)
[23 Dec 2005 10:41]
Sergei Golubchik
> This also suggests that INNER JOIN is not exactly (syntactically) > equivalent to "," whereas the docs say it should be. Could you point where the manual says that ? It is not 100% equivalent, it has lower precedence, that's why you see "Unknown column" error. FROM t1, t2 JOIN t3 means FROM t1, (t2 JOIN t3) If the manual says that comma "," is absolutely equivalent to INNER JOIN we'll have to correct that.
[14 Jan 2006 0:25]
[ name withheld ]
I'm also having the same problem. The following SQL statement runs correctly, Select S_eventEnd, S_eventStart,S_eventID, S_eventType, S_eventDesc, facilityName, S_resourceName, S_eventHteamID, S_eventVteamID, S_eventVscore, S_eventHscore, vt.teamName as vtTeamName, ht.teamName as htTeamName, ra.S_resourceAreaName as S_resourceAreaName from S_event left join team ht on S_eventHteamID = ht.teamID left join team vt on S_eventVteamID = vt.teamID left join S_resourceArea ra on S_event_resourceAreaID = ra.S_resourceAreaID ,S_resource,facility where S_event_resourceID = S_resourceID and S_resource_facilityID = facilityID and (S_eventHteamID = 20293 || S_eventVteamID = 20293) and (S_eventHteamID = ht.teamID || S_eventVteamID = vt.teamID) order by S_eventStart but when having comma's before a left join starts fails, Select S_eventEnd, S_eventStart,S_eventID, S_eventType, S_eventDesc, facilityName, S_resourceName, S_eventHteamID, S_eventVteamID, S_eventVscore, S_eventHscore, vt.teamName as vtTeamName, ht.teamName as htTeamName, ra.S_resourceAreaName as S_resourceAreaName from S_event ,S_resource,facility left join team ht on S_eventHteamID = ht.teamID left join team vt on S_eventVteamID = vt.teamID left join S_resourceArea ra on S_event_resourceAreaID = ra.S_resourceAreaID where S_event_resourceID = S_resourceID and S_resource_facilityID = facilityID and (S_eventHteamID = 20293 || S_eventVteamID = 20293) and (S_eventHteamID = ht.teamID || S_eventVteamID = vt.teamID) order by S_eventStart ERROR 1054 (42S22): Unknown column 'S_eventHteamID' in 'on clause'
[14 Jan 2006 0:37]
[ name withheld ]
Ballz, Here's the reason: http://dev.mysql.com/doc/refman/5.0/en/join.html # Before MySQL 5.0.12, the comma operator (,) and JOIN both had the same precedence, so the join expression t1, t2 JOIN t3 was interpreted as ((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression is interpreted as (t1, (t2 JOIN t3)). This change affects statements that use an ON clause, because that clause can refer only to columns in the operands of the join, and the change in precedence changes interpretation of what those operands are. Example: CREATE TABLE t1 (i1 INT, j1 INT); CREATE TABLE t2 (i2 INT, j2 INT); CREATE TABLE t3 (i3 INT, j3 INT); INSERT INTO t1 VALUES(1,1); INSERT INTO t2 VALUES(1,1); INSERT INTO t3 VALUES(1,1); SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3); Prior to 5.0.12, the SELECT is legal due to the implicit grouping of t1,t2 as (t1,t2). From 5.0.12 on, the JOIN takes precedence, so the operands for the ON clause are t2 and t3. Because t1.i1 is not a column in either of the operands, the result is an Unknown column 't1.i1' in 'on clause' error. To allow the join to be processed, group the first two tables explicitly with parentheses so that the operands for the ON clause are (t1,t2) and t3: SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3); This change also applies to INNER JOIN, CROSS JOIN, LEFT JOIN, and RIGHT JOIN.
[24 Jan 2006 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".