%f is locale dependent in db_query

MySQL expects floats to be formatted with a period as the decimal separator. But if you have called setlocale() then db_query("%f") may produce something else.

I've seen this problem in the queue already couple of times... I wonder if we can just do something like attached patch (UNTESTED!!!) to be on the safe side always. I think that on a float, we won't encounter any 'good' commas to be left unchanged, and the length of these strings is tiny, so no problem with performance...

Opinions?

Again: This patch is just theoretical, I didn't test it!!! (rolled for 6.x-dev, BTW)

Replacing all commas with dots is a bad idea, since other number formats have commas which do no separate decimals.

Well, if that's the case, then I have no clue how to fix this :(

For D7 we could maybe use sprintf('%F') but that is only available since PHP 4.3.10 / 5.0.3.

The only other option I could find is number_format but that seems to require an exact number of decimal places to be specified.

Since I had the same problem, fast "workaround" is to do some setlocale changes in function do_search() in search.module.

--- search.module 2007-12-16 22:32:41.000000000 +0100
+++ search.module 2007-07-26 21:16:48.000000000 +0200
@@ -838,8 +838,6 @@
function do_search($keywords, $type, $join1 = '', $where1 = '1', $arguments1 = array(), $select2 = 'i.relevance AS score', $join2 = '', $arguments2 = array(), $sort_parameters = 'ORDER BY score DESC') {
$query = search_parse_query($keywords);

- $default_locale=setlocale(LC_ALL, 'C');
-
if ($query[2] == '') {
form_set_error('keys', t('You must include at least one positive keyword with @count characters or more.', array('@count' => variable_get('minimum_word_size', 3))));
}
@@ -864,9 +862,6 @@
$conditions = '('. $query[0] .')';
$arguments = array_merge($arguments2, $query[1]);
$result = db_query_temporary("SELECT i.type, i.sid, $select2 FROM temp_search_sids i INNER JOIN {search_dataset} d ON i.sid = d.sid AND i.type = d.type $join2 WHERE $conditions $sort_parameters", $arguments, 'temp_search_results');
-
- setlocale(LC_ALL, $default_locale);
-
if (($count = db_result(db_query('SELECT COUNT(*) FROM temp_search_results'))) == 0) {
return array();
}

Tested on Drupal 5.5

The above solution worked -> http://drupal.org/comment/reply/193214/661371#comment-661371

I was having errors in search, when i do the search in Portuguese. In English was having no problem.

The setlocale changes in function do_search() is working fine.

My Drupal version is 5.5.

The patch described by AdrianS (see above) is very helpful to me, too.

There are similar situations with SQL errors that occur if I try to run the standard Drupal cron job manually (as opposed to having it run automatically!). I am using Drupal 5.6 with a German locale and have the "Search attachments" module, version 5.x-3, also installed. The Internationalization module is also installed (however, this might be of no importance here).

I found two additional sections in search.module that caused these problems and was able to fix that by applying the same type of patch to these sections. These are:

• function search_update_totals: I added the first "setlocale" line right at the beginning of the function body, and the second one after the closing brace of the foreach loop. The deletion loop at the end of the function doesn't need to be enclosed by the setlocale calls, because no floating point values are being used here.
• function search_index: Here, the first "setlocale" line has to be inserted at the latest directly before the loop after the comment "// Insert results into search index", and the second "setlocale" line has to be inserted right before the end of the function definition.

However, I don't know how the cron functionality works and I cannot tell if any problem could arise from a cron job being killed by an execution time overflow just at that moment when the locale is changed. Thus, I propose to check my idea and I hope that a good solution will be found.

Thanks!

Does this issue is solved in drupal 6?

I have the same problem when searching in Spanish. It seems that there's a solution at #6, but I can't understand what to do. Should I replace some text? Which one? should I add the code? Isn't there any file to replace it? Any official solution to correct this issue?

Thank you

There is no bug in Drupal Core on that matter.

unicode.inc calls setlocale(LC_CTYPE, 'C'); during early bootstrap of the Drupal code base in order to guarantee that the locale settings are sane. Changing the language of a Drupal site does not change this system-specific and low-level parameter.

It is possible, thou, that other modules in your installation incorrectly call setlocale(). These are bugs of those modules, not bugs of Drupal itself.

So marking this as "won't fix".