Details
-
Type:
Bug
-
Status:
Open
-
Priority:
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))