Project

General

Profile

Actions

Bug #82837

closed

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

Added by Stephan Großberndt almost 7 years ago. Updated over 4 years 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 2 (1 open1 closed)

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
Actions #1

Updated by Susanne Moog over 6 years ago

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

Updated by Georg Ringer over 6 years ago

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

Updated by Christian Eßl over 4 years ago

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

Updated by Gerrit Code Review over 4 years 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

Actions #5

Updated by Gerrit Code Review over 4 years 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

Actions #6

Updated by Gerrit Code Review over 4 years 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

Actions #7

Updated by Gerrit Code Review over 4 years 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

Actions #8

Updated by Manuel Selbach over 4 years ago

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

Updated by Gerrit Code Review over 4 years 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

Actions #10

Updated by Manuel Selbach over 4 years ago

  • Status changed from Under Review to Resolved
Actions #11

Updated by Benni Mack over 4 years ago

  • Status changed from Resolved to Closed
Actions

Also available in: Atom PDF