Unknown column 'n.language' in 'where clause' query

I tested this bug on beta3. The result is still the same. But I found a fix.

I'm trying to understand which module inserts the "AND (n.language ='en' OR n.language ='' OR n.language IS NULL)" fragment in the query. My best guess is that the default behavior of do_search is to look at nodes which are listed in the node table, often referenced as n, and which indeed contains a language column.

A first check I did in the code was the value for i.type in the first do_search() query, which resulted from trying out the following SQL queries:

mysql> SELECT DISTINCT type FROM search_index ORDER BY type ASC;
+------------------+
| type             |
+------------------+
| field_1          |
| field_10         |
| field_11         |
| field_8          |
| field_9          |
| help             |
| node             |
| search_files_att |
+------------------+
8 rows in set (0.52 sec)

mysql> select i.score, t.count,i.type FROM search_index i INNER JOIN search_total t ON i.word = t.word INNER JOIN files AS f ON f.`fid` = i.`sid` WHERE f.status = 1 AND (i.word = 'wagen') HAVING COUNT(*) >= 1 ORDER BY score DESC LIMIT 0, 1;
+----------+---------+------------------+
| score    | count   | type             |
+----------+---------+------------------+
| 0.535157 | 0.04296 | search_files_att |
+----------+---------+------------------+
1 row in set (0.00 sec)

Although there are two do_search() queries in search_files_attachments.module the first apparently does nothing since there's no 'file' type in my search index. Maybe this is from a previous version of this module? Or is it from another module?

Rewriting the score as i.score * t.count and only looking at 'search_files_att' type, this query yields:

mysql> SELECT SUM(i.score * t.count) AS score FROM search_index i INNER JOIN search_total t ON i.word = t.word INNER JOIN files AS f ON f.`fid` = i.`sid` WHERE f.status = 1 AND (i.word = 'wagen') AND i.type = 'search_files_att' GROUP BY i.type, i.sid HAVING COUNT(*) >= 1 ORDER BY score DESC LIMIT 0, 1;           +-------------------+
| score             |
+-------------------+
| 0.121776272785118 |
+-------------------+
1 row in set (0.00 sec)

Time to revisit the 'n.language' error. To get to the language given a file, the only way I know is by going through the upload table using 'fid' as join key, and then to the node table via 'nid'. When I add the 2 missing LEFT JOIN statements (to get rid of the missing 'n.language') I get the same query result as with the previous query:

mysql> SELECT SUM(i.score * t.count) AS score FROM search_index i INNER JOIN search_total t ON i.word = t.word INNER JOIN files AS f ON f.`fid` = i.`sid` LEFT JOIN upload u USING (fid) LEFT JOIN node n USING (nid) WHERE f.status = 1 AND (n.language ='en' OR n.language ='' OR n.language IS NULL) AND (i.word = 'wagen') AND i.type = 'search_files_att' GROUP BY i.type, i.sid HAVING COUNT(*) >= 1 ORDER BY score DESC LIMIT 0, 1;
+-------------------+
| score             |
+-------------------+
| 0.121776272785118 |
+-------------------+
1 row in set (0.01 sec)

I now understand how both do_search queries work. The first one will look for language-tainted 'file' entries. That query bails out as soon as you configure your Drupal to serve content in more than one language, yielding the aforementioned error message. The fix is simple: add the missing links to get to the language. The second query is not hindered by a language check hence it runs smoothly.

I managed to get rid of the error reported in my bug report by editing the first do_search() statement in search_files_attachments.module by adding 2 LEFT JOIN statements as follows:

$find = do_search($keys, 'file', 'INNER JOIN {files} AS f ON f.`fid` = i.`sid` LEFT JOIN upload u USING (`fid`) LEFT JOIN node n USING (`nid`)'. $join1, $conditions1 . (empty($where1) ? '' : ' AND '. $where1), $arguments1, $select2, $join2, $arguments2);

Unfortunately I have no cvs or svn installed on my machine hence I can't easily create a patch. The fix is really only one line of code.

Hope this helps!

Olivier