TYPO3 Core

i'm having the same problem. it seems, that the DBAL sql parser does not like the "position" construct.

but i've also found an additional problem with that select-statement:

the problematic sql-statement is built in the funtction ExtensionRepository->findByTitleOrAuthorNameOrExtensionKey in the file "ExtensinoRepository.php" and 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
ORDER BY position desc

apart from the sql-parser problem, this statement has two problems on postgres:

- to determine the relevance of an extension for a given search-request, the select-statement tries to multiply boolian values (e.g. "extension_key like ' . $quotedSearchString . '") with integers. since mysql uses tinyint as boolean values, this works in mysql. but postgres uses real boolean values and throws an error. as a solution i would recommend using CASE-statements for this. e.g. by using this construct to calculate the position:

    (
        (CASE WHEN (extension_key like ' . $quotedSearchString . ') THEN 8 ELSE 0 END) +
        (CASE WHEN (extension_key like ' . $quotedSearchStringForLike . ') THEN 8 ELSE 0 END) +
        (CASE WHEN (title like ' . $quotedSearchStringForLike . ') THEN 2 ELSE 0 END) +
        (CASE WHEN (author_name like ' . $quotedSearchStringForLike . ') THEN 1 ELSE 0 END)
    )

this statement works both on mysql and postgres, but unfortunately the dbal sql-parser again does not seem to like it and throws this error:
"SQL engine parse ERROR: No field name found as expected in parseFieldList(): near "( (case when "

so for this solution to work, the sql-parser should also be adapted.

- the second problem is using a HAVING-clause ("HAVING position > 0") with no GROUP BY clause, which is not allowed with postgres. a solution would be integrating this into the WHERE-clause instead.

fixing these two problems results in the sql-statement looking like this:

SELECT     tx_extensionmanager_domain_model_extension.*,
        (
            (CASE WHEN extension_key like 'realurl' THEN 8 ELSE 0 END) +
            (CASE WHEN extension_key like '%realurl%' THEN 4 ELSE 0 END) +
            (CASE WHEN title like '%realurl%' THEN 2 ELSE 0 END) +
            (CASE WHEN author_name like '%realurl%' THEN 1 ELSE 0 END)
        ) 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
        AND (
                (CASE WHEN extension_key like 'realurl' THEN 8 ELSE 0 END) +
                (CASE WHEN extension_key like '%realurl%' THEN 4 ELSE 0 END) +
                (CASE WHEN title like '%realurl%' THEN 2 ELSE 0 END) +
                (CASE WHEN author_name like '%realurl%' THEN 1 ELSE 0 END)
            ) > 0
ORDER BY position desc

this statement works on postgres and should also work on mysql - given that the DBAL SQL engine parser accepts it.