Tags in Blog Posts cause SQL error

Wow, that's a weird one. I've never seen this before. What kind of database are you using?

Thanks for this clear, detailed bug report -- I'm ensuring you get issue/commit credit.

The problem is caused by the "Related blog posts" views block that we show on the default view display.

This is a core issue. Surprisingly the culprit comes from when views made it into core, and haven't been modified at all in 14 years.
This uses some obscure technique where 1+2+3 means "1 or 2 or 3". I can't find any documentation at all on mysql about it, but definitely doesn't work on other engines.

This was introduced in #1197030: No way to use "any tid" as default contextual filter → to views module.

The resulting query (I might have modified this for simplification, e.g. ignore trash module alters):

MariaDB [db]> SELECT node_field_data.sticky AS node_field_data_sticky, node_field_data.created AS node_field_data_created, node_field_data.nid AS nid FROM     node_field_data node_field_data LEFT JOIN node__fi
eld_tags node__field_tags ON node_field_data.nid = node__field_tags.entity_id WHERE (((node__field_tags.field_tags_target_id = '1+2+3')) AND ((node_field_data.nid != 2 OR node_field_data.nid IS NULL))) AND ((n
ode_field_data.type IN ('blog')) AND (node_field_data.status = '1')) ORDER BY node_field_data_sticky DESC, node_field_data_created DESC;
Empty set (0.003 sec)

db=# SELECT node_field_data.sticky AS node_field_data_sticky, node_field_data.created AS node_field_data_created, node_field_data.nid AS nid
FROM
    node_field_data node_field_data
LEFT JOIN node__field_tags node__field_tags ON node_field_data.nid = node__field_tags.entity_id AND node__field_tags.deleted = '0'
WHERE (((node__field_tags.field_tags_target_id = '1+2+3')) AND ((node_field_data.nid != 2 OR node_field_data.nid IS NULL)) AND (node_field_data.deleted IS NULL)) AND ((node_field_data.type IN ('blog')) AND (node_field_data.status = '1'))
ORDER BY node_field_data_sticky DESC NULLS LAST, node_field_data_created DESC NULLS LAST;

ERROR:  invalid input syntax for type bigint: "1+2+3"
LINE 5: WHERE (((node__field_tags.field_tags_target_id = '1+2+3')) A...

As a workaround we could change the tid argument on that view to not use multiple values, but that would mean different results (e.g. related could only consider the first tag).

If I understand what the view is trying to do, then https://www.drupal.org/project/similarterms → handles this use case extremely well, with views integration. I've used that module for more than ten years on a personal project. Whether that module eventually ends up running the same SQL that's a problem here with postgres, I do not know, because the personal project is on MySQL.

https://dbfiddle.uk/G19zP3qi

This doesn't work as expected in sqlite neither, but no errors or exceptions are shown.

This seems to be a related issue: 🐛 Not checking "Allow multiple values" in contextual-filter, with PostgreSQL, results SQL error Active

But the Related blog posts display in the Blog view does not have Allow multiple values checked in the Content: Tags (Default: Taxonomy term ID from URL) contextual filter. Once the option is checked, the error is gone. So I think this can be fixed in Drupal CMS as well with adjusting the view config - if we set Allow multiple values by default.