Jobs list API is throwing 500 on connection without any jobs

[flash1293]

Calling the v1/jobs/list API with an unknown configId in the payload is returning a 500 status code with the following error:

{
    "message": "Internal Server Error: SQL [SELECT atmpt.attempt_number, atmpt.job_id,stats.estimated_bytes, stats.estimated_records, stats.bytes_emitted, stats.records_emitted FROM sync_stats stats INNER JOIN attempts atmpt ON stats.attempt_id = atmpt.id WHERE job_id IN ( );]; ERROR: syntax error at or near \")\"\n  Position: 231",
    "exceptionClassName": "org.jooq.exception.DataAccessException",
    "exceptionStack": [
        "org.jooq.exception.DataAccessException: SQL [SELECT atmpt.attempt_number, atmpt.job_id,stats.estimated_bytes, stats.estimated_records, stats.bytes_emitted, stats.records_emitted FROM sync_stats stats INNER JOIN attempts atmpt ON stats.attempt_id = atmpt.id WHERE job_id IN ( );]; ERROR: syntax error at or near \")\"",
        "  Position: 231",
        "\tat org.jooq_3.13.4.POSTGRES.debug(Unknown Source)",
        "\tat org.jooq.impl.Tools.translate(Tools.java:2753)",
        "\tat org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:755)",
// SNAP
        "\tat org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:938)",
        "\tat java.base/java.lang.Thread.run(Thread.java:1589)"
    ]
}

It was most likely introduced by #20937

This is destabilizing the frontend E2E tests which click through different screens very quickly and might hit the connection default page before it's fully set up.

Before that PR, the behavior of the API was to simply return an empty job list in a 200 reponse so the UI worked fine. Possible fixes:

• Restore the old behavior of returning an empty job list on unknown config id
• Return a 404 or something and have the frontend handle it more gracefully than showing an error screen

[flash1293]

cc @davinchia @alafanechere

[flash1293]

This is not just because the E2E tests are very fast, it always fails if there are no jobs - this also happens if a connection is created with a manual schedule.

[davinchia]

Closing this as we reverted the offending PR. Thanks for bringing this to my attention @flash1293 !