Doctrine 1
  1. Doctrine 1
  2. DC-896

MySQL Limit implementation leads to incorrect results if the original where clause contains an "OR" operator

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Query
    • Labels:
      None
    • Environment:
      Doctrine + MySQL

      Description

      If adding the Where In clause for the subquery "limit" implementation, the original where clause needs to be isolated with parenthesis. In my sample where clause the first character is in fact an opening parenthesis and the last character is in fact a closing parenthesis, however, there are a bunch of conditions and parenthesis between them as well that may contain "OR" operators. When this is combined with the Where In clause generated by the Pager tool's "limit" implementation for MySQL it leads to incorrect results.

      Here is my "Before" Where condition:

      WHERE c.id IN ('25', '31', '6', '28', '30', '16', '32', '26', '22', '14') AND (a2.data_item_id = ? AND a2.value = ?) AND (a3.data_item_id = ? AND a3.value = ?) OR (a4.data_item_id = ? AND a4.value = ?) AND (a5.data_item_id = ? AND a5.value = ?)

      And the "After" Where condition
      WHERE c.id IN ('25', '31', '6', '28', '30', '16', '32', '26', '22', '14') AND ((a2.data_item_id = ? AND a2.value = ?) AND (a3.data_item_id = ? AND a3.value = ?) OR (a4.data_item_id = ? AND a4.value = ?) AND (a5.data_item_id = ? AND a5.value = ?))

      Fix:

      Line 1305 in Query.php:
      $where = ($where == '' || (substr($where, 0, 1) === '(' && substr($where, -1) === ')'))
      ? $where : '(' . $where . ')';

      I ended up just adding the following just after the above statement in the code:
      if (!empty($limitSubquerySql) && !empty($where))

      { $where = '(' . $where . ')'; }

        Activity

        There are no comments yet on this issue.

          People

          • Assignee:
            Guilherme Blanco
            Reporter:
            James Pakele
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated: