TYPO3 Core

Currently, the Extension Manager creates a virtual "position" field containing the relevance of a search hit. The relevance is calculated in a hacky way by casting the boolean of a LIKE (1=hit, 0=miss) into an integer by multiplying it with a specific number:

8 = exact match in extension_key
4 = partial match in extension_key
2 = partial match in title
1 = partial match in author_name

A query looks like this:
SELECT tx_extensionmanager_domain_model_extension.* ((extension_key like 'realurl') * 8 + (extension_key like '%realurl%') * 4 + (title like '%realurl%') * 2 + (author_name like '%realurl%')) as position FROM tx_extensionmanager_domain_model_extension WHERE (extension_key = 'realurl' OR extension_key LIKE '%realurl%' OR title LIKE '%realurl%' OR description LIKE '%realurl%' OR author_name LIKE '%realurl%') AND current_version = 1 AND review_state >= 0 HAVING position > 0"

When using DBAL, this crashes as hard as Wile E. Coyote.

BTW: Why is the keyword searched in "description" but does not take effect to the position?

Related: #60653