fix: ignore case while sorting usernames (coder#7870)

mtojek · web-flow · commit ee45b3df77f4 · 2023-06-06T12:37:41.000+02:00
diff --git a/coderd/database/dbfake/databasefake.go b/coderd/database/dbfake/databasefake.go
@@ -933,7 +933,7 @@ func (q *fakeQuerier) GetUsers(_ context.Context, params database.GetUsersParams
 
 	// Database orders by username
 	slices.SortFunc(users, func(a, b database.User) bool {
-		return a.Username < b.Username
+		return strings.ToLower(a.Username) < strings.ToLower(b.Username)
 	})
 
 	// Filter out deleted since they should never be returned..
diff --git a/coderd/database/queries.sql.go b/coderd/database/queries.sql.go
diff --git a/coderd/database/queries/users.sql b/coderd/database/queries/users.sql
@@ -145,9 +145,9 @@ WHERE
 			-- The pagination cursor is the last ID of the previous page.
 			-- The query is ordered by the username field, so select all
 			-- rows after the cursor.
-			(username) > (
+			(LOWER(username)) > (
 				SELECT
-					username
+					LOWER(username)
 				FROM
 					users
 				WHERE
@@ -184,7 +184,7 @@ WHERE
 	-- End of filters
 ORDER BY
 	-- Deterministic and consistent ordering of all users. This is to ensure consistent pagination.
-	username ASC OFFSET @offset_opt
+	LOWER(username) ASC OFFSET @offset_opt
 LIMIT
 	-- A null limit means "no limit", so 0 means return all
 	NULLIF(@limit_opt :: int, 0);
diff --git a/coderd/users_test.go b/coderd/users_test.go
@@ -1553,6 +1553,9 @@ func TestPaginatedUsers(t *testing.T) {
 				email = fmt.Sprintf("%d@gmail.com", i)
 				username = fmt.Sprintf("specialuser%d", i)
 			}
+			if i%3 == 0 {
+				username = strings.ToUpper(username)
+			}
 			// One side effect of having to use the api vs the db calls directly, is you cannot
 			// mock time. Ideally I could pass in mocked times and space these users out.
 			//
@@ -1694,7 +1697,7 @@ func assertPagination(ctx context.Context, t *testing.T, client *codersdk.Client
 // sortUsers sorts by (created_at, id)
 func sortUsers(users []codersdk.User) {
 	sort.Slice(users, func(i, j int) bool {
-		return users[i].Username < users[j].Username
+		return strings.ToLower(users[i].Username) < strings.ToLower(users[j].Username)
 	})
 }
 
