#86859Search with indexed_search plugin throws exception: Prepared statement contains too many placeholders
Description
Searching with indexed_search for a term (word) with a large number of results throws exception:
An exception occurred while executing
'SELECT `ISEC`.*, `IP`.*, MAX(`IR`.`flags`) AS `order_val1`, SUM(`IR`.`freq`) AS `order_val2` FROM `index_phash` `IP`, `index_section` `ISEC`, `index_words` `IW`, `index_rel` `IR`
WHERE (`IP`.`phash` IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
.... etc.
)) AND (`IW`.`wid` = `IR`.`wid`) AND (`ISEC`.`phash` = `IR`.`phash`) AND (`IW`.`baseword` LIKE '%institut%') GROUP BY `IP`.`phash`, `ISEC`.`phash`, `ISEC`.`phash_t3`, `ISEC`.`rl0`, `ISEC`.`rl1`, `ISEC`.`rl2`, `ISEC`.`page_id`, `ISEC`.`uniqid`, `IP`.`phash_grouping`, `IP`.`data_filename`, `IP`.`data_page_id`, `IP`.`data_page_reg1`, `IP`.`data_page_type`, `IP`.`data_page_mp`, `IP`.`gr_list`, `IP`.`item_type`, `IP`.`item_title`, `IP`.`item_description`, `IP`.`item_mtime`, `IP`.`tstamp`, `IP`.`item_size`, `IP`.`contentHash`, `IP`.`crdate`, `IP`.`parsetime`, `IP`.`sys_language_uid`, `IP`.`item_crdate`, `IP`.`cHashParams`, `IP`.`externalUrl`, `IP`.`recordUid`, `IP`.`freeIndexUid`, `IP`.`freeIndexSetId` ORDER BY `order_val1` DESC, `order_val2` DESC' with params [754, 2499,
6477, 11827, 12442, 1
... etc.
Prepared statement contains too many placeholders
System environment
TYPO3 version 8.7.20
Steps to reproduce
- Get installation with a large number of pages / words.
- Search for a term that is common on those page.
(I could probably test to get the exact number, but I assume, someone knows this without tedious tests).
Exception
Exception is thrown in IndexSearchRepository::execFinalQuery when $queryBuilder->execute is called. There may be other occurances.
Best to generally search for expr()->in(
Comment
As indexed_search is not suited for large installations and this may not get fixed, I think at least the docs should name the restrictions specifically, e.g. what exactly are the limits, e.g. number of pages, number of words, if possible.
Reviews 4
- main 80081[BUGFIX] Restore accidently removed `in()` expression wrapper
- 11.5 80080[BUGFIX] Prevent reaching the placeholder limit in `indexed_search`
- 12.4 80057[BUGFIX] Prevent reaching the placeholder limit in `indexed_search`
- main 79913[BUGFIX] Prevent reaching the placeholder limit in `indexed_search`
Related issues 4
- Bug #80875Multiple problems with relation handler if too many records exist on a page
- Bug #92493 linkvalidator: scheduler task + list of broken links dies if too many pages ("Prepared statement contains too many placeholders")
- Task #93305Long lists of parameters in QueryBuilder handled differently ("too many placeholders")
- Epic #93547Collection of problems with large sites