Bug #25538 subquery with LEFT JOIN cannot use column of outer query in ON condition

• Submitted: 10 Jan 2007 23:58
• Modified: 11 Jan 2007 2:40
• Reporter: Thiemo Nagel
• Status: Not a Bug
• Category: MySQL Server: Parser
• Severity: S3 (Non-critical)
• Version: 5.0.22-standard
• OS: Linux (Debian 3.1)
• CPU Architecture: Any

[10 Jan 2007 23:58] Thiemo Nagel

Description:
I think it is a little strange that one may use a column from an outer query inside the WHERE condition but not the ON condition.

I'm not sure whether this limitation really is a bug, however it is not documented in the reference manual (in the "Restrictions on Subqueries" section).  At least I couldn't find any mentioning of that issue.  So I'm trying to be useful and report it ;-)

How to repeat:
create table A (a integer);
create table B (b integer);
create table C (c integer);

mysql> select * from A where a=(SELECT c from B,C WHERE b=a);
Empty set (0.00 sec)

mysql> select * from A where a=(SELECT c from B,C ON b=a);
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 'ON b=a)' at line 1

Suggested fix:
In my opinion, either the limitation should be removed or it should be documented as a limitation.

[11 Jan 2007 2:40] MySQL Verification Team

We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.