"PaginaDeSpud" <[EMAIL PROTECTED]> wrote on 12/27/2005 03:33:58 PM:
> hi, > I've upgraded from mysql 4.1 to mysql 5 and some queries doesn't work. It's > not explained on mysql changes incompatibilities... > > for example: > <snip> > FROM yabbse_topics, yabbse_messages, yabbse_messages as m2 LEFT JOIN > yabbse_members AS mem ON (mem.ID_MEMBER=yabbse_messages.ID_MEMBER) LEFT JOIN > 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 AND > lmr.ID_MEMBER=2) > > WHERE yabbse_topics.ID_TOPIC IN > (38562,39516,66603,24732,67085,59867,43884,68331,68330,56897,68090, > 68144,68174,68202,68329,68280,68258,68294,68299,68326,68325,67977, > 67286,55125,67897,65748,68316,68317,68152,68291,68187,68000,68240, > 68237,66796,68271,67486,57293,68027,67332,67990,68089,68072,42517, > 68315,63612,66121,68275,68032,68261,68308,67919,68305,68302,68300, > 68296,40983,68256,68293,68295,68270,68212,68198,45444,67246,68215, > 68264,68208,68133,67017) > 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' <snip> I don't know which incompatible change list you looked at but it is definitely on the top of this page: http://dev.mysql.com/doc/refman/5.0/en/news-5-0-12.html complete with a link to more information: http://dev.mysql.com/doc/refman/5.0/en/join.html 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. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
