The current reparenting approach sometimes doesn't work. This commit
fixes three issues in the implementation:

1. If an email is being reparented that was previously the root of a
   thread, then it's `thread_id` is `None`. In this case, add its `id`
   to the list of thread IDs to update.
2. The update statement's where clause uses an `in` expressions, which
   is evaluated by Python immediately. Instead, use the `.in_` function.
3. The update statement is not tied to the session and never gets
   executed. Explicitly `execute()` it.
---
Note: I have little experience with SQLAlchemy. Especially with regards
to the third point, this was the only way I could get it to work, but I
might be missing something.

 listssrht/process.py | 9 ++++++---
 1 file changed, 6 insertions(+), 3 deletions(-)

diff --git a/listssrht/process.py b/listssrht/process.py
index 6223c0c..1653005 100644
--- a/listssrht/process.py
+++ b/listssrht/process.py
@@ -264,10 +264,13 @@ def _archive(dest, envelope, do_webhooks=True):
     for child in children:
         child.parent_id = mail.id
         if child.thread_id != thread.id:
-            ex_threads.update({ child.thread_id })
+            ex_thread_id = child.thread_id if child.thread_id else child.id
+            ex_threads.update({ ex_thread_id })
         child.thread_id = thread.id
-    (Email.__table__.update().where(Email.thread_id in ex_threads)
-            .values(thread_id=thread.id))
+    if len(ex_threads) > 0:
+        db.session.execute(Email.__table__.update()
+                .where(Email.thread_id.in_(ex_threads))
+                .values(thread_id=thread.id))
 
     db.session.flush()
     # Update thread nreplies & nparticipants
-- 
2.36.1

lists.sr.ht/patches: SUCCESS in 5m43s

[worker: fix reparenting for out-of-order emails][0] from [Conrad Hoffmann][1]

[0]: https://lists.sr.ht/~sircmpwn/sr.ht-dev/patches/33251
[1]: ch@bitfehler.net

✓ #786893 SUCCESS lists.sr.ht/patches/alpine.yml    https://builds.sr.ht/~sircmpwn/job/786893
✓ #786895 SUCCESS lists.sr.ht/patches/debian.yml    https://builds.sr.ht/~sircmpwn/job/786895
✓ #786894 SUCCESS lists.sr.ht/patches/archlinux.yml https://builds.sr.ht/~sircmpwn/job/786894

Just saw that this got applied, thanks.

FWIW, I managed to construct the following SQL query, which should fix 
the already broken instances in the database:

UPDATE email SET thread_id = parent.thread_id FROM email parent WHERE 
email.parent_id = parent.id AND parent.thread_id IS NOT NULL AND 
email.thread_id != parent.thread_id;

This is the corresponding SELECT statement showing the records that 
would be corrected:

SELECT email.id, email.thread_id, email.parent_id, parent.thread_id AS 
parent_thread_id FROM email, email parent WHERE email.parent_id = 
parent.id AND parent.thread_id IS NOT NULL AND email.thread_id != 
parent.thread_id;

Is is this something you could just run as a one-off?

Done.