Re: [Phpwiki-talk] Wanted Pages SQL Problem

[William L. <wi...@pr...>]

William Leader wrote:

Alright, I ended up figuring this one out... I got Google lucky.

According to http://dev.mysql.com/doc/refman/5.0/en/join.html:
|
<Quote>
INNER JOIN| and |,| (comma) are semantically equivalent in the absence 
of a join condition: both produce a Cartesian product between the 
specified tables (that is, each and every row in the first table is 
joined to each and every row in the second table).

However, the precedence of the comma operator is less than than of 
|INNER JOIN|, |CROSS JOIN|, |LEFT JOIN|, and so on. If you mix comma 
joins with the other join types when there is a join condition, an error 
of the form |Unknown column '/|col_name|/' in 'on clause'| may occur. 
Information about dealing with this problem is given later in this section.
</Quote>

and sure enough later in the document they explain it. Basically prior 
to MySQL 5.0.12, Join and the comma operator were treated the same, but 
in 5.0.12 and later, the join keyword has higher precedence than the 
comma. So because the wanted pages code simplified is "... From 
link,page Join page join nonempty ..." MySQLl joins the link table last 
because it is joined by a comma rather than the join keyword.

The Solution:
in wiki/lib/WikiDB/backend/PearDB_mysql.php line 127:
Change
. " FROM $link_tbl,$page_tbl as linked "
To
. " FROM $link_tbl JOIN $page_tbl  as linked "

-William Leader

> Ok, as mentioned in my other message I have a new problem. This 
> problem came up when upgrading from MySQL 4.1.18 to 5.0.21. 
> WantedPages now seems to be broken. Anyone should be able to see it at 
> http://www.k2wrpg.org/wiki/index.php/WantedPages. If you do check it 
> out you will see that it bombs out with this error:
>
> lib\WikiDB\backend\PearDB.php (In template 'body' < 'html'):1027: 
> Error: wikidb_backend_peardb_mysql: fatal database error
>
>    * DB Error: no such field
>    * (SELECT page.pagename,linked.pagename as wantedfrom FROM link,page
>      as linked LEFT JOIN page ON (link.linkto=page.id) LEFT JOIN
>      nonempty ON (link.linkto=nonempty.id) WHERE ISNULL(nonempty.id)
>      AND linked.id=link.linkfrom AND linked.pagename NOT IN
>      ('PgsrcTranslation','InterWikiMap') LIMIT 0, 300 [nativecode=1054
>      ** Unknown column 'link.linkto' in 'on clause'])
>
> Now I know just enough SQL to see that this is a really complicated 
> join, and that I don't completely understand what the problem is. What 
> really confuses me is the Unknown column 'link.linkto' because linkto 
> certainly is a column of link,
>
> I really would like to help fix this because WantedPages is one of my 
> favorite pages, and I use it frequently to find stuff that needs to be 
> done.
>
> Thanks!
> -Will
>