When converting search fields to a sql clause, the qvar parsing
code is just wrong/broke/brain dead...
e.g. I modify query_fields and add in a derived column like
IF(pw_postmeta.meta_value = 1, 'Active, 'Inactive') AS active,
Then that derived column isn't available in a WHERE clause, so you
cannot put it there, it must be in a HAVING clause.
However, you cannot just blanket move the entire search section into a HAVING clause because at that point, only the result set columns are available, so any WHERE condition that affects a column in a joined table that do not put that field in the select set will fail in a HAVING clause.
There is a query run pre-post fetch that just grabs the ID, then tacks on the normal pre_query filter, where someone can modify the fields selected and add or remove something. So a column that is visible during WHERE is not necessarily visible to HAVING, and it's up to the code to figure out which is which and place in appropriate clause.
SELECT wp_users.ID, IF(wp_usermeta.meta_value, 'Active', 'Inactive') AS active FROM wp_users LEFT JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id AND (wp_usermeta.meta_key = 'active') WHERE (wp_users.display_name LIKE '%Active%') HAVING active LIKE '%Active%'
You can see that 'display_name' is visible in the WHERE clause but would not be visible in the HAVING as it's not in the result set. Likewise, the derived column 'active' is visible in the HAVING clause but would not be visible to a WHERE as it has not yet been calculated.
This is a SERIOUS bug. Including 1 derived column in a field list/search column setup will break the query.