Details
-
Type: Patch
-
Status: Done/Fixed
-
Priority: Minor
-
Resolution: Fixed/Completed
-
Affects Version/s: 4.3.3
-
Fix Version/s: 4.4.0
-
Component/s: Core CiviCRM
-
Labels:
Description
I found that my activity results in contacts tab were out of order, in fact whatever order I clicked on was ignored. Looking at the code in CRM/Activity/BAO/Activity.php I can see why.
The current way was to create a temporary table from a query that properly sorts the results, and later to join that table with others to create the output. The problem with this is that it makes assumptions that the rows in the final query are in the same order as inserted into the temporary table. However, SQL makes no commitment to this so any optimisations done by the engine can result in the rows being out of order.
I imagine that some versions of MySQL happen to produce sorted results while others don't. I have moved to MariaDB, and this may have been the difference. Anyway, this still affects MySQL and any other engines in SQL theory.
This patch locks in the original sort order by adding an auto increment primary key to the first temporary table, and then adding an ORDER BY on this key to the final query. The activityId was previously used as a primary key, and while I'm not sure a unique key on this is important (the other queries will only return one row per activityId anyway), I've maintained this in the patch by adding a UNIQUE KEY to it.