https://bugzilla.wikimedia.org/show_bug.cgi?id=22613

           Summary: Potential MySQL syntax error in function
                    tableNamesWithUseIndexOrJOIN
           Product: MediaWiki
           Version: 1.16-svn
          Platform: All
        OS/Version: All
            Status: NEW
          Severity: normal
          Priority: Normal
         Component: Database
        AssignedTo: wikibugs-l@lists.wikimedia.org
        ReportedBy: m...@tgries.de


During my work using the hook "SpecialRecentChangesQuery" (code and detailed
analysis see [1]) I found a reproducible problem which arise only under the
following conditions:

- if MySQL >= 5.0.12 AND
- if the hook function for SpecialRecentChangesQuery adds table(s) to $table[].

Analysis:

The code in [1] modifies the Recent Changes main SQL statement to this 

SELECT  *  FROM `recentchanges` FORCE INDEX (rc_timestamp),`page` LEFT JOIN
`tag_summary` ON ((ts_rc_id=rc_id))  WHERE (rc_timestamp >= '20100211000000')
AND rc_bot = '0'  ORDER BY rc_timestamp DESC LIMIT 50  

This throws an error Unknown column 'rc_id' in 'on clause' (localhost) (MySQL
>= 5.0.12 due to new JOIN processing)

This ad-hoc modification works (parentheses added around the table names
outside the JOIN)

SELECT  *  FROM (`recentchanges` FORCE INDEX (rc_timestamp),`page`) LEFT JOIN
`tag_summary` ON ((ts_rc_id=rc_id))  WHERE (rc_timestamp >= '20100211000000')
AND rc_bot = '0'  ORDER BY rc_timestamp DESC LIMIT 50  

I add an ad-hoc and very hacky - only experimental - patch, which corrects the
problem in a certain case for [1]. The patch is not mentioned for SVN
submisson.

Citing [2]: Beginning with MySQL 5.0.12, 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
must be rewritten to comply with the standard.

Citing [3]:
SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);

Previously, the SELECT was legal due to the implicit grouping of t1,t2 as
(t1,t2). Now the JOIN takes precedence, so the operands for the ON clause are
t2 and t3. Because t1.i1 is not a column in either of the operands, the result
is an Unknown column 't1.i1' in 'on clause' error. To allow the join to be
processed, group the first two tables explicitly with parentheses so that the
operands for the ON clause are (t1,t2) and t3:

SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);

Alternatively, avoid the use of the comma operator and use JOIN instead:
SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);


[1] http://www.mediawiki.org/wiki/Extension:OnlyRecentRecentChanges
[2] MySQL Manual Join Processing Changes in MySQL 5.0.12
http://dev.mysql.com/doc/refman/5.0/en/join.html
[3] Bug #19053 MySQL Unknown column in 'on clause'
http://bugs.mysql.com/bug.php?id=19053

-- 
Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
You are on the CC list for the bug.

_______________________________________________
Wikibugs-l mailing list
Wikibugs-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikibugs-l

Reply via email to