SQL query error on PostgreSQL 9.5 when try to list Bookmarks

Created on 10 December 2017, over 6 years ago

Updated 15 January 2024, 6 months ago

Problem/Motivation

The /bookmarks page showing error "The website encountered an unexpected error. Please try again later." when try to list user's own bookmarks list.

[error] 12722#0: *1860 FastCGI sent in stderr: "PHP message: Uncaught PHP Exception Drupal\Core\Database\DatabaseExceptionWrapper: "Exception in Flag Bookmark list[flag_bookmark]: SQLSTATE[42883]: Undefined function: 7 ERROR:  operator does not exist: bigint = character varying
LINE 6: ...g flagging_node_field_data ON node_field_data.nid = flagging...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.: SELECT COUNT(*) AS expression
FROM 
(SELECT 1 AS expression
FROM 
{node_field_data} node_field_data
INNER JOIN {flagging} flagging_node_field_data ON node_field_data.nid = flagging_node_field_data.entity_id AND (flagging_node_field_data.flag_id = :views_join_condition_0 AND flagging_node_field_data.uid = :views_join_condition_1)
INNER JOIN {users_field_data} users_field_data_node_field_data ON node_field_data.uid = users_field_data_node_field_data.uid
WHERE node_field_data.status = :db_condition_placeholder_0) subquery; Array
( [...]

I think the cause of exception is comparing 'bigint' with string on PostgreSQL. I tried to build same installation with MySQL 5.5 and everything works as expected.

Drupal installation is running on Drupal 8.x and PostgreSQL 9.5.

🐛 Bug report

Status

Active

Component

Flag Bookmark

Created by

🇹🇷Turkey kaanklky

Live updates comments and jobs are added and updated live.

PostgreSQL
Particularly affects sites running on the PostgreSQL database.

Incomplete comments

Comments & Activities

Not all content is available!

It's likely this issue predates Contrib.social: some issue and comment data are missing.

Comment 9 months ago →
🇲🇽Mexico luisnicg
I got the same error with group and webform modules when you assign one of the element to the form as a group entity.
#6 works for me.
I also applied #9, the one that was posted for the core, but I got duplicated rows in different views, for example, I got duplicated results after filtering users by mail in /admin/people.
Comment 6 months ago →
🇨🇦Canada Liam Morland Ontario, CA 🇨🇦
This seems to be a duplicate of #2864440: PostgreSQL problems on entity_id of type varchar with flagging and flag_count tables → .

I had this problem and was able to solve it with in the patch in #3079534-27: Views JOIN condition fails in PostgreSQL when comparing entity identifiers of different datatypes → .

contrib.social Blog FAQ Discussions

Production build 0.69.0 2024