Searching for 10k+ results in API gives an Error [SQLITE_TOOBIG]

[MoreDinosaurs]

Steps to reproduce

I perform an API search for a term that is common to the book titles in my library (in this case CVDB)
http://xxx.xxx.xxx.xxx:2202/api/v1/books?search=CVDB&size=50

Expected behavior

The expected behavior would be a normal API search result like this but with a totalElements that is very large.

{"content":[{},{},{}.......{}],
"pageable":{"sort":{"empty":false,"unsorted":false,"sorted":true},"offset":0,"pageSize":50,"pageNumber":0,"unpaged":false,"paged":true},"totalPages":198,"totalElements":9893,"last":false,"size":50,"number":0,"sort":{"empty":false,"unsorted":false,"sorted":true},"numberOfElements":50,"first":true,"empty":false}

Actual behavior

Rather than a clean response I get an error:
Whitelabel Error Page
This application has no explicit mapping for /error, so you are seeing this as a fallback.

Fri Aug 05 12:42:02 PDT 2022
There was an unexpected error (type=Internal Server Error, status=500).
SQL [select BOOK.ID, BOOK.CREATED_DATE, BOOK.LAST_MODIFIED_DATE, BOOK.FILE_LAST_MODIFIED, BOOK.NAME, BOOK.URL, BOOK.SERIES_ID, BOOK.FILE_SIZE, BOOK.NUMBER, BOOK.LIBRARY_ID, BOOK.FILE_HASH, BOOK.DELETED_DATE, MEDIA.MEDIA_TYPE, MEDIA.STATUS, MEDIA.CREATED_DATE, MEDIA.LAST_MODIFIED_DATE, MEDIA.COMMENT, MEDIA.BOOK_ID, MEDIA.PAGE_COUNT, BOOK_METADATA.CREATED_DATE, BOOK_METADATA.LAST_MODIFIED_DATE, BOOK_METADATA.NUMBER, BOOK_METADATA.NUMBER_LOCK, BOOK_METADATA.NUMBER_SORT, BOOK_METADATA.NUMBER_SORT_LOCK, BOOK_METADATA.RELEASE_DATE, BOOK_METADATA.RELEASE_DATE_LOCK, BOOK_METADATA.SUMMARY, BOOK_METADATA.SUMMARY_LOCK, BOOK_METADATA.TITLE, BOOK_METADATA.TITLE_LOCK, BOOK_METADATA.AUTHORS_LOCK, BOOK_METADATA.TAGS_LOCK, BOOK_METADATA.BOOK_ID, BOOK_METADATA.ISBN, BOOK_METADATA.ISBN_LOCK, BOOK_METADATA.LINKS_LOCK, READ_PROGRESS.BOOK_ID, READ_PROGRESS.USER_ID, READ_PROGRESS.CREATED_DATE, READ_PROGRESS.LAST_MODIFIED_DATE, READ_PROGRESS.PAGE, READ_PROGRESS.COMPLETED, READ_PROGRESS.READ_DATE, SERIES_METADATA.TITLE from BOOK left outer join MEDIA on BOOK.ID = MEDIA.BOOK_ID left outer join BOOK_METADATA on BOOK.ID = BOOK_METADATA.BOOK_ID left outer join READ_PROGRESS on (BOOK.ID = READ_PROGRESS.BOOK_ID and READ_PROGRESS.USER_ID = '08AD8X3MG114R') left outer join SERIES_METADATA on BOOK.SERIES_ID = SERIES_METADATA.SERIES_ID where BOOK.ID in ('09GVFV7X4BF3C', '09GVFV7XRBDKW', '09GVFV85WB41E', '09GVFV85WB41J', '09GVFV86GBFKS', '09GVFV878BET3', '09GVFV878BET4', '09GVFV87CB6EP', '09GVFV87CB6EQ', '09GVFV87CB6ER', '09GVFV87CB6ES', '09GVFV87CB6ET', '09GVFV87CB6EV', '09GVFV87CB6EW', '09GVFV87CB6EX', '09GVFV87CB6F0', '09GVFV87CB6F1', '09GVFV87CB6F2', '09GVFV87CB6F3', '09GVFV87CB6F4', '09GVFV87CB6F5', '09GVFV87GBC2K', '09GVFV87GBC2M', '09GVFV87GBC2N', '09GVFV87GBC2P', '09GVFV87GBC2Q', '09GVFVA50B8A9', '09GVFVA50B8AG', '09GVFVA54B4R5', '09GVFVABCB9ZT', '09GVFVABCB9ZV', '09GVFVABWB8P8', '09GVFVAC4B5CW', '09GVFVAC4B5D0', '09GVFVAC4B5D6', '09GVFVAC8B6YM', '09GVFVAC8B6YP', '09GVFVAC8B6YR', '09GVFVAC8B6YS', '09GVFVAC8B6YV', '09GVFVAC8B6YX', '09GVFVAC8B6YZ', '09GVFVAC8B6Z0', '09GVFVAC8B6Z1', '09GVFVAC8B6Z3', '09GVFVAC8B6Z4', '09GVFVACCBCZF', '09GVFVAC8B6Z2', '09GVFVACCBCZP', '09GVFVACCBCZQ', '09GVFVACCBCZT', '09GVFVACCBCZV', '09GVFVACCBCZW', '09GVFVACGBE4Z', '09GVFVAJ8BD90', '09GVFVAJ8BD91', '09GVFVAJ8BD93', '09GVFVAJ8BD94', '09GVFVAJ8BD96', '09GVFVAJ8BD97', '09GVFVAJ8BD99', '09GVFVAJCBBGS', '09GVFVAJCBBGV', '09GVFVAJCBBH2', '09GVFVAJCBBH3', '09GVFVB8GBFBM', '09BA0QEZ75MQX', '09BA0QEZB5THD', '09BA0QEZB5THE', '09BA0QEZB5THF', '09BA0QEZB5THG', '09BA0QEZB5THH', '09BA0QEZB5THJ', '09BA0QEZB5THK', '09BA0QEZB5THQ', '09BA0QEZF5YSK', '09BA0QEZK5XWG', '09BA0QEZK5XWH', '09BA0QEZZ5J3H', '09BA0QF035NZT', '09BA0QF035NZW', '09BA0QF035NZY', '09BA0QF075SG9', 
......
the error message goes on for roughly 10k entries
and then ends with:
......
VFVAW4BAT8' then 23650 when '09GVFVAW4BAT9' then 23651 when '09GVFVAW4BATA' then 23652 when '09GVFVAW4BATB' then 23653 when '09GVFV6GCB71Y' then 23654 when '09H0CVW15G9KN' then 23655 when '09FRY6NF4B79V' then 23656 when '09CXX0H5A5ZNE' then 23657 when '09CXX0H5E5VET' then 23658 when '09CXX106P5M06' then 23659 else 2147483647 end limit 50 offset 0]; [SQLITE_TOOBIG] String or BLOB exceeds size limit (statement too long)

Logs

Log file captures an SQL request that is way too long to post in here. Full SQL request is in the atttached log file.

2022-08-05 12:09:55.092 ERROR 6120 --- [http-nio-2202-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.jooq.exception.DataAccessException: SQL [select BOOK.ID, BOOK.CREATED_DATE, BOOK.LAST_MODIFIED_DATE, BOOK.FILE_LAST_MODIFIED, BOOK.NAME, BOOK.URL, BOOK.SERIES_ID, BOOK.FILE_SIZE, BOOK.NUMBER, BOOK.LIBRARY_ID, BOOK.FILE_HASH, BOOK.DELETED_DATE, MEDIA.MEDIA_TYPE, MEDIA.STATUS, MEDIA.CREATED_DATE, MEDIA.LAST_MODIFIED_DATE, MEDIA.COMMENT, MEDIA.BOOK_ID, MEDIA.PAGE_COUNT, BOOK_METADATA.CREATED_DATE, BOOK_METADATA.LAST_MODIFIED_DATE, BOOK_METADATA.NUMBER, BOOK_METADATA.NUMBER_LOCK, BOOK_METADATA.NUMBER_SORT, BOOK_METADATA.NUMBER_SORT_LOCK, BOOK_METADATA.RELEASE_DATE, BOOK_METADATA.RELEASE_DATE_LOCK, BOOK_METADATA.SUMMARY, BOOK_METADATA.SUMMARY_LOCK, BOOK_METADATA.TITLE, BOOK_METADATA.TITLE_LOCK, BOOK_METADATA.AUTHORS_LOCK, BOOK_METADATA.TAGS_LOCK, BOOK_METADATA.BOOK_ID, BOOK_METADATA.ISBN, BOOK_METADATA.ISBN_LOCK, BOOK_METADATA.LINKS_LOCK, READ_PROGRESS.BOOK_ID, READ_PROGRESS.USER_ID, READ_PROGRESS.CREATED_DATE, READ_PROGRESS.LAST_MODIFIED_DATE, READ_PROGRESS.PAGE, READ_PROGRESS.COMPLETED, READ_PROGRESS.READ_DATE, SERIES_METADATA.TITLE from BOOK left outer join MEDIA on BOOK.ID = MEDIA.BOOK_ID left outer join BOOK_METADATA on BOOK.ID = BOOK_METADATA.BOOK_ID left outer join READ_PROGRESS on (BOOK.ID = READ_PROGRESS.BOOK_ID and READ_PROGRESS.USER_ID = '08AD8X3MG114R') left outer join SERIES_METADATA on BOOK.SERIES_ID = SERIES_METADATA.SERIES_ID where BOOK.ID in ('09GVFV7X4BF3C', '09GVFV7XRBDKW', '09GVFV85WB41E', '09GVFV85WB41J', '09GVFV86GBFKS', '09GVFV878BET3', '09GVFV878BET4', '09GVFV87CB6EP', '09GVFV87CB6EQ', '09GVFV87CB6ER', '09GVFV87CB6ES', '09GVFV87CB6ET', '09GVFV87CB6EV', '09GVFV87CB6EW', '09GVFV87CB6EX', '0

komga.log

Komga version

v0.157.0-master

Operating system

Windows 10

Other details

No response

Acknowledgements

• I have searched the existing issues and this is a new ticket, NOT a duplicate or related to another open issue.
• I have written a short but informative title.
• I have checked the FAQ.
• I have updated the app to the latest version.
• I will fill out all of the requested information in this form.

[gotson]

@MoreDinosaurs would you be able to share your Komga database with me via a Discord PM ?

[github-actions]

🎉 This issue has been resolved in version 0.157.1 🎉

The release is available on:

• v0.157.1
• GitHub release

Your semantic-release bot 📦🚀