MySQL 5.0.12+ compatibility issue with JOIN statements

Started by dj-opie, September 25, 2005, 06:19:53 AM

Previous topic - Next topic

dj-opie

I posted this elsewhere, but figured it needed to be brought to the attention of [unknown], et al.
Starting with MySql 5.0.12, join statements now follow the sql 2003 standard, and thus are a bit more picky about syntax.
Places like this in the forum code:
$request = db_query("
      SELECT
         m.posterTime, m.subject, hxxp:m.id [nonactive]_TOPIC, hxxp:m.id [nonactive]_MEMBER, hxxp:m.id [nonactive]_MSG,
         IFNULL(mem.realName, m.posterName) AS posterName, hxxp:t.id [nonactive]_BOARD, hxxp:b.name [nonactive] AS bName,
         LEFT(m.body, 384) AS body, m.smileysEnabled
      FROM {$db_prefix}messages AS m, {$db_prefix}topics AS t, {$db_prefix}boards AS b
         LEFT JOIN {$db_prefix}members AS mem ON (mem.ID_MEMBER = hxxp:m.id [nonactive]_MEMBER)
      WHERE hxxp:m.id [nonactive]_MSG >= " . max(0, $modSettings['maxMsgID'] - 20 * $showlatestcount) . "
         AND hxxp:t.id [nonactive]_TOPIC = hxxp:m.id [nonactive]_TOPIC
         AND hxxp:b.id [nonactive]_BOARD = hxxp:t.id [nonactive]_BOARD" . (!empty($modSettings['recycle_enable']) && $modSettings['recycle_board'] > 0 ? "
         AND hxxp:b.id [nonactive]_BOARD != $modSettings[recycle_board]" : '') . "
         AND $user_info[query_see_board]
      ORDER BY hxxp:m.id [nonactive]_MSG DESC
      LIMIT $showlatestcount", __FILE__, __LINE__);


need to have parentheses around the table names in the FROM section, or else it doesn't work and the forum dies.
I noticed this issue in a few files, including recent.php and repairboards.php.  I'm sure it exists elsewhere, as well.
This is in SMF 1.1RC1.



EDIT:
Fixed, the code should be:
$request = db_query("
      SELECT
         m.posterTime, m.subject, hxxp:m.id [nonactive]_TOPIC, hxxp:m.id [nonactive]_MEMBER, hxxp:m.id [nonactive]_MSG,
         IFNULL(mem.realName, m.posterName) AS posterName, hxxp:t.id [nonactive]_BOARD, hxxp:b.name [nonactive] AS bName,
         LEFT(m.body, 384) AS body, m.smileysEnabled
      FROM ({$db_prefix}messages AS m, {$db_prefix}topics AS t, {$db_prefix}boards AS b)
         LEFT JOIN {$db_prefix}members AS mem ON (mem.ID_MEMBER = hxxp:m.id [nonactive]_MEMBER)
      WHERE hxxp:m.id [nonactive]_MSG >= " . max(0, $modSettings['maxMsgID'] - 20 * $showlatestcount) . "
         AND hxxp:t.id [nonactive]_TOPIC = hxxp:m.id [nonactive]_TOPIC
         AND hxxp:b.id [nonactive]_BOARD = hxxp:t.id [nonactive]_BOARD" . (!empty($modSettings['recycle_enable']) && $modSettings['recycle_board'] > 0 ? "
         AND hxxp:b.id [nonactive]_BOARD != $modSettings[recycle_board]" : '') . "
         AND $user_info[query_see_board]
      ORDER BY hxxp:m.id [nonactive]_MSG DESC
      LIMIT $showlatestcount", __FILE__, __LINE__);

dj-opie

#1
hxxp:bugs.mysql.com/bug.php?id=12943 [nonactive]

Tracks the status of the 'bug.'

Not sure if it wouldn't be such a bad idea to just do the parentheses, anyway, for good form.

ThomasJ

MySQL 5.0 is now GA with version 5.0.15.

This issue still exsists, and the mysql database entry indicates that this is intended behaviour and not a bug.
See also http://www.simplemachines.org/community/index.php?topic=54936.0 (french)

It would be nice if rc2 or 1.1 final aswell as 1.0.x could be brought up to speed to let it work with MySQL 5.0
Whoops! Did i break that?

Advertisement: