When using a name for a user in an entity reference field, uid 0 gets chosen

[eiriksm]

I am using a fairly out of the box setup with creating some users and then using them as entity references.

An excerpt from my steps:

  Background:
    Given users:
      | name | roles | mail |
      # Some other stuff (...)
      | test_user_with_enterprise_team       |  | testteamenterprise@example.com |

    Given "team" content:
      | title | field_plan | field_team_members |
      | test team  | plan_enterprise | test_user_with_enterprise_team |

This has been working quite fine, up until upgrading to v.2.3.0.

Now, the entity reference handler that expands the field chooses uid 0 instead. Basically it translates to an SQL query like so:

SELECT "base_table"."uid" AS "uid", "base_table"."uid" AS "base_table_uid"
FROM
"users" "base_table"
LEFT JOIN "users_field_data" "users_field_data" ON "users_field_data"."uid" = "base_table"."uid"
WHERE ("users_field_data"."uid" = 'test_user_with_enterprise_team') or ("users_field_data"."name" LIKE 'test\\_user\\_with\\_enterprise\\_team' ESCAPE '\\')

To me this looks quite OK, but for some reason this gives me 2 hits. One is the correct one, but the first one is uid 0.

I can easily reproduce it directly in my sql, without any test setup:

mysql> select * from users LEFT JOIN users_field_data ON users_field_data.uid = users.uid WHERE users_field_data.uid = 'test_user_with_enterprise_team';
+-----+--------------------------------------+----------+------+----------+--------------------+--------------------------+------+------+------+----------+--------+------------+------------+--------+-------+------+------------------+
| uid | uuid                                 | langcode | uid  | langcode | preferred_langcode | preferred_admin_langcode | name | pass | mail | timezone | status | created    | changed    | access | login | init | default_langcode |
+-----+--------------------------------------+----------+------+----------+--------------------+--------------------------+------+------+------+----------+--------+------------+------------+--------+-------+------+------------------+
|   0 | 9656d6d9-060a-4fc4-b3f2-f8e131e17e53 | en       |    0 | en       | en                 | NULL                     |      | NULL | NULL | NULL     |      0 | 1730625944 | 1730625944 |      0 |     0 | NULL |                1 |
+-----+--------------------------------------+----------+------+----------+--------------------+--------------------------+------+------+------+----------+--------+------------+------------+--------+-------+------+------------------+
1 row in set, 1 warning (0.00 sec)

The warning generated is this:

mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------------------------------+
| Level   | Code | Message                                                            |
+---------+------+--------------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'test_user_with_enterprise_team' |
+---------+------+--------------------------------------------------------------------+

I am probably not an SQL expert enough to tell you why it works like that, but it surely is a regression, and it totally breaks many many of my tests 😛

I see it was introduced with this #241

To me this indicates we could probably add an additional condition on uid not being 0 at least.

[sonnykt]

Confirming on the issue with 2.3.0.

The query is

  SELECT `base_table`.`uid` AS `uid`, `base_table`.`uid` AS `base_table_uid`
  FROM `users` `base_table`
  LEFT JOIN `users_field_data` `users_field_data` ON `users_field_data`.`uid` = `base_table`.`uid`
  WHERE 
    (`users_field_data`.`uid` = 'qa+1@salsa.digital') 
    OR 
    (`users_field_data`.`name` LIKE 'qa+1@salsa.digital' ESCAPE '\\');

The troublesome condition is (`users_field_data`.`uid` = 'qa+1@salsa.digital'). The uid column is INT and when compared with a string, both MySQL and MariaDB return 1.

MariaDB [drupal]> SELECT 0 = 'qa+1@salsa.digital';
+--------------------------+
| 0 = 'qa+1@salsa.digital' |
+--------------------------+
|                        1 |
+--------------------------+
1 row in set, 1 warning (0.000 sec)

[amanpilgrim]

The commit in #241 has caused breaking changes when using entity reference labels and a postgres database. It appears to be same error - postgres throws SQLSTATE[22P02] when a non-numeric value is used to query a bigint id field; mysql returns zero (as observed by eiriksm).

  Background:
    Given the "Role1" role exists
    And restricted "access_control" terms:
      | name     | access_role |
      | Termall  | role1       |
    And "page" content:                      # FeatureContext::createNodes()
      | title            | body                      | moderation_state | author | field_access_control |
      | Test page Role1  | Page body for Role1 page  | published        | admin  | Termall              |
      SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syntax for type bigint: "Termall"
      LINE 6: WHERE (("taxonomy_term_field_data"."tid" = 'Termall') or ("t...
      ...
      WHERE (("taxonomy_term_field_data"."tid" = :db_condition_placeholder_0) or ("taxonomy_term_field_data"."name"::text ILIKE :db_condition_placeholder_1)) AND ("taxonomy_term_field_data_2"."vid" IN (:db_condition_placeholder_2));

Ids can be integers or strings, so we need to first determine the id and value types, then build the query conditions based on that, e.g.

      if ($id_type === 'integer' && is_numeric($value)) {
        $query->condition($id_key, $value);
      } else {
        $query->condition($label_key, $value);
      }

[RoSk0]

Observed the same issue on PostgreSQL as @amanpilgrim .

Tested #280 and can confirm it fixes the problem.