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:
None 
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
Description:
Hi all,

New feature in 5.0: LEFT JOIN with an ON clause can no longer be mixed freely with other join types, such as "," (equivalent to INNER JOIN). Having a LEFT JOIN after a comma join results in a spurious "Unknown column" error.

This did not happen in MySQL 4.0.

How to repeat:
create temporary table events (id int4);
create temporary table memberships (id int4);
create temporary table groups (id int4);
create temporary table eventtypes (id int4);

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 LEFT JOIN eventtypes ON eventtypes.ID = events.id,memberships,groups limit 10;
Empty set (0.00 sec)

mysql> SELECT 1 FROM events INNER JOIN memberships LEFT JOIN eventtypes ON eventtypes.ID = events.id,groups limit 10;
Empty set (0.00 sec)

This also suggests that INNER JOIN is not exactly (syntactically) equivalent to "," whereas the docs say it should be.

Suggested fix:
Make "," exactly the same as INNER JOIN in the SQL parser.

Might possibly be related to bugs 15229 and 9067, but I'm not using USING, and as you can see I already give the table name in the ON clause (not that it makes any difference).

Thanks!
[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".