"PaginaDeSpud" <webmaster@stripped> wrote on 12/27/2005 03:33:58
> I've upgraded from mysql 4.1 to mysql 5 and some queries doesn't work.
> not explained on mysql changes incompatibilities...
> for example:
> FROM yabbse_topics, yabbse_messages, yabbse_messages as m2 LEFT JOIN
> yabbse_members AS mem ON (mem.ID_MEMBER=yabbse_messages.ID_MEMBER) LEFT
> yabbse_members AS mem2 ON (mem2.ID_MEMBER=m2.ID_MEMBER) LEFT JOIN
> yabbse_log_topics AS lt ON (lt.ID_TOPIC=yabbse_topics.ID_TOPIC AND
> lt.ID_MEMBER=2) LEFT JOIN yabbse_log_mark_read AS lmr ON (lmr.ID_BOARD=3
> WHERE yabbse_topics.ID_TOPIC IN
> AND yabbse_messages.ID_MSG=yabbse_topics.ID_LAST_MSG AND
> m2.ID_MSG=yabbse_topics.ID_FIRST_MSG ORDER BY t.isSticky DESC,
> yabbse_messages.posterTime DESC
> ERROR: Unknown column 'yabbse_messages.ID_MEMBER' in 'on clause'
I don't know which incompatible change list you looked at but it is
definitely on the top of this page:
complete with a link to more information:
Once you read over that, if you can't find the flaw in your query come
back to the list and I or someone else will help point it out to you. Your
query has always been broken, it's just that some of the bugs in the SQL
engine were eliminated with 5.0.12 so that it evaluates SQL statements
more according to the specification and your query cannot be evaluated
according to the updated rules.
It's like having a bad spell-checker in a word processing appliction. You
could go for months using one spelling of a word and never get flagged for
it. However if you upgraded the spell-checker it may start flagging you on
the same word that used to pass muster in the old version. This is a good
thing because as your query becomes more ANSI compliant, it becomes more
likely to be used cross-platform and not just on MySQL.
As an observation: unless you are using the comma-separated form of
creating an implicit CROSS JOIN, you don't get caught by the tightening of
the rules. Nobody has posted a question of this same nature to the list
that only uses explicit JOIN statements. hmmm.
Unimin Corporation - Spruce Pine