Bug #44711
closedSQL: No field name found as expected in parseFieldList()
0%
Description
I'm running a blank installation of TYPO3 on a PostgreSQL-database (version 8.4.13).
When clicking on 'Get extensions' in the extension manager, entering something in the search field and pressing 'Submit' I get this error:
SQL engine parse ERROR: No field name found as expected in parseFieldList(): near "( (extension_key like 'blabla') * 8 + (ext"
Updated by Gerald Buttinger over 11 years ago
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.
Updated by Oliver Hader over 11 years ago
- Project changed from 2269 to TYPO3 Core
Updated by Mathias Schreiber over 9 years ago
- Target version set to 7.3 (Packages)
- Is Regression set to No
Updated by Benni Mack about 9 years ago
- Target version changed from 7.3 (Packages) to 7.4 (Backend)
Updated by Susanne Moog about 9 years ago
- Target version changed from 7.4 (Backend) to 7.5
Updated by Susanne Moog about 9 years ago
- Category changed from Extension Manager to 999
Updated by Morton Jonuschat about 9 years ago
- Status changed from New to Resolved
Issue has been resolved through #61204, applied in commit 4f78570845f5c97d739c25b4d288cc6776cfd262
Updated by Riccardo De Contardi almost 7 years ago
- Status changed from Resolved to Closed