Bug #13103 AS or ON stopped working since 5.0.11-beta
Submitted: 10 Sep 2005 10:51 Modified: 23 Sep 2005 10:46
Reporter: Micheas Herman Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.12-beta OS:FreeBSD (freebsd4.10 (i386))
Assigned to: CPU Architecture:Any

[10 Sep 2005 10:51] Micheas Herman
Description:
mysql -V produces:

mysql  Ver 14.12 Distrib 5.0.12-beta, for portbld-freebsd4.10 (i386) using readline 5.0

Mambo's query to select content for editing now fails with a 1054 error number. even though all the tables and columns seem to exist.

I am sorry for the lack of details, but I don't really know what is wrong. Let me know what else I can provide.

How to repeat:
Install Mambo server with the mos_ prefix and execute the following query:

SELECT c . * , g.name AS groupname, cc.name, u.name AS editor, f.content_id AS frontpage, s.title AS section_name, v.name AS author
FROM mos_content AS c, mos_categories AS cc, mos_sections AS s
LEFT JOIN mos_groups AS g ON g.id = c.access
LEFT JOIN mos_users AS u ON u.id = c.checked_out
LEFT JOIN mos_users AS v ON v.id = c.created_by
LEFT JOIN mos_content_frontpage AS f ON f.content_id = c.id
WHERE c.state >=0
AND c.catid = cc.id
AND cc.section = s.id
AND s.scope = 'content'
ORDER BY s.title, c.catid, cc.ordering, cc.title, c.ordering
LIMIT 0 , 20
[10 Sep 2005 13:47] Hartmut Holzgraefe
I assume this is due to the following 5.0.12 changelog item? (see also bug #12964)

* Natural joins and joins with USING, including outer join variants, now are
processed according to the SQL:2003 standard. (Bug #4789, Bug #6136, Bug #6276,
Bug #6495, Bug #6558, Bug #9978, Bug #10646, Bug #10972, Bug #11710)
[11 Sep 2005 3:58] Micheas Herman
The FreeBSD port does not warn you about the change in joins, that I suspect will bite a lot of people.

I did not find my issue with a cursory look in bugzilla. So maybe this bug report should stay here, but the bug is resolved. This is the result of joins being updated in 5.0.12.

The solution is to put '()' around the joined tables.
[11 Sep 2005 8:14] Valeriy Kravchuk
So, it is not a bug. It's just a new behaviour (based on standard), that we need to take into account.
[11 Sep 2005 17:06] Micheas Herman
Correct, except that this change breaks a lot of code so the installation should give warning. So I would consider the lack of warning (at least in the FreeBSD ports collection) a bug. (minor polish type bug,)
[23 Sep 2005 10:46] Sergei Golubchik
No, as the mambo query does not use NATURAL JOIN or USING it should be unaffected by this change.

In fact this bug is a duplicate of bug#12943  - which is already fixed.