Contrib.social

Not checking "Allow multiple values" in contextual-filter, with PostgreSQL, results SQL error

Open on Drupal.org →
Created on 8 March 2025, 30 days ago

Problem/Motivation

We have a node that has a field, "Term List", which references one or more taxonomy-terms.

We have a views-block that is placed to display when that node is displayed.

In the views-block is a contextual filter, which filters on the Term List field, using:

  • Provide default value
  • of type "Taxonomy term ID from URL"
  • checking the box for "Load default filter from node page, that's good for related taxonomy blocks"
  • NOT checking "Allow multiple values", in the "More" section at the bottom of the form

When viewing this views-block, under PostgreSQL (but not under MySQL), we see this error:
Drupal\Core\Database\DatabaseExceptionWrapper: Exception in Test View[test_view]: SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for type bigint: "1,2,4" LINE 5: ...defield_terms_list.field_terms_list_target_id = '1,2,4')) ... ^: SELECT "node_field_data"."created" AS "node_field_data_created", "node_field_data"."nid" AS "nid" FROM "ess"."node_field_data" "node_field_data" LEFT JOIN "ess"."nodefield_terms_list" "nodefield_terms_list" ON node_field_data.nid = nodefield_terms_list.entity_id AND nodefield_terms_list.deleted = :views_join_condition_0 WHERE ((nodefield_terms_list.field_terms_list_target_id = :nodefield_terms_list_field_terms_list_target_id)) AND ("node_field_data"."status" = :db_condition_placeholder_1) ORDER BY "node_field_data_created" DESC NULLS LAST LIMIT 11 OFFSET 0; Array ( [:nodefield_terms_list_field_terms_list_target_id] => 1,2,4 [:db_condition_placeholder_1] => 1 [:views_join_condition_0] => 0 ) in main() (line 19 of index.php).

What I would expect to happen is what happens under MySQL: The contextual filter ends up filtering items out of the results list, and the view displays normally, without nasty SQL errors.

(This is likely related to issue 3268601 ( Views PostgreSQL Error | SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for type bigint 🐛 Views PostgreSQL Error | SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for type bigint Needs work ), which gets a similar error message under different circumstances.)

Steps to reproduce

  • Environment
    - DDEV, under WSL2
    - PHP 8.2.24
    - DB Postgres 15.7 (Debian 15.7-1.pgdg120+1)
    - 10.3.13 plain drupal "standard" install.
  • Create a vocab, "Test Vocab"
  • Add to several terms: "Term 1", "Term 2", etc.
  • Create a new content type, "Test type"
  • In it, create a new reference field, "Terms list"
    - Referencing Taxonomy Term
    - Unlimited number of values
    - Reference type "Default"; For Vocabulary, check our "Test Vocab"
  • Add a view, "Test View", and create a block display for this view.
    - In this display set the "block name" to "Test block"
    - Add a contextual filter based on our "Terms List (field_terms_list)". When the filter is not available, provide default value, "Type: Taxonomy Term ID from URL", Check "Load default filter from node page, that's good for related taxonomy blocks"
    - Do NOT check "Allow multiple values" in the More section at the bottom of the form
    - Save the view.
  • Display the block:
    - Go to Structure, Block Layout
    - Click "Place block" in the Content region
    - Choose our Test block
  • Create a node of "Test type", and in its TermsList field, select more than one term.

When you view this node, you'll see the SQL error.

🐛 Bug report
Status

Active

Version

10.3 ✨

Component

views.module

Created by

🇨🇦Canada dan3h

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

Comments & Activities

contrib.social Blog FAQ Discussions
Production build 0.71.5 2024