Ghost Admin errors on post list since version 5.21.0

Issue Summary

Since updating to Ghost version 5.22.10 the following error message popped up when opening any of the admin post lists/views:
Request not understood error, cannot list posts. Could not understand request.

• Testing a bit around and downgrading showed, that the first affected version is 5.21.0.

• Version 5.22.0 through 5.22.10 are also affected.

• Version 5.20.0 is the last unaffected one.

Steps to Reproduce

1. Install Ghost version 5.22.10

   • Either through an update or a clean install.

   • It doesn't matter if there are already posts created or not.

2. Open the Ghost admin backend

3. Open one of the post lists/views.

Relevant log / error output

Browser HTTP Request

URL

Browser HTTP Request URL:

/ghost/api/admin/posts/?formats=mobiledoc%2Clexical&limit=30&page=1&filter=status%3A%5Bdraft%2Cscheduled%2Cpublished%2Csent%5D

Response

Browser HTTP Request Response:

{
  "errors": [
    {
      "message": "Request not understood error, cannot list posts.",
      "context": "Could not understand request.",
      "type": "BadRequestError",
      "details": null,
      "property": null,
      "help": null,
      "code": "ER_BAD_FIELD_ERROR",
      "id": "c1801070-6273-11ed-8e21-c102c3d5082c",
      "ghostErrorCode": null
    }
  ]
}

Server

Server/Backend log entry:

{
  "name": "Log",
  "hostname": "**REDACTED**",
  "pid": **REDACTED**,
  "level": 50,
  "req": {
    "meta": {
      "requestId": "88c4655a-1137-40fe-b3e4-9c2c85456430",
      "userId": "1"
    },
    "url": "/posts/?formats=mobiledoc%2Clexical&limit=30&page=1&filter=status%3A%5Bdraft%2Cscheduled%2Cpublished%2Csent%5D",
    "method": "GET",
    "originalUrl": "/ghost/api/admin/posts/?formats=mobiledoc%2Clexical&limit=30&page=1&filter=status%3A%5Bdraft%2Cscheduled%2Cpublished%2Csent%5D",
    "params": {},
    "headers": {
      "host": "**REDACTED**",
      "sec-ch-ua": "\"Google Chrome\";v=\"107\", \"Chromium\";v=\"107\", \"Not=A?Brand\";v=\"24\"",
      "dnt": "1",
      "sec-ch-ua-mobile": "?0",
      "app-pragma": "no-cache",
      "user-agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/107.0.0.0 Safari/537.36",
      "content-type": "application/json; charset=UTF-8",
      "accept": "application/json, text/javascript, */*; q=0.01",
      "x-requested-with": "XMLHttpRequest",
      "x-ghost-version": "5.22",
      "sec-ch-ua-platform": "\"macOS\"",
      "sec-fetch-site": "same-origin",
      "sec-fetch-mode": "cors",
      "sec-fetch-dest": "empty",
      "referer": "https://**REDACTED**/ghost/",
      "accept-encoding": "gzip, deflate, br",
      "accept-language": "de,de-DE;q=0.9,en-GB;q=0.8,en;q=0.7,en-US;q=0.6,en-DE;q=0.5",
      "cookie": "**REDACTED**",
      "sec-gpc": "1",
      "x-forwarded-proto": "https",
      "x-forwarded-for": "**REDACTED**",
      "x-forwarded-host": "**REDACTED**",
      "x-forwarded-server": "**REDACTED**",
      "connection": "close"
    },
    "query": {
      "formats": "mobiledoc,lexical",
      "limit": "30",
      "page": "1",
      "filter": "status:[draft,scheduled,published,sent]"
    }
  },
  "res": {
    "_headers": {
      "x-powered-by": "Express",
      "vary": "Accept-Version, Accept-Encoding",
      "cache-control": "no-cache, private, no-store, must-revalidate, max-stale=0, post-check=0, pre-check=0",
      "content-type": "application/json; charset=utf-8",
      "content-length": "279",
      "etag": "W/\"117-g2i2/Ll57qIbCQdZFnvScK7Hl3c\""
    },
    "statusCode": 400,
    "responseTime": "30ms"
  },
  "err": {
    "id": "c1801070-6273-11ed-8e21-c102c3d5082c",
    "domain": "https://**REDACTED**/",
    "code": "ER_BAD_FIELD_ERROR",
    "name": "BadRequestError",
    "statusCode": 400,
    "level": "normal",
    "message": "Could not understand request.",
    "stack": "Error: select `posts`.*, (with `k` as (select `member_id` from `members_subscription_created_events` where posts.id = members_subscription_created_events.attribution_id union select `member_id` from `members_created_events` where posts.id = members_created_events.attribution_id) select count(*) from `k`) as `count__conversions`, `posts`.*, (select count(distinct `members_click_events`.`member_id`) from `members_click_events` inner join `redirects` on `members_click_events`.`redirect_id` = `redirects`.`id` where posts.id = redirects.post_id) as `count__clicks`, `posts`.*, (select COALESCE(ROUND(AVG(score) * 100), 0) from `members_feedback` where posts.id = members_feedback.post_id) as `count__sentiment`, `posts`.*, (select count(*) from `members_feedback` where posts.id = members_feedback.post_id AND members_feedback.score = 0) as `count__negative_feedback`, `posts`.*, (select sum(`score`) from `members_feedback` where posts.id = members_feedback.post_id) as `count__positive_feedback` from `posts` where (`posts`.`status` in ('draft', 'scheduled', 'published', 'sent') and `posts`.`type` = 'post') order by CASE WHEN posts.status = 'scheduled' THEN 1 WHEN posts.status = 'draft' THEN 2 ELSE 3 END ASC,CASE WHEN posts.status != 'draft' THEN posts.published_at END DESC,posts.updated_at DESC,posts.id DESC limit 30 - Unknown column 'posts.id' in 'where clause'\n    at **REDACTED**/versions/5.22.10/node_modules/@tryghost/bookshelf-pagination/lib/bookshelf-pagination.js:259:27\n    at Packet.asError (**REDACTED**/versions/5.22.10/node_modules/mysql2/lib/packets/packet.js:728:17)\n    at Query.execute (**REDACTED**/versions/5.22.10/node_modules/mysql2/lib/commands/command.js:29:26)\n    at Connection.handlePacket (**REDACTED**/versions/5.22.10/node_modules/mysql2/lib/connection.js:456:32)\n    at PacketParser.onPacket (**REDACTED**/versions/5.22.10/node_modules/mysql2/lib/connection.js:85:12)\n    at PacketParser.executeStart (**REDACTED**/versions/5.22.10/node_modules/mysql2/lib/packet_parser.js:75:16)\n    at Socket.<anonymous> (**REDACTED**/versions/5.22.10/node_modules/mysql2/lib/connection.js:92:25)\n    at Socket.emit (node:events:513:28)\n    at addChunk (node:internal/streams/readable:315:12)\n    at readableAddChunk (node:internal/streams/readable:289:9)\n    at Socket.Readable.push (node:internal/streams/readable:228:10)\n    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)",
    "hideStack": false
  },
  "msg": "Could not understand request.",
  "time": "2022-11-12T10:21:22.564Z",
  "v": 0
}

GitHub issue:

Workarounds, tips and hints