Uploaded image for project: 'phpBB3'
  1. phpBB3
  2. PHPBB3-10296

incorrect cross join in SQL Server

    Details

    • Type: Bug
    • Status: Unverified Fix
    • Priority: Blocker
    • Resolution: Fixed
    • Affects Version/s: 3.0.9
    • Fix Version/s: 3.0.10-RC1
    • Labels:
      None
    • Environment:
      PHP 5.1.2, SQL Server 2008, Any Browser

      Description

      Cross Joins are done using an approach like this:
      table_a, table_b
      but this generates an errore when there is also an inner join. This query, for example, is generated when trying to send a Mass Email to a group:

      SELECT u.user_email, u.username, u.username_clean, u.user_lang, u.user_jabber, u.user_notify_type FROM phpbb_users u, phpbb_user_group ug LEFT JOIN phpbb_banlist b ON (u.user_id = b.ban_userid) WHERE ug.group_id = 8 AND ug.user_pending = 0 AND u.user_id = ug.user_id AND u.user_allow_massemail = 1 AND u.user_type IN (0, 3) AND (b.ban_id IS NULL OR b.ban_exclude = 1) ORDER BY u.user_lang, u.user_notify_type

      and produces the following error:

      SQLSTATE: 42000 code: 4104 message: [Microsoft][SQL Server Native Client 10.0][SQL Server]The multi-part identifier "u.user_id" could not be bound. [4104]

      The problem can be solved using the "CROSS JOIN" keyword, instead of the comma, at row 612 of dbal.php file:

      $sql .= $this->_sql_custom_build('FROM', implode(' CROSS JOIN ', $table_array));

      After this change the query will be the following

      SELECT u.user_email, u.username, u.username_clean, u.user_lang, u.user_jabber, u.user_notify_type FROM phpbb_users u CROSS JOIN phpbb_user_group ug LEFT JOIN phpbb_banlist b ON (u.user_id = b.ban_userid) WHERE ug.group_id = 8 AND ug.user_pending = 0 AND u.user_id = ug.user_id AND u.user_allow_massemail = 1 AND u.user_type IN (0, 3) AND (b.ban_id IS NULL OR b.ban_exclude = 1) ORDER BY u.user_lang, u.user_notify_type

      and it will be correctly executed.

        Attachments

          Activity

            People

            • Assignee:
              nickvergessen Joas Schilling
              Reporter:
              dmauri dmauri
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: