Re: [Wikitech-l] SQL for Subversion update-report

On 9/28/07, Jack Bates ms419@freezone.co.uk wrote:

 SELECT page_title, t1.rev_id, t2.rev_id FROM page, revision AS t1
 LEFT JOIN revision AS t2 ON t1.rev_page = t2.rev_page AND t2.rev_id
 < t1.rev_id WHERE page_id = t1.rev_page AND t1.rev_id > 18 GROUP BY
 page_id;

However I actually get:

 SELECT  page_title,MAX(t1.rev_id),t2.rev_id  FROM `page`,`revision
 AS t1 LEFT JOIN revision AS t2 ON t1.rev_page = t2.rev_page AND
 t2.rev_id < t1.rev_id`  WHERE (page_id = t1.rev_page) AND
 (t1.rev_id > 18) GROUP BY page_id

I'm sure the back ticks are a problem, but am not yet fully conversant with MediaWiki's database layer, so don't know the "right" way to fix them. Suggestions?

I haven't looked over your actual problem, but as far as the queries go: 1) the backticks aren't a problem, they're MySQL's non-standard way of escaping table names. The queries you posted are functionally identical except for what are presumably transcription errors (e.g. t1.rev_id vs. MAX(t1.rev_id) ). 2) It's considered inadvisable to mix implicit and explicit join syntax, because associativity between them tends to be unexpected. Instead of

array( 'page', 'revision AS t1 LEFT JOIN revision AS t2 ON t1.rev_page = t2.rev_page AND t2.rev_id < t1.rev_id' )

try

array( 'page JOIN revision AS t1 LEFT JOIN revision AS t2 ON t1.rev_page = t2.rev_page AND t2.rev_id < t1.rev_id' )