Bug #403 MySQL do not understand the syntax 'left join + table alias'

• Submitted: 8 May 2003 12:27
• Modified: 12 Jun 2003 8:29
• Reporter: Dmitry Katsubo
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.0
• OS: Linux (Linux)
• CPU Architecture: Any

[8 May 2003 12:27] Dmitry Katsubo

Description:
Release: mysql-4.1.0-alpha (Official MySQL RPM)

I got the error, when trying to use the table alias in left join.
When using fully qualified table names without aliases, the query works.
The query is known to work in 4.0.12.

How to repeat:
SELECT m.last_activity
FROM ibf_members m
LEFT JOIN ibf_moderators mod ON (mod.member_id = m.id)
WHERE m.id='1';

ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'mod ON (mod.member_id = m.id)
WHERE m.id='1'' at line 3

[8 May 2003 12:32] Dmitry Katsubo

Sorry, missed the version.

[1 Jun 2003 14:17] Michael Widenius

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

The problem is that you are trying to give the table an alias that is also a reserverd word.

Fix: use another alias or put ` around the alias.

[6 Jun 2003 9:34] Dmitry Katsubo

Thank you for the answer. You are 100% right.
But your answer also means, that 4.0.12 behaves itself incorrectly. Or 'mod' is not a reserved word there?

[12 Jun 2003 8:29] Michael Widenius

Yes, MOD is a new operator in 4.1.0.  This is deocumented in the 4.1 changelog