Problem/Motivation

The SQL executed when searching for the user with username 560235 when the module is disabled is:

SELECT users_field_data.uid AS uid FROM {users_field_data} users_field_data
WHERE ((CONCAT_WS(' ', users_field_data.name, ' ', users_field_data.mail) LIKE '%560235%')) AND (users_field_data.default_langcode = '1') AND (users_field_data.uid != '0')
ORDER BY users_field_data.created DESC LIMIT 50 OFFSET 0

Whereas when the module is enabled, it becomes:

SELECT users_field_data.langcode AS users_field_data_langcode, users_field_data.uid AS uid FROM {users_field_data} users_field_data
WHERE (users_field_data.uid < '0') AND (users_field_data.default_langcode = '1') AND (users_field_data.uid != '0')
ORDER BY users_field_data.created DESC LIMIT 50 OFFSET 0

It can easily be seen that the second query will not return any result as there's no negative uids. This happens frequently, even though some of the usernames do return results.

Steps to reproduce

  1. Enable the module
  2. Create a large set of users (maybe with numeric usernames)
  3. Search for a user with one of the numeric usernames

Proposed resolution

Fix the query modification function to still use the %wildcard% query for usernames.

Remaining tasks

Implement the fix.

CommentFileSizeAuthor
#7 support-combine-filter-profile-fields.patch3.72 KBwilfred.waltman

Comments

jcnventura created an issue. See original summary.

thedut’s picture

thedut commented

Hello João,

This happens frequently, even though some of the usernames do return results.

I am not able to reproduce this issue, going to admin/people page.
The function _dbee_db_functions_clause() is reponsible for this behavior.

returning the sql command
WHERE (users_field_data.uid < '0') AND (users_field_data.default_langcode = '1') AND (users_field_data.uid != '0')
is the normal behavior IF the 560235 username does not exists in the database.
Or it should returns
WHERE (users_field_data.uid = 25) AND (users_field_data.default_langcode = '1') AND (users_field_data.uid != '0')
instead IF the username 560235 username does exist and has the 25 UID.
two functionnal tests explore wildcards queries : this one and this one
Are you sure about this issue ?

jcnventura’s picture

jcnventura
Primary language English
commented

Yes, I think you need to create maybe a few thousand users (10K should be enough). For some reason, at some point (quite early even), the dbee module stops being able to run this replacement. Even when there's only one possible match (i.e. our user with username 560235).

thedut’s picture

thedut commented

Hello I still can't reproduce this issue.
Tested with 10 000 users on DbeeSearchUserTest and DbeeAllUsersBatchTest (settings protected $nUsers = 10000;).
I close the issue as can't reproduce.

thedut’s picture

thedut commented
Assigned: Unassigned » thedut
Status: Active » Closed (cannot reproduce)
wilfred.waltman’s picture

wilfred.waltman commented
Version: 8.x-2.x-dev » 3.1.2
Status: Closed (cannot reproduce) » Needs work

I kind of had the same issue with an field I needed to include from a profile connected to the user account. As far as Claude is concerned, this is what is happening:

Root cause (final):
the dbee module encrypts users_field_data.mail and rewrites any query touching it.
It has special-case handling for Views' CONCAT_WS(...) combine-filter pattern, but its value-resolver only recognized the columns mail, init, name. Your field value column wasn't recognized, so dbee substituted the literal string "field_..._value" instead of the real , found zero matches, and replaced the whole query's WHERE clause with its "no results" fallback (uid < 0).
That's why the search silently returned nothing regardless of the Views config

wilfred.waltman’s picture

wilfred.waltman commented
StatusFileSize
new support-combine-filter-profile-fields.patch3.72 KB

Claude also fabricated a patch for me that I will attach here.

Although I understand this is specifically for the connection to the profile module. But maybe it will provide some input for a solution when somebody believes this is something the dbee module needs to address.

thedut’s picture

thedut commented

Hello

Thank you wilfred.waltman (and Claude !) for digging this 6 years old issue !
After a quick look to the fix, I understand the dbee module may fail

  1. with the Profile contrib module enabled
  2. and with an addition field on a profile type (bundle).
  3. when searching a user

I need to explore this issue and probably create a unit test for the profile module with dbee.

wilfred.waltman’s picture

wilfred.waltman commented

@thedut,

I am not sure but maybe this issue also appears when somebody adds a field to the user entity.
Did not test that, but ik seem to make sense.

So, I would not focus on the Profile module alone.

thedut’s picture

thedut commented

@wilfred.waltman
Yes I will.
First I need to reproduce this issue but I fail
Could you help me explaining how I can reproduce it ?

Tested with dbee module and profile and search module enabled on a fresh D11.4.6 install :
adding a profile type and a text field on it.
Filling the text field for a user.
Going to admin/people page (and /search/user)
tiping the partial username ou email :
--> The result correctly display the corresponding user (no issue).

thedut’s picture

thedut commented

Ok, I'm not completly sure, but regarding to the provided fix, seems that dbee module fails in a very specific situation : including all those conditions together

  1. when using a view
  2. with Combine fields filter (machine name = 'combine')
  3. on the user entity
  4. including more fields than name and email.

So this is for specific custom views.
The only core view that use this filter is user_admin_people view (accessible from the path admin/people) but with the right fields. So no issue.
I guess adding a text field (from user or profile entity or any relationship entity to user) to this combine filter on the user_admin_people view will permit to reproduce this issue.
I will test and if I succeed i wil change the title of this issue, and of course fix it, with a more specific fix (the provided patch may have bad performance).

wilfred.waltman’s picture

wilfred.waltman commented

@thedut, thanks for looking into this and sorry for my somewhat late response.

So in our case:
- We installed the profile module, created a profile bundle and added a field "nickname" to it.
- We altered the the user_admin_people view by adding a relationship to the profile.
- We added the "nickname" field to the list of fields.
- We added and exposed an Global: Combine Fields filter. In this filter we selected the Profile: Nickname field end the User: Email field.

And then, without the patch, the query "breaks" with the Dbee module enabled.
The patch fixes it, but I wil not go as far to say it is optimized for performance. On the other hand, I would not know how tot improve it unfortunately.