MySQL Bugs: #8829: views don't work with more than one join in the select statement

Bug #8829	views don't work with more than one join in the select statement
Submitted:	27 Feb 2005 11:07	Modified:	11 May 2005 16:59
Reporter:	Harald Gölles	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.3-alpha (bk 25.02.05)	OS:	Linux (linux)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
mysql> select * from tv;
ERROR 1064 (42000): 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 'dms`.`t1` join `dms`.`t2` on((`dms`.`t1`.`id` = `dms`.`t2`.`id`))) join `dms`.`t' at line 1
mysql>

the error occours when more than one join is used in the select - statement

How to repeat:
create table t1 (id int);
create table t2 (id int);
create table t3 (id int);

create view tv as 
select t1.id from t1
inner join t2 on t1.id = t2.id
inner join t3 on t3.id = t3.id;

select * from tv;

drop view tv;
drop table t1;
drop table t2;
drop table t3;

I've the same problem with alpha-nightly 2005-03-04.
Also my MySQL-Query Browser shows views as table and not as views anymore. With 5.0.1 on windows it works.

Addition: I tried a workaround to create 2 views where one joins the other... then I get the "Connection lost during Query" error.

new Workaround: Don't use the "INNER JOIN ... ON" clause, use a WHERE condition on 2 tables.

eg.:
SELECT t1.id, t2.id, t3.id
FROM t1, t2 LEFT JOIN (t3)
ON (t1.id=t3.id)
WHERE t1.id=t2.id;

Thank you for taking the time to report a problem.  Unfortunately
you are not using a current version of the product your reported a
problem with -- the problem might already be fixed. Please download
a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions,
please change the version on this bug report to the version you
tested and change the status back to "Open".  Again, thank you for
your continued support of MySQL.