DB_COLLATE is not used in database queries

[bertilow]

Testing WP-2.5RC1 I found that the collation set in "wp-config.php" (DB_COLLATE) is not being used in searches. I think it should.

This can easily be fixed. In "wp-includes/query.php" the following part can be changed as indicated here:

// If a search pattern is specified, load the posts that match
if ( !empty($q['s']) ) {

  // [...]

  foreach((array)$q['search_terms'] as $term) {
    $term = addslashes_gpc($term);
    // The following like has been added
    if ($wpdb->collate) $collatesql = ' COLLATE ' . $wpdb->collate;
    // The following line has been changed
    // $collatesql was inserted in two places
    $search .= "{$searchand}(($wpdb->posts.post_title LIKE '{$n}{$term}{$n}'$collatesql) OR ($wpdb->posts.post_content LIKE '{$n}{$term}{$n}'$collatesql))";
    $searchand = ' AND ';
  }

  // [...]
} 

There is probably a more general way to fix this, e.g. by including a "COLLATE" statement (using DB_COLLATE) along with "SET NAME" $this->query("SET NAMES '$this->charset'") in "wp-db.php", but the above solution should also work.

bertilow, can you attach a patch?

Added a patch implementing my solution

Relates to #3517 and [4860].

At present the DB_COLLATE define is only used when creating the tables at install time.

(In [7579]) Specify collation as well when connecting to the database if it is provided. See #6410 for trunk.

That does it for trunk.

Ryan can you give this the once over before we possibly merge into 2.5.1

when SAVEQUERIES is turned on, the first query always is:

[0]=>
  array(3) {
    [0]=>
    string(16) "SET NAMES 'utf8'"
    [1]=>
    float(0.00020217895507812)
    [2]=>
    string(12) "require_once"

doesn't this mean db_collate is actually used?

D.

doesn't this mean db_collate is actually used?

"SET NAMES 'utf8'" sets the charset to utf8
"SET NAMES 'utf8' COLLATE 'abcde'" sets the charset to utf8 AND the collate to abcde(I'm not sure of a valid collate, so i'm just using that as an example)

Replying to DD32:

doesn't this mean db_collate is actually used?

"SET NAMES 'utf8'" sets the charset to utf8
"SET NAMES 'utf8' COLLATE 'abcde'" sets the charset to utf8 AND the collate to abcde(I'm not sure of a valid collate, so i'm just using that as an example)

This is correct. Collation is rarely needed unless the db charset has many possible collations.