[3.0]: Searching personal messages by author finds nothing on PostgreSQL

[albertlast]

Basic Information

Searching personal messages by their author finds nothing on PostgreSQL when the name is typed with a capital letter. On MySQL any capitalisation works.

Search::setUserQuery() folds the column but not the value it compares against. Sources/PersonalMessage/Search.php:479-486:

foreach ($possible_users as $k => $v) {
    $where_params['name_' . $k] = $v;
    $where_clause[] = '{raw:real_name} LIKE {string:name_' . $k . '}';

    if (!isset($where_params['real_name'])) {
        $where_params['real_name'] = Db::$db->case_sensitive ? 'LOWER(real_name)' : 'real_name';
    }
}

$possible_users is built at Sources/PersonalMessage/Search.php:455-467 from $this->params['userspec'] and only has Utils::htmlspecialchars() and a wildcard strtr() applied. Nothing folds it.

So on PostgreSQL the comparison becomes LOWER(real_name) LIKE 'John%'. A folded column cannot equal an unfolded value, so it matches nothing — not even the member it names. On MySQL the column is left alone and the collation folds both sides, so it matches.

Sources/PersonalMessage/Search.php:501-506 has the same shape for pm.from_name, used to search messages sent by guests.

This is the same fault as #9594, in a different feature: one side of the comparison is folded and the other is not. It is worth noting that it is worse than folding neither side. Folding neither would match too much on MySQL and too little on PostgreSQL; folding only the column matches nothing at all on PostgreSQL, including the exact row that was being looked for.

Steps to reproduce

1. Install SMF 3.0 on PostgreSQL.
2. Have a member named John send you a personal message.
3. Go to Personal Messages → Search, and put John in the "By user" box.
4. Search for a word from that message.

Expected result

The message is found, the same as on MySQL.

Actual result

Nothing is found. Typing john in lower case finds it; typing John does not, even though that is the member's name as displayed.

Version/Git revision

3.0 Alpha 4 (f12217b88)

Database Engine

PostgreSQL

Database Version

PostgreSQL 17.10

PHP Version

8.4.24

Logs

# PostgreSQL 17.10 — the comparison the query builds
smf=> SELECT LOWER('John') LIKE 'John%';
 f

# MySQL 8.4.11 — no LOWER() is added, and the collation folds both sides
mysql> SELECT 'John' LIKE 'John%';  -- 1

Additional Information

The fix is to fold the value as well as the column. Utils::strtolower() on $possible_users, in the way AutoSuggest and RequestMembers already fold their search terms, would do it. #9596 introduces a {ci_string:} query type that folds the value in SQL instead, for the cases where folding in PHP is not convenient.

#9596 converts these two lines to {ci:real_name} and {ci:pm.from_name}. That is deliberately behaviour-preserving and does not fix this; #9597 lists both lines so that the fault stays visible until it is.

Related: #9594 is the same fault in the registration email check, #9593 is the same feature broken a different way in the main search.

[albertlast]

Same root cause, filed separately: #9592, #9593, #9594 and #9599. Of those, #9594 and #9599 are the two that fold one side of the comparison and not the other, as this one does.