Views JOIN condition fails in PostgreSQL when comparing entity identifiers of different datatypes

Added a CAST as CHAR to solve the issue.

Removed the trailing spaces.

The last submitted patch, 22: 3079534-21_psql-views-join-id.patch, failed testing. View results →

Complementing #12
In addition to performance issues, I got duplicated rows in different views, for example, I got duplicated results after filtering users by mail in /admin/people.

I would recommend validating the database type before doing the CAST, since this only happens with "pgsql", then validate the type of the field (if entity type exists) and validate if the field doesn't have "id" as sub-string (assuming all ids are INT)

I solve my specific issue with this https://www.drupal.org/project/flag/issues/2929733#comment-12836956 🐛 SQL query error on PostgreSQL 9.5 when try to list Bookmarks Active

Based on my previous comment I've created this patch.

I have placed strrpos params wrong, patch was updated.

The last patch didn't work for me, using Drupal 9.5.11.
I updated to make it working.

This improved patch casts to text instead of bigint. This fixes additional failures I was having.

I have updated the tests so that they pass with the changes.

@Liam Morland, while the tests are passing on the merge request, it's not apparent which database system is being tested. It would be good to just make sure that MariaDB/Mysql/Sqlite are also passing and also all versions of PostgreSQL. With the MR test, it wasn't obvious which setup was being tested (maybe I'm missing something?).

I have run the tests on all the platforms available in the pipelines. They have all passed.

Can we use \Drupal\views\Plugin\views\join\CastedIntFieldJoin for this and avoid adding pgsql specific code to base plugins?

It looks like it will be deprecated: 📌 [PP-1] Deprecate and remove Drupal\views\Plugin\views\join\CastedIntFieldJoin Postponed .

By its name, it sounds like it would cast to int. The merge request has it casting to text because entity identifiers are not always integers.

Current changes without the test updates.

Hiding patches for clarity.

Leaving in NR as seems desire is/was to move pgsql specific code out of base

Where would you suggest this code be put?

@smustgrave , the approach suggsested to move pgsql specific code out of base was deprecated.
see comment #35
[#307953-35]

Well if there is a use for it, we can always undeprecate it (or in this case, never deprecate it in the first place), or if it needs a string version of this we should add a string version and put it in the pgsql module where this seems to belong (is PostgreSQL the exception here or is MySQL? How does SQLite handle this?)

Also, I think the general consensus is that int joins are more performant than string joins, so what performance impact does casting everything to a string have? We should check.

How is this handled outside Views? It's not like Views is the only place you can make joins. Is there maybe some method we could/should add to the database drivers that handles this?

I would like to see test coverage added that actually shows we need this, so something that would fail without this fix. All the modified test coverage changes only show that the ':text' is added and this doesn't break existing queries.

At the moment, Postgres is the exception, though I think that it is doing it more correct under the SQL standard. I don't know what SQLite does.

I ran into this problem as described in #2864440: PostgreSQL problems on entity_id of type varchar with flagging and flag_count tables → . Other related issues describe how this has surfaced.

The problem is that id columns can be either string or integer. If there is a way to of knowing which it is then it could use integer casting when that is possible.