Bug #82837

Exception in backend searching in columns of type integer/bigint on PostgreSQL

Added by Stephan Großberndt about 3 years ago. Updated 7 months ago.

Status:
Closed
Priority:
Must have
Assignee:
-
Category:
Database API (Doctrine DBAL)
Target version:
-
Start date:
2017-10-20
Due date:
% Done:

100%

Estimated time:
TYPO3 Version:
8
PHP Version:
Tags:
postgresql
Complexity:
Is Regression:
Sprint Focus:

Description

Define a column in a TYPO3 PostgreSQL database as integer or bigint, add it to TCA and make it available to the backend search by adding it to the TCA in ['ctrl']['searchFields']

Searching for a value in the backend record list leads to two different exceptions depending on the search configuration in TCA.

If you define the column as usual with simply

'config' => [
    'type' => 'input',
]

an exception is thrown because the query tries to call LOWER() on the column value which fails for integer/bigint on PostgreSQL:

An exception occurred while executing 
'SELECT COUNT(*) FROM "tx_my_table" WHERE ("tx_my_table"."pid" = 1) AND ((LOWER("my_column") LIKE LOWER('%MySearchTerm%'))' 
SQLSTATE[42883]: Undefined function: 7 
FEHLER: Funktion lower(bigint) existiert nicht 
HINT: Keine Funktion stimmt mit dem angegebenen Namen und den Argumenttypen überein. Sie müssen möglicherweise ausdrückliche Typumwandlungen hinzufügen.

Configuring the search to be case-sensitive like this

'config' => [
    'type' => 'input',
    'search' => [
        'case'
    ]
]

does not help as even if the LOWER() call is omitted the search fails too as doing a LIKE query on a integer/bigint column does not work either:

An exception occurred while executing 
'SELECT COUNT(*) FROM "tx_my_table" WHERE ("tx_my_table"."pid" = 1) AND (("my_column" LIKE '%MySearchTerm%')'
SQLSTATE[42883]: Undefined function: 7 
FEHLER: Operator existiert nicht: bigint ~~ unknown
HINT: Kein Operator stimmt mit dem angegebenen Namen und den Argumenttypen überein. Sie müssen möglicherweise ausdrückliche Typumwandlungen hinzufügen.

Related issues

Related to TYPO3 Core - Bug #81802: SQL Exception when searching in recycler using PostgreSQLClosed2017-07-05

Actions
Related to TYPO3 Core - Epic #90719: PostgreSQL related issuesAccepted2020-03-10

Actions
#1

Updated by Susanne Moog about 3 years ago

  • Category set to Database API (Doctrine DBAL)
#2

Updated by Georg Ringer almost 3 years ago

  • Related to Bug #81802: SQL Exception when searching in recycler using PostgreSQL added
#3

Updated by Christian Eßl 9 months ago

  • Related to Epic #90719: PostgreSQL related issues added
#4

Updated by Gerrit Code Review 8 months ago

  • Status changed from New to Under Review

Patch set 5 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/63885

#5

Updated by Gerrit Code Review 8 months ago

Patch set 6 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/63885

#6

Updated by Gerrit Code Review 8 months ago

Patch set 7 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/63885

#7

Updated by Gerrit Code Review 8 months ago

Patch set 1 for branch 9.5 of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/64153

#8

Updated by Manuel Selbach 8 months ago

  • Status changed from Under Review to Resolved
  • % Done changed from 0 to 100
#9

Updated by Gerrit Code Review 8 months ago

  • Status changed from Resolved to Under Review

Patch set 2 for branch 9.5 of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/64153

#10

Updated by Manuel Selbach 8 months ago

  • Status changed from Under Review to Resolved
#11

Updated by Benni Mack 7 months ago

  • Status changed from Resolved to Closed

Also available in: Atom PDF