Bug #102397
closed
PostgreSQL: Argument of AND must be type boolean, not type integer
0%
Description
When translating a page in TYPO3 v12 using a Postgres Database the following error appears:
An exception occurred while executing a query: SQLSTATE42804: Datatype mismatch: 7 ERROR: argument of AND must be type boolean, not type integer LINE 1: ...RE ("pid" = $1) AND ("sys_language_uid" = 0) AND (1 AND (("...
The full query is this:
SELECT "uid" FROM "pages" WHERE ("pid" = ?) AND ("sys_language_uid" = 0) AND
(1 AND (("pages"."hidden" = 0) AND ("pages"."starttime" <= 1700242560) AND
((("pages"."endtime" = 0) OR ("pages"."endtime" > 1700242560))))) AND ("pag
es"."deleted" = 0) ORDER BY "uid" ASC
This is due to the fact that Postgres can't handle 1 and 0 as boolean and fails.
The translated page record is still created as this query is not responsible for the new record but executed after the record was created.
A similar error was reported here (https://forge.typo3.org/issues/75912) a long time ago but was related to content elements.
Can you please take a look at this?
Thank you!
Files
Updated by cosmoblonde GmbH 6 months ago
- Tags changed from Postgres to PostgreSQL
Updated by Atol C&D 5 months ago
· Edited
I can confirm that this bug still exists from TYPO3 9.5 onward (as mentioned in #75912)
I've migrated a large DB from Mysql to PgSQL using PGloader and by pure chance on several runs I managed to avoid that bug. On others I've encountered it. Yet after inspecting the structure and the data, especially pages and be_users, I've not found the cause.
The bug is easy to reproduce. Either attempt to translate a page, or copy-paste a root page or a bundle of pages containing a root page.
The root of the problem seems to be the root page. If I edit my root page into a Standard page, I can copypaste or translate without triggering the exception.
Updated by Christian Kuhn 5 months ago
- Related to Bug #75912: PostgreSQL: Argument of AND must be type boolean, not type integer added
Updated by Christian Kuhn 5 months ago
Do you have the exception backtrace at hand? This would allow us to see where the query is created in the backend.
Also, what is the exact way to reproduce the issue - which translate button is used (there are multiple ones, they may trigger different functionality, a screenshot could help).
Updated by Atol C&D 5 months ago
· Edited
I can only copypaste you the entire exception :
Core: Exception handler (WEB): Uncaught TYPO3 Exception: An exception occurred while executing 'SELECT "uid" FROM "pages" WHERE ("pid" = ?) AND ("sys_language_uid" = 0) AND (1 AND ("pages"."hidden" = 0) AND ("pages"."starttime" <= 1701096660) AND (("pages"."endtime" = 0) OR ("pages"."endtime" > 1701096660))) AND ("pages"."deleted" = 0) ORDER BY "uid" ASC' with params [5645]: SQLSTATE42804: Datatype mismatch: 7 ERROR: argument AND must be type boolean, and not type integer LINE 1: ...RE ("pid" = $1) AND ("sys_language_uid" = 0) AND (1 AND ("p... ^ | Doctrine\DBAL\Exception\DriverException thrown in file /var/www/html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractPostgreSQLDriver.php in line 102. Requested URL: {mydomain}/typo3/index.php?route=%%2Frecord%%2Fcommit&token=--AnonymizedToken--&cmd[pages]5645[localize]=1&redirect=%%2Ftypo3%%2Findex.php%%3Froute%%3D%%252Fweb%%252Flist%%252F%%26token%%3Dc31a08c130b80c96877c5ef13fb3ed2c8719af1b%%26id%%3D5638%%26table%%3D%%26imagemode%%3D1%%26justLocalized%%3Dpages%%253A5645%%253A1
I've tried to dig into the core, like the DataHandler or the BackendConfigurationManager but I've not been able to find where the query's built.
Every translate button trigger the exception. Be it the select menu atop a page record, or a flag button in the List mode. The moment I try to translate a page, or try to copy a page, or a treelist of pages and one of them contain a "rootpage" type page, the exception is triggered.
However, translating a content doesn't seem to trigger it. It seems restrainted to pages.
Updated by Christian Kuhn 5 months ago
· Edited
Hmm. I'm unable to reproduce:
I've set up a core main instance (which will become v13, this detail should be the same in v12 I suppose), running php 8.2 with pdo_pgsql on postgres 14. Then loaded ext:styleguide extension, created 'styleguide frontend demo' page tree using the styleguide backend module, created a sub-page in the tree and created a language successfully.
A back trace should really help here: You could use Admin Tools -> Settings -> Confiruation Presets -> Debug settings -> Debug to see full back traces instead of only the 'short' message. Be careful to not do that on production systems, though! Alternatively, or if that does not work out, there should be a log/typo3_*.log file that contains the full back trace. Could you have another look, please? Having the full backtrace should help to see where this broken query stems from and why I and the tests don't trigger it.
Updated by Atol C&D 5 months ago
· Edited
My mistake, I forgot to mention I'm working on a postgres 15. However yes you should be able to reproduce from TYPO3 9.5 onward.
I've put down the trace, is it good enough ?
EDIT : the forge refuses that I attach a .txt file. Here's a pastebin instead : https://pastebin.com/KfRYtyJ3
Updated by Christian Kuhn 5 months ago
· Edited
- Status changed from New to Needs Feedback
Thanks. This helps.
Turns out this is a bug in ext:solr:
typo3conf/ext/solr/Classes/AbstractDataHandlerListener.php
$permissionClause = ' 1 ' . BackendUtility::BEenableFields('pages');
$treePageIdList = $queryGenerator->getTreeList($pageId, 20, 0, $permissionClause);
Class QueryGenerator then creates an 'andWhere('1 ...')' which is the postgres-invalid call.
TYPO3 v12 moved class QueryGenerator to ext:lowlevel, which is probably why current github solr now has an own getTreeList() method in ApacheSolrForTypo3\Solr\System\Records\Pages\PagesRepository. This still seems to carry the bug:
$permissionClause = ' 1 ' . $this->getPagesRepository()->getBackendEnableFields(); $treePageIdList = $this->getPagesRepository()->getTreeList($pageId, 20, 0, $permissionClause);
The ' 1 ' prefix is probably done since BackendUtility::BEenableFields('pages') adds an AND if it found something.
I guess the easiest solution is to change it to ' 1=1 ', another one is (probably) to trim() the result of BackendUtility::BEenableFields() and skip the "1", since getTreeList() will then strip the 'AND' at the beginning due to 'QueryHelper::stripLogicalOperatorPrefix($permClause)'.
-> This needs to be fixed in solr.
Could you confirm this and maybe open an issue or pull request for ext:solr maybe referencing my analysis and your backtrace?
Updated by Christian Kuhn 5 months ago
- Status changed from Needs Feedback to Closed