After the latest Drupal 7 update, while we were verifying that everything still works on our website, I came across a problem searching for a test forum entry / reply comment by typing in "ha ha" into the search field. The error I got from the API was the following:
SQLSTATE[42S02]: Base table or view not found: 1146 Table '<database>.t' doesn't exist
I get this error every time I search for a string with multiple two-letter-words divided by a whitespace or a special character (' ', '%', ','). It does not seem to matter how many two character words I use, so it happens for "ha ha" but also for "ha pu do", "ha pu do mi", etc.
As soon as either of those space separated words contains more than two letters it works fine again ("haa ha", "ha haa", "haa pu do", "ha puu do", ...), as is the case for a single two-letter word (just "ha"). It also does not seem to matter whether the query uses a node or user index, or if the operator is = or LIKE, I get the same error no matter what. So it can be as simple as:
Index: <name>_user_index
Keys: NULL
Filters:
name = 'ha pu do mi'
Options: array (
'conjunction' => 'AND',
'parse mode' => 'terms',
'filter class' => 'SearchApiQueryFilter',
'search id' => 'SearchApiQuery',
)
or as complex as:
Index: <name>_node_index
Keys: NULL
Filters:
type = 'forums'
AND
[
title LIKE 'ha pu do mi'
OR
body:value LIKE 'ha pu do mi'
OR
author:name LIKE 'ha pu do mi'
OR
taxonomy_forums:name LIKE 'ha pu do mi'
OR
taxonomy_forums:description LIKE 'ha pu do mi'
OR
taxonomy_forums:parents_all:name LIKE 'ha pu do mi'
OR
taxonomy_forums:parents_all:description LIKE 'ha pu do mi'
OR
taxonomy_forums:parents_all:parents_all:name LIKE 'ha pu do mi'
OR
taxonomy_forums:parents_all:parents_all:description LIKE 'ha pu do mi'
]
Options: array (
'conjunction' => 'AND',
'parse mode' => 'terms',
'filter class' => 'SearchApiQueryFilter',
'search id' => 'SearchApiQuery',
)
It always seems to lose the table name for any subqueries it attempts to do:
SELECT COUNT(*) AS expression
FROM
(SELECT DISTINCT t.item_id AS item_id, :score AS score, 1 AS expression
FROM
{search_api_db_<name>_user_index} t
WHERE ( (t.item_id IN (SELECT t.item_id AS item_id
FROM
{} t)) )) subquery
SELECT COUNT(*) AS expression
FROM
(SELECT DISTINCT t.item_id AS item_id, :score AS score, 1 AS expression
FROM
{search_api_db_<name>_node_index} t
WHERE ( (t.type = :db_condition_placeholder_0) AND( (t.item_id IN (SELECT t.item_id AS item_id
FROM
{} t)) OR (t.item_id IN (SELECT t.item_id AS item_id
FROM
{} t)) OR (t.item_id IN (SELECT t.item_id AS item_id
FROM
{} t)) OR (t.item_id IN (SELECT t.item_id AS item_id
FROM
{} t)) OR (t.item_id IN (SELECT t.item_id AS item_id
FROM
{} t)) OR (t.item_id IN (SELECT t.item_id AS item_id
FROM
{} t)) OR (t.item_id IN (SELECT t.item_id AS item_id
FROM
{} t)) OR (t.item_id IN (SELECT t.item_id AS item_id
FROM
{} t)) OR (t.item_id IN (SELECT t.item_id AS item_id
FROM
{} t)) ))) subquery
though with all of the sub-queries receiving the alias `t` I get the feeling it messes up in that regard already, not just in dropping the table names (but as I never attempted something like this myself, I cannot be sure).
I also tried to add the "%" wildcard automatically in between words and at the beginning and end of the search string when the operator is LIKE, in case that might help in any way, however that did not result in any change of the output (this actually only worked for any fields that had :parents_all in it, for all other fields the % character was changed into a single space).
I already re-index'd everything, ran the cron (to check for further updates), Drupal 7 and Search API are current and unchanged, though this issue is already present on the live version of our website, which is still using Search API v 7.x-1.13, so it seems the issue has been around for a while already.
Comment | File | Size | Author |
---|---|---|---|
#15 | 2511860-15--filter_on_multiple_short_words.patch | 1.81 KB | drunken monkey |
Comments
Comment #1
drunken monkeyThis patch (the one without the "tests_only" suffix) should fix it, please test!
Comment #4
drunken monkeyRe-posting.
Comment #6
drunken monkeyLooks good. Would have been great if someone could have verified this works, but since the test bot is happy, let's just hope for the best.
Committed.
Moving to Search API 8.x for porting (since I guess the same bug is in the D8 version).
Comment #7
drunken monkeyThis should do it.
Comment #12
drunken monkeySecond try, this time actually tested. Unfortunately now overlaps with the port of #1299238: Add option for partial matching.
Comment #15
drunken monkeyRe-roll.
Comment #19
drunken monkeySeems to work fine now, so: committed.