Contrib.social

Orphan File Filter ERROR: "operator does not exist" - Type mismatch in a query

Open on Drupal.org →
Created on 20 January 2023, almost 2 years ago
Updated 12 June 2023, over 1 year ago

Problem/Motivation

Steps to reproduce

Drupal version: 9.4
DB: Postgres 13

After an initial module installation, the 'orphaned' option throws this error:
ERROR: operator does not exist: character varying = integer
LINE 1: ...d = fu.fid) LEFT OUTER JOIN "node" AS n ON (fu.id = n.nid) W...

The affected query: SELECT fm.* FROM "file_managed" AS fm LEFT OUTER JOIN "file_usage" AS fu ON (fm.fid = fu.fid) LEFT OUTER JOIN "node" AS n ON (fu.id = n.nid) WHERE fu.type = 'node' AND n.nid IS NULL

Proposed resolution

'id' in 'file_usage' and 'nid' in 'node' have different types (character varying and integer). So explicit casting may be needed. Besides that, we have both 'id' and 'uuid' fields in the node table. And apparently, in 'file_usage' table, the 'id' field can contain both types of id's. So maybe a query rewrite could solve this

Remaining tasks

User interface changes

API changes

Data model changes

🐛 Bug report
Status

Active

Version

2.0

Component

Code

Created by

deushl

Live updates comments and jobs are added and updated live.
Sign in to follow issues

Comments & Activities

Not all content is available!

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

  • Comment over 1 year ago →
    🇺🇸United States jmouse888

    We ran into the same issue. Yes, the query should be rewritten to accommodate both uuid (charvar) and id (int). As a short-term solution, I just converted (with the CAST operator "::") the nid from the {node} table from int to charvar so the "=" operator will work as intended.

    around line 75 in src/Plugin/views/filter/FancyFileDeleteOrphanFileFilter.php

    from:
    AS fu ON (fm.fid = fu.fid) LEFT OUTER JOIN {node} AS n ON (fu.id = n.nid)

    to:
    AS fu ON (fm.fid = fu.fid) LEFT OUTER JOIN {node} AS n ON (fu.id = n.nid::character varying)

    I also created a patch. This will not resolve the uuid orphan query. However, with the majority of the file attached to nodes on our site, this will work fine for the time being.

contrib.social Blog FAQ Discussions
Production build 0.71.5 2024