Re: [Phpwiki-talk] Wanted Pages SQL Problem

[Reini U. <ru...@x-...>]

2006/5/9, William Leader <le...@k2...>:
> I composed this message once before but it seems to have gotten lost in
> the ether. 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 sectio=
n.
> </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 "

Thanks! Fixed now in CVS.
According ANSI SQL rules, so it is now:

$sql =3D "SELECT p.pagename, pp.pagename as wantedfrom"
            . " FROM $page_tbl p JOIN $link_tbl linked"
            . " LEFT JOIN $page_tbl pp ON linked.linkto =3D pp.id"
            . " LEFT JOIN $nonempty_tbl ne ON linked.linkto =3D ne.id"
            . " WHERE ne.id is NULL"
=09    .       " AND p.id =3D linked.linkfrom"
            . $exclude_from
            . $exclude
            . $orderby;

mysql uses the derivate: WHERE ISNULL(ne.id)


--=20
Reini Urban
http://phpwiki.org/
http://spacemovie.mur.at/   http://helsinki.at/