Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Open
eiriksm opened this issue Nov 3, 2024 · 2 comments · May be fixed by #279
Open

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

eiriksm opened this issue Nov 3, 2024 · 2 comments · May be fixed by #279

Comments

@eiriksm
Copy link
Contributor

eiriksm commented Nov 3, 2024 •
edited
Loading

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       |  | [email protected] |

    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.
@eiriksm eiriksm linked a pull request Nov 4, 2024 that will close this issue
Open
@sonnykt
Copy link

sonnykt commented Nov 5, 2024

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` = '[email protected]') 
    OR 
    (`users_field_data`.`name` LIKE '[email protected]' ESCAPE '\\');

The troublesome condition is (`users_field_data`.`uid` = '[email protected]'). The uid column is INT and when compared with a string, both MySQL and MariaDB return 1.

MariaDB [drupal]> SELECT 0 = '[email protected]';
+--------------------------+
| 0 = '[email protected]' |
+--------------------------+
|                        1 |
+--------------------------+
1 row in set, 1 warning (0.000 sec)

@amanpilgrim
Copy link

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);
      }

@amanpilgrim amanpilgrim mentioned this issue Nov 7, 2024
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Avoid referencing UID 0 not on purpose eiriksm/DrupalDriver
3 participants
@sonnykt @eiriksm @amanpilgrim