Cannot perform non-ascii selects when COLLATE is being used

[willstedt]

When working with non-ascii characters, one often need to be able to sort the search results the way the local user is expecting it, and then COLLATE is being used on the query. In this bug, that was implemented in the 4.1.2 security release, COLLATE doesn't work anymore on SELECT queries. I haven't digged into the reason for this, but suspect it is the preg_match in the get_table_from_query function (the same as #32090) that can't handle queries that includes COLLATE.

Example of results sorting using COLLATE utf8_swedish_ci:
ABCDEFGHIJKLMNOPQRSTUVWXYZÅÄÖ (this is the correct way to represent the swedish alphabet)

Example of results sorting without COLLATE utf8_swedish_ci:
AÅÄBCDEFGHIJKLMNOÖPQRSTUVWXYZ (this is wrong)

This fails:

$customers = $wpdb->get_results("SELECT 
			client.firstname,
			client.lastname,
			client.email,
			client.image,
			client.zipcode
			FROM 
			{$current_client}.{$wpdb->base_prefix}clients client,
			WHERE 
			(client.firstname LIKE '%$searchterm%' COLLATE utf8_swedish_ci OR client.lastname LIKE '%$searchterm%' COLLATE utf8_swedish_ci OR client.email LIKE '%$searchterm%' COLLATE utf8_swedish_ci) ORDER BY firstname DESC $limit");

This works:

	$customers = $wpdb->get_results("SELECT 
			client.firstname,
			client.lastname,
			client.email,
			client.image,
			client.zipcode
			FROM 
			{$current_client}.{$wpdb->base_prefix}clients client,
			WHERE 
			(client.firstname LIKE '%$searchterm%' OR client.lastname LIKE '%$searchterm%' OR client.email LIKE '%$searchterm%') ORDER BY firstname DESC $limit");

[willstedt]

Just noticed that the reason for using COLLATE in the query was not for the sorting of the results, but to exclude false results. Searching for "aaaa" would return a hit when the text contains "åååå" even if the characters are different and should be treated differently. This doesn't change anything though, as the bug is still there, but maybe clarifies the use case a little bit.

[pento]

Thanks for the bug report! This appears to be the same issue as #32090 - wpdb::get_table_from_query() doesn't find the table name if you're using a schema.table format, instead of just table.

[willstedt]

Replying to pento:

Thanks for the bug report! This appears to be the same issue as #32090 - wpdb::get_table_from_query() doesn't find the table name if you're using a schema.table format, instead of just table.

You are of course right, it is most probably the same issue. I guess it is one of the other pregs in the get_table_from_query function - I only fixed the first one in the quick fix I showed in #32090.