Uploaded image for project: 'phpBB'
  1. phpBB
  2. PHPBB-10296

incorrect cross join in SQL Server

XMLWordPrintable

      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.

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

              Created:
              Updated:
              Resolved: