Search module fails and produce SQL error:
PDOException: SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 1 column(s): SELECT i.type AS type, i.sid AS sid, SUM(CAST(:multiply_0 AS DECIMAL) * COALESCE(( (8,1918095044299 * i.score * t.count)), 0) / CAST(:total_0 AS DECIMAL)) AS calculated_score FROM {search_index} i INNER JOIN {node} n ON n.nid = i.sid INNER JOIN {search_total} t ON i.word = t.word INNER JOIN {search_dataset} d ON i.sid = d.sid AND i.type = d.type WHERE (n.status = :db_condition_placeholder_0) AND( (i.word = :db_condition_placeholder_1) )AND (i.type = :db_condition_placeholder_2) AND( (d.data LIKE :db_condition_placeholder_3 ESCAPE '\\') ) GROUP BY i.type, i.sid HAVING (COUNT(*) >= :matches) ORDER BY calculated_score DESC LIMIT 10 OFFSET 0; Array ( [:db_condition_placeholder_0] => 1 [:db_condition_placeholder_1] => 2011 [:db_condition_placeholder_2] => node [:db_condition_placeholder_3] => % 2011 % [:matches] => 1 [:multiply_0] => 5 [:total_0] => 5 ) в функции PagerDefault->execute() (строка 80 в файле /srv/www/hsib.msu.ru/WWW/includes/pager.inc).

This error produced in search.extender.inc, execute routine, line ~436:
$this->scores = str_replace('i.relevance', '(' . (1.0 / $this->normalize) . ' * i.score * t.count)', $this->scores);

On system locales, where decimal separator is comma, not dot, this expression gives string with float number with comma separator ....COALESCE(( (8,1918095044299 * i.score * t.count))...., which processed by MySQL with error (Cardinality violation: 1241).

So the problem is in division operation (1.0 / $this->normalize), it's necessary to replace comma by dot on locales. Something like:

$this->scores = str_replace('i.relevance', '(' . number_format(1.0 / $this->normalize, 8, '.', '') . ' * i.score * t.count)', $this->scores);

Comments

jhodgdon’s picture

jhodgdon
she/her
Primary language English
commented
Title: search module throw SQL error on SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 1 column(s) » search module running into comma as decimal place problem
Status: Active » Postponed (maintainer needs more info)

Hmmm...

Looking at search.extender.inc:

 public function addScore($score, $arguments = array(), $multiply = FALSE) {
...
      $score = "CAST(:multiply_$i AS DECIMAL) * COALESCE(( " . $score . "), 0) / CAST(:total_$i AS DECIMAL)";

So the problem is that it's assuming that a printed $score would work as a MySQL expresssion.

In this case, it appears that the caller of addScore is passing in

$score = "(8,1918095044299 * i.score * t.count)";

The question, though, is where does this ranking come from? I am not seeing it in Drupal core's node search rankings (which come from hook_ranking()). Is it possible you have a contributed module that is adding to the search rankings? The only scores added by core modules are in node_ranking(), comment_ranking(), and statistics_ranking(), and none of them appears to use i.score or t.count.

So I think this is not a Drupal Core problem, but a problem with another module you have installed.

damien tournoud’s picture

damien tournoud commented
Version: 7.0 » 8.x-dev
Status: Postponed (maintainer needs more info) » Active

Well, we need to fix that, who though it was a good idea to use string concatenation like that? We don't have placeholders for nothing.

jhodgdon’s picture

jhodgdon
she/her
Primary language English
commented
Status: Active » Postponed (maintainer needs more info)

Damien: I don't think the problem is in Drupal Core. This function is assuming it's getting in a well-formed query as its argument, which may be stupid, but that is what the function does... and some contrib module is sending in a non-well-formed $score.

lmeurs’s picture

lmeurs commented
Version: 8.x-dev » 7.12
Status: Postponed (maintainer needs more info) » Active

I think it might be a problem in core, using Drupal v7.12. It is useful to set locale manually for several projects, the LC_ALL constant sets locale in all categories, but uses commas as decimal separators in Dutch. This unfortunately makes MySQL trip when used in a query...

if (preg_match('/win/i' , PHP_OS)) setlocale(LC_ALL, 'nld_nld');
else setlocale(LC_ALL, 'nl_NL');
var_dump('concat ' . 0.123); // prints "concat 0,123"

kb@'s solution to use number_format() before concatenation looks good to me.

Now I set all categories but LC_NUMERIC in the following way:

$locale = preg_match('/win/i' , PHP_OS) ? 'nld_nld' : 'nl_NL';
foreach (array(LC_COLLATE , LC_CTYPE , LC_MONETARY , LC_TIME) as $category) setlocale($category , $locale);
jhodgdon’s picture

jhodgdon
she/her
Primary language English
commented
Status: Active » Closed (duplicate)

This is a duplicate of #2016497: Search query extender should not use floats directly, which is newer but has a solution posted (not yet a patch but at least a suggestion).