Fix imsync's SELECT query to only find the rooms I'm actually joined in, not every room I have ever joined

1 files changed, 5 insertions, 2 deletions

diff --git a/synapse/storage/roommember.py b/synapse/storage/roommember.py
index ef73be4af4..c45d128f1b 100644
--- a/synapse/storage/roommember.py
+++ b/synapse/storage/roommember.py
@@ -111,9 +111,12 @@ class RoomMemberStore(SQLBaseStore):
         for membership in membership_list:
             args.append(membership)
 
+        # sub-select finds the row ID for the most recent (i.e. current)
+        # state change of this user per room, then the outer select finds those
         query = ("SELECT room_id, membership FROM room_memberships"
-                 + " WHERE user_id=? AND " + where_membership
-                 + " GROUP BY room_id ORDER BY id DESC")
+                 + " WHERE id IN (SELECT MAX(id) FROM room_memberships"
+                 + "   WHERE user_id=? GROUP BY room_id)"
+                 + " AND " + where_membership)
         return self._execute(
             self.cursor_to_dict, query, *args
         )