MySQL Bugs: #36857: table alias - unknown in on-clause when using table-aliases other than the last

Bug #36857	table alias - unknown in on-clause when using table-aliases other than the last
Submitted:	21 May 2008 16:48	Modified:	21 May 2008 17:42
Reporter:	Thomas Wittich	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.1.11 & 5.0.44	OS:	Any (Linux as well Win XP Prof SP2)
Assigned to:		CPU Architecture:	Any
Tags:	join, on-clause, sql-parser, table alias, usage

Description:
When using a LEFT JOIN CLAUSE, a query throws an exception berfore executing due to an unlogical behavior of the parser:

Consider this query (regardsless the content and details):

SELECT e.id,e.remark, duration, c.name last,first,text_a, text_b, text_c, cat.remark cat_remark
FROM semorg_description d, semorg_event e, semorg_contact c
LEFT JOIN semorg_category_rel car
  ON category_type='event' and other_id=e.id
LEFT JOIN semorg_category cat
  ON cat.type=car.category_type and car.category_name = cat.name
where e.description_id=d.id and e.contact_id = c.id
ORDER BY c.name, c.organisation;

Consider the slightly change of one Line (containing the "from"):
FROM semorg_description d, semorg_contact c, semorg_event e

Results should be each the same.

What happens is in the first case:
Unknown column 'e.id' in 'on clause'

SQL-Parser means line:
  ON category_type='event' and other_id=e.id

But in the 2nd case, where table "semorg_event" is the last of the from clause, works as expected.

So, i assume it's not really true that a join only works with table aliases when they occur at the end of the from-clause.

I verified it with sql's with only one table in from (that works).
and a similar query with the same result - only working when at the end of from.

I've a problem with that - because my queries are not fast changable since they are generated form a sql-engine.

How to repeat:
Use any table
Join another two tables with aliases
Join a third one by left join clause with an on-clause using an column of the first table of the from clause, by using a table alias.

CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci */;

DROP TABLE IF EXISTS `test`.`t1`;
CREATE TABLE  `test`.`t1` (
  `id` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

DROP TABLE IF EXISTS `test`.`t2`;
CREATE TABLE  `test`.`t2` (
  `id` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

DROP TABLE IF EXISTS `test`.`t3`;
CREATE TABLE  `test`.`t3` (
  `id` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

DROP TABLE IF EXISTS `test`.`t3`;
CREATE TABLE  `test`.`t3` (
  `id` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

select a.id,b.id,c.id
from t1 a, t2 b
left join t3 c on a.id = c.id;

Result: Unknown column 'a.id' in 'on clause'

select a.id,b.id,c.id
from t2 b, t1 a
left join t3 c on a.id = c.id;

Result: OK

Suggested fix:
Please take care of multiple table alias notations in FORM-clause when parsing table aliases.

Thank you for the bug report. Please read the Manual regarding Join syntax
changes from version 5.0.13 to be more sql standard. Thanks in advance.

c:\dbs>5.0\bin\mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.62-nt Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DROP TABLE IF EXISTS `test`.`t1`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE  `test`.`t1` (
    ->   `id` int(10) unsigned NOT NULL auto_increment,
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
Query OK, 0 rows affected (0.09 sec)

mysql>
mysql> DROP TABLE IF EXISTS `test`.`t2`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE  `test`.`t2` (
    ->   `id` int(10) unsigned NOT NULL auto_increment,
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
Query OK, 0 rows affected (0.06 sec)

mysql>
mysql> DROP TABLE IF EXISTS `test`.`t3`;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE  `test`.`t3` (
    ->   `id` int(10) unsigned NOT NULL auto_increment,
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
Query OK, 0 rows affected (0.08 sec)

mysql>
mysql> DROP TABLE IF EXISTS `test`.`t3`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE  `test`.`t3` (
    ->   `id` int(10) unsigned NOT NULL auto_increment,
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
Query OK, 0 rows affected (0.05 sec)

mysql>
mysql> select a.id,b.id,c.id
    -> from t1 a, t2 b
    -> left join t3 c on a.id = c.id;
ERROR 1054 (42S22): Unknown column 'a.id' in 'on clause'

mysql> select a.id,b.id,c.id
    -> from (t1 a, t2 b)
    -> left join t3 c on a.id = c.id;
Empty set (0.00 sec)