Try to fix HTTP 500s on `/members` (#2581)

3 files changed, 13 insertions, 10 deletions

diff --git a/roomserver/storage/postgres/membership_table.go b/roomserver/storage/postgres/membership_table.go
index ee457393..eaaea22a 100644
--- a/roomserver/storage/postgres/membership_table.go
+++ b/roomserver/storage/postgres/membership_table.go
@@ -86,24 +86,24 @@ const insertMembershipSQL = "" +
 
 const selectMembershipFromRoomAndTargetSQL = "" +
 	"SELECT membership_nid, event_nid, forgotten FROM roomserver_membership" +
-	" WHERE room_nid = $1 AND target_nid = $2"
+	" WHERE room_nid = $1 AND event_nid != 0 AND target_nid = $2"
 
 const selectMembershipsFromRoomAndMembershipSQL = "" +
 	"SELECT event_nid FROM roomserver_membership" +
-	" WHERE room_nid = $1 AND membership_nid = $2 and forgotten = false"
+	" WHERE room_nid = $1 AND event_nid != 0 AND membership_nid = $2 and forgotten = false"
 
 const selectLocalMembershipsFromRoomAndMembershipSQL = "" +
 	"SELECT event_nid FROM roomserver_membership" +
-	" WHERE room_nid = $1 AND membership_nid = $2" +
+	" WHERE room_nid = $1 AND event_nid != 0 AND membership_nid = $2" +
 	" AND target_local = true and forgotten = false"
 
 const selectMembershipsFromRoomSQL = "" +
 	"SELECT event_nid FROM roomserver_membership" +
-	" WHERE room_nid = $1 and forgotten = false"
+	" WHERE room_nid = $1 AND event_nid != 0 and forgotten = false"
 
 const selectLocalMembershipsFromRoomSQL = "" +
 	"SELECT event_nid FROM roomserver_membership" +
-	" WHERE room_nid = $1" +
+	" WHERE room_nid = $1 AND event_nid != 0" +
 	" AND target_local = true and forgotten = false"
 
 const selectMembershipForUpdateSQL = "" +
diff --git a/roomserver/storage/sqlite3/membership_table.go b/roomserver/storage/sqlite3/membership_table.go
index 00a9bd4f..9dda82fb 100644
--- a/roomserver/storage/sqlite3/membership_table.go
+++ b/roomserver/storage/sqlite3/membership_table.go
@@ -62,24 +62,24 @@ const insertMembershipSQL = "" +
 
 const selectMembershipFromRoomAndTargetSQL = "" +
 	"SELECT membership_nid, event_nid, forgotten FROM roomserver_membership" +
-	" WHERE room_nid = $1 AND target_nid = $2"
+	" WHERE room_nid = $1 AND event_nid != 0 AND target_nid = $2"
 
 const selectMembershipsFromRoomAndMembershipSQL = "" +
 	"SELECT event_nid FROM roomserver_membership" +
-	" WHERE room_nid = $1 AND membership_nid = $2 and forgotten = false"
+	" WHERE room_nid = $1 AND event_nid != 0 AND membership_nid = $2 and forgotten = false"
 
 const selectLocalMembershipsFromRoomAndMembershipSQL = "" +
 	"SELECT event_nid FROM roomserver_membership" +
-	" WHERE room_nid = $1 AND membership_nid = $2" +
+	" WHERE room_nid = $1 AND event_nid != 0 AND membership_nid = $2" +
 	" AND target_local = true and forgotten = false"
 
 const selectMembershipsFromRoomSQL = "" +
 	"SELECT event_nid FROM roomserver_membership" +
-	" WHERE room_nid = $1 and forgotten = false"
+	" WHERE room_nid = $1 AND event_nid != 0 and forgotten = false"
 
 const selectLocalMembershipsFromRoomSQL = "" +
 	"SELECT event_nid FROM roomserver_membership" +
-	" WHERE room_nid = $1" +
+	" WHERE room_nid = $1 AND event_nid != 0" +
 	" AND target_local = true and forgotten = false"
 
 const selectMembershipForUpdateSQL = "" +
diff --git a/roomserver/storage/tables/membership_table_test.go b/roomserver/storage/tables/membership_table_test.go
index 14e8ce50..f789ef4a 100644
--- a/roomserver/storage/tables/membership_table_test.go
+++ b/roomserver/storage/tables/membership_table_test.go
@@ -60,6 +60,9 @@ func TestMembershipTable(t *testing.T) {
 			// This inserts a left user to the room
 			err = tab.InsertMembership(ctx, nil, 1, stateKeyNID, true)
 			assert.NoError(t, err)
+			// We must update the membership with a non-zero event NID or it will get filtered out in later queries
+			_, err = tab.UpdateMembership(ctx, nil, 1, stateKeyNID, userNIDs[0], tables.MembershipStateLeaveOrBan, 1, false)
+			assert.NoError(t, err)
 		}
 
 		// ... so this should be false