MySQL Bugs: #24725: migration from 4.1.8-max(winXPpro) to 5.1.beta(FreeBSD 7.0)

Bug #24725	migration from 4.1.8-max(winXPpro) to 5.1.beta(FreeBSD 7.0) - query sintax error
Submitted:	30 Nov 2006 15:47	Modified:	1 Dec 2006 7:12
Reporter:	Alexander Rumos	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1.beta	OS:	FreeBSD (FreeBSD 7.0)
Assigned to:		CPU Architecture:	Any

Description:
Hello, ALL.

I have a query that works correctly in 4.1.8-max:
SELECT c.itemid, d.`data` AS title, COUNT(cc.itemid) AS cnt, COUNT(ccc.itemid) AS onWay
FROM _dataset_items2items AS c
INNER JOIN _dataset_items2items AS ci ON ci.parent=c.itemid AND ci.i_nav=0 AND ci.i_publish=1
INNER JOIN _dataset_items2items AS cc ON cc.parent=97 AND cc.itemid=ci.itemid
LEFT JOIN _dataset_items2items AS ccc ON ccc.parent=c.itemid AND ccc.itemid=250
LEFT JOIN _dataset_data AS d ON d.itemid=i.itemid AND d.fieldid=297
INNER JOIN _dataset_items AS i ON i.itemid=c.itemid
WHERE c.parent=92 AND c.distance=0 AND c.i_nav=1 AND c.i_publish=1
GROUP BY c.itemid
ORDER BY i.nav_order
But this query doesn't work in 5.0.24a - I get error about unknown column i.itemid in ON clause
- it is about "LEFT JOIN _dataset_data AS d ON d.itemid=i.itemid AND d.fieldid=297". As I understand it happens because "_dataset_items AS i" is joined after using of its alias in previous joins. I changed order of joins and everything began working successfully. But the trouble is in big number of queries like this with the same trouble in my CMS - and it is hard to find them all (because there are too many various situations and "dinamic" queries in it) - so my questions is how to correct this bug in MySQL (bug,
because I talk about versions back-compatibility)

How to repeat:
See original query

Suggested fix:
queryes pre-optimizer block - I assume that error was here..

Thank you for the bug report. Please read:

http://dev.mysql.com/doc/refman/5.1/en/join.html

 Join Processing Changes in MySQL 5.0.12

Note: Natural joins and joins with USING, including outer join variants, are processed according to the SQL:2003 standard. The goal was to align the syntax and semantics of MySQL with respect to NATURAL JOIN and JOIN ... USING according to SQL:2003. However, these changes in join processing can result in different output columns for some joins. Also, some queries that appeared to work correctly in older versions (prior to 5.0.12) must be rewritten to comply with the standard. 

.....

I understand.
Thank you for your time and attention.

Rumos.