Page MenuHomePhabricator
Files F3550494

D4675.diff
No OneTemporary
Actions

D4675.diff
View Options

diff --git a/keyserver/src/fetchers/message-fetchers.js b/keyserver/src/fetchers/message-fetchers.js
--- a/keyserver/src/fetchers/message-fetchers.js
+++ b/keyserver/src/fetchers/message-fetchers.js
@@ -275,24 +275,60 @@
const truncationStatuses = {};
const viewerID = viewer.id;
- const query = SQL`
- SELECT * FROM (
- SELECT x.id, x.content, x.time, x.type, x.user AS creatorID,
- x.creation, x.subthread_permissions, x.uploadID, x.uploadType,
- x.uploadSecret, x.uploadExtra,
- @num := if(
- @thread = x.thread,
- if(@message = x.id, @num, @num + 1),
- 1
- ) AS number,
- @message := x.id AS messageID,
- @thread := x.thread AS threadID
- FROM (SELECT @num := 0, @thread := '', @message := '') init
- JOIN (
- SELECT m.id, m.thread, m.user, m.content, m.time, m.type,
- m.creation, stm.permissions AS subthread_permissions,
+ const dbType = await getDBType();
+
+ let query;
+ if (dbType === 'mysql5.7') {
+ query = SQL`
+ SELECT * FROM (
+ SELECT x.id, x.content, x.time, x.type, x.user AS creatorID,
+ x.creation, x.subthread_permissions, x.uploadID, x.uploadType,
+ x.uploadSecret, x.uploadExtra,
+ @num := if(
+ @thread = x.thread,
+ if(@message = x.id, @num, @num + 1),
+ 1
+ ) AS number,
+ @message := x.id AS messageID,
+ @thread := x.thread AS threadID
+ FROM (SELECT @num := 0, @thread := '', @message := '') init
+ JOIN (
+ SELECT m.id, m.thread, m.user, m.content, m.time, m.type,
+ m.creation, stm.permissions AS subthread_permissions,
+ up.id AS uploadID, up.type AS uploadType, up.secret AS uploadSecret,
+ up.extra AS uploadExtra
+ FROM messages m
+ LEFT JOIN uploads up ON up.container = m.id
+ LEFT JOIN memberships mm
+ ON mm.thread = m.thread AND mm.user = ${viewerID}
+ LEFT JOIN memberships stm ON m.type = ${messageTypes.CREATE_SUB_THREAD}
+ AND stm.thread = m.content AND stm.user = ${viewerID}
+ WHERE JSON_EXTRACT(mm.permissions, ${visibleExtractString}) IS TRUE AND
+ `;
+ query.append(selectionClause);
+ // - We specify a ridiculously high LIMIT here to force MariaDB to maintain
+ // the ORDER BY. Otherwise the query optimizer will assume that the order
+ // doesn't matter because the outer query doesn't have an ORDER BY.
+ // - Setting an ORDER BY on the outer query won't work because our quirky
+ // counting logic in the SELECT would be executed before the ORDER, on the
+ // unsorted results from the inner query.
+ // - More details available here:
+ // https://mariadb.com/kb/en/why-is-order-by-in-a-from-subquery-ignored/
+ query.append(SQL`
+ ORDER BY m.thread, m.time DESC
+ LIMIT 18446744073709551615
+ ) x
+ ) y
+ WHERE y.number <= ${numberPerThread}
+ `);
+ } else {
+ query = SQL`
+ WITH thread_window AS (
+ SELECT m.id, m.thread AS threadID, m.user AS creatorID, m.content,
+ m.time, m.type, m.creation, stm.permissions AS subthread_permissions,
up.id AS uploadID, up.type AS uploadType, up.secret AS uploadSecret,
- up.extra AS uploadExtra
+ up.extra AS uploadExtra,
+ ROW_NUMBER() OVER (PARTITION BY threadID ORDER BY time DESC) n
FROM messages m
LEFT JOIN uploads up ON up.container = m.id
LEFT JOIN memberships mm
@@ -300,23 +336,14 @@
LEFT JOIN memberships stm ON m.type = ${messageTypes.CREATE_SUB_THREAD}
AND stm.thread = m.content AND stm.user = ${viewerID}
WHERE JSON_EXTRACT(mm.permissions, ${visibleExtractString}) IS TRUE AND
- `;
- query.append(selectionClause);
- // - We specify a ridiculously high LIMIT here to force MariaDB to maintain
- // the ORDER BY. Otherwise the query optimizer will assume that the order
- // doesn't matter because the outer query doesn't have an ORDER BY.
- // - Setting an ORDER BY on the outer query won't work because our quirky
- // counting logic in the SELECT would be executed before the ORDER, on the
- // unsorted results from the inner query.
- // - More details available here:
- // https://mariadb.com/kb/en/why-is-order-by-in-a-from-subquery-ignored/
- query.append(SQL`
- ORDER BY m.thread, m.time DESC
- LIMIT 18446744073709551615
- ) x
- ) y
- WHERE y.number <= ${numberPerThread}
- `);
+ `;
+ query.append(selectionClause);
+ query.append(SQL`
+ )
+ SELECT * FROM thread_window WHERE n <= ${numberPerThread};
+ `);
+ }
+
const [result] = await dbQuery(query);
const derivedMessages = await fetchDerivedMessages(result, viewer);
const messages = await parseMessageSQLResult(result, derivedMessages, viewer);

File Metadata

Mime Type
text/plain
Expires
Fri, Dec 27, 6:15 PM (8 h, 7 m)
Storage Engine
blob
Storage Format
Raw Data
Storage Handle
2717860
Default Alt Text
D4675.diff (4 KB)

Event Timeline