Project

General

Profile

Actions

Bug #44711

closed

SQL: No field name found as expected in parseFieldList()

Added by plop over 11 years ago. Updated over 6 years ago.

Status:
Closed
Priority:
Should have
Category:
Database API (Doctrine DBAL)
Target version:
Start date:
2013-01-22
Due date:
% Done:

0%

Estimated time:
TYPO3 Version:
6.0
PHP Version:
Tags:
Complexity:
Is Regression:
No
Sprint Focus:

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"

http://typo3.org/go/exception/v4/1310027490


Related issues 1 (0 open1 closed)

Related to TYPO3 Core - Task #61204: Change relevance sortingClosed2014-08-26

Actions
Actions #1

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.

Actions #2

Updated by Oliver Hader about 11 years ago

  • Target version set to 2222
Actions #3

Updated by Oliver Hader about 11 years ago

  • Project changed from 2269 to TYPO3 Core
Actions #4

Updated by Oliver Hader about 11 years ago

  • Category set to Extension Manager
Actions #5

Updated by Oliver Hader about 11 years ago

  • Target version deleted (2222)
Actions #6

Updated by Mathias Schreiber over 9 years ago

  • Target version set to 7.3 (Packages)
  • Is Regression set to No
Actions #7

Updated by Benni Mack about 9 years ago

  • Target version changed from 7.3 (Packages) to 7.4 (Backend)
Actions #8

Updated by Susanne Moog almost 9 years ago

  • Target version changed from 7.4 (Backend) to 7.5
Actions #9

Updated by Susanne Moog almost 9 years ago

  • Category changed from Extension Manager to 999
Actions #10

Updated by Morton Jonuschat almost 9 years ago

  • Assignee set to Morton Jonuschat
Actions #11

Updated by Morton Jonuschat almost 9 years ago

  • Status changed from New to Resolved

Issue has been resolved through #61204, applied in commit 4f78570845f5c97d739c25b4d288cc6776cfd262

Actions #12

Updated by Riccardo De Contardi over 6 years ago

  • Status changed from Resolved to Closed
Actions

Also available in: Atom PDF