rawMessageInfoFromRows seems to require subthread_permissions. The other queries that call this method use stm.permissions with LEFT JOIN memberships stm ON m.type = ${messageTypes.CREATE_SUB_THREAD} AND stm.thread = m.content AND stm.user = n.user.

I'm not particularly sure why, in this case, I'd need to left join on memberships when m.type = CREATE_SUB_THREAD, so I've just selected subtread_permissions as NULL.

I tried looking at the git history for any queries that do call this method, but they're mostly from several years ago, so if I'm missing something and I do need it, feel free to let me know

I noticed that, taking another look now, I seem to have missed parseMessageSQLResult that seems to be helpful. It looks like in functions that will be fetching multiple messages (fetchCollapsableNotifs, fetchMessageInfos, fetchMessageInfosSince, and fetchDerivedMessages) they each call parseMessageSQLResult and the logic is already there to handle multiple rows. Meanwhile, I'm seeing a warning now when passing the entire messageRows array directly to rawMessageInfoFromRows by assertSingleRow.

That's probably my first sign, now that I'm noticing that, that I'm doing it the wrong way. My new understanding is that in these cases, where we expect to have multiple rows, parseMessageSQLResult creates a map of {id: row}, and iterates over that and calls rawMessageInfoFromRows. I think MULTIMEDIA is a scenario where the key may have multiple rows, but besides that, each key should associate to one row.

My thinking is to update this diff and just call parseMessageSQLResult directly, and return the array of rawMessageInfos from the return type MessageSQLResult.

Something like this

This sounds right!! Yeah, multimedia uploads are the only case where you'll see multiple rows for the same ID. That's because you are LEFT JOINing the uploads table, which can match multiple rows for a single message

Oh my comment explaining my uncertainty disappeared between revisions: https://phab.comm.dev/D7110?id=23902#inline-46565

You definitely missed something. Can you try to do 30-60 minutes of more digging to try to figure out why the stm join exists, and whether we should be using it here?

Ok I've got some more understanding now. So when I create a subchannel, a message gets inserted into messages where the content is the thread ID of the new channel was created from. So in my original thread, 84449, when a new subchannel is created (id = 84910), a message is inserted into 84449 with content 84910.

Now when I retrieve all messages and left join for subthread permissions, that message will be the only message with non-null values

The only thing is, I don't think it's relevant here since in this query we query on pinned = 1, and the only messages that can be pinned are 1, 14 15 (text, image, multimedia). So the message type 3 will never be retrieved and the subthread_permissions will remain NULL for all of the rows, with or without the LEFT JOIN. That's why I thought we don't need the additional LEFT JOIN and we can just select NULL as subthread_permissions

Oh sorry a typo, the only messages that can be pinned are 0, 14, 15 (text, image, multimedia).

Good point, here we'd probably want to ORDER BY m.pin_time DESC to have the pinned messages from newest to oldest.

Sure I'll add a comment